sqlite
SQLite is a lightweight disk/memory based database that does not require a server or third party applications. It allows users to work with the database using its sigtly variant version of the Structured Query Lanaguage. For this reason, it is a perfect fit for embeded and highly portable database applications. It can be used as a prototyping database from which an application can be migrated to a more robust database system such as Oracle.
This module provides an interface to working with SQLite databases and is compartible with SQLite3.
The open()
function is the entry point to this module and must be called to create a valid SQLite3 connection to a valid database. The following example shows how to create a connection to a database.
import sqlite
var con = sqlite.open('test.db')
The above code opens a connection to the database file test.db
in the current directory. Any valid file path is acceptable here. You can also open a kind of connection a database that is stored completely in virtual memory, allowing you to use SQLite like an in-memory database (albeit relational). The following example shows how to open a that make SQLite behave like an in-memory database.
import sqlite
var con = sqlite.open()
Once a connection has been established, you can use the connection to run all sorts of queries. For example, you can run queries that do not return a result set (for example, a CREATE TABLE
query) using the exec()
function as shown in the example below.
# Create a new table
con.exec('CREATE TABLE users (id integer primary key, name text, gender text)')
# Insert data into the table
# This isn't exactly the most optimal way to do it but you get the idea. Right?
con.exec('INSERT INTO users (id, name, gender) VALUES (0, "James", "Male")')
con.exec('INSERT INTO users (id, name, gender) VALUES (1, "Lilith", "Female")')
con.exec('INSERT INTO users (id, name, gender) VALUES (2, "Candy", "Non-Binary")')
This function will return true
if the query was successful or false
if it failed.
You can retrieve the ID of the last insert query in the above command for example using the last_insert_id()
function. For example,
con.last_insert_id()
# 2
On the other hand, there are two ways to run queries that return a dataset.
Using the query()
method.
This function returns a SQLite3Cursor
that allows you iterate through the dataset and do as you wish with them. For example,
var result = con.query('SELECT * FROM users')
There are two ways to loop through this result set. The first way is to use the has_next()
function. This function automatically moves the cursor to the next datarow in the result set and return true
or false
when there are no more rows in the result set.
while result.has_next() {
var name = result.get(1)
var gender = result.get(2)
echo 'Name = ${name}, Gender = ${gender}'
}
# ---- result ----------
Name = James, Gender = Male
Name = Lilith, Gender = Female
Name = Candy, Gender = Non-Binary
Once has_next()
returns true, you’ll be able to get the value of the different columns in the result run using the get()
method of the SQLite3Cursor by passing their ordinal position as an argument to the function.
Another way to get the result entries in a SQLite3Cursor is obviously using the for
loop as the class implements the iterable decorators (as indicated in the class documentation below). For example,
for row in result {
echo 'Name = ${row.name}, Gender = ${row.gender}'
}
# ---- result ----------
Name = James, Gender = Male
Name = Lilith, Gender = Female
Name = Candy, Gender = Non-Binary
Much shorter right? Care should be taken though as a few of our tests have shown that for result sets with a large number of columns, the first option (using
while
) might be slightly faster for performace critical applications. However, no realworld dataset has been testd.
Using the fetch()
method.
The second way to run queries that return a result set is to use the fetch()
function. Unlike the query()
function that allows you to lazily access the resultset of a SQL query, the fetch()
function retrieves all results into a dictionary as a flat object. This function is useful for returning all the data in the resultset.
For example,
con.fetch('SELECT * FROM users')
# ---- result ---------
[
{
id: 0,
name: James,
gender: Male
},
{
id: 1,
name: Lilith,
gender: Female
},
{
id: 2,
name: Candy,
gender: Non-Binary
}
]
Parameterized Queries
This module provides support for parameterized queries and as such offer protection against SQL injection. An example of a parameterized query is show below.
%> con.fetch('SELECT * FROM users WHERE name = ?', [ 'James' ])
# ---- result ---------
[
{
id: 0,
name: James,
gender: Male
}
]
You can also used a dictionary as an argument instead of a list for named parameterized queries. When you do this, the order or count of the parameters will not matter. Instead, parameters will be matched based on their value in the dictionary. For example,
con.fetch(
'select * from users where name = :name and id = :id',
{
':id': 0,
':name': 'James',
}
)
# ---- result ---------
[
{
id: 0,
name: James,
gender: Male
}
]
It is also a very good practice to always close your connection once done with it. This is really simple.
con.close()
See below for more info
Functions
- sqlite.open([path: string])
- Returns an handle to a sqlite3 database. If path is not given, it will create an in-memory sqlite database. return SQLite3
Classes
class SQLite3
SQLite3 management class
class SQLite3 properties
- SQLite3.path
- The path to the SQLite3 file default = :memory:
class SQLite3 methods
- SQLite3(path: string)
-
- the database doesn’t need to exist.
constructor - open()
- Opens the handle to a database file
- close()
- Closes the handle to the database and return
true
if successfully closed orfalse
otherwise.return boolean - exec(query: string [, params: list | dict])
- Executes a query string as is and returns
true
if the query was executed orfalse
otherwise.- this method does not return a query result
- this method takes optional params like
query()
(see below).
return booleanthrows SQLiteException if an error occured - last_insert_id()
- The id of the last insert operation.
Returns:
-1
if the last insert failed,0
if no insert statement has been executed or- A number greater than 0 if it succeeded
returns numberthrows SQLiteException if database is not opened - query(sql: string [, params: list | dict])
- Executes and sql query and returns the result of the execution. return SQLite3Cursorthrows SQLiteException if an error occured.
- Pass a list as params if you have unnamed parameterized queries.
For example,
sqlite.query('SELECT * FROM users WHERE id = ? AND name = ?', [3, 'James'])
- Or pass a dictionary as params if you use named paramters
For Example,
sqlite.query( 'SELECT * FROM user WHERE id = :id AND name = :name', {':id': 1, ':name': 'James'} )
- fetch(sql: string [, params: list | dict])
- Runs an SQL query and returns the result as a list of dictionaries.
- if the result is empty or the query is not a SELECT, it returns an empty list
class SQLiteException < Exception
General Exception for SQLite
class SQLiteException methods
- SQLiteException(message: string)
- constructor
class SQLite3Cursor
A cursor for navigation through sql results @iterable
class SQLite3Cursor properties
- SQLite3Cursor.connection
- The SQLite3 connection that owns this cursor readonly
- SQLite3Cursor.row_count
- The number of rows in the cursor readonly
- SQLite3Cursor.modified_count
- This value hold the number of rows modified, inserted or deleted by the the query that owns this cursor provided the query is one of INSERT, UPDATE or DELETE statement. Executing any other type of SQL statement does not change this value from 0.
Only changes made directly by the INSERT, UPDATE or DELETE statement are considered
- auxiliary changes caused by triggers, foreign key actions or REPLACE constraint resolution are not counted.
Changes to a view that are intercepted by INSTEAD OF triggers are not counted. The value returned by
modified_count
immediately after an INSERT, UPDATE or DELETE statement run on a view is always zero. Only changes made to real tables are counted.readonlyIf a separate thread makes changes on the same database connection at the exact time the original query was also making a change, the result of this value will become undependable.
- SQLite3Cursor.columns
- A list of the columns available in the result set. readonly
class SQLite3Cursor methods
- SQLite3Cursor(db: SQLite3, cursor: pointer)
- constructor
- SQLite3Cursor should NEVER be maually instantiated.
- close()
- Closes the cursor and prevents further reading return bool
- has_next()
- Returns
true
if there are more rows in the result set not yet retrieved, otherwise it returnsfalse
.return boolean - get(index: number | string)
- Returns the value of the column matching the index in the current result set.
- if index is a number, it returns the value in the column at the given index.
- that index must be lower than columns.length() in this case.
- if index is a string, it returns the value in the column with the given name.
throws SQLiteException if no matching column can be found.