SQLite's simplicity and ease of use have made it one of the most ubiquitous database systems in the world. Its lightweight nature allows it to be embedded into almost any application, providing a robust database solution without the need for a separate server process. This guide delves into the Python sqlite3 module, which facilitates the integration of SQLite databases within Python applications. By following this tutorial, you'll learn how to create and manipulate SQLite databases with Python.

Key Terms

• Cursor: A cursor in database management is akin to an iterator in programming languages, designed for navigating through and interacting with records in a database.

• Transaction: A transaction represents a coherent unit of work within a database, encapsulating a series of operations into a single, indivisible process.

What is the Python sqlite3 Module?

The sqlite3 module is a part of the Python Standard Library, offering an API to SQLite databases. This means you don't need to install any external packages to work with SQLite databases in Python. The module adheres to the Database API Specification v2.0 (PEP 249), ensuring consistent behavior across different Python Database APIs.

SQLite itself is a C library that provides a lightweight disk-based database. It doesn't require a separate server process, allowing it to be integrated directly into applications. SQLite databases are fully functional SQL databases stored in a single file, making them incredibly easy to distribute. The sqlite3 module wraps this functionality, allowing Python applications to use SQLite databases natively.

Now, let’s take a look at how to utilize the sqlite3 module.

Creating a Connection with Python and sqlite3

The first step in working with an SQLite database in Python is to establish a connection. If the specified database does not exist, SQLite will automatically create it.

# Import sqlite3 module
import sqlite3


# Create a connection to the SQLite database
connection = sqlite3.connect("sports-cars-tutorial.db")

# The returned Connection object represents the database connection

The connection object represents the database, allowing us to execute SQL commands through it.

Creating a Table with Python and sqlite3

To execute SQL statements, such as creating a table, we need a cursor object obtained from the connection. First, we'll

# Create a cursor object using the connection's "cursor" method
cur = con.cursor()

# Execute a SQL statement to create a table
cur.execute("CREATE TABLE sportscar(make, model, year, horsepower)")

# Verify the table creation
res = cur.execute("SELECT name FROM sqlite_master WHERE type='table'")

print(res.fetchone())

Inserting Records with Python and sqlite3

Inserting records into the newly created table requires using the cursor's execute method.

# Insert records into the sportscar table
cur.execute("""
    INSERT INTO sportscar VALUES ('Ferrari', 'F8 Tributo', 2020, 710),
    ('Lamborghini', 'Huracan EVO', 2020, 640)
""")

# Commit the transaction
con.commit()

Transactions are used implicit in SQLite - calling commit ensures that all changes are saved to the database.

Selecting Rows with Python and sqlite3

Now, let’s retrieve and verify the inserted data with a SELECT query.

# Execute a SELECT query
res = cur.execute("SELECT * FROM sportscar")

print(res.fetchall())

Inserting Multiple Records with Python and sqlite3

Use the executemany method to insert multiple records.

# Data to insert

data = [
    ("Porsche", "911 Turbo S", 2020, 640),
    ("McLaren", "720S", 2020, 710),
    ("Aston Martin", "DBS Superleggera", 2020, 715)
]
# Insert data
cur.executemany("INSERT INTO sportscar VALUES (?, ?, ?, ?)", data)
con.commit()

Note: Always use placeholders (?) to prevent SQL injection attacks.

Querying and Iterating Over Results with Python and sqlite3

Here’s how you execute a SELECT query, and iterate over the results.

# Query and print sorted results
for row in cur.execute("SELECT year, make, model FROM sportscar ORDER BY year"):
    print(row)

Adapting Custom Python Types to SQLite Values

SQLite natively supports a limited set of data types, which might not cover all Python types you'd want to store in the database. Fortunately, the sqlite3 module provides mechanisms to adapt custom Python types to one of the SQLite-compatible types.

Writing Adaptable Objects in Python

Let's consider a Color class that represents a color through its RGB (red, green, blue) components. To store an instance of Color in SQLite, it must be adapted to a format SQLite understands.

class Color:
    def init(self, r, g, b):
            self.r, self.g, self.b = r, g, b

    def conform(self, protocol):
            if protocol is sqlite3.PrepareProtocol:
               return f"{self.r},{self.g},{self.b}"

With this setup, you can directly insert Color objects into the database:

import sqlite3

# Connect to the database in memory
con = sqlite3.connect(":memory:")
cur = con.cursor()
# Attempt to insert a Color object
cur.execute("SELECT ?", (Color(255, 165, 0),))  # Example: Orange
print(cur.fetchone()[0])

Registering Adapter Callables in sqlite3

Another approach is to use an adapter callable, which is a function that converts your custom Python object to a SQLite-compatible type.

def adapt_color(color):
    return f"{color.r},{color.g},{color.b}"

# Register the adapter
sqlite3.register_adapter(Color, adapt_color)

# Connect to the database in memory
con = sqlite3.connect(":memory:")
cur = con.cursor()

# Insert a Color object
cur.execute("SELECT ?", (Color(0, 255, 127),))  # Example: Spring Green

print(cur.fetchone()[0])

Now, Color instances can be seamlessly adapted when inserted into SQLite!

Converting SQLite Values Back to Custom Python Types

Converting stored SQLite values back into Python objects involves defining a converter function. For our Color class stored as a comma-separated string, the conversion might look like this:

def convert_color(s):
  r, g, b = map(int, s.split(b","))
  return Color(r, g, b)

# Register the converter
sqlite3.register_converter("color", convert_color)

# Connect to the database with type detection
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()


# Create a table and insert a Color
cur.execute("CREATE TABLE test(c color)")
cur.execute("INSERT INTO test(c) VALUES(?)", (Color(135, 206, 235),)) 

# Fetch the inserted Color object
cur.execute("SELECT c FROM test")
print("with declared types:", cur.fetchone()[0])

Alternatives to Sqlite3 for Python

If you're looking for a database-agnostic toolkit, SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides a full suite of well-known enterprise-level persistence patterns and is designed for efficient and high-performing database access.

Conclusion

Thanks for reading this guide on sqlite3. If you’re building applications with SQLite, it’s worth checking out SQLite Cloud. SQLite Cloud is a fully-managed backend platform for client-heavy applications. Sign up for a free account here.