kayak/pypika

PyPika is a python SQL query builder that exposes the full richness of the SQL language using a syntax that reflects the resulting query. PyPika excels at all sorts of SQL queries but is especially useful for data analysis.

PyPika - Python Query Builder

Abstract

What is PyPika?

PyPika is a Python API for building SQL queries. The motivation behind PyPika is to provide a simple interface for building SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, PyPika 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 PyPika?

PyPika 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 PyPika. 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 PyPika or appropriately handle errors raised from your SQL database - just as you would have if you were writing SQL yourself.

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

Installation

PyPika supports python 3.6+. It may also work on pypy, cython, and jython, but is not being tested for these versions.

To install PyPika run the following command:

pip install pypika

Tutorial

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

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.

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

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

str(q)

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

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.

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:

SELECT id,fname,lname,phone FROM customers

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

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

SELECT id,phone FROM x_view_customers customers

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

from pypika import Table, Query, Schema

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

SELECT id,phone FROM views.customers

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

from pypika import Table, Query, Database

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

SELECT id,phone FROM my_db.analytics.customers

Results can be ordered by using the following syntax:

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

This results in the following 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.

from pypika import Field

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

SELECT revenue-cost FROM accounts

Using pypika.Table

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

SELECT revenue-cost FROM accounts

An alias can also be used for fields and expressions.

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

SELECT revenue-cost profit FROM accounts

More arithmetic examples

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,
)

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

Filtering

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

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

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

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

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

Filters such as IN and BETWEEN are also supported

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

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

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

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

OR

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

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

XOR

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

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.

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",
    ])
)

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.

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))

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

After adding a GROUP BY clause to a query, the HAVING clause becomes available. The method Query.having() takes a Criterion parameter similar to the method Query.where().

from pypika import functions as fn

payments = Table('payments')
q = Query \
    .from_(payments) \
    .where(payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]) \
    .groupby(payments.customer_id) \
    .having(fn.Sum(payments.total) >= 1000) \
    .select(payments.customer_id, fn.Sum(payments.total))

SELECT customer_id,SUM(total) FROM payments
WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'
GROUP BY customer_id HAVING SUM(total)>=1000

Joining Tables and Subqueries

Tables and subqueries can be joined to any query using the Query.join() method. Joins can be performed with either a USING or ON clauses. The USING clause can be used when both tables/subqueries contain the same field and the ON clause can be used with a criterion. To perform a join, ...join() can be chained but then must be followed immediately by ...on(<criterion>) or ...using(*field).

Join Types

All join types are supported by PyPika.

Query \
    .from_(base_table)
    ...
    .join(join_table, JoinType.left)
    ...

Query \
    .from_(base_table)
    ...
    .left_join(join_table) \
    .left_outer_join(join_table) \
    .right_join(join_table) \
    .right_outer_join(join_table) \
    .inner_join(join_table) \
    .outer_join(join_table) \
    .full_outer_join(join_table) \
    .cross_join(join_table) \
    .hash_join(join_table) \
    ...

See the list of join types here pypika.enums.JoinTypes

Example of a join using ON

history, customers = Tables('history', 'customers')
q = Query \
    .from_(history) \
    .join(customers) \
    .on(history.customer_id == customers.id) \
    .select(history.star) \
    .where(customers.id == 5)

SELECT "history".* FROM "history" JOIN "customers" ON "history"."customer_id"="customers"."id" WHERE "customers"."id"=5

As a shortcut, the Query.join().on_field() function is provided for joining the (first) table in the FROM clause with the joined table when the field name(s) are the same in both tables.

Example of a join using ON

history, customers = Tables('history', 'customers')
q = Query \
    .from_(history) \
    .join(customers) \
    .on_field('customer_id', 'group') \
    .select(history.star) \
    .where(customers.group == 'A')

SELECT "history".* FROM "history" JOIN "customers" ON "history"."customer_id"="customers"."customer_id" AND "history"."group"="customers"."group" WHERE "customers"."group"='A'

Example of a join using USING

history, customers = Tables('history', 'customers')
q = Query \
    .from_(history) \
    .join(customers) \
    .using('customer_id') \
    .select(history.star) \
    .where(customers.id == 5)

SELECT "history".* FROM "history" JOIN "customers" USING "customer_id" WHERE "customers"."id"=5

Example of a correlated subquery in the SELECT

history, customers = Tables('history', 'customers')
last_purchase_at = Query.from_(history).select(
    history.purchase_at
).where(history.customer_id==customers.customer_id).orderby(
    history.purchase_at, order=Order.desc
).limit(1)
q = Query.from_(customers).select(
    customers.id, last_purchase_at.as_('last_purchase_at')
)

SELECT
  "id",
  (SELECT "history"."purchase_at"
   FROM "history"
   WHERE "history"."customer_id" = "customers"."customer_id"
   ORDER BY "history"."purchase_at" DESC
   LIMIT 1) "last_purchase_at"
FROM "customers"

Unions

