pysqlite3

Warnings

• gotcha SQLite's transactional model (and thus `pysqlite3`'s) implicitly commits open transactions before Data Definition Language (DDL) statements (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE, VACUUM, PRAGMA). This can lead to unexpected behavior if you expect DDL statements to be part of a larger, explicit transaction that can be rolled back. Ensure you commit or rollback explicitly before DDL if transaction integrity is critical.
  Fix: Be aware of implicit commits for DDL. If precise transaction control is needed around DDL, ensure preceding DML changes are committed, or the DDL is executed in its own implicit transaction scope.
• gotcha SQLite is designed for single-process access, though it handles concurrent reads well. Concurrent *writes* from multiple processes or threads can lead to 'database is locked' errors or contention. While `pysqlite3` allows specifying a timeout for locks, it does not fundamentally change SQLite's locking model.
  Fix: Design applications to minimize concurrent writes, or implement retry logic with appropriate timeouts. For high-concurrency write scenarios, consider a different database system or a client-server SQLite solution like LiteFS or rqlite.
• gotcha Always use parameter substitution (e.g., `?` placeholders) when executing SQL queries with user-provided data, rather than Python string formatting. Failure to do so exposes your application to SQL injection vulnerabilities.
  Fix: Rewrite queries using parameter substitution: `cursor.execute("INSERT INTO users (name) VALUES (?)", (user_name,))` instead of `cursor.execute(f"INSERT INTO users (name) VALUES ('{user_name}')")`.
• gotcha Improper application shutdowns, especially during active transactions, can lead to data corruption in SQLite databases. Ensure that all transactions are properly committed or rolled back before the application terminates.
  Fix: Implement proper transaction management, including `try...except...finally` blocks to ensure `conn.commit()` or `conn.rollback()` are called, and ensure database connections are gracefully closed using `conn.close()` or context managers (`with pysqlite3.connect(...) as conn:`).
• gotcha The `pysqlite3-binary` package provides a statically-linked, feature-rich, and up-to-date SQLite. If you install `pysqlite3` without the `-binary` suffix, it attempts to link against your system's `libsqlite3`, which might be older or lack features. Mixing these installations or expecting features only present in the binary version when using the system-linked one can lead to confusion or runtime errors.
  Fix: Decide whether you need the bundled, latest SQLite features (`pysqlite3-binary`) or wish to use your system's SQLite (`pysqlite3`). Explicitly install the desired package. For a robust and consistent environment, `pysqlite3-binary` is often preferred.

Install

• pip install pysqlite3-binary For a self-contained, statically linked SQLite with latest features (recommended)
• pip install pysqlite3 To link against the system's SQLite library

Imports

• pysqlite3
  wrong:

  __import__('pysqlite3'); import sys; sys.modules['sqlite3'] = sys.modules.pop('pysqlite3')

  correct:

  import pysqlite3

  While this pattern was once used to 'patch' the standard library's `sqlite3`, it is often unnecessary or can cause issues with newer Python versions and can be fragile. Direct import of `pysqlite3` is the intended usage.

Quickstart

This quickstart demonstrates how to connect to an SQLite database (in-memory in this case) using `pysqlite3`, create a table, insert data with parameter substitution to prevent SQL injection, and query the data. It also includes basic error handling for insertions.

import pysqlite3

# Connect to an in-memory database
conn = pysqlite3.connect(':memory:')
cursor = conn.cursor()

# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
)
''')
conn.commit()

# Insert data
try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))
    conn.commit()
    print('Data inserted successfully.')
except pysqlite3.IntegrityError as e:
    print(f'Error inserting data: {e}')
    conn.rollback()

# Query data
cursor.execute("SELECT id, name, email FROM users")
rows = cursor.fetchall()

print('\nUsers:')
for row in rows:
    print(f'ID: {row[0]}, Name: {row[1]}, Email: {row[2]}')

# Close the connection
conn.close()