PyPika: a SQL query builder that stays out of your way

Validation of SQL correctness is explicitly out of scope

The design goal for PyPika is a fast, expressive and flexible way to replace handwritten SQL, or even an ORM for the courageous. The stated motivation is a simple interface for building queries without limiting the flexibility of handwritten SQL, and the builder design pattern is used to construct queries instead of messy string formatting and concatenation.

One decision stands out and it shapes everything else. Validation of SQL correctness is not an explicit goal of the library. The reasoning given is practical: with so many SQL database vendors, providing validation of input data is difficult, so you are encouraged to check the inputs you pass in or to handle the errors your database raises, exactly as you would if you were writing SQL yourself.

That is a real trade-off rather than a gap. You get full SQL expressiveness with no safety net, and the failure surfaces at execution time instead of at build time.

Query, Table and Field are the three entry points

The main classes are pypika.Query, pypika.Table and pypika.Field. A query starts by adding a table to it, and for simple single table queries you can pass column names as strings.

from pypika import Query, Table, Field

q = Query.from_('customers').select('id', 'fname', 'lname', 'phone')

The result is converted to raw SQL either by casting the query to a string or by calling the get_sql function.

str(q)

q.get_sql()

So the workflow is the same as writing SQL by hand, in the same order, which is the point of the design. A builder that reorders your clauses or renames your tables would force you to learn a second language, and PyPika does not do that.

Table attributes reach into schemas, databases and aliases

String column names are enough until a query gets complicated, at which point you switch to a pypika.Table object and reference columns as attributes on it.

from pypika import Table, Query

customers = Table('customers')
q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)

The same attribute access pattern extends upward through the database hierarchy. A Schema gives you its tables as attributes, and a Database gives you its schemas as attributes.

from pypika import Table, Query, Schema

views = Schema('views')
q = Query.from_(views.customers).select(customers.id, customers.phone)

from pypika import Table, Query, Database

my_db = Database('my_db')
q = Query.from_(my_db.analytics.customers).select(customers.id, customers.phone)

Aliases use the as_ function on a table, so a view can be queried under a friendlier name. The reason to reach for Table objects is that the attribute chain is what lets one expression describe a database, a schema, a table and a column without string concatenation.

Field arithmetic builds expressions instead of strings

Columns are not just names in PyPika. The pypika.Field class implements the arithmetic operators, so revenue minus cost becomes an expression that renders as SQL rather than as Python formatting.

from pypika import Field

q = Query.from_('account').select(
    Field('revenue') - Field('cost')
)

The same operators work on table attributes, and the four arithmetic operators are joined, subtracted, multiplied and divided. An expression can be given an alias with as_ as well.

accounts = Table('accounts')
q = Query.from_(accounts).select(
    (accounts.revenue - accounts.cost).as_('profit')
)

Bitwise operations have their own methods, bitwiseand and bitwiseor, and they compose with comparison operators inside a where clause.

from pypika import Query, Field

q = Query.from_('flags').select('name').where(Field('permissions').bitwiseand(4) == 4)

This is the part that separates a query builder from a template engine. The result of an expression is a query object, so it can be aliased, filtered on and nested inside larger queries.

Filtering with Criterion and repeated where calls

Conditions are built with pypika.Criterion using ordinary equality and inequality operators, so the filtering code reads like the WHERE clause it produces.

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    customers.lname == 'Mustermann'
)

One behaviour is worth knowing before you refactor a large query. The query methods select, where, groupby and orderby can each be called more than once, and multiple calls to where add additional conditions rather than replacing the previous ones. That is the opposite of a builder that treats the last call as the final answer, and it means you can assemble a query in pieces without keeping a list of conditions yourself.

Ordering follows the same pattern, using pypika.Order with the desc option for a descending sort.

from pypika import Order
Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)

Installing PyPika and running the test suite locally

Installation is a single pip command.

pip install pypika

The project states that it is tested for Python 3.9 and later, and that it is tested for PyPy3.9 and PyPy3.10. It may also work on Cython and Jython, but those are not covered by the CI script, so treat them as untested rather than supported.

For working on the library itself, the Makefile at the repository root covers the usual loop. The install target pulls in the development requirements, installs the package in editable mode and sets up the pre-commit hooks.

pip install -r requirements-dev.txt pre-commit -e .

The test target runs pytest under coverage and then prints the report.

coverage run -m pytest

coverage report -m

The documentation is built with Sphinx, and the Makefile also has a docs.clean target that removes the generated output. The docs themselves are published at pypika.readthedocs.io.

A 0.x version number with a busy release line

PyPika is packaged as PyPika on PyPI, authored by Timothy Heys, and licensed under the Apache License Version 2.0. That licence is permissive for commercial use and modification, with the usual obligation to keep the notice in copies of the software.

The packaging is deliberately light. The only runtime requirement is typing_extensions for Python versions below 3.11, and the package ships a py.typed marker so type checkers use the inline annotations. setup.py lists two packages, pypika and pypika.clickhouse, which is the first sign that dialect support is split rather than switched by a flag. The package metadata also classifies the project as Production/Stable despite the 0.x version.

The release line has been busy: v0.50.0 on 2026-01-14, v0.51.0 on 2026-02-01 and v0.51.1 on 2026-02-04, and the last push to the repository was on 2026-10-19. The 0.x version number is the part to keep an eye on, since it signals that the API may still change between minor releases.

Editorial conclusion

PyPika suits analytics and reporting code where you want SQL you can read, and where an ORM would hide the query shape, since every builder call maps onto a clause you can print. It is the wrong tool for applications that depend on identity mapping, lazy loading or migrations, and it will not stop you from writing invalid SQL because validation is explicitly out of scope. Before adopting it, check the pypika.clickhouse package against your database dialect, and pin a version, since the release line moved from v0.50.0 on 2026-01-14 to v0.51.1 on 2026-02-04 while the repository itself was pushed to on 2026-10-19.

Frequently asked questions

Official sources

1. kayak/pypika on GitHub
2. License: Apache-2.0
3. Project website
4. README
5. Releases