SQLite Python API tutorial

SQLite is a file-based relational database management system that allows applications to have an embedded SQL database without the need for a connection to an external database server. Python has support for SQLite via the sqlite3 module (pysqlite) which provides an easy to use and powerful API.

This tutorial presents how to work with SQLite using Python and the sqlite3 module.

Database creation

In SQLite, the database is represented by a file on disk. It is used to hold the database objects (tables, rows, …).

The sqlite3 module provides the function connect() to open a SQLite database file (or create a new one if it does not exist). It is passed the database file name and returns an object of type Connection that is used later for all interactions with the database.

When the database is no longer needed, the connection object provides the close() method to release resources and close the file.

Example

The following example demonstrates how to create a SQLite database file ‘my_users.db‘:

import sqlite3

con = None
dbname = './my_users.db'

try:
    con = sqlite3.connect(dbname)
    print('The database is successfully open')
except sqlite3.Error as e:
    print(e)
    exit(1)

con.close()

SQLite has support for in-memory databases. With this feature, the database is only created in memory (RAM) and destroyed when the process is terminated or the close() method is called. To create an in-memory database, the connect() function is passed the special string ‘:memory:‘ argument.

Example:

con = sqlite3.connect(':memory:')

Execution of SQL statements

The connection object provides the cursor() method that returns an object of type Cursor. This object has a set of useful methods and attributes that are used to execute SQL statements and fetch results.

The most used methods and attributes are summarized in the following table and later sections provide examples of their usage:

execute(sql[, parameters])A method to execute one optionally parameterized SQL statement
executescript(sql_script)A method to execute multiple SQL statements (separated by ‘;’) at once.
fetchone()A method to fetch the next row from a SQL query. The value None is returned when no more data is available
fetchall()A method to fetch a list of rows from a SQL query. An empty list is returned when the query returns no rows.
close()A method to close the cursor and release resources
lastrowidAn attribute that provides the rowid of the last modified row. It is very useful when working with auto-increment columns

Data types

The following table summarizes the natively supported types by SQLite and their default corresponding Python types:

Python typeSQLite type
NoneNULL
intINTEGER
floatREAL
strTEXT
bytesBLOB

SQL injection

Most of the time, SQL queries are built using Python variables. Using the usual Python concatenation operations is insecure as it may lead to SQL injection vulnerabilities. The recommended way is to use the API’s parameter substitution features (place holders).

The following example presents an insecure way to perform a SQL query:

id = 123
cursor.execute('SELECT * FROM USERS WHERE ID = %s' % id )

Instead, the previous example should be writing in the following secure way:

id = 123
cursor.execute('SELECT * FROM USERS WHERE ID = ?', id)

Tables creation

In SQLite, tables are created using the standard SQL CREATE TABLE statement.

To create a table, a connection object is created from the database file then a cursor object is created to execute the SQL statement using the execute() method. Multiple statements may be executed at once with the executescript() method instead.

The following example presents how to create the USERS table that will also be used in the rest of the tutorial:

import sqlite3

con = None
dbname = './my_users.db'

try:
    con = sqlite3.connect(dbname)
    print('The database is successfully open ')
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor = con.cursor()
try:
    cursor.execute(
        """
        CREATE TABLE USERS (
        ID     INTEGER PRIMARY KEY AUTOINCREMENT,
        NAME   TEXT NOT NULL,
        PHOTO  BLOB NOT NULL
        );
        """
    )
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor.close()
con.close()

Data insertion

In SQLite, rows insertion is done using the standard SQL statement INSERT INTO.

To insert a row into a table, a cursor object is created to execute the SQL statement using the execute() method. Multiple statements may be executed at once with the executescript() method.

The execute() method accepts parameterized SQL statements and is capable of managing binary data (passed as an argument of type buffer).

Data modification SQL statements are run as a transaction and a commit is necessary using the commit() method. The commit() method is provided by the connection object.

The following example presents how to insert a row into the USERS table. Only the name and photo are inserted as the ID is automatically generated (AUTOINCREMENT option). The photo is a binary image file that is read from disk.

The cursor object provides the attribute lastrowid that is used to fetch the last auto-generated ID after each SQL INSERT statement.

import sqlite3

con = None
dbname = './my_users.db'

try:
    con = sqlite3.connect(dbname)
    print('The database is successfully open ')
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor = con.cursor()
newuser = 'John'
newuser_photo = None
try:
    newuser_photo = buffer(open('./John.jpg', 'rb').read())
except IOError as e:
    print(e)
    exit(1)

try:
    cursor.execute('INSERT INTO USERS (NAME, PHOTO) VALUES (?, ?)', (newuser, newuser_photo))
    print('New generated user ID is: ', cursor.lastrowid)
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor.close()
con.commit()
con.close()

SQL queries

In SQLite, the standard SQL SELECT statement is used to execute queries. To fetch a query result, the cursor object provides different methods for different scenarios.

The cursor method fetchone() returns the next row from the result set on each call. It returns the value None when no more rows are available.

The cursor method fetchall() returns the list of all rows from the result set. An empty list is returned when the query returns no result.

The following example presents how to fetch the user with ID 1 from the USERS table. The user ID and name are shown on the screen but the user’s photo (binary image) is saved to a file on disk.

import sqlite3

con = None
dbname = './my_users.db'

try:
    con = sqlite3.connect(dbname)
    print('The database is successfully open')
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor = con.cursor()
userid = 1
row = None
try:
    cursor.execute('SELECT ID, NAME, PHOTO FROM USERS WHERE ID = ?', (userid,))
    row = cursor.fetchone()
    if row == None:
        raise sqlite3.Error('No user found with ID ' + str(userid))
except sqlite3.Error as e:
    print(e)
    exit(1)

username = str(row[1])
photofile = './'+username+'_photo.jpg'
print('User name: ', username)

try:
    open(photofile, 'wb').write(row[2])
except IOError as e:
    print(e)
    exit(1)

print('Photo saved to file: ', photofile)

cursor.close()
con.close()

Data updates

In SQLite, a data update is done using the standard SQL UPDATE statement. A single update statement may be executed using the cursor method execute(). Multiple update statements may be executed in one call using the cursor method executescript().

Data modification SQL statements are run as a transaction and a commit is necessary using the commit() method. The commit() method is provided by the connection object.

The following example presents how to change the name of the user with ID 1:

import sqlite3

con = None
dbname = './my_users.db'

try:
    con = sqlite3.connect(dbname)
    print('The database is successfully open')
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor = con.cursor()
userid = 1
row = None
try:
    cursor.execute('UPDATE USERS SET NAME = "Linus" WHERE ID = ?', (userid,))
    row = cursor.fetchone()
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor.close()
con.commit()
con.close()

Data deletion

In SQLite, data deletion is done using the standard SQL DELETE statement. A single delete statement may be executed using the cursor method execute(). Multiple delete statements may be executed in one call using the cursor method executescript().

Data modification SQL statements are run as a transaction and a commit is necessary using the commit() method. The commit() method is provided by the connection object.

The following example presents how to delete the user with ID 1:

import sqlite3

con = None
dbname = './my_users.db'

try:
    con = sqlite3.connect(dbname)
    print('The database is successfully open')
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor = con.cursor()
userid = 1
row = None
try:
    cursor.execute('DELETE FROM USERS WHERE ID = ?', (userid,))
    row = cursor.fetchone()
except sqlite3.Error as e:
    print(e)
    exit(1)

cursor.close()
con.commit()
con.close()

References

sqlite3 module reference: https://docs.python.org/3/library/sqlite3.html