{"id":7500,"library":"piccolo","title":"Piccolo ORM","description":"Piccolo is a fast, user-friendly, and fully type-annotated ORM and query builder for Python, primarily focused on asynchronous operations. It supports PostgreSQL, SQLite, and CockroachDB, and comes with batteries included like migrations, an admin GUI, and ASGI application templates. Currently at version 1.33.0, Piccolo releases frequently, often with minor version bumps every few weeks, incorporating new features and improvements.","status":"active","version":"1.33.0","language":"en","source_language":"en","source_url":"https://github.com/piccolo-orm/piccolo","tags":["ORM","async","database","SQL","PostgreSQL","SQLite","CockroachDB","migrations","admin-gui"],"install":[{"cmd":"pip install piccolo","lang":"bash","label":"Basic installation"},{"cmd":"pip install \"piccolo[all]\"","lang":"bash","label":"Install with all database drivers and extras"},{"cmd":"pip install \"piccolo[postgres]\"","lang":"bash","label":"Install with PostgreSQL support"}],"dependencies":[{"reason":"Piccolo requires Python 3.10.0 or higher. Specific features like UUID v7 may require Python 3.14.","package":"python","optional":false},{"reason":"Required for PostgreSQL database support.","package":"asyncpg","optional":true},{"reason":"Required for SQLite database support.","package":"aiosqlite","optional":true}],"imports":[{"symbol":"Table","correct":"from piccolo.table import Table"},{"symbol":"Varchar","correct":"from piccolo.columns import Varchar"},{"symbol":"Integer","correct":"from piccolo.columns import Integer"},{"symbol":"SQLiteEngine","correct":"from piccolo.engine.sqlite import SQLiteEngine"},{"symbol":"PostgresEngine","correct":"from piccolo.engine.postgres import PostgresEngine"}],"quickstart":{"code":"import asyncio\nfrom piccolo.table import Table\nfrom piccolo.columns import Varchar, Integer\nfrom piccolo.engine.sqlite import SQLiteEngine\n\n# 1. Define your database engine (in-memory SQLite for quick start)\nDB = SQLiteEngine(path=':memory:')\n\n# 2. Define your Table\nclass Band(Table, db=DB):\n    name = Varchar(length=100)\n    popularity = Integer(default=0)\n\nasync def main():\n    # 3. Create tables\n    await Band.create_table(if_not_exists=True)\n\n    # 4. Insert data\n    await Band.insert(\n        Band(name=\"Pythonistas\", popularity=1000),\n        Band(name=\"Asyncio Allstars\", popularity=800)\n    ).run()\n\n    # 5. Select data\n    all_bands = await Band.select(Band.name, Band.popularity).run()\n    print(\"All bands:\", all_bands)\n\n    popular_bands = await Band.select(Band.name).where(Band.popularity > 900).run()\n    print(\"Popular bands:\", popular_bands)\n\n    # 6. Update data\n    await Band.update({\"popularity\": 1100}).where(Band.name == \"Pythonistas\").run()\n    updated_bands = await Band.select(Band.name, Band.popularity).run()\n    print(\"Updated bands:\", updated_bands)\n\n    # 7. Close the connection pool (important for persistent databases)\n    await DB.close_connection_pool()\n\nif __name__ == \"__main__\":\n    asyncio.run(main())","lang":"python","description":"This quickstart demonstrates defining a simple Piccolo Table, initializing an in-memory SQLite database, performing basic CRUD (Create, Read, Update) operations, and running the async code."},"warnings":[{"fix":"Ensure your Python environment is 3.14+ and PostgreSQL database is version 18+ to utilize UUID v7.","message":"Using UUID v7 for columns requires Python 3.14 and PostgreSQL 18. Attempting to use this feature on older versions may result in errors or unexpected behavior.","severity":"breaking","affected_versions":"1.33.0+"},{"fix":"Correct `nul=True` to `null=True` in your column definitions.","message":"When defining columns, ensure you use `null=True` for nullable fields. Piccolo includes typo detection and will warn if `nul=True` is used instead.","severity":"gotcha","affected_versions":"1.27.0+"},{"fix":"Ensure your Python version meets the `piccolo` requirements (>=3.10) where `graphlib` is native.","message":"The `graphlib` backport was removed in version 1.31.0 as it's no longer needed in supported Python versions. This mostly affects internal dependencies, but extremely old Python versions relying on the backport might behave unexpectedly.","severity":"deprecated","affected_versions":"1.31.0+"},{"fix":"Upgrade to Piccolo version 1.26.1 or later if you use `ForeignKey` with `target_column` in your auto migrations.","message":"A bug existed in auto migrations where `ForeignKey` columns specifying `target_column` could lead to multiple primary key columns being added. This was fixed in 1.26.1.","severity":"gotcha","affected_versions":"<1.26.1"},{"fix":"Define new columns as `null=True` in their initial migration, then create a separate migration to set `null=False` if desired.","message":"When adding a new column to an existing table via auto migrations, it must initially be set to `null=True`. You can make it non-nullable in a subsequent migration.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Install the necessary driver: `pip install \"piccolo[postgres]\"` for PostgreSQL or `pip install \"piccolo[sqlite]\"` for SQLite.","cause":"The required database driver for PostgreSQL (asyncpg) or SQLite (aiosqlite) is not installed.","error":"ModuleNotFoundError: No module named 'piccolo.engine.postgres'"},{"fix":"Define a `piccolo_conf.py` file in your project root with a `DB` variable pointing to your `Engine` instance, or pass the `db=YOUR_ENGINE` argument directly to your `Table` classes. Ensure the engine is initialized (e.g., `await DB.start_connection_pool()`).","cause":"Piccolo could not find a configured database engine, often because `piccolo_conf.py` is missing, incorrectly configured, or the engine isn't explicitly passed to `Table` instances.","error":"RuntimeWarning: No database engine found. Make sure 'DB' is defined in your `piccolo_conf.py` or passed directly to your Tables."},{"fix":"Ensure `DB.close_connection_pool()` is called after all operations complete. For heavy concurrent workloads, consider using PostgreSQL or CockroachDB, which handle concurrency better than SQLite.","cause":"This typically occurs with SQLite when multiple asynchronous operations attempt to write to the database concurrently, or when a connection isn't properly closed.","error":"sqlite3.OperationalError: database is locked"},{"fix":"Rewrite your query to use `await Band.select().run()`, `await Band.insert().run()`, etc., directly on the `Table` class. The `.objects()` call might be relevant for specific patterns like `Band.objects().get(...)` but isn't the primary query builder interface.","cause":"In modern Piccolo versions, queries are typically run directly on the `Table` class or instances, not through a separate `objects()` manager, though `Table.objects()` style queries are shown in older examples or for specific use cases.","error":"AttributeError: type object 'Band' has no attribute 'objects'"}]}