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)

The table can also be created via class-style:

.. code-block:: python

from pypika import table_class, Table, Field, Query

@table_class('customers')
class Customer(Table):
    id = Field('id')
    first_name = Field('fname')
    last_name = Field('lname')
    phone = Field('phone')

q = Query.from_(Customer).select(Customer.id, Customer.first_name, Customer.last_name, Customer.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:: python

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:: python

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:: python

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:: python

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.

This is the beginning of the README. Read the rest at the original. Read the full README ↗