Introduction
Python has a built-in Sqlite3 module named sqlite3.
This module allows you to create, connect, and modify SQLite 3 databases.
To learn more about SQLite3 and how to use it in general, check out my SQLite3 Tutorial and my other sqlite tutorials.
If you need a database-agnostic library, something that you can use with SQLite and then easily configure to start using MySQL without rewriting all of your code, you want to use an ORM. ORM stands for Object-Relational-Mapping and the most popular one for Python is SQLAlchemy and Django's ORM.
Install
The sqlite3 package comes with the Python standard library so no extra installation is needed.
Get documentation
You can read the API documentation locally by running pydoc and then using your web browser to view the sqlite3 package details.
# Run then open browser http://localhost:9999 and find sqlite
pydoc -p 9999
You can also read the online tutorial in the official Python documentation at: https://docs.python.org/3/library/sqlite3.html
Open or create a database
SQLite3 databases are single stand-alone files that commonly end with a .db extension.
If a database file does not exist when you try to connect, it will create the file.
from sqlite3 import connect
db = connect('test.db')
db.close()
Memory-only databases
Creating an in-memory only database will not persist between runs, but can be useful if you only need to store
the information during the run. To do it, you simply replace the filename with :memory:
when opening a database.
from sqlite3 import connect
# For an in-memory only database:
db = connect(':memory:')
db.close()
Backup a database
If you decide you do want to store the database from memory to a file, you can use the backup() function. The backup() function essentially copies one database to another. You can use it to take a memory database and store it in a file, but you can also do the reverse, that is, "backup" a database from a file in to a :memory: database, creating a copy in memory without directly loading/modifying the copy on disk.
This first example shows how to backup a memory database to a file.
from sqlite3 import connect
# Backup a memory database to a file
memory_db = connect(':memory:')
backup_db = connect('my_backup.db')
memory_db.backup(backup_db)
memory_db.close()
backup_db.close()
This example shows how to take the contents of a database on disk and load it in to memory. This is different from loading the file directly where SQL statements would alter the file. This creates a unique copy of the database in to memory that is separate from the file on disk.
from sqlite3 import connect
# Load the contents of a database file on disk to a
# transient copy in memory without modifying the file
disk_db = connect('my.db')
memory_db = connect(':memory:')
disk_db.backup(memory_db)
disk_db.close()
# Now use `memory_db` without modifying disk db
memory_db.close()
Note that if you are not using the database actively, you can safely just copy the database file and you don't have to use this backup() method.
Executing statements
To execute a SQL statement, use the execute() method on a database cursor.
Depending on the type of statement, you will either get back None or sqlite3.Row objects.
You can use fetchone(), fetchmany(), and fetchall() to get the results.
We will look at examples shortly.
Note that Python's sqlite3 package by default is configured with transactions turned on so you must commit changes to the database for them to persist.
Create table
You can create tables with the CREATE TABLE statement, or you can ensure
a table is created with the CREATE TABLE IF NOT EXISTS statement.
This example shows how to open to the database and create a table if it does not exist.
There are only a few Sqlite3 datatypes.
NULL- Empty valueINTEGER- Basic whole numberREAL- 64-bit floating pointTEXT- StringBLOB- Binary or raw data
from sqlite3 import connect
db_connection = connect('test.db')
db_cursor = db_connection.cursor()
statement = 'CREATE TABLE IF NOT EXISTS users (username TEXT, email TEXT)'
db_cursor.execute(statement) # Returns None on create table
db_cursor.close()
db_connection.close()
Important Note: The CREATE TABLE statement does not require a commit, but keep in mind
that Python sqlite3 is manual transaction by default so insert statements later
will need to be committed.
Parameterized statements
When constructing statements, you may want to insert variables. It can be very dangerous to to the string concatenatino yourself, exposing you to SQL injection attacks.
There are two ways to parameterize your statements to safely insert variables.
One is to use a question mark ? as a placeholder and the other is to use named placeholders in the form of :myplaceholder. Let's look at an example of each.
from sqlite3 import connect
db = connect('test.db')
cursor = db.cursor()
# Simple question mark placeholder
statement = 'UPDATE users SET email=? WHERE username=?'
data = ('nanodano@devdungeon.com', 'nanodano')
cursor.execute(statement, data)
# Named placeholders
statement = statement = 'UPDATE users SET email=:email WHERE username=:username'
data = {
'email': 'nanodano@devdungeon.com',
'username': 'nanodano'
}
cursor.execute(statement, data)
db.commit()
Insert rows
To insert rows, you call execute() like you do with any other SQL statement.
This example shows how to insert rows to a database and how to get the row ID of the last row inserted.
from sqlite3 import connect
db = connect('test.db')
cursor = db.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS users (username TEXT, email TEXT)')
cursor.execute('INSERT INTO users (username, email) VALUES ("admin", "admin@devdungeon.com")')
cursor.execute('INSERT INTO users (username, email) VALUES ("nanodano", "nanodano@devdungeon.com")')
print(cursor.lastrowid) # Id of inserted row
# IMPORTANT!
# Until you commit, the changes will not be saved, only in memory!
db.commit()
To insert multiple rows at once you can use executemany().
from sqlite3 import connect
db = connect('test.db')
cursor = db.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS users (username TEXT, email TEXT)')
row_data = [
('admin', 'admin@devdungeon.com'),
('nanodano', 'nanodano@devdungeon.com')
]
cursor.executemany("INSERT INTO users (username, email) VALUES (?, ?)", row_data)
db.commit()
Query for rows
This example shows how to fetch results from a database. Once you fetch the results you have a couple options for consuming the data. You can iterate through the data with a for loop or you can use fetchone(), fetchmany() or fetchall() to extract the results. This example shows both methods.
By default, the database cursor will return tuples with the row data. The first example demonstrates the tuples which is very efficient.. The next example will show how to use sqlite3.Row as the row factory to give you dictionary-like result objects.
from sqlite3 import connect
# Db setup
db = connect('test.db')
cursor = db.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS users (username TEXT, email TEXT)')
cursor.execute('INSERT INTO users (username, email) VALUES ("nanodano", "nanodano@devdungeon.com")')
db.commit()
# Query for rows and then iterate through results
rows = cursor.execute('SELECT * FROM users LIMIT 100')
print(type(rows)) # sqlite3.Cursor
for row in rows:
print(type(row)) # tuple
print(row)
# Query for rows and then use `fetchall()` to get as a list
rows = cursor.execute('SELECT * FROM users ORDER BY username ASC')
all_rows = rows.fetchall() # Get a list of rows as tuples
print(all_rows)
# Or fetch a specific amount of rows with `fetchmany()`
some_rows = rows.fetchmany(1)
# Or fetch a single row with `fetchone()`
row_id = rows.fetchone()
# Db cleanup
cursor.close()
db.close()
This example shows you how to use a different row factory which will provide you a dictionary-like object that lets you access fields using a case-insensitive keys as well as providing a keys() function to get the list of columns in order. This method is more convenient to use as the programmer, but is less efficient than using the default tuple format from the previous example.
from sqlite3 import connect, Row
db = connect('test.db') # Using the database created in previous example
db.row_factory = Row
cursor = db.cursor()
rows = cursor.execute('SELECT * FROM users')
print(type(rows)) # sqlite3.Cursor
for row in rows:
print(type(row)) # sqlite3.Row
print(row.keys())
print(row)
print(row['uSeRName'])
Error handling
A number of things can go wrong when working with a sqlite3 database. For example, if there is an error in your SQL syntax or you attempt to insert an invalid value.
You can view all sqlite3 exceptions in the official documentation.
Most of the exceptions are subclasses of sqlite3.DatabaseError, but a couple are sublcasses of the basic Exception. These are the exceptions you can expect if there are any errors during a database operation.
Conclusion
After reading this, you should have a solid understanding of how to use SQLite3 in Python for basic tasks like creating a database, creating tables, inserting data, updating rows, querying data, and catching exceptions.