{"id":4357,"library":"fastlite","title":"Fastlite: Enhanced SQLite Usability","description":"Fastlite provides quality-of-life improvements for interactive use of the `sqlite-utils` library, particularly in Jupyter environments. It enhances database and table access with auto-completion, simplifies dataclass creation from schemas, and streamlines data manipulation. The library is currently active, with version 0.2.4 addressing recent bug fixes and continuing to evolve with new usability features.","status":"active","version":"0.2.4","language":"en","source_language":"en","source_url":"https://github.com/AnswerDotAI/fastlite","tags":["sqlite","database","orm","jupyter","apsw","sqlite-utils","productivity"],"install":[{"cmd":"pip install fastlite","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"Core database driver, switched from `sqlite3` for performance and concurrency improvements.","package":"apsw","optional":false},{"reason":"Provides the underlying utility functions that Fastlite builds upon.","package":"sqlite-utils","optional":false},{"reason":"Often used in the Fastlite ecosystem for utility functions and interactive development, though not a strict core dependency for basic functionality.","package":"fastcore","optional":true}],"imports":[{"note":"Commonly used to import all Fastlite utilities, including the `database` function.","symbol":"*","correct":"from fastlite import *"},{"note":"Specifically imports the function to create or connect to a SQLite database.","symbol":"database","correct":"from fastlite import database"}],"quickstart":{"code":"from fastlite import database\nimport os\n\n# Create an in-memory database for quick testing\ndb = database(':memory:')\n\n# Or, for a persistent file-based database\n# db_file = 'example.db'\n# if os.path.exists(db_file): os.remove(db_file) # Clean up for repeatable example\n# db = database(db_file)\n\n# Create a table named 'users'\nusers_table = db.t.users\nusers_table.create(\n    id=int, \n    name=str, \n    email=str,\n    pk='id' # Set 'id' as the primary key\n)\n\n# Insert data into the 'users' table\nusers_table.insert({'id': 1, 'name': 'Alice', 'email': 'alice@example.com'})\nusers_table.insert({'id': 2, 'name': 'Bob', 'email': 'bob@example.com'})\n\n# Query all users\nprint('All users:')\nfor user in users_table():\n    print(user)\n\n# Query a specific user by primary key\nalice = users_table.get(1)\nprint(f'\\nUser with ID 1: {alice}')\n\n# Update a user\nusers_table.update({'id': 1, 'name': 'Alicia'})\nprint('\\nUpdated user with ID 1:')\nfor user in users_table(id=1):\n    print(user)\n","lang":"python","description":"This quickstart demonstrates how to initialize an in-memory SQLite database, create a table with a primary key, insert new records, and retrieve data. It covers basic table creation, insertion, and querying using Fastlite's simplified `db.t` table access."},"warnings":[{"fix":"Migrate calls from `fetchone` to `selectone`. If your code expects multiple rows or handles non-unique results, adjust logic to catch `RowCountError` or use alternative querying methods like `table()` for a list of results.","message":"In version 0.2.0, the `table.fetchone` method was renamed to `table.selectone`. Additionally, `selectone` now strictly raises an exception (`fastlite.core.RowCountError`) if the query returns more than one row, ensuring uniqueness.","severity":"breaking","affected_versions":">=0.2.0"},{"fix":"Ensure `apsw` is installed (`pip install apsw`). Code that directly interacted with `sqlite3` specifics or relied on its exact concurrency model might need review, although Fastlite aims to abstract most of these differences.","message":"Starting with versions 0.1.0/0.1.1, Fastlite was rewritten to use `apsw` instead of Python's standard `sqlite3` module. This change was primarily driven by concurrency and performance issues found in `sqlite3` (especially with Python 3.12).","severity":"breaking","affected_versions":">=0.1.0"},{"fix":"When querying or inserting, be mindful of this distinction. Use `WHERE column IS NULL` for `None` values and `WHERE column = ''` for empty strings in SQL queries, and ensure your Python data correctly reflects this semantic difference.","message":"Fastlite maintains a strict distinction between `None` (representing SQL `NULL`) and an empty string (`''`) when interacting with SQLite. This differs from some ORMs that might treat them interchangeably.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Upgrade to version 0.2.4 or newer to ensure `insert(..., ignore=True)` behaves as expected and gracefully handles duplicate keys without raising a `KeyError`.","message":"When using `insert` with `ignore=True` and a duplicate primary key exists, older versions (<0.2.4) could raise a `KeyError` instead of ignoring the insertion.","severity":"gotcha","affected_versions":"<0.2.4"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}