PyPika - Python Query Builder

.. _intro_start:

|BuildStatus| |CoverageStatus| |Codacy| |Docs| |PyPi| |License|

Abstract

What is |Brand|?

|Brand| is a Python API for building SQL queries. The motivation behind |Brand| is to provide a simple interface for building SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, |Brand| leverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also easily extended to take full advantage of specific features of SQL database vendors.

What are the design goals for |Brand|? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

|Brand| is a fast, expressive and flexible way to replace handwritten SQL (or even ORM for the courageous souls amongst you). Validation of SQL correctness is not an explicit goal of |Brand|. With such a large number of SQL database vendors providing a robust validation of input data is difficult. Instead you are encouraged to check inputs you provide to |Brand| or appropriately handle errors raised from your SQL database - just as you would have if you were writing SQL yourself.

.. _intro_end:

Read the docs: http://pypika.readthedocs.io/en/latest/

Installation

.. _installation_start:

|Brand| supports is tested for supported Python, i.e. 3.9+. It is tested for PyPy3.9 and PyPy3.10. It may also work Cython, and Jython but is not being tested for in the CI script.

To install |Brand| run the following command:

.. code-block:: bash

pip install pypika

.. _installation_end:

Tutorial

.. _tutorial_start:

The main classes in pypika are pypika.Query, pypika.Table, and pypika.Field.

.. code-block:: python

from pypika import Query, Table, Field

Selecting Data ^^^^^^^^^^^^^^

The entry point for building queries is pypika.Query. In order to select columns from a table, the table must first be added to the query. For simple queries with only one table, tables and columns can be references using strings. For more sophisticated queries a pypika.Table must be used.

.. code-block:: python

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

To convert the query into raw SQL, it can be cast to a string.

.. code-block:: python

str(q)

Alternatively, you can use the Query.get_sql() function:

.. code-block:: python

q.get_sql()

Tables, Columns, Schemas, and Databases ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In simple queries like the above example, columns in the "from" table can be referenced by passing string names into the select query builder function. In more complex examples, the pypika.Table class should be used. Columns can be referenced as attributes on instances of pypika.Table.

.. code-block:: python

from pypika import Table, Query

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

Both of the above examples result in the following SQL:

.. code-block:: sql

SELECT id,fname,lname,phone FROM customers

An alias for the table can be given using the .as_ function on pypika.Table

.. code-block:: sql

customers = Table('x_view_customers').as_('customers')
q = Query.from_(customers).select(customers.id, customers.phone)

.. code-block:: sql

SELECT id,phone FROM x_view_customers customers

A schema can also be specified. Tables can be referenced as attributes on the schema.

.. code-block:: sql

from pypika import Table, Query, Schema

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

.. code-block:: sql

SELECT id,phone FROM views.customers

Also references to databases can be used. Schemas can be referenced as attributes on the database.

.. code-block:: sql

from pypika import Table, Query, Database

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

.. code-block:: sql

SELECT id,phone FROM my_db.analytics.customers

Results can be ordered by using the following syntax:

.. code-block:: python

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

This results in the following SQL:

.. code-block:: sql

SELECT "id","fname","lname","phone" FROM "customers" ORDER BY "id" DESC

Arithmetic """"""""""

Arithmetic expressions can also be constructed using pypika. Operators such as +, -, *, and / are implemented by pypika.Field which can be used simply with a pypika.Table or directly.

.. code-block:: python

from pypika import Field

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

.. code-block:: sql

SELECT revenue-cost FROM accounts

Using pypika.Table

.. code-block:: python

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

.. code-block:: sql

SELECT revenue-cost FROM accounts

An alias can also be used for fields and expressions.

.. code-block:: sql

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

.. code-block:: sql

SELECT revenue-cost profit FROM accounts

More arithmetic examples

.. code-block:: python

table = Table('table')
q = Query.from_(table).select(
    table.foo + table.bar,
    table.foo - table.bar,
    table.foo * table.bar,
    table.foo / table.bar,
    (table.foo+table.bar) / table.fiz,
)

.. code-block:: sql

SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table

Bitwise operations are also supported using the bitwiseand and bitwiseor methods.

.. code-block:: python

from pypika import Query, Field

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

.. code-block:: sql

SELECT "name" FROM "flags" WHERE ("permissions" & 4)=4

.. code-block:: python

q = Query.from_('flags').select('name').where(Field('permissions').bitwiseor(2) == 3)

.. code-block:: sql

SELECT "name" FROM "flags" WHERE ("permissions" | 2)=3

Filtering """""""""

Queries can be filtered with pypika.Criterion by using equality or inequality operators

.. code-block:: python

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

.. code-block:: sql

SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'

Query methods such as select, where, groupby, and orderby can be called multiple times. Multiple calls to the where method will add additional conditions as

.. code-block:: python

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

.. code-block:: sql

SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'

Filters such as IN and BETWEEN are also supported

.. code-block:: python

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id,customers.fname
).where(
    customers.age[18:65] & customers.status.isin(['new', 'active'])
)

.. code-block:: sql

SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')

Filtering with complex criteria can be created using boolean symbols &, |, and ^.

AND

.. code-block:: python

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

.. code-block:: sql

SELECT id,fname,lname,phone FROM customers WHERE age>=18 AND lname='Mustermann'

OR

.. code-block:: python

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

.. code-block:: sql

SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'

XOR

.. code-block:: python

customers = Table('customers') q = Query.from_(customers).select( customers.id, customers.fname, customers.lname, customers.phone ).where( (customers.age >= 18) ^ customers.is_registered )

.. code-block:: sql

SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered

Convenience Methods """""""""""""""""""

In the Criterion class, there are the static methods any and all that allow building chains AND and OR expressions with a list of terms.

.. code-block:: python

from pypika import Criterion

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id,
    customers.fname
).where(
    Criterion.all([
        customers.is_registered,
        customers.age >= 18,
        customers.lname == "Jones",
    ])
)

.. code-block:: sql

SELECT id,fname FROM customers WHERE is_registered AND age>=18 AND lname = "Jones"

Grouping and Aggregating """"""""""""""""""""""""

Grouping allows for aggregated results and works similar to SELECT clauses.

.. code-block:: python

from pypika import functions as fn

customers = Table('customers')
q = Query \
    .from_(customers) \
    .where(customers.age >= 18) \
    .groupby(customers.id) \
    .select(customers.id, fn.Sum(customers.revenue))

.. code-block:: sql

SELECT id,SUM("revenue") FROM "customers" WHERE "age">=18 GROUP BY "id"

After adding a GROUP BY clause t