SQLite 🔗

Supported drivers:

dbapi	default	driver	connection class
sqlite3		`sqlite+sqlite3`	`sqlite3.Connection`

sqlite3🔗

sqlite3 is the default dbapi driver for SQLite in pydapper.

Installation🔗

sqlite3 is part of the stdlib and thus does not require installing an extra.

pippoetry

pip install pydapper

poetry add pydapper

DSN format🔗

TemplateExampleExample (Default Driver)

dsn = f"sqlite+sqlite3:///{relative_or_absolute_path_to_db}"

dsn = "sqlite+sqlite3://my.db"

dsn = "sqlite://my.db"

SQLite slash counts distinguish relative and absolute paths. The connection target below is the value available as database / dbname and passed to sqlite3.connect(); the parse result's path remains the decoded URL path before this convenience normalization.

DSN	SQLite connection target
`sqlite://relative.db`	`relative.db`
`sqlite+sqlite3://relative.db`	`relative.db`
`sqlite:///relative/path.db`	`relative/path.db`
`sqlite:////absolute/path.db`	`/absolute/path.db`
`sqlite:///:memory:`	`:memory:`
`sqlite://`	empty string

Paths are percent-decoded without treating plus signs as spaces. For example, sqlite:///data/my%20database%23one.db connects to data/my database#one.db.

Example - `connect`🔗

See Transactions below for what the connection's context manager does on exit.

import pydapper

with pydapper.connect("sqlite://pydapper.db") as commands:
    print(type(commands))
    # <class 'pydapper.sqlite.sqlite3.Sqlite3Commands'>

    print(type(commands.connection))
    # <class 'sqlite3.Connection'>

    # sqlite3 cursors are not context managers; close explicitly if you use one directly
    # (cursors owned by pydapper command methods are cleaned up for you)
    raw_cursor = commands.cursor()
    print(type(raw_cursor))
    # <class 'sqlite3.Cursor'>
    raw_cursor.close()

Example - `using`🔗

Use pydapper with a custom connection pool.

import sqlite3
from collections import deque

import pydapper


class SimplePool:
    """sqlite3 does not provide a pool interface, this is a simple example that should never be used in production"""

    def __init__(self, database: str):
        self._database = database
        self._connections = deque()

    def getconn(self):
        if len(self._connections) == 0:
            return sqlite3.connect(self._database)
        return self._connections.pop()

    def putconn(self, conn):
        self._connections.append(conn)

    def __del__(self):
        for conn in self._connections:
            conn.close()


my_pool = SimplePool("pydapper.db")

commands = pydapper.using(my_pool.getconn())
print(type(commands))
# <class 'pydapper.sqlite.sqlite3.Sqlite3Commands'>

print(type(commands.connection))
# <class 'sqlite3.Connection'>

my_pool.putconn(commands.connection)

Transactions🔗

sqlite3 connects in its legacy isolation_level mode by default: an implicit transaction opens before the first DML statement, and DDL issued while no transaction is open runs in autocommit — so a rolled-back transaction() block can leave a CREATE TABLE behind while its inserts are rolled back. (DDL issued after DML has already opened the implicit transaction participates in it and rolls back with it.) All of pydapper's transaction APIs (commit(), rollback(), transaction()) are supported.

Exiting with pydapper.connect(...) delegates to sqlite3.Connection's own context manager: it commits on clean exit, rolls back on error, and never closes the connection — close it explicitly when you are done:

with pydapper.connect("sqlite://my.db") as commands:
    commands.execute(insert_sql, params=task)
# the exit committed the insert, but the connection is still open
commands.connection.close()

See Transactions and Context manager semantics for the cross-driver picture.

SQLite🔗