PyPika

PyPika is a Python SQL query builder that exposes the full richness of the SQL language through a fluent, chainable API that mirrors the resulting query. Rather than concatenating strings or fighting an ORM, you compose Query, Table, and Field objects that render to clean SQL for a wide range of database vendors.

Designed with data analysis in mind, PyPika leverages the builder design pattern to construct SELECT, INSERT, UPDATE, and DDL statements, including joins, subqueries, window functions, and vendor-specific dialects. It focuses on flexible query construction rather than validation, leaving correctness checks and execution to your database driver.

What You Get

• A fluent, chainable query-builder API centered on Query, Table, and Field
• Support for joins, subqueries, aggregation, grouping, HAVING, and window functions
• Vendor-specific dialects for PostgreSQL, MySQL, Oracle, Vertica, ClickHouse, and more
• Arithmetic, bitwise, and boolean criterion composition with Python operators
• DDL helpers for CREATE and schema/database qualified references

Common Use Cases

• Generating analytical SQL dynamically for data-analysis pipelines
• Building parameterized reporting queries without string concatenation
• Constructing dialect-specific SQL across multiple database vendors
• Programmatically assembling complex joins and subqueries at runtime

Under The Hood

Architecture PyPika is built around a small set of composable term classes. queries.py (2,200+ lines) defines the Query, QueryBuilder, Table, Schema, and Database entry points that accumulate clauses (select, where, groupby, joins) and render them via get_sql(). terms.py (1,800+ lines) implements Field, Criterion, Function, and arithmetic/boolean operator overloading so Python expressions like table.a + table.b and (table.age >= 18) & criterion translate into SQL fragments. dialects.py and the clickhouse package specialize the base builder for vendor differences, while functions.py, analytics.py, and enums.py supply aggregate, window, and enum helpers. Query construction is immutable-style: builder methods return decorated copies, and stringification walks the accumulated term tree.

Tech Stack Pure Python (99.9% of the codebase) targeting Python 3.9+, with the only runtime dependency being typing_extensions for Python versions below 3.11. Tooling uses Black (line length 120), Make, and a standard setuptools setup.py/pyproject.toml layout. The package ships a py.typed marker for type-checking consumers.

Code Quality The repo has an extensive test suite under pypika/tests/ covering aggregates, analytic queries, criterions, custom functions, data types, date math, dialects, and ClickHouse specifics, indicating strong behavioral coverage. Code is organized by concern into focused modules and is fully type-annotated. The project is marked Production/Stable and has a long history of releases, though recent commit activity is low.

API Design The public API is deliberately expressive: Query.from_('customers').select('id', 'fname').where(...) reads close to the SQL it produces. Operator overloading (&, |, ^, arithmetic) and slice-based BETWEEN (customers.age[18:65]) make criteria concise, and Criterion.all/Criterion.any help build dynamic chains. Documentation on Read the Docs plus a thorough README tutorial keep the learning curve low for anyone who already knows SQL.