Both UNION and UNION ALL are supported. UNION DISTINCT is synonymous with "UNIONso |Brand| does not provide a separate function for it. Unions require that queries have the same number ofSELECTclauses so trying to cast a unioned query to string will throw aSetOperationExceptionif the column sizes are mismatched. To create a union query, use either theQuery.union()method or `+` operator with two query instances. For a union all, useQuery.union_all()or the `*` operator. .. code-block:: python provider_a, provider_b = Tables('provider_a', 'provider_b') q = Query.from_(provider_a).select( provider_a.created_time, provider_a.foo, provider_a.bar ) + Query.from_(provider_b).select( provider_b.created_time, provider_b.fiz, provider_b.buz ) .. code-block:: sql SELECT "created_time","foo","bar" FROM "provider_a" UNION SELECT "created_time","fiz","buz" FROM "provider_b" Intersect """""""""INTERSECTis supported. Intersects require that queries have the same number ofSELECTclauses so trying to cast a intersected query to string will throw aSetOperationExceptionif the column sizes are mismatched. To create a intersect query, use theQuery.intersect()method. .. code-block:: python provider_a, provider_b = Tables('provider_a', 'provider_b') q = Query.from_(provider_a).select( provider_a.created_time, provider_a.foo, provider_a.bar ) r = Query.from_(provider_b).select( provider_b.created_time, provider_b.fiz, provider_b.buz ) intersected_query = q.intersect(r) .. code-block:: sql SELECT "created_time","foo","bar" FROM "provider_a" INTERSECT SELECT "created_time","fiz","buz" FROM "provider_b" Minus """""MINUSis supported. Minus require that queries have the same number ofSELECTclauses so trying to cast a minus query to string will throw aSetOperationExceptionif the column sizes are mismatched. To create a minus query, use either theQuery.minus()method or `-` operator with two query instances. .. code-block:: python provider_a, provider_b = Tables('provider_a', 'provider_b') q = Query.from_(provider_a).select( provider_a.created_time, provider_a.foo, provider_a.bar ) r = Query.from_(provider_b).select( provider_b.created_time, provider_b.fiz, provider_b.buz ) minus_query = q.minus(r) (or) minus_query = Query.from_(provider_a).select( provider_a.created_time, provider_a.foo, provider_a.bar ) - Query.from_(provider_b).select( provider_b.created_time, provider_b.fiz, provider_b.buz ) .. code-block:: sql SELECT "created_time","foo","bar" FROM "provider_a" MINUS SELECT "created_time","fiz","buz" FROM "provider_b" EXCEPT """"""EXCEPTis supported. Minus require that queries have the same number ofSELECTclauses so trying to cast a except query to string will throw aSetOperationExceptionif the column sizes are mismatched. To create a except query, use theQuery.except_of()method. .. code-block:: python provider_a, provider_b = Tables('provider_a', 'provider_b') q = Query.from_(provider_a).select( provider_a.created_time, provider_a.foo, provider_a.bar ) r = Query.from_(provider_b).select( provider_b.created_time, provider_b.fiz, provider_b.buz ) minus_query = q.except_of(r) .. code-block:: sql SELECT "created_time","foo","bar" FROM "provider_a" EXCEPT SELECT "created_time","fiz","buz" FROM "provider_b" Date, Time, and Intervals """"""""""""""""""""""""" Usingpypika.Interval, queries can be constructed with date arithmetic. Any combination of intervals can be used except for weeks and quarters, which must be used separately and will ignore any other values if selected. .. code-block:: python from pypika import functions as fn fruits = Tables('fruits') q = Query.from_(fruits) \ .select(fruits.id, fruits.name) \ .where(fruits.harvest_date + Interval(months=1) < fn.Now()) .. code-block:: sql SELECT id,name FROM fruits WHERE harvest_date+INTERVAL 1 MONTH<NOW() Tuples """""" Tuples are supported through the classpypika.Tuplebut also through the native python tuple wherever possible. Tuples can be used withpypika.Criterionin **WHERE** clauses for pairwise comparisons. .. code-block:: python from pypika import Query, Tuple q = Query.from_(self.table_abc) \ .select(self.table_abc.foo, self.table_abc.bar) \ .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2)) .. code-block:: sql SELECT "foo","bar" FROM "abc" WHERE ("foo","bar")=(1,2) Usingpypika.Tupleon both sides of the comparison is redundant and |Brand| supports native python tuples. .. code-block:: python from pypika import Query, Tuple q = Query.from_(self.table_abc) \ .select(self.table_abc.foo, self.table_abc.bar) \ .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2)) .. code-block:: sql SELECT "foo","bar" FROM "abc" WHERE ("foo","bar")=(1,2) Tuples can be used in **IN** clauses. .. code-block:: python Query.from_(self.table_abc) \ .select(self.table_abc.foo, self.table_abc.bar) \ .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)])) .. code-block:: sql SELECT "foo","bar" FROM "abc" WHERE ("foo","bar") IN ((1,1),(2,2),(3,3)) Strings Functions """"""""""""""""" There are several string operations and function wrappers included in |Brand|. Function wrappers can be found in thepypika.functionspackage. In addition, `LIKE` and `REGEX` queries are supported as well. .. code-block:: python from pypika import functions as fn customers = Tables('customers') q = Query.from_(customers).select( customers.id, customers.fname, customers.lname, ).where( customers.lname.like('Mc%') ) .. code-block:: sql SELECT id,fname,lname FROM customers WHERE lname LIKE 'Mc%' .. code-block:: python from pypika import functions as fn customers = Tables('customers') q = Query.from_(customers).select( customers.id, customers.fname, customers.lname, ).where( customers.lname.regex(r'^[abc][a-zA-Z]+&') ) .. code-block:: sql SELECT id,fname,lname FROM customers WHERE lname REGEX '^[abc][a-zA-Z]+&'; .. code-block:: python from pypika import functions as fn customers = Tables('customers') q = Query.from_(customers).select( customers.id, fn.Concat(customers.fname, ' ', customers.lname).as_('full_name'), ) .. code-block:: sql SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers Custom Functions """""""""""""""" Custom Functions allows us to use any function on queries, as some functions are not covered by PyPika as default, we can appeal to Custom functions. .. code-block:: python from pypika import CustomFunction customers = Tables('customers') DateDiff = CustomFunction('DATE_DIFF', ['interval', 'start_date', 'end_date']) q = Query.from_(customers).select( customers.id, customers.fname, customers.lname, DateDiff('day', customers.created_date, customers.updated_date) ) .. code-block:: sql SELECT id,fname,lname,DATE_DIFF('day',created_date,updated_date) FROM customers Case Statements """"""""""""""" Case statements allow fow a number of conditions to be checked sequentially and return a value for the first condition met or otherwise a default value. The Case object can be used to chain conditions together along with their output using thewhenmethod and to set the default value usingelse. .. code-block:: python from pypika import Case, functions as fn customers = Tables('customers') q = Query.from_(customers).select( customers.id, Case() .when(customers.fname == "Tom", "It was Tom") .when(customers.fname == "John", "It was John") .else_("It was someone else.").as_('who_was_it') ) .. code-block:: sql SELECT "id",CASE WHEN "fname"='Tom' THEN 'It was Tom' WHEN "fname"='John' THEN 'It was John' ELSE 'It was someone else.' END "who_was_it" FROM "customers" With Clause """"""""""""""" With clause allows give a sub-query block a name, which can be referenced in several places within the main SQL query. The SQL WITH clause is basically a drop-in replacement to the normal sub-query. .. code-block:: python from pypika import Table, AliasedQuery, Query customers = Table('customers') sub_query = (Query .from_(customers) .select('*')) test_query = (Query .with_(sub_query, "an_alias") .from_(AliasedQuery("an_alias")) .select('*')) You can use as much as `.with_()` as you want. .. code-block:: sql WITH an_alias AS (SELECT * FROM "customers") SELECT * FROM an_alias Inserting Data ^^^^^^^^^^^^^^ Data can be inserted into tables either by providing the values in the query or by selecting them through another query. By default, data can be inserted by providing values for all columns in the order that they are defined in the table. Insert with values """""""""""""""""" .. code-block:: python customers = Table('customers') q = Query.into(customers).insert(1, 'Jane', 'Doe', '[email protected]') .. code-block:: sql INSERT INTO customers VALUES (1,'Jane','Doe','[email protected]') .. code-block:: python customers = Table('customers') q = customers.insert(1, 'Jane', 'Doe', '[email protected]') .. code-block:: sql INSERT INTO customers VALUES (1,'Jane','Doe','[email protected]') Multiple rows of data can be inserted either by chaining theinsertfunction or passing multiple tuples as args. .. code-block:: python customers = Table('customers') q = Query.into(customers).insert(1, 'Jane', 'Doe', '[email protected]').insert(2, 'John', 'Doe', '[email protected]') .. code-block:: python customers = Table('customers') q = Query.into(customers).insert((1, 'Jane', 'Doe', '[email protected]'), (2, 'John', 'Doe', '[email protected]')) Insert with constraint violation handling """"""""""""""""""""""""""""""""""""""""" MySQL ~~~~~ .. code-block:: python customers = Table('customers') q = MySQLQuery.into(customers) \ .insert(1, 'Jane', 'Doe', '[email protected]') \ .on_duplicate_key_ignore()) .. code-block:: sql INSERT INTO `customers` VALUES (1,'Jane','Doe','[email protected]') ON DUPLICATE KEY IGNORE .. code-block:: python customers = Table('customers') q = MySQLQuery.into(customers) \ .insert(1, 'Jane', 'Doe', '[email protected]') \ .on_duplicate_key_update(customers.email, Values(customers.email)) .. code-block:: sql INSERT INTO `customers` VALUES (1,'Jane','Doe','[email protected]') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`).on_duplicate_key_updateworks similar to.setfor updating rows, additionally it provides theValueswrapper to update to the value specified in theINSERTclause. PostgreSQL ~~~~~~~~~~ .. code-block:: python customers = Table('customers') q = PostgreSQLQuery.into(customers) \ .insert(1, 'Jane', 'Doe', '[email protected]') \ .on_conflict(customers.email) \ .do_nothing() .. code-block:: sql INSERT INTO "customers" VALUES (1,'Jane','Doe','[email protected]') ON CONFLICT ("email") DO NOTHING .. code-block:: python customers = Table('customers') q = PostgreSQLQuery.into(customers) \ .insert(1, 'Jane', 'Doe', '[email protected]') \ .on_conflict(customers.email) \ .do_update(customers.email, '[email protected]') .. code-block:: sql INSERT INTO "customers" VALUES (1,'Jane','Doe','[email protected]') ON CONFLICT ("email") DO UPDATE SET "email"='[email protected]' Insert from a SELECT Sub-query """""""""""""""""""""""""""""" .. code-block:: sql INSERT INTO "customers" VALUES (1,'Jane','Doe','[email protected]'),(2,'John','Doe','[email protected]') To specify the columns and the order, use thecolumnsfunction. .. code-block:: python customers = Table('customers') q = Query.into(customers).columns('id', 'fname', 'lname').insert(1, 'Jane', 'Doe') .. code-block:: sql INSERT INTO customers (id,fname,lname) VALUES (1,'Jane','Doe','[email protected]') Inserting data with a query works the same as querying data with the additional call to theintomethod in the builder chain. .. code-block:: python customers, customers_backup = Tables('customers', 'customers_backup') q = Query.into(customers_backup).from_(customers).select('*') .. code-block:: sql INSERT INTO customers_backup SELECT * FROM customers .. code-block:: python customers, customers_backup = Tables('customers', 'customers_backup') q = Query.into(customers_backup).columns('id', 'fname', 'lname') .from_(customers).select(customers.id, customers.fname, customers.lname) .. code-block:: sql INSERT INTO customers_backup SELECT "id", "fname", "lname" FROM customers The syntax for joining tables is the same as when selecting data .. code-block:: python customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup') q = Query.into(orders_backup).columns('id', 'address', 'customer_fname', 'customer_lname') .from_(customers) .join(orders).on(orders.customer_id == customers.id) .select(orders.id, customers.fname, customers.lname) .. code-block:: sql INSERT INTO "orders_backup" ("id","address","customer_fname","customer_lname") SELECT "orders"."id","customers"."fname","customers"."lname" FROM "customers" JOIN "orders" ON "orders"."customer_id"="customers"."id" Updating Data ^^^^^^^^^^^^^^ PyPika allows update queries to be constructed with or without where clauses. .. code-block:: python customers = Table('customers') Query.update(customers).set(customers.last_login, '2017-01-01 10:00:00') Query.update(customers).set(customers.lname, 'smith').where(customers.id == 10) .. code-block:: sql UPDATE "customers" SET "last_login"='2017-01-01 10:00:00' UPDATE "customers" SET "lname"='smith' WHERE "id"=10 The syntax for joining tables is the same as when selecting data .. code-block:: python customers, profiles = Tables('customers', 'profiles') Query.update(customers) .join(profiles).on(profiles.customer_id == customers.id) .set(customers.lname, profiles.lname) .. code-block:: sql UPDATE "customers" JOIN "profiles" ON "profiles"."customer_id"="customers"."id" SET "customers"."lname"="profiles"."lname" Usingpypika.Tablealias to perform the update .. code-block:: python customers = Table('customers') customers.update() .set(customers.lname, 'smith') .where(customers.id == 10) .. code-block:: sql UPDATE "customers" SET "lname"='smith' WHERE "id"=10 Usinglimitfor performing update .. code-block:: python customers = Table('customers') customers.update() .set(customers.lname, 'smith') .limit(2) .. code-block:: sql UPDATE "customers" SET "lname"='smith' LIMIT 2 Parametrized Queries ^^^^^^^^^^^^^^^^^^^^ PyPika allows you to useParameter(str)term as a placeholder for parametrized queries. .. code-block:: python customers = Table('customers') q = Query.into(customers).columns('id', 'fname', 'lname') .insert(Parameter(':1'), Parameter(':2'), Parameter(':3')) .. code-block:: sql INSERT INTO customers (id,fname,lname) VALUES (:1,:2,:3) This allows you to build prepared statements, and/or avoid SQL-injection related risks. Due to the mix of syntax for parameters, depending on connector/driver, it is required that you specify the parameter token explicitly or use one of the specialized Parameter types per [PEP-0249](https://www.python.org/dev/peps/pep-0249/#paramstyle):QmarkParameter(),NumericParameter(int),NamedParameter(str),FormatParameter(),PyformatParameter(str)An example of some common SQL parameter styles used in Python drivers are: PostgreSQL:$numberOR%s+:name(depending on driver) MySQL:%sSQLite:?Vertica::nameOracle::number+:nameMSSQL:%(name)sOR:name+:number(depending on driver) You can find out what parameter style is needed for DBAPI compliant drivers here: https://www.python.org/dev/peps/pep-0249/#paramstyle or in the DB driver documentation. Temporal support ^^^^^^^^^^^^^^^^ Temporal criteria can be added to the tables. Select """""" Here is a select using system time. .. code-block:: python t = Table("abc") q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select("*") This produces: .. code-block:: sql SELECT * FROM "abc" FOR SYSTEM_TIME AS OF '2020-01-01' You can also use between. .. code-block:: python t = Table("abc") q = Query.from_( t.for_(SYSTEM_TIME.between('2020-01-01', '2020-02-01')) ).select("*") This produces: .. code-block:: sql SELECT * FROM "abc" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01' You can also use a period range. .. code-block:: python t = Table("abc") q = Query.from_( t.for_(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01')) ).select("*") This produces: .. code-block:: sql SELECT * FROM "abc" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01' Finally you can select for all times: .. code-block:: python t = Table("abc") q = Query.from_(t.for_(SYSTEM_TIME.all_())).select("*") This produces: .. code-block:: sql SELECT * FROM "abc" FOR SYSTEM_TIME ALL A user defined period can also be used in the following manner. .. code-block:: python t = Table("abc") q = Query.from_( t.for_(t.valid_period.between('2020-01-01', '2020-02-01')) ).select("*") This produces: .. code-block:: sql SELECT * FROM "abc" FOR "valid_period" BETWEEN '2020-01-01' AND '2020-02-01' Joins """"" With joins, when the table object is used when specifying columns, it is important to use the table from which the temporal constraint was generated. This is because `Table("abc")` is not the same table as `Table("abc").for_(...)`. The following example demonstrates this. .. code-block:: python t0 = Table("abc").for_(SYSTEM_TIME.as_of('2020-01-01')) t1 = Table("efg").for_(SYSTEM_TIME.as_of('2020-01-01')) query = ( Query.from_(t0) .join(t1) .on(t0.foo == t1.bar) .select("*") ) This produces: .. code-block:: sql SELECT * FROM "abc" FOR SYSTEM_TIME AS OF '2020-01-01' JOIN "efg" FOR SYSTEM_TIME AS OF '2020-01-01' ON "abc"."foo"="efg"."bar" Update & Deletes """""""""""""""" An update can be written as follows: .. code-block:: python t = Table("abc") q = Query.update( t.for_portion( SYSTEM_TIME.from_to('2020-01-01', '2020-02-01') ) ).set("foo", "bar") This produces: .. code-block:: sql UPDATE "abc" FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01' SET "foo"='bar' Here is a delete: .. code-block:: python t = Table("abc") q = Query.from_( t.for_portion(t.valid_period.from_to('2020-01-01', '2020-02-01')) ).delete() This produces: .. code-block:: sql DELETE FROM "abc" FOR PORTION OF "valid_period" FROM '2020-01-01' TO '2020-02-01' Creating Tables ^^^^^^^^^^^^^^^ The entry point for creating tables ispypika.Query.create_table, which is used with the classpypika.Column. As with selecting data, first the table should be specified. This can be either a string or a `pypika.Table`. Then the columns, and constraints. Here's an example that demonstrates much of the functionality. .. code-block:: python stmt = Query \ .create_table("person") \ .columns( Column("id", "INT", nullable=False), Column("first_name", "VARCHAR(100)", nullable=False), Column("last_name", "VARCHAR(100)", nullable=False), Column("phone_number", "VARCHAR(20)", nullable=True), Column("status", "VARCHAR(20)", nullable=False, default=ValueWrapper("NEW")), Column("date_of_birth", "DATETIME")) \ .unique("last_name", "first_name") \ .primary_key("id") This produces: .. code-block:: sql CREATE TABLE "person" ( "id" INT NOT NULL, "first_name" VARCHAR(100) NOT NULL, "last_name" VARCHAR(100) NOT NULL, "phone_number" VARCHAR(20) NULL, "status" VARCHAR(20) NOT NULL DEFAULT 'NEW', "date_of_birth" DATETIME, UNIQUE ("last_name","first_name"), PRIMARY KEY ("id") ) There is also support for creating a table from a query. .. code-block:: python stmt = Query.create_table("names").as_select( Query.from_("person").select("last_name", "first_name") ) This produces: .. code-block:: sql CREATE TABLE "names" AS (SELECT "last_name","first_name" FROM "person") Managing Table Indices ^^^^^^^^^^^^^^^^^^^^^^ Create Indices """""""""""""""" The entry point for creating indices ispypika.Query.create_index. An index name (asstr) or apypika.terms.Indexa table (asstrorpypika.Table) and columns (aspypika.Column) must be specified. .. code-block:: python my_index = Index("my_index") person = Table("person") stmt = Query \ .create_index(my_index) \ .on(person) \ .columns(person.first_name, person.last_name) This produces: .. code-block:: sql CREATE INDEX my_index ON person (first_name, last_name) It is also possible to create a unique index .. code-block:: python my_index = Index("my_index") person = Table("person") stmt = Query \ .create_index(my_index) \ .on(person) \ .columns(person.first_name, person.last_name) \ .unique() This produces: .. code-block:: sql CREATE UNIQUE INDEX my_index ON person (first_name, last_name) It is also possible to create an index if it does not exist .. code-block:: python my_index = Index("my_index") person = Table("person") stmt = Query \ .create_index(my_index) \ .on(person) \ .columns(person.first_name, person.last_name) \ .if_not_exists() This produces: .. code-block:: sql CREATE INDEX IF NOT EXISTS my_index ON person (first_name, last_name) Drop Indices """""""""""""""" Then entry point for dropping indices ispypika.Query.drop_index. It takes eitherstrorpypika.terms.Indexas an argument. .. code-block:: python my_index = Index("my_index") stmt = Query.drop_index(my_index) This produces: .. code-block:: sql DROP INDEX my_index It is also possible to drop an index if it exists .. code-block:: python my_index = Index("my_index") stmt = Query.drop_index(my_index).if_exists() This produces: .. code-block:: sql DROP INDEX IF EXISTS my_index Chaining Functions ^^^^^^^^^^^^^^^^^^ TheQueryBuilder.pipe`` method gives a more readable alternative while chaining functions.

# This 
(
    query
    .pipe(func1, *args)
    .pipe(func2, **kwargs)
    .pipe(func3)
)

# Is equivalent to this
func3(func2(func1(query, *args), **kwargs))

Or for a more concrete example:

from pypika import Field, Query, functions as fn
from pypika.queries import QueryBuilder

def filter_days(query: QueryBuilder, col, num_days: int) -> QueryBuilder: 
    if isinstance(col, str): 
        col = Field(col)

    return query.where(col > fn.Now() - num_days)

def count_groups(query: QueryBuilder, *groups) -> QueryBuilder: 
    return query.groupby(*groups).select(*groups, fn.Count("*").as_("n_rows"))

base_query = Query.from_("table")

query = (
    base_query
    .pipe(filter_days, "date", num_days=7)
    .pipe(count_groups, "col1", "col2")
)

This produces:

SELECT "col1","col2",COUNT(*) n_rows 
FROM "table" 
WHERE "date">NOW()-7 
GROUP BY "col1","col2"

Contributing

We welcome community contributions to PyPika. Please see the contributing guide to more info.

License

Copyright 2020 KAYAK Germany, GmbH

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Crafted with ♥ in Berlin.

pypika's Issues

Add order by example to README

As order by is such a common use case, pypika should include examples of how to add an asc and desc order by.

Add support for DELETE queries

Add support for DELETE queries.

• DELETE FROM *** WHERE ***

additional Join functionality

• It would be a nice convenience function just to specify a string or list of strs if the column(s) to join on are the same in both tables. e.g. from_(t1).join(t2).on('c1')

• it's not clear how I can construct a join USING clause "... from t1 join t2 USING (c1, c2)"

i'm happy to contribute if this is not yet available but you think would be helpful

I'm sorry the docs don't show a preferred method of getting in touch. please suggest most appropriate communication channel. You can look me up as "CJ Lee" from London on linkedin

P.S. love this package thanks for this

Sqlite not supported?

Add support for selecting from queries

Need support for queries like:

table = Table('abc')
Query.from_(
Query.from_(table).select((table.foo+table.bar).as_('a')
).select('a')

Alias for Criterions

Criterion expressions cannot be given an alias due to missing .as_ function

Replace None with 'NULL'

The Python None should be replaced with the SQL string 'NULL'.

How can I use 'order by' as MySQL builder?

How can I use 'order by' as MySQL builder?

Support analytic queries for Vertica and Oracle

https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/AnalyticFunctions.htm

Add support for analytical functions and queries using PARTITION OVER

Add support for parametrized queries

Hello,

Props for this wonderful tool!

My understanding is that the end product for the user is always the SQL string as returned by calling str on a Query object. Such string can then be passed to whatever database interface the user is using. However, SQL interfaces also accept so-called parametrized queries, where the input isn't a single SQL string, but rather a string containing placeholders alongside a tuple containing values to replace the placeholders with. Parameterized queries are very important as they're the only working solution against SQL injection.

The interface could be improved such that the Query object would have two properties statement and values corresponding respectively to the parametrized statement and the values for this statement. The behavior of str would still be relevant as it's always useful to know what a query looks like even when parametrized, although its output should be documented as insecure with unstrusted data.

Suggestion for functions

Hi , I like pypika and here is one suggestion in functions.py , majority of functions can use something like this

def make_func(func_name):
cls = type(func_name,(Function,),{})
cls.init = lambda self, term, alias=None: super(cls, self).init(func_name, term, alias=alias)
return cls

LTRIM = make_func('LTRIM')
RTRIM = make_func('RTRIM')

and use it
fn.RTRIM(A.name)

Escape special characters in select

The select operation isn't escaping spaces and special characters. Please add this.

Add escaping for SQL characters

Any input into pypika should escape invalid characters. This includes table names, field names, and values.

Union queries with `+` seems to be broken when used for more than 2 queries

Example

from pypika import Tables, Query
a,b,c = Tables('a', 'b', 'c')
qa=Query.from_(a).select(a.a, 'name')
qb=Query.from_(b).select(b.b, 'name')
qc=Query.from_(c).select(c.c, 'name')

qa+qb
str(qa+qb)  # 1)
qa+qb+qc
str(qa+qb+qc)  # 2)

this used to output

1. '(SELECT "a","name" FROM "a") UNION (SELECT "b","name" FROM "b")'
2. '((SELECT "a","name" FROM "a") UNION (SELECT "b","name" FROM "b")) UNION (SELECT "c","name" FROM "c")'

Issue
Now, it out puts for

1. still the same but for
2. '(SELECT "a","name" FROM "a") UNION (SELECT "b","name" FROM "b")+SELECT "c","name" FROM "c"'

qa+qb is a UnionQuery, but qa+qb+qc is an ArithmeticExpression

if my bisect was correct, this was introduced in commit 3e850da1fdca72e0766a4e7b5bceceb88a97acaa

work around
using .union() instead of the + sign still works fine

Select from multiple tables or subqueries

Please make it possible to select from multiple tables and subqueries:

select * from
    table1 as T1,
    table2 as T2,
    table3  as T3

• or -

select * from
    (select * from table1) as T1,
    (select * from table2) as T2,
    (select * from table3) as T3

This can be done by chaining the from_ function.

Subquery in from clause missing alias when one is explicitly set

Subqueries require an alias and should also implicitly set one if needed

Query.from( subquery ) .select('*')

Add support for UPDATE queries

Add support for UPDATE queries

• UPDATE SET *** FROM *** WHERE ***
• UPDATE SET *** FROM *** JOIN *** WHERE ***

Attempting to select a column called 'table_name' as a column from a table object results in the table name being returned

I was trying to run a query to return table information from Vertica. The table showing this information includes a column called 'table_name'. I wanted to access this when running a union command as in the example by specifying the column names against the table I defined in the PyPika Table object, however just the table name is returned. In my query I wanted to include the 'table_name' column of the 'view_columns' table, but 'view_columns' was returned.

Example union from docs:

provider_a, provider_b = Tables('provider_a', 'provider_b')
q = Query.from_(provider_a).select(
    provider_a.created_time, provider_a.foo, provider_a.bar
) + Query.from_(provider_b).select(
    provider_b.created_time, provider_b.fiz, provider_b.buz
)

from pypika import Query, Tables

view_columns, table_columns = Tables('view_columns', 'columns')

query = Query.from_(view_columns).select(view_columns.table_id, view_columns.table_schema, view_columns.table_name,
                                         view_columns.column_name, view_columns.data_type) + \
        Query.from_(table_columns).select(table_columns.table_id, table_columns.table_schema, table_columns.table_name,
                                          table_columns.column_name, table_columns.data_type).orderby('table_schema', 'table_name')

Output:

'SELECT "table_id","table_schema","view_columns","column_name","data_type" FROM "view_columns" UNION SELECT "table_id","table_schema","columns","column_name","data_type" FROM "columns"'

I can get round this issue by specifying the column names as strings for the time being as below:

    query = Query.from_(view_columns).select('table_id', 'table_schema', 'view_name',
                                             'column_name', 'data_type') + \
            Query.from_(table_columns).select('table_id', 'table_schema', 'table_name',
                                              'column_name', 'data_type')

UnicodeDecodeError when pip install

When I use pip install, UnicodeDecodeError occured.
I use Windows 7 korean version and python3.

The details of the error are as follows:
Complete output from command python setup.py egg_info:
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "\AppData\Local\Temp\pip-gz8hjtca-build\setup.py", line 60, in
long_description=readme(),
File "\AppData\Local\Temp\pip-gz8hjtca-build\setup.py", line 10, in readme
return f.read()
UnicodeDecodeError: 'cp949' codec can't decode byte 0xe2 in position 16934: illegal multibyte sequence

I open the setup.py file and I found that this error is caused by 'README.rst' file.
so I delete the code 'long_description=readme(),' and repack, then I install successfully.
I don't understand why this error occured, but I thought this error should be reported.
Sorry for my poor english.

expose "with_quotes" param on Query and Table objects

would like this to propagate the "with_quotes" down to all the get_sql() functions.

Reason:
I would like a way to turn off quoting. My use cases are very simple and would like to keep the SQL I generate easy to read. this also avoids case sensitivity errors.

ListField not wrapped with quote character

Formatting a ListField does not properly use the quote character

Cannot use union result as a subquery

I've tried to use result of abc and efg tables as a subquery to calculate the average value from those union tables

 abc, efg = Tables('abc', 'efg')
 hij = Query.from_(abc).select(abc.t).union(Query.from_(efg).select(efg.t))
 q = Query.from_(hij).select(an.Avg(hij.t))

but actual query I get is

'SELECT AVG("sq0"."t") FROM (SELECT "t" FROM "abc") "sq0"'

expected query should be

'SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") UNION (SELECT "t" FROM "efg")) "sq0"'

Am I doing something wrong? or there is any other ways to achieve this.

Thanks

Is In and Between not available for Functions

Trying to use isin, between, or a between slice returns an AttributeError on a Function.

fn.Coalesce(Field('foo'), 0).isin([0, 1])

Move these from Field into Term so they are available.

terms.Interval should be able to accept a function e.g. WeekDay(...)

Currently terms.Interval should be able to accept a function such as WeekDay(...) instead of just an integer.

For example:

Interval(days=Weekday(CurDate())) should result in INTERVAL WEEKDAY(CURDATE()) DAY

Add support for INSERT queries

Add support for INSERT queries

• INSERT INTO *** VALUES
• INSERT INTO *** FROM *** WHERE ***

Installation of the latest version version fails

Steps to reproduce:

pip install pypika==0.9.2

Output:

Collecting pypika==0.9.2
  Downloading PyPika-0.9.2.tar.gz
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-build-ho1zhl5j/pypika/setup.py", line 35, in <module>
        with open('requirements.txt') as f:
    FileNotFoundError: [Errno 2] No such file or directory: 'requirements.txt'
    
    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-ho1zhl5j/pypika/

This is a regression from previous patch version:

pip install pypika==0.9.1

Output:

Collecting aenum (from pypika==0.9.1)
  Downloading aenum-2.0.9-py3-none-any.whl
Installing collected packages: aenum
  Found existing installation: aenum 2.0.8
    Uninstalling aenum-2.0.8:
      Successfully uninstalled aenum-2.0.8
Successfully installed aenum-2.0.9

I'm using Anaconda 4.3.29 (Python 3.6.3) on Ubuntu 16.04

as_() omitting 'as'

I tried the example in the tutorial but the as_() seems to be omitting the 'as' word.

from pypika import Query, Table
accounts = Table('accounts')
q = Query.from_(accounts).select(
     (accounts.revenue - accounts.cost).as_('profit')
)
q.get_sql()
# 'SELECT revenue-cost profit FROM accounts'

An alias is set in the GROUP BY when a CASE term is used, which is invalid

A fix for the use of alias' in GROUP BY clauses was previously added in #42.

However, this fix was only added to the Function class.

If a CASE statement is being used in the GROUP BY clause, then this needs to stop adding the alias after the CASE 'END' in the GROUP BY clause, as this is invalid SQL.

Add support for Vertica labels

Please add support for Vertica labels https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/AdministratorsGuide/Profiling/HowToLabelQueriesForProfiling.htm
something like:

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

output:
SELECT /* + label(my custom label)*/ id,fname,lname,phone FROM customers

Support specifying columns on INSERT

I'm trying to build such a query:
INSERT INTO some_table (column1, column2) VALUES(val1, val2)
Is it doable now?

Rename ListField class to Tuple and allow Criterions to be created with it

In order to support tuples in queries, please rename and repurpose the ListField as Tuple and allow it to be used in Criterions

Example query using inequality

select 
 a, b, c
from t
where (a, b, c) <= (1, 1, 1)

Example query using in

select 
 a, b
from t
where (a, b) in ((1, 1), (2, 2), (3, 3))

Add support for different query language dialects

Add support for setting a query language dialect so that pypika can generate database specific SQL.

Python None should be converted to 'NULL' instead of 'null'

Very low priority ticket for neatness, it would be nice if Python None converted to 'NULL' instead of 'null'

str(Query.from_('testtable').select(None))
'SELECT null FROM "testtable"'

Disable group by alias for Oracle and SQL Server dialects as it is not supported

MySQL, PostgreSQL (and Redshift I believe, as it is based on PostgreSQL) and Vertica support grouping by an alias defined in a select due to the order that certain parts of the query are executed:
https://stackoverflow.com/a/3841804/1224372

We should disable grouping by alias for SQL Server and Oracle dialects.

Invalid outer join for postgresql

Current join enum have only basic "OUTER" join, which is not supported by postgresql.
how parameter for joins should accept list of JoinType's, so join could be concatenated, or JoinType enum should be extended to have `LEFT OUTER' 'RIGHT OUTER' 'FULL OUTER'.

