{"id":6820,"library":"pysqlite3","title":"pysqlite3","description":"pysqlite3 is a Python library that provides a DB-API 2.0 compliant interface for SQLite 3.x databases. It effectively takes the `sqlite3` module from the Python standard library and packages it separately, often with a more recent, statically compiled SQLite library that includes additional features not always present in system-bundled SQLite versions. The current version is 0.6.0. It offers a self-contained binary distribution (`pysqlite3-binary`) that requires no external dependencies.","status":"active","version":"0.6.0","language":"en","source_language":"en","source_url":"https://github.com/pysqlite3/pysqlite3","tags":["database","sqlite","db-api","embedded database"],"install":[{"cmd":"pip install pysqlite3-binary","lang":"bash","label":"For a self-contained, statically linked SQLite with latest features (recommended)"},{"cmd":"pip install pysqlite3","lang":"bash","label":"To link against the system's SQLite library"}],"dependencies":[],"imports":[{"note":"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.","wrong":"__import__('pysqlite3'); import sys; sys.modules['sqlite3'] = sys.modules.pop('pysqlite3')","symbol":"pysqlite3","correct":"import pysqlite3"}],"quickstart":{"code":"import pysqlite3\n\n# Connect to an in-memory database\nconn = pysqlite3.connect(':memory:')\ncursor = conn.cursor()\n\n# Create a table\ncursor.execute('''\nCREATE TABLE IF NOT EXISTS users (\n    id INTEGER PRIMARY KEY,\n    name TEXT NOT NULL,\n    email TEXT UNIQUE NOT NULL\n)\n''')\nconn.commit()\n\n# Insert data\ntry:\n    cursor.execute(\"INSERT INTO users (name, email) VALUES (?, ?)\", ('Alice', 'alice@example.com'))\n    cursor.execute(\"INSERT INTO users (name, email) VALUES (?, ?)\", ('Bob', 'bob@example.com'))\n    conn.commit()\n    print('Data inserted successfully.')\nexcept pysqlite3.IntegrityError as e:\n    print(f'Error inserting data: {e}')\n    conn.rollback()\n\n# Query data\ncursor.execute(\"SELECT id, name, email FROM users\")\nrows = cursor.fetchall()\n\nprint('\\nUsers:')\nfor row in rows:\n    print(f'ID: {row[0]}, Name: {row[1]}, Email: {row[2]}')\n\n# Close the connection\nconn.close()","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions, inherent to SQLite's behavior; `pysqlite` 2.8.0 changed some DDL commit behavior, but the general principle holds."},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions, inherent to SQLite's design."},{"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}')\")`.","message":"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.","severity":"gotcha","affected_versions":"All versions."},{"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:`).","message":"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.","severity":"gotcha","affected_versions":"All versions, inherent to SQLite."},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions offering both `pysqlite3` and `pysqlite3-binary`."}],"env_vars":null,"last_verified":"2026-04-15T00:00:00.000Z","next_check":"2026-07-14T00:00:00.000Z","problems":[]}