Python SQLite3: How to Install and Use SQLite Databases

SQLite is a lightweight, embedded SQL database engine that provides a fast, self-contained, server-free, zero-configuration, transactional SQL database. The sqlite3 module in Python provides an interface for accessing SQLite databases.

SQLite is built into Python – installing anything extra is unnecessary. The sqlite3 module provides a SQL interface compliant with the Python DB-API 2.0 specification. This makes working with SQLite databases in Python straightforward.

In this comprehensive guide, you will learn:

• How to install and configure SQLite and Python sqlite3
• Creating, connecting to, and querying databases with SQL
• Advanced usage of sqlite3 methods and attributes
• How to handle non-UTF8 data and custom objects
• Transaction control and isolation levels
• Exporting, backing up, and restoring SQLite databases

So let’s get started!

Installing and Configuring SQLite

SQLite comes pre-installed with Python. To check the version, import the sqlite3 module and print the sqlite_version attribute:

import sqlite3
print(sqlite3.sqlite_version)

This prints the underlying SQLite library version that Python sqlite3 was compiled against.

There is nothing else you need to install or configure – you can start using SQLite in Python immediately!

The sqlite3 module provides everything you need to work with SQLite databases, including methods for:

• Connecting to databases
• Executing SQL statements
• Fetching data
• Handling transactions

Let’s look at each of these by going through some end-to-end examples of using SQLite in Python.

Creating a Database Connection

To start working with an SQLite database, import the sqlite3 module and create a Connection object:

import sqlite3

con = sqlite3.connect('mydatabase.db') 

This creates a file called mydatabase.db in the current directory and opens a connection to it.

If the file does not exist, it will be created automatically.

You can also connect to temporary in-memory databases by passing in ':memory:' instead of a filename. This is useful for testing or temporary data storage:

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

Once you have a Connection, you can start executing SQL statements to interact with the database.

Creating Tables

To create a table in the database, call the execute() method on the Connection.

For example, to create a table called users with columns for id, name and email:

import sqlite3

con = sqlite3.connect('mydatabase.db')

con.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
    );
