PyPika for Tortoise-ORM

Warnings

• gotcha 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.
  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.
• gotcha 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.
  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.
• gotcha 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.
  Fix: Consistently use the `Field` instances provided by the model-bound `Table` object (e.g., `products_table.name`) within your `pypika-tortoise` queries.

Install

• pip install pypika-tortoise Install stable version

Imports

• Query

  from pypika_tortoise import Query

  Import from `pypika_tortoise` to get the extended `Query` object with Tortoise-ORM model integration.
• Table

  from pypika_tortoise import Table

  Import `Table` from `pypika_tortoise` to utilize features like initializing with a Tortoise-ORM model for automatic field mapping.
• Field

  from pypika_tortoise import Field

  Import `Field` from `pypika_tortoise` for fields that correctly resolve within the Tortoise-ORM context.
• functions

  from pypika_tortoise import functions as fn

Quickstart

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.

from tortoise import fields, models
from pypika_tortoise import Query, Table, functions as fn

# Define a Tortoise-ORM Model
class Product(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=255)
    price = fields.DecimalField(max_digits=10, decimal_places=2)
    in_stock = fields.BooleanField(default=True)

    class Meta:
        table = "products" # Ensure the table name is set

# Create a Table object from the Tortoise-ORM Model
products_table = Table(Product)

# Build a query using pypika-tortoise, referencing model fields
query = Query.from_(products_table).select(
    products_table.id,
    products_table.name,
    products_table.price
).where(
    products_table.in_stock == True
).orderby(
    products_table.name
).limit(10)

# Generate the SQL string (does not execute against a DB)
sql_query = query.get_sql()
print(f"Generated SELECT SQL:\n{sql_query}")

# Example with aggregation using pypika_tortoise functions
query_agg = Query.from_(products_table).select(
    fn.Count(products_table.id).as_('total_products'),
    fn.Sum(products_table.price).as_('total_price_in_stock')
).where(
    products_table.in_stock == True
)

sql_agg_query = query_agg.get_sql()
print(f"\nGenerated AGGREGATION SQL:\n{sql_agg_query}")