{"id":6191,"library":"pypika-tortoise","title":"PyPika for Tortoise-ORM","description":"pypika-tortoise is an extension of the PyPika SQL query builder library, specifically designed to integrate seamlessly with Tortoise-ORM. It enables users to construct complex SQL queries using a Pythonic API directly from Tortoise-ORM models, leveraging PyPika's powerful query generation capabilities while ensuring compatibility with Tortoise-ORM's database interactions. The current version is 0.6.5, with its development closely tied to updates in `pypika` and `tortoise-orm`.","status":"active","version":"0.6.5","language":"en","source_language":"en","source_url":"https://github.com/vittoriolipia/pypika-tortoise","tags":["database","sql","orm","query-builder","tortoise-orm"],"install":[{"cmd":"pip install pypika-tortoise","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"Core SQL query builder functionality, pypika-tortoise extends its API.","package":"pypika","optional":false},{"reason":"Integration target; provides ORM models for query generation.","package":"tortoise-orm","optional":false}],"imports":[{"note":"Import from `pypika_tortoise` to get the extended `Query` object with Tortoise-ORM model integration.","wrong":"from pypika import Query","symbol":"Query","correct":"from pypika_tortoise import Query"},{"note":"Import `Table` from `pypika_tortoise` to utilize features like initializing with a Tortoise-ORM model for automatic field mapping.","wrong":"from pypika import Table","symbol":"Table","correct":"from pypika_tortoise import Table"},{"note":"Import `Field` from `pypika_tortoise` for fields that correctly resolve within the Tortoise-ORM context.","wrong":"from pypika import Field","symbol":"Field","correct":"from pypika_tortoise import Field"},{"symbol":"functions","correct":"from pypika_tortoise import functions as fn"}],"quickstart":{"code":"from tortoise import fields, models\nfrom pypika_tortoise import Query, Table, functions as fn\n\n# Define a Tortoise-ORM Model\nclass Product(models.Model):\n    id = fields.IntField(pk=True)\n    name = fields.CharField(max_length=255)\n    price = fields.DecimalField(max_digits=10, decimal_places=2)\n    in_stock = fields.BooleanField(default=True)\n\n    class Meta:\n        table = \"products\" # Ensure the table name is set\n\n# Create a Table object from the Tortoise-ORM Model\nproducts_table = Table(Product)\n\n# Build a query using pypika-tortoise, referencing model fields\nquery = Query.from_(products_table).select(\n    products_table.id,\n    products_table.name,\n    products_table.price\n).where(\n    products_table.in_stock == True\n).orderby(\n    products_table.name\n).limit(10)\n\n# Generate the SQL string (does not execute against a DB)\nsql_query = query.get_sql()\nprint(f\"Generated SELECT SQL:\\n{sql_query}\")\n\n# Example with aggregation using pypika_tortoise functions\nquery_agg = Query.from_(products_table).select(\n    fn.Count(products_table.id).as_('total_products'),\n    fn.Sum(products_table.price).as_('total_price_in_stock')\n).where(\n    products_table.in_stock == True\n)\n\nsql_agg_query = query_agg.get_sql()\nprint(f\"\\nGenerated AGGREGATION SQL:\\n{sql_agg_query}\")","lang":"python","description":"This quickstart demonstrates how to define a Tortoise-ORM model and then use `pypika-tortoise` to construct SQL queries. It shows how to initialize `Table` directly from a model and build a `SELECT` query with filtering, ordering, and limiting. An example with aggregation functions is also included, showcasing how `pypika-tortoise` can generate complex SQL without needing an active database connection."},"warnings":[{"fix":"Refer to the pypika-tortoise documentation and examples, especially when migrating from a pure pypika setup or encountering unexpected behavior with advanced pypika features.","message":"pypika-tortoise extends and wraps the original pypika library. While many pypika features are available, some specialized components (e.g., `QueryBuilder`) might be absent or replaced by pypika-tortoise's own constructs for better Tortoise-ORM integration. Always consult pypika-tortoise's documentation for features specific to its Tortoise-ORM integration.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always initialize `Table` objects with your Tortoise-ORM model class (e.g., `Table(MyModel)`) to leverage automatic column resolution and integration. Ensure fields are accessed through these model-bound `Table` instances.","message":"The `Table` and `Field` objects in `pypika-tortoise` gain special capabilities when initialized with Tortoise-ORM models. If `Table` is created with a plain string (e.g., `Table('my_table')`) or `Field` is used without a model context, it will behave like standard `pypika` objects, potentially leading to inconsistencies if Tortoise-ORM-specific features are expected.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Consistently use the `Field` instances provided by the model-bound `Table` object (e.g., `products_table.name`) within your `pypika-tortoise` queries.","message":"When constructing queries, especially with aggregate functions like `fn.Count` or `fn.Sum`, ensure that the field arguments correctly resolve to Tortoise-ORM model fields (e.g., `products_table.id`). Mixing raw strings or incorrectly referenced fields can lead to malformed SQL or runtime errors when Tortoise-ORM attempts to execute the query.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-14T00:00:00.000Z","next_check":"2026-07-13T00:00:00.000Z"}