''')

con.close()

This uses SQL syntax to define each column, data type, and constraints like NOT NULL and UNIQUE.

Now the users table is created and ready for us to insert data!

Inserting Data

To insert data into a table, use the SQL INSERT statement. Execute it using con.execute() and pass in parameter values:

import sqlite3

con = sqlite3.connect('mydatabase.db')

users = [
    (1, 'John', '[email protected]'),
    (2, 'Jane', '[email protected]'), 
]

con.executemany("INSERT INTO users VALUES(?, ?, ?)", users)
con.commit()

con.close()

This inserts two rows for John and Jane.

Important things to note:

• Use ? placeholders and pass values as a separate tuple
• Call con.commit() to save changes after INSERT

You can also insert one row at a time using con.execute(), passing values directly:

con.execute("INSERT INTO users VALUES(3, 'Bob', '[email protected]')") 
con.commit()

This inserts a row for Bob.

So using executemany() and execute() methods we can populate tables with data.

Querying Data with SELECT

To query data from a table, use the SELECT statement.

For example, to fetch all rows and print the result:

import sqlite3

con = sqlite3.connect('mydatabase.db')
cur = con.cursor() 

cur.execute("SELECT * FROM users")
rows = cur.fetchall() 

for row in rows:
    print(row)

con.close()

This prints out all the rows we inserted earlier:

(1, 'John', '[email protected]')
(2, 'Jane', '[email protected]')  
(3, 'Bob', '[email protected]')

We can select specific columns too:

SELECT id, name FROM users

And use a WHERE clause to filter rows:

SELECT * FROM users WHERE name='John' 

So that covers the basics of querying data with SELECT statements.

Advanced Queries

SQLite supports more advanced SELECT query features like:

Ordering results

SELECT * FROM users ORDER BY name DESC

Aggregate functions

SELECT COUNT(*) FROM users

Joining tables

SELECT * 
FROM users
INNER JOIN orders 
    ON users.id = orders.user_id

Subqueries

SELECT *
FROM users
WHERE id IN (
    SELECT user_id FROM orders
)

And many more!

I won’t cover all of them here, but you can use any valid SQL syntax within execute(). This includes things like grouping, aliases, nested queries etc.

Now, let’s look at how we can update and delete data in SQLite databases.

Updating and Deleting Data

To modify existing records, you can use the UPDATE SQL statement.

For example, to update Bob’s email address:

import sqlite3

con = sqlite3.connect('mydatabase.db') 

con.execute("UPDATE users SET email='[email protected]' WHERE name='Bob'")
con.commit()  

con.close()

Make sure to use a WHERE clause to filter the rows you want to update!

To delete records, use the DELETE statement:

con.execute("DELETE FROM users WHERE id=3")
con.commit() 

This combines a WHERE clause to remove Bob’s record.

So that’s the basics of executing INSERT, SELECT, UPDATE and DELETE queries to manipulate data.

Executing Multiple Statements

In many cases you may want to run multiple SQL statements together in a batch.

For example, creating a table and populating initial rows.

One way is to execute each statement individually:

# Create table
con.execute("CREATE TABLE...")   

# Insert data 
con.execute("INSERT INTO...")
con.execute("INSERT INTO...")

However, this requires separate calls and can be inefficient.

A better way is to combine statements into a script and execute using executescript():

sql = """
CREATE TABLE articles (
    id INTEGER PRIMARY KEY, 
    title TEXT,
    content TEXT
);

INSERT INTO articles VALUES (1, 'SQLite Tutorial', 'Content here...');
INSERT INTO articles VALUES (2, 'Python SQLite Guide', 'Content here...');
"""

con.executescript(sql) 
con.commit()

This is useful because it allows the execution of multiple statements efficiently in one go.

Shortcuts for Executing Queries

Creating a Cursor object explicitly to execute queries can be verbose.

sqlite3 provides shortcut methods directly on the Connection:

con.execute("SELECT...") 

con.executemany("INSERT...", data)

The return value is a Cursor instance which you can use to fetch data.

For example:

cur = con.execute("SELECT * FROM articles")
for row in cur: 
    print(row)

So, you can avoid creating Cursor instances explicitly in many cases.

Working with Cursors

While you can execute queries directly on the Connection, working with Cursor objects provides more flexibility.

Here is how you can use Cursor objects to execute queries and fetch data:

import sqlite3

con = sqlite3.connect('mydatabase.db')
cur = con.cursor() 

# Execute 
cur.execute("SELECT * FROM articles")

# Fetch one result 
row1 = cur.fetchone()  

# Fetch all remaining   
rows23 = cur.fetchall()   

for row in (row1, *rows23):
    print(row)
    
cur.close()
con.close()

Key things Cursors provide:

• Context for fetch methods like fetchone(), fetchmany() etc
• Managing state of a query
• Access to status attributes/metadata

So, for complex queries, Cursors give you better control during fetch and processing.

Using Variables in Queries

Directly injecting values in queries makes them vulnerable to SQL injection attacks.

Never interpolate variables like this:

name = input() 
sql = f"SELECT * FROM users WHERE name = '{name}'"

Instead, use query parameters:

name = input()
sql = "SELECT * FROM users WHERE name = ?" 
cur.execute(sql, (name,)) 

The key things here are:

1. Use ? placeholders for variables
2. Pass values as separate parameter

This ensures correct escaping and prevents SQL injection.

Transactions

By default, sqlite3 implicitly wraps a transaction’s INSERT/UPDATE/DELETE queries.

For example:

con.execute("INSERT ...")  # Begin transaction

con.execute("UPDATE ...")  

con.commit() # Commit transaction 

If commit succeeds, the changes are saved.

If any error occurs at any point until the commit, all changes are rolled back automatically.

This ensures the atomicity of the statements within the transaction.

You can also explicitly use BEGIN, COMMIT and ROLLBACK SQL statements for starting, committing and rolling back transactions.

Handling Data Types

SQLite natively handles data types like NULL, INTEGER, REAL (float), TEXT, BLOB.

SQLite data types get converted to Python types when fetching rows:

NULL -> None  
INTEGER -> int
REAL -> float
TEXT -> str 
BLOB -> bytes

This handles most common scenarios.

For advanced cases, sqlite3 allows customizing how:

1. Python values get adapted to SQLite data types
2. SQLite return values get converted to custom Python objects

This allows efficiently storing things like dates, times, enums etc in SQLite databases.

We will cover this more in the section on Custom Objects and Types.

Custom Objects and Types

The sqlite3 type system can be extended in two ways:

1. Register adapters to convert custom Python types like dates, UUIDs etc into one of SQLite’s native data types.
2. Register converters to customize how SQLite return values are converted into custom Python objects.

Let’s take the example of efficiently storing date values, which SQLite does not directly support as a native type.

Adapting custom objects

First we define a custom MyDate class representing a date:

from dataclasses import dataclass

@dataclass  
class MyDate:
    year: int
    month: int 
    day: int
    
    def isoformat(self):
        return f"{self.year:04d}-{self.month:02d}-{self.day:02d}" 

Then register an adapter function to convert MyDate instances to ISO formatted date strings:

import sqlite3
from datetime import date

def adapt_mydate(val):
    return val.isoformat()  

sqlite3.register_adapter(MyDate, adapt_mydate)

con = sqlite3.connect('database.db')  

date = MyDate(2023, 2, 28)  

con.execute("INSERT INTO events VALUES (?)", (date,))  

So objects of type MyDate will seamlessly get adapted to strings when storing into SQLite.

Converting SQLite data to custom objects

For reading the data back, we register a converter to convert ISO date strings back to MyDate instances:

from datetime import date
from iso8601 import parse_date
  
def convert_date(iso_date):
    d = parse_date(iso_date)
    return MyDate(d.year, d.month, d.day)
  
 sqlite3.register_converter("date", convert_date)

con.row_factory = sqlite3.Row 

row = con.execute("SELECT date FROM events").fetchone()

date = row['date'] # Will be converted to MyDate object  

By registering adapters and converters like this, we can efficiently work with custom data types.

Exporting and Importing Data

The sqlite3 module provides functions to export the complete database schema and records into a single file.

This SQL dump file serves as a database backup archive and also facilitates migrating data to other databases.

Exporting database into SQL file

To export, call Connection.iterdump() which gives you SQL CREATE TABLE statements for the complete schema, along with INSERT statements for populating data:

with open('backup.sql', 'w') as f: 
    for line in con.iterdump(): 
        f.write(f'{line}\n')

This writes the SQL dump to backup.sql.

Importing SQL dump back

To import the SQL dump into an empty in-memory database:

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

with open('backup.sql') as f:
    sql = f.read()
    
fresh_con.executescript(sql)     

So iterdump() and executescript() provide a straightforward way to export and import SQLite databases into standard SQL text files.

Backing Up and Restoring Databases

The SQLite database files are simply normal OS files that you can directly copy to back up. But this can result in errors if the database is being written to during copying.

The sqlite3 module provides a Connection.backup() api specifically for crash-safe hot backups:

import sqlite3  

db = sqlite3.connect('main.db')  

backup = sqlite3.connect('backup.db')

with backup:
    db.backup(backup) 

This copies main.db into backup.db safely, even while main.db is being read from or written to.

To restore a backup:

backup = sqlite3.connect('backup.db')
restore = sqlite3.connect('main.db')  

with restore:
    backup.backup(restore)  

So backup() provides a simple built-in hot backup and restore capability.

The database can also be exported into a binary format using Connection.serialize() and loaded back using Connection.deserialize().

Summary

That wraps up this comprehensive guide on using SQLite databases in Python!

Here are some key things we learned:

• SQLite comes built-in with Python, no extra installation needed
• Connecting and creating databases is simple using sqlite3 module
• Basic CRUD can be performed using SQL statements like INSERT and SELECT
• Advanced queries involving JOINs, grouping, subqueries etc are supported
• Transactions ensure integrity and atomicity
• Custom types can be adapted for storing complex data efficiently
• Built-in backup/restore capabilities

So I hope this article served as a hands-on guide to start working with SQLite databases in your Python applications! Let me know if you have any other questions.