I can implement it, but would like to hear your opinion on this matter, may be I am missing something, how joins should be built with pypika

DB selection for table in Query.from_

Currently following SQL string cannot be obtained from pypika -

SELECT * FROM db.table

OFFSET and LIMIT are applied in the wrong order

Example:

    def test_select_with_limit_and_offset(self):
        q1 = Query.from_('abc').select('foo')[10:10]

        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10 LIMIT 10', str(q1))

The query should actually be SELECT "foo" FROM "abc" LIMIT 10 OFFSET 10

Select fields are surrounded by parenthesis, causing SQL statement errors

I am trying to automatically build the query from my configuration, it's something like this:
query = Query.from_(table).select([Field(f) for f in typeConfig['select']])

The issue is that the query object looks like this:
SELECT ("field1","field2","field3") FROM "myTable"

And converting it to string results in:
SELECT ("field1","field2","field3") FROM "myTable"

I did some debugging and I see the cause for that is in terms.py get_sql method:
def get_sql(self, **kwargs): return '({})'.format( ','.join(term.get_sql(**kwargs) for term in self.values) )

Why is the code surrounding the terms with parenthesis? MySQL refuses this SQL query

Thanks in advance

When nesting several queries, aliases are reused

The query is:

SELECT COALESCE(SUM("sq0"."expected"),0) "expected"
FROM (
    (
        SELECT SUM("sq0"."view_count") FROM (
            SELECT MIN("user_queries"."view_count")
            FROM "user_queries"
            JOIN (
                SELECT "user_query_word"."user_query_id","user_query_word"."language"
                FROM "words" JOIN "user_query_word" USING ("word_id")
                WHERE "words"."word"='best'
            ) "sq0" USING ("user_query_id")
            JOIN (
                SELECT "user_query_word"."user_query_id","user_query_word"."language" 
                FROM "words" 
                JOIN "user_query_word" USING ("word_id")
                WHERE "words"."word"='mortgage'
            ) "sq1" USING ("user_query_id")
            WHERE "user_queries"."user_query"<>'bestmortgage' AND "sq0"."language"="sq1"."language" 
            GROUP BY "user_queries"."user_query_id"
        ) "sq0"
    ) UNION ALL (
        SELECT "view_count"*1.0 "expected" FROM "user_queries" WHERE "user_query"='bestmortgage'
    )
) "sq0"

