How to use SQLAlchemy with SQLite: From Setup to Advanced Features

SQLAlchemy's robustness and flexibility have established it as a go-to ORM (Object-Relational Mapping) framework for Python developers. It offers a comprehensive set of tools for working with databases in a way that abstracts away specific database dialects, making it easier to switch between database backends. This guide explores SQLAlchemy, demonstrating how to integrate it into Python applications to handle databases more efficiently and idiomatically.

Key Terms

• Session: In SQLAlchemy, a session is akin to a staging zone for all objects loaded into the database session object. It's comparable to a buffer or a transactional workspace where objects are either pending to be persisted or fetched from the database.

• Engine: The Engine in SQLAlchemy serves as the core interface to the database, providing connectivity and the ability to execute SQL commands. It encapsulates a database connection pool and dialect configuration, enabling efficient and dialect-specific database access.

What is SQLAlchemy?

SQLAlchemy is an SQL toolkit and Object-Relational Mapping (ORM) library for Python. It offers a full suite of enterprise-level persistence patterns and is designed for high-performing database access. SQLAlchemy abstracts the differences between various SQL database systems, allowing developers to write database-agnostic code. It integrates with the Python Database API and provides a powerful ORM layer for mapping Python objects to database tables.

SQLAlchemy Basics

Creating a Connection in SQLAlchemy

The first step in using SQLAlchemy is to create an Engine instance, which represents the core interface to the database. This engine provides connectivity and the ability to execute SQL commands.

from sqlalchemy import create_engine

# Create an engine connected to the SQLite database
engine = create_engine('sqlite:///sports-cars-tutorial.db')

This code snippet creates an Engine instance connected to a SQLite database file named sports-cars-tutorial.db.

Defining and Creating Tables with SQLAlchemy

To work with databases using SQLAlchemy's ORM, you first define your tables as Python classes, using a declarative base.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class Sportscar(Base):
    tablename = 'sportscar'
    id = Column(Integer, primary_key=True)
    make = Column(String)
    model = Column(String)
    year = Column(Integer)
    horsepower = Column(Integer)

# Create all tables in the engine
Base.metadata.create_all(engine)

This code defines a Sports car class mapped to a table named sports car and then creates the table in the database.

Inserting Records with SQLAlchemy

Inserting records into the database involves creating instances of the defined class and adding them to a session.

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# Insert records into the sportscar table
ferrari = Sportscar(make='Ferrari', model='F8 Tributo', year=2020, horsepower=710)
lamborghini = Sportscar(make='Lamborghini', model='Huracan EVO', year=2020, horsepower=640)
session.add(ferrari)
session.add(lamborghini)

# Commit the transaction
session.commit()

Selecting Rows with SQLAlchemy

To retrieve and inspect data from the table, you query the session for instances of the mapped class.

# Query for all sportscars
for sportscar in session.query(Sportscar).all():
    print(sportscar.make, sportscar.model)

Inserting Multiple Records with SQLAlchemy

To insert multiple records efficiently, use the add_all method of the session object.

# Data to insert
data = [
    Sportscar(make="Porsche", model="911 Turbo S", year=2020, horsepower=640),
    Sportscar(make="McLaren", model="720S", year=2020, horsepower=710),
    Sportscar(make="Aston Martin", model="DBS Superleggera", year=2020, horsepower=715)
]

# Insert data
session.add_all(data)
session.commit()

Custom Python Types with SQLAlchemy

SQLAlchemy supports custom data types, allowing you to define how Python types translate to and from database types. This is achieved by extending existing types or creating entirely new types.

from sqlalchemy.types import TypeDecorator, String
# Assuming a Color class is defined elsewhere
class ColorType(TypeDecorator):
    impl = String
    def process_bind_param(self, value, dialect):
        return f'{value.r},{value.g},{value.b}' if value else None


    def process_result_value(self, value, dialect):
        r, g, b = map(int, value.split(','))
        return Color(r, g, b)

Advanced Features and Extensions in SQLAlchemy

As applications grow in complexity and requirements, developers may seek ways to extend SQLite's capabilities. This is where SQLAlchemy shines, by offering advanced features and extensions for SQLite, including support for asynchronous database access and encrypted databases.

SQLAlchemy and SQLCipher: Securing Your Data

For applications requiring data encryption, SQLAlchemy supports SQLCipher, an extension that adds 256-bit AES encryption to SQLite databases. This feature is crucial for applications handling sensitive information, ensuring that data at rest is not easily accessible or compromised. Using the pysqlcipher dialect, SQLAlchemy allows developers to utilize SQLCipher's encryption capabilities seamlessly:

from sqlalchemy import create_engine

# Creating an encrypted SQLite database with SQLCipher
engine = create_engine('sqlite+pysqlcipher://:passphrase@/path/to/database.db')

SQLAlchemy's integration with SQLCipher extends beyond simple encryption, allowing for the customization of encryption settings through URL query parameters, ensuring flexibility to meet various security requirements.

Asynchronous Database Access with Aiosqlite

Asynchronous programming has become increasingly important in Python, particularly for I/O-bound tasks. SQLAlchemy's aiosqlite dialect enables asynchronous database access, wrapping SQLite operations in an asyncio-compatible interface. This approach allows developers to perform non-blocking database operations, enhancing the performance of applications that rely on concurrency:

from sqlalchemy.ext.asyncio import create_async_engine

# Creating an async engine with SQLAlchemy and aiosqlite
async_engine = create_async_engine("sqlite+aiosqlite:///filename.db")

This feature is particularly useful for web applications and services where responsiveness and scalability are key considerations.

User-Defined Functions (UDFs) and Advanced SQLite Usage

Both the pysqlite and aiosqlite dialects support the creation of User-Defined Functions (UDFs), enabling the execution of custom Python logic within SQLite queries. This capability allows for more dynamic and flexible SQL queries, blending the power of Python with the efficiency of SQL:

from sqlalchemy import create_engine, event
from sqlalchemy import text

def my_custom_function(x, y):
    return x * y

engine = create_engine("sqlite:///mydatabase.db")

@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_record):
    dbapi_connection.create_function("my_custom_function", 2, my_custom_function)

Serializable Isolation, Transactional DDL, and SAVEPOINT Support

SQLAlchemy provides mechanisms to address some of pysqlite's limitations regarding transaction control and isolation. By manually managing transaction states through event listeners, SQLAlchemy ensures features like serializable isolation, transactional DDL, and SAVEPOINT are fully supported, offering more robust transaction control:

from sqlalchemy import event

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    dbapi_connection.isolation_level = None


@event.listens_for(engine, "begin")
def do_begin(conn):
    conn.exec_driver_sql("BEGIN")

Conclusion

Thanks for reading this guide on using SQLAlchemy with SQLite. For developers building applications that require database interactions, SQLAlchemy offers a flexible ORM solution that simplifies database operations. Check out our guides on sqlite3 for more options to interact with your SQLite databases.

To leverage the performance, efficiency and simplicity of SQLite at scale, try SQLite Cloud for free.