{"id":5503,"library":"sqlite-utils","title":"sqlite-utils","description":"sqlite-utils is a CLI tool and Python library for conveniently creating and manipulating SQLite databases. It simplifies common tasks like importing data, running queries, and managing schema. The current stable version is 3.39, with development on 4.0 in alpha. The library maintains a frequent release cadence, with over 128 releases since 2018.","status":"active","version":"3.39","language":"en","source_language":"en","source_url":"https://github.com/simonw/sqlite-utils","tags":["sqlite","database","cli","data-tool"],"install":[{"cmd":"pip install sqlite-utils","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"sqlite-utils 3.39 and later requires Python 3.10 or higher.","package":"python","optional":false}],"imports":[{"symbol":"Database","correct":"from sqlite_utils import Database"}],"quickstart":{"code":"from sqlite_utils import Database\n\n# Create an in-memory database\ndb = Database(memory=True)\n\n# Or create a file-based database\n# db = Database('my_data.db')\n\n# Insert data into a table, creating it if it doesn't exist\ndb[\"dogs\"].insert_all(\n    [\n        {\"id\": 1, \"name\": \"Cleo\", \"age\": 4},\n        {\"id\": 2, \"name\": \"Pancakes\", \"age\": 2}\n    ],\n    pk=\"id\"\n)\n\n# Query data\nfor row in db.query(\"select * from dogs where age > ?\", [3]):\n    print(row)\n\n# Access table objects and perform operations\ndogs_table = db[\"dogs\"]\nprint(f\"Total dogs: {dogs_table.count}\")\n\ndogs_table.update(1, {\"age\": 5})\n\nfor row in dogs_table.rows: # Iterate through all rows\n    print(row)\n","lang":"python","description":"This quickstart demonstrates how to create a Database object, insert data into a table (which is automatically created if it doesn't exist), and then query and update data. It shows both in-memory and file-based database creation."},"warnings":[{"fix":"Update calls from `db.table()` to `db.view()` for SQL views.","message":"The `db.table(table_name)` method now exclusively works with tables. To access SQL views, you must use `db.view(view_name)` instead. This change improves type hinting capabilities for tables vs views.","severity":"breaking","affected_versions":"4.0a1 and later"},{"fix":"If the old behavior is critical, pass `use_old_upsert=True` to the `Database()` constructor. Otherwise, ensure your code is compatible with the new upsert mechanism.","message":"Upsert operations (`.upsert()` and `.upsert_all()`) now utilize SQLite's `INSERT ... ON CONFLICT SET` syntax on SQLite versions newer than 3.23.1. Previously, it used `INSERT OR IGNORE` followed by an `UPDATE`. While largely functionally equivalent, applications depending on the exact old behavior might see minor differences.","severity":"breaking","affected_versions":"4.0a0 and later (for SQLite versions > 3.23.1)"},{"fix":"Upgrade your Python environment to 3.10 or a newer compatible version.","message":"sqlite-utils now requires Python 3.10 or higher. Older Python versions are no longer supported.","severity":"breaking","affected_versions":"3.39 and later"},{"fix":"No direct fix needed unless explicit type declarations are required, in which case specify column types during table creation or insertion.","message":"The default floating point column type has been changed from `FLOAT` to `REAL`, which is the correct SQLite type for floating-point values. This primarily affects auto-detected column types when inserting data.","severity":"breaking","affected_versions":"4.0a1 and later"},{"fix":"Use the `table.transform()` method with `foreign_keys=` or `add_foreign_keys=` parameters, or `db['table'].add_foreign_key()` instead of direct schema manipulation.","message":"Foreign key creation no longer directly manipulates the `sqlite_master` table using `PRAGMA writable_schema = 1`. Instead, it uses a table transformation mechanism. Code that relied on directly writing to `sqlite_master` for foreign key management may break or behave unexpectedly.","severity":"gotcha","affected_versions":"3.35 and later"}],"env_vars":null,"last_verified":"2026-04-13T00:00:00.000Z","next_check":"2026-07-12T00:00:00.000Z"}