And the error:

psycopg2.ProgrammingError: column sq0.view_count does not exist
LINE 1: ..."sq0"."expected"),0) "expected" FROM ((SELECT SUM("sq0"."vie...

Left and Right join

Is there a way to create left (outer or inner) join and right (outer or inner) join statements?

Bug when using joined tables

pypika.utils.JoinException: Invalid join criterion. One field is required from the joined item and another from the selected table or an existing join. Found ["database"."table"]

Add support for ROLLUP

Add support for queries using GROUP BY ROLLUP:

http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html
https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets
https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/ROLLUPClause.htm

Add docstrings to PyPika classes and functions

PyPika is lacking class and function docstrings in many areas, meaning they are not picked up by Sphinx/ReadTheDocs.

We should add docstrings to ensure that the code is very easy to read for any users that want to read the code if the written documentation does not provide enough detail.

Fix double quote bug for MySQL

Pypika messes up the raw SQL string for MySQL queries by adding double quotes:

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

> 'SELECT "id","fname","lname","phone" FROM "myschema"."customers"'

Support for "ON DUPLICATE KEY UPDATE" in mysql

When doing bulk adds into a mysql database it is very useful to use the "ON DUPLICATE KEY UPDATE" feature from here https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

This allows us to specify fields to be updated when the key for the row already exists. For example:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Notice that we are adding values a, b, c in the insert but if there is a duplicate we are only updating the c value.

Some Vertica 'UNION ALL' queries need brackets

Currently PyPika creates union queries as query1 UNION ALL query2.

This works fine in most cases, e.g.

SELECT id FROM myTable
UNION ALL
SELECT id FROM myTable

At least for Vertica though, there are some queries where this throws an error, e.g.:

SELECT id FROM myTable ORDER BY id ASC
UNION ALL
SELECT id FROM myTable ORDER BY id ASC

The fix is as simple as adding brackets:

(SELECT id FROM myTable ORDER BY id ASC)
UNION ALL
(SELECT id FROM myTable ORDER BY id ASC)

[feature request] INSERT IGNORE

Please add the option of using 'INSERT IGNORE', right now all insertions are just 'INSERT'.

Add update example to README

No update example available.

Tuple omits table alias

Table aliases do not render for fields inside of a tuple.

Clickhouse dialect

I'd like to use fireant with clickhouse database, but I could not find an easy way to extend pypika with another sql backend. What is the recommended way?