PyPika

0.51.1 · active · verified Thu Apr 09

PyPika is a SQL query builder API for Python, allowing users to construct SQL queries programmatically. It supports various SQL dialects like PostgreSQL, MySQL, Oracle, and Redshift, and even JQL. The library is actively maintained, with frequent patch and minor releases, currently at version 0.51.1.

Warnings

gotcha Always call `.get_sql()` on your query object to render the SQL string. Without it, you'll be working with the PyPika query object itself, not the executable SQL.
Fix: Append `.get_sql()` to your query chain, e.g., `query_object.get_sql()`.
gotcha Be mindful of SQL dialect differences, especially with quoting characters and specific functions. PyPika defaults to standard SQL but allows specifying a dialect.
Fix: Instantiate a dialect-specific query class (e.g., `PostgreSQLQuery`, `MySQLQuery`) or pass `dialect=Dialects.POSTGRES` (or similar) to `get_sql()`. For quoting issues, `get_sql(quote_char=None)` can be useful for databases like SQLite or when embedding in environments that handle quoting.
gotcha Do not confuse string literals with `Field` objects. When referring to column names, use `Table.field_name` or `Field('field_name')`. Only use raw strings for actual string values in WHERE clauses, etc., to prevent SQL injection.
Fix: Use `table.column_name` for column references. For string values in comparisons, ensure they are passed as regular Python strings, PyPika handles their escaping/quoting.

Install

pip install pypika Default install

Imports

Query
```
from pypika import Query
```
Table
```
from pypika import Table
```
Field
```
from pypika import Field
```
functions
```
from pypika import functions as fn
```
Commonly imported with an alias for brevity.
PostgreSQLQuery
```
from pypika.postgres import PostgreSQLQuery
```
Dialect-specific query classes are in submodules.

Quickstart

This quickstart demonstrates how to construct basic SELECT queries, including filtering with WHERE, using aggregate functions, and performing JOINs and GROUP BY clauses using PyPika's fluent API.

from pypika import Query, Table, Field
from pypika import functions as fn

customers = Table('customers')
orders = Table('orders')

# Basic SELECT statement
q1 = Query.from_(customers).select(customers.id, customers.name, customers.email)
print(f"\nBasic Select:\n{q1.get_sql()}")

# Select with WHERE clause and function
q2 = Query.from_(orders).select(orders.id, orders.amount).where(orders.amount > 100)
print(f"\nSelect with WHERE:\n{q2.get_sql()}")

# Select with JOIN and GROUP BY
q3 = Query.from_(customers).join(orders).on(customers.id == orders.customer_id) \
    .groupby(customers.name).select(customers.name, fn.Sum(orders.amount))
print(f"\nSelect with JOIN and GROUP BY:\n{q3.get_sql()}")

view raw JSON →