{
  "result": {
    "0.48.9": {
      "name": "PyPika",
      "version": "0.48.9",
      "metadata_version": "2.4",
      "summary": "A SQL query builder API for Python",
      "home_page": "https://github.com/kayak/pypika",
      "author": "Timothy Heys",
      "author_email": "theys@kayak.com",
      "maintainer": "",
      "maintainer_email": "",
      "license": "Apache License Version 2.0",
      "description": "PyPika - Python Query Builder\n=============================\n\n.. _intro_start:\n\n|BuildStatus|  |CoverageStatus|  |Codacy|  |Docs|  |PyPi|  |License|\n\nAbstract\n--------\n\nWhat is |Brand|?\n\n|Brand| is a Python API for building SQL queries. The motivation behind |Brand| is to provide a simple interface for\nbuilding SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, |Brand|\nleverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also\neasily extended to take full advantage of specific features of SQL database vendors.\n\nWhat are the design goals for |Brand|?\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n|Brand| is a fast, expressive and flexible way to replace handwritten SQL (or even ORM for the courageous souls amongst you).\nValidation of SQL correctness is not an explicit goal of |Brand|. With such a large number of\nSQL 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\nyour SQL database - just as you would have if you were writing SQL yourself.\n\n.. _intro_end:\n\nRead the docs: http://pypika.readthedocs.io/en/latest/\n\nInstallation\n------------\n\n.. _installation_start:\n\n|Brand| supports python ``3.6+``.  It may also work on pypy, cython, and jython, but is not being tested for these versions.\n\nTo install |Brand| run the following command:\n\n.. code-block:: bash\n\n    pip install pypika\n\n\n.. _installation_end:\n\n\nTutorial\n--------\n\n.. _tutorial_start:\n\nThe main classes in pypika are ``pypika.Query``, ``pypika.Table``, and ``pypika.Field``.\n\n.. code-block:: python\n\n    from pypika import Query, Table, Field\n\n\nSelecting Data\n^^^^^^^^^^^^^^\n\nThe entry point for building queries is ``pypika.Query``.  In order to select columns from a table, the table must\nfirst be added to the query.  For simple queries with only one table, tables and columns can be references using\nstrings.  For more sophisticated queries a ``pypika.Table`` must be used.\n\n.. code-block:: python\n\n    q = Query.from_('customers').select('id', 'fname', 'lname', 'phone')\n\nTo convert the query into raw SQL, it can be cast to a string.\n\n.. code-block:: python\n\n    str(q)\n\nAlternatively, you can use the `Query.get_sql()` function:\n\n.. code-block:: python\n\n    q.get_sql()\n\n\nTables, Columns, Schemas, and Databases\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nIn simple queries like the above example, columns in the \"from\" table can be referenced by passing string names into\nthe ``select`` query builder function. In more complex examples, the ``pypika.Table`` class should be used. Columns can be\nreferenced as attributes on instances of ``pypika.Table``.\n\n.. code-block:: python\n\n    from pypika import Table, Query\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)\n\nBoth of the above examples result in the following SQL:\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers\n\nAn alias for the table can be given using the ``.as_`` function on ``pypika.Table``\n\n.. code-block:: sql\n\n    customers = Table('x_view_customers').as_('customers')\n    q = Query.from_(customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM x_view_customers customers\n\nA schema can also be specified. Tables can be referenced as attributes on the schema.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Schema\n\n    views = Schema('views')\n    q = Query.from_(views.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM views.customers\n\nAlso references to databases can be used. Schemas can be referenced as attributes on the database.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Database\n\n    my_db = Database('my_db')\n    q = Query.from_(my_db.analytics.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM my_db.analytics.customers\n\n\nResults can be ordered by using the following syntax:\n\n.. code-block:: python\n\n    from pypika import Order\n    Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)\n\nThis results in the following SQL:\n\n.. code-block:: sql\n\n    SELECT \"id\",\"fname\",\"lname\",\"phone\" FROM \"customers\" ORDER BY \"id\" DESC\n\nArithmetic\n\"\"\"\"\"\"\"\"\"\"\n\nArithmetic expressions can also be constructed using pypika.  Operators such as `+`, `-`, `*`, and `/` are implemented\nby ``pypika.Field`` which can be used simply with a ``pypika.Table`` or directly.\n\n.. code-block:: python\n\n    from pypika import Field\n\n    q = Query.from_('account').select(\n        Field('revenue') - Field('cost')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nUsing ``pypika.Table``\n\n.. code-block:: python\n\n    accounts = Table('accounts')\n    q = Query.from_(accounts).select(\n        accounts.revenue - accounts.cost\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nAn alias can also be used for fields and expressions.\n\n.. code-block:: sql\n\n    q = Query.from_(accounts).select(\n        (accounts.revenue - accounts.cost).as_('profit')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost profit FROM accounts\n\nMore arithmetic examples\n\n.. code-block:: python\n\n    table = Table('table')\n    q = Query.from_(table).select(\n        table.foo + table.bar,\n        table.foo - table.bar,\n        table.foo * table.bar,\n        table.foo / table.bar,\n        (table.foo+table.bar) / table.fiz,\n    )\n\n.. code-block:: sql\n\n    SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table\n\n\nFiltering\n\"\"\"\"\"\"\"\"\"\n\nQueries can be filtered with ``pypika.Criterion`` by using equality or inequality operators\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'\n\nQuery methods such as select, where, groupby, and orderby can be called multiple times.  Multiple calls to the where\nmethod will add additional conditions as\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.fname == 'Max'\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'\n\nFilters such as IN and BETWEEN are also supported\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,customers.fname\n    ).where(\n        customers.age[18:65] & customers.status.isin(['new', 'active'])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')\n\nFiltering with complex criteria can be created using boolean symbols ``&``, ``|``, and ``^``.\n\nAND\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) & (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 AND lname='Mustermann'\n\nOR\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) | (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'\n\nXOR\n\n.. code-block:: python\n\n customers = Table('customers')\n q = Query.from_(customers).select(\n     customers.id, customers.fname, customers.lname, customers.phone\n ).where(\n     (customers.age >= 18) ^ customers.is_registered\n )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered\n\n\nConvenience Methods\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nIn the `Criterion` class, there are the static methods `any` and `all` that allow building chains AND and OR expressions with a list of terms.\n\n.. code-block:: python\n\n    from pypika import Criterion\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname\n    ).where(\n        Criterion.all([\n            customers.is_registered,\n            customers.age >= 18,\n            customers.lname == \"Jones\",\n        ])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE is_registered AND age>=18 AND lname = \"Jones\"\n\n\nGrouping and Aggregating\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nGrouping allows for aggregated results and works similar to ``SELECT`` clauses.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Table('customers')\n    q = Query \\\n        .from_(customers) \\\n        .where(customers.age >= 18) \\\n        .groupby(customers.id) \\\n        .select(customers.id, fn.Sum(customers.revenue))\n\n.. code-block:: sql\n\n    SELECT id,SUM(\"revenue\") FROM \"customers\" WHERE \"age\">=18 GROUP BY \"id\"\n\nAfter adding a ``GROUP BY`` clause to a query, the ``HAVING`` clause becomes available.  The method\n``Query.having()`` takes a ``Criterion`` parameter similar to the method ``Query.where()``.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    payments = Table('payments')\n    q = Query \\\n        .from_(payments) \\\n        .where(payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]) \\\n        .groupby(payments.customer_id) \\\n        .having(fn.Sum(payments.total) >= 1000) \\\n        .select(payments.customer_id, fn.Sum(payments.total))\n\n.. code-block:: sql\n\n    SELECT customer_id,SUM(total) FROM payments\n    WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'\n    GROUP BY customer_id HAVING SUM(total)>=1000\n\n\nJoining Tables and Subqueries\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nTables and subqueries can be joined to any query using the ``Query.join()`` method.  Joins can be performed with either\na ``USING`` or ``ON`` clauses.  The ``USING`` clause can be used when both tables/subqueries contain the same field and\nthe ``ON`` clause can be used with a criterion. To perform a join, ``...join()`` can be chained but then must be\nfollowed immediately by ``...on(<criterion>)`` or ``...using(*field)``.\n\n\nJoin Types\n~~~~~~~~~~\n\nAll join types are supported by |Brand|.\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .join(join_table, JoinType.left)\n        ...\n\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .left_join(join_table) \\\n        .left_outer_join(join_table) \\\n        .right_join(join_table) \\\n        .right_outer_join(join_table) \\\n        .inner_join(join_table) \\\n        .outer_join(join_table) \\\n        .full_outer_join(join_table) \\\n        .cross_join(join_table) \\\n        .hash_join(join_table) \\\n        ...\n\nSee the list of join types here ``pypika.enums.JoinTypes``\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on(history.customer_id == customers.id) \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"id\" WHERE \"customers\".\"id\"=5\n\nAs a shortcut, the ``Query.join().on_field()`` function is provided for joining the (first) table in the ``FROM`` clause\nwith the joined table when the field name(s) are the same in both tables.\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on_field('customer_id', 'group') \\\n        .select(history.star) \\\n        .where(customers.group == 'A')\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"customer_id\" AND \"history\".\"group\"=\"customers\".\"group\" WHERE \"customers\".\"group\"='A'\n\n\nExample of a join using `USING`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .using('customer_id') \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" USING \"customer_id\" WHERE \"customers\".\"id\"=5\n\n\nExample of a correlated subquery in the `SELECT`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    last_purchase_at = Query.from_(history).select(\n        history.purchase_at\n    ).where(history.customer_id==customers.customer_id).orderby(\n        history.purchase_at, order=Order.desc\n    ).limit(1)\n    q = Query.from_(customers).select(\n        customers.id, last_purchase_at.as_('last_purchase_at')\n    )\n\n\n.. code-block:: sql\n\n    SELECT\n      \"id\",\n      (SELECT \"history\".\"purchase_at\"\n       FROM \"history\"\n       WHERE \"history\".\"customer_id\" = \"customers\".\"customer_id\"\n       ORDER BY \"history\".\"purchase_at\" DESC\n       LIMIT 1) \"last_purchase_at\"\n    FROM \"customers\"\n\n\nUnions\n\"\"\"\"\"\"\n\nBoth ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with \"UNION`` so |Brand| does not\nprovide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a union query, use either the ``Query.union()`` method or `+` operator with two query instances. For a\nunion all, use ``Query.union_all()`` or the `*` operator.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) + Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" UNION SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nIntersect\n\"\"\"\"\"\"\"\"\"\n\n``INTERSECT`` is supported. Intersects require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a intersected query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a intersect query, use the ``Query.intersect()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    intersected_query = q.intersect(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" INTERSECT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nMinus\n\"\"\"\"\"\n\n``MINUS`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a minus query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a minus query, use either the ``Query.minus()`` method or `-` operator with two query instances.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.minus(r)\n\n    (or)\n\n    minus_query = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) - Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" MINUS SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nEXCEPT\n\"\"\"\"\"\"\n\n``EXCEPT`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a except query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a except query, use the ``Query.except_of()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.except_of(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" EXCEPT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nDate, Time, and Intervals\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nUsing ``pypika.Interval``, queries can be constructed with date arithmetic.  Any combination of intervals can be\nused except for weeks and quarters, which must be used separately and will ignore any other values if selected.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    fruits = Tables('fruits')\n    q = Query.from_(fruits) \\\n        .select(fruits.id, fruits.name) \\\n        .where(fruits.harvest_date + Interval(months=1) < fn.Now())\n\n.. code-block:: sql\n\n    SELECT id,name FROM fruits WHERE harvest_date+INTERVAL 1 MONTH<NOW()\n\n\nTuples\n\"\"\"\"\"\"\n\nTuples are supported through the class ``pypika.Tuple`` but also through the native python tuple wherever possible.\nTuples can be used with ``pypika.Criterion`` in **WHERE** clauses for pairwise comparisons.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nUsing ``pypika.Tuple`` on both sides of the comparison is redundant and |Brand| supports native python tuples.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nTuples can be used in **IN** clauses.\n\n.. code-block:: python\n\n    Query.from_(self.table_abc) \\\n            .select(self.table_abc.foo, self.table_abc.bar) \\\n            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)]))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\") IN ((1,1),(2,2),(3,3))\n\n\nStrings Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nThere are several string operations and function wrappers included in |Brand|.  Function wrappers can be found in the\n``pypika.functions`` package.  In addition, `LIKE` and `REGEX` queries are supported as well.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.like('Mc%')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname LIKE 'Mc%'\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.regex(r'^[abc][a-zA-Z]+&')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname REGEX '^[abc][a-zA-Z]+&';\n\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        fn.Concat(customers.fname, ' ', customers.lname).as_('full_name'),\n    )\n\n.. code-block:: sql\n\n    SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers\n\n\nCustom Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCustom Functions allows us to use any function on queries, as some functions are not covered by PyPika as default, we can appeal\nto Custom functions.\n\n.. code-block:: python\n\n    from pypika import CustomFunction\n\n    customers = Tables('customers')\n    DateDiff = CustomFunction('DATE_DIFF', ['interval', 'start_date', 'end_date'])\n\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n        DateDiff('day', customers.created_date, customers.updated_date)\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,DATE_DIFF('day',created_date,updated_date) FROM customers\n\nCase Statements\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCase statements allow fow a number of conditions to be checked sequentially and return a value for the first condition\nmet or otherwise a default value.  The Case object can be used to chain conditions together along with their output\nusing the ``when`` method and to set the default value using ``else_``.\n\n\n.. code-block:: python\n\n    from pypika import Case, functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        Case()\n           .when(customers.fname == \"Tom\", \"It was Tom\")\n           .when(customers.fname == \"John\", \"It was John\")\n           .else_(\"It was someone else.\").as_('who_was_it')\n    )\n\n\n.. code-block:: sql\n\n    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\"\n\n\nWith Clause\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nWith clause allows give a sub-query block a name, which can be referenced in several places within the main SQL query.\nThe SQL WITH clause is basically a drop-in replacement to the normal sub-query.\n\n.. code-block:: python\n\n    from pypika import Table, AliasedQuery, Query\n\n    customers = Table('customers')\n\n    sub_query = (Query\n                .from_(customers)\n                .select('*'))\n\n    test_query = (Query\n                .with_(sub_query, \"an_alias\")\n                .from_(AliasedQuery(\"an_alias\"))\n                .select('*'))\n\nYou can use as much as `.with_()` as you want.\n\n.. code-block:: sql\n\n    WITH an_alias AS (SELECT * FROM \"customers\") SELECT * FROM an_alias\n\n\nInserting Data\n^^^^^^^^^^^^^^\n\nData can be inserted into tables either by providing the values in the query or by selecting them through another query.\n\nBy default, data can be inserted by providing values for all columns in the order that they are defined in the table.\n\nInsert with values\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\n.. code-block:: python\n\n    customers =  Table('customers')\n\n    q = customers.insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\nMultiple rows of data can be inserted either by chaining the ``insert`` function or passing multiple tuples as args.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com').insert(2, 'John', 'Doe', 'john@example.com')\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert((1, 'Jane', 'Doe', 'jane@example.com'),\n                                     (2, 'John', 'Doe', 'john@example.com'))\n\nInsert with constraint violation handling\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nMySQL\n~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_ignore())\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY IGNORE\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_update(customers.email, Values(customers.email))\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`)\n\n``.on_duplicate_key_update`` works similar to ``.set`` for updating rows, additionally it provides the ``Values``\nwrapper to update to the value specified in the ``INSERT`` clause.\n\nPostgreSQL\n~~~~~~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_nothing()\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO NOTHING\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_update(customers.email, 'bob@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO UPDATE SET \"email\"='bob@example.com'\n\n\nInsert from a SELECT Sub-query\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')\n\n\nTo specify the columns and the order, use the ``columns`` function.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname').insert(1, 'Jane', 'Doe')\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (1,'Jane','Doe','jane@example.com')\n\n\nInserting data with a query works the same as querying data with the additional call to the ``into`` method in the\nbuilder chain.\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).from_(customers).select('*')\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT * FROM customers\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).columns('id', 'fname', 'lname')\n        .from_(customers).select(customers.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT \"id\", \"fname\", \"lname\" FROM customers\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup')\n\n    q = Query.into(orders_backup).columns('id', 'address', 'customer_fname', 'customer_lname')\n        .from_(customers)\n        .join(orders).on(orders.customer_id == customers.id)\n        .select(orders.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n   INSERT INTO \"orders_backup\" (\"id\",\"address\",\"customer_fname\",\"customer_lname\")\n   SELECT \"orders\".\"id\",\"customers\".\"fname\",\"customers\".\"lname\" FROM \"customers\"\n   JOIN \"orders\" ON \"orders\".\"customer_id\"=\"customers\".\"id\"\n\nUpdating Data\n^^^^^^^^^^^^^^\nPyPika allows update queries to be constructed with or without where clauses.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    Query.update(customers).set(customers.last_login, '2017-01-01 10:00:00')\n\n    Query.update(customers).set(customers.lname, 'smith').where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"last_login\"='2017-01-01 10:00:00'\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, profiles = Tables('customers', 'profiles')\n\n    Query.update(customers)\n         .join(profiles).on(profiles.customer_id == customers.id)\n         .set(customers.lname, profiles.lname)\n\n.. code-block:: sql\n\n   UPDATE \"customers\"\n   JOIN \"profiles\" ON \"profiles\".\"customer_id\"=\"customers\".\"id\"\n   SET \"customers\".\"lname\"=\"profiles\".\"lname\"\n\nUsing ``pypika.Table`` alias to perform the update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nUsing ``limit`` for performing update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .limit(2)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' LIMIT 2\n\n\nParametrized Queries\n^^^^^^^^^^^^^^^^^^^^\n\nPyPika allows you to use ``Parameter(str)`` term as a placeholder for parametrized queries.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname')\n        .insert(Parameter(':1'), Parameter(':2'), Parameter(':3'))\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (:1,:2,:3)\n\nThis allows you to build prepared statements, and/or avoid SQL-injection related risks.\n\nDue to the mix of syntax for parameters, depending on connector/driver, it is required that you specify the\nparameter token explicitly or use one of the specialized Parameter types per [PEP-0249](https://www.python.org/dev/peps/pep-0249/#paramstyle):\n``QmarkParameter()``, ``NumericParameter(int)``,  ``NamedParameter(str)``, ``FormatParameter()``, ``PyformatParameter(str)``\n\nAn example of some common SQL parameter styles used in Python drivers are:\n\nPostgreSQL:\n    ``$number`` OR ``%s`` + ``:name`` (depending on driver)\nMySQL:\n    ``%s``\nSQLite:\n    ``?``\nVertica:\n    ``:name``\nOracle:\n    ``:number`` + ``:name``\nMSSQL:\n    ``%(name)s`` OR ``:name`` + ``:number`` (depending on driver)\n\nYou 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.\n\nTemporal support\n^^^^^^^^^^^^^^^^\n\nTemporal criteria can be added to the tables.\n\nSelect\n\"\"\"\"\"\"\n\nHere is a select using system time.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n\nYou can also use between.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'\n\nYou can also use a period range.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n\nFinally you can select for all times:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.all_())).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME ALL\n\nA user defined period can also be used in the following manner.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(t.valid_period.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR \"valid_period\" BETWEEN '2020-01-01' AND '2020-02-01'\n\nJoins\n\"\"\"\"\"\n\nWith joins, when the table object is used when specifying columns, it is\nimportant to use the table from which the temporal constraint was generated.\nThis is because `Table(\"abc\")` is not the same table as `Table(\"abc\").for_(...)`.\nThe following example demonstrates this.\n\n.. code-block:: python\n\n    t0 = Table(\"abc\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    t1 = Table(\"efg\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    query = (\n        Query.from_(t0)\n        .join(t1)\n        .on(t0.foo == t1.bar)\n        .select(\"*\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    JOIN \"efg\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    ON \"abc\".\"foo\"=\"efg\".\"bar\"\n\nUpdate & Deletes\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nAn update can be written as follows:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.update(\n        t.for_portion(\n            SYSTEM_TIME.from_to('2020-01-01', '2020-02-01')\n        )\n    ).set(\"foo\", \"bar\")\n\nThis produces:\n\n.. code-block:: sql\n\n    UPDATE \"abc\"\n    FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n    SET \"foo\"='bar'\n\nHere is a delete:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_portion(t.valid_period.from_to('2020-01-01', '2020-02-01'))\n    ).delete()\n\nThis produces:\n\n.. code-block:: sql\n\n    DELETE FROM \"abc\"\n    FOR PORTION OF \"valid_period\" FROM '2020-01-01' TO '2020-02-01'\n\nCreating Tables\n^^^^^^^^^^^^^^^\n\nThe entry point for creating tables is ``pypika.Query.create_table``, which is used with the class ``pypika.Column``.\nAs with selecting data, first the table should be specified. This can be either a\nstring or a `pypika.Table`. Then the columns, and constraints. Here's an example\nthat demonstrates much of the functionality.\n\n.. code-block:: python\n\n    stmt = Query \\\n        .create_table(\"person\") \\\n        .columns(\n            Column(\"id\", \"INT\", nullable=False),\n            Column(\"first_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"last_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"phone_number\", \"VARCHAR(20)\", nullable=True),\n            Column(\"status\", \"VARCHAR(20)\", nullable=False, default=ValueWrapper(\"NEW\")),\n            Column(\"date_of_birth\", \"DATETIME\")) \\\n        .unique(\"last_name\", \"first_name\") \\\n        .primary_key(\"id\")\n\nThis produces:\n\n.. code-block:: sql\n\n    CREATE TABLE \"person\" (\n        \"id\" INT NOT NULL,\n        \"first_name\" VARCHAR(100) NOT NULL,\n        \"last_name\" VARCHAR(100) NOT NULL,\n        \"phone_number\" VARCHAR(20) NULL,\n        \"status\" VARCHAR(20) NOT NULL DEFAULT 'NEW',\n        \"date_of_birth\" DATETIME,\n        UNIQUE (\"last_name\",\"first_name\"),\n        PRIMARY KEY (\"id\")\n    )\n\nThere is also support for creating a table from a query.\n\n.. code-block:: python\n\n    stmt = Query.create_table(\"names\").as_select(\n        Query.from_(\"person\").select(\"last_name\", \"first_name\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n        CREATE TABLE \"names\" AS (SELECT \"last_name\",\"first_name\" FROM \"person\")\n\n.. _tutorial_end:\n\n\n.. _license_start:\n\n\nLicense\n-------\n\nCopyright 2020 KAYAK Germany, GmbH\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n    http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n\n\nCrafted with \u2665 in Berlin.\n\n.. _license_end:\n\n\n.. _appendix_start:\n\n.. |Brand| replace:: *PyPika*\n\n.. _appendix_end:\n\n.. _available_badges_start:\n\n.. |BuildStatus| image:: https://github.com/kayak/pypika/workflows/Unit%20Tests/badge.svg\n   :target: https://github.com/kayak/pypika/actions\n.. |CoverageStatus| image:: https://coveralls.io/repos/kayak/pypika/badge.svg?branch=master\n   :target: https://coveralls.io/github/kayak/pypika?branch=master\n.. |Codacy| image:: https://api.codacy.com/project/badge/Grade/6d7e44e5628b4839a23da0bd82eaafcf\n   :target: https://www.codacy.com/app/twheys/pypika\n.. |Docs| image:: https://readthedocs.org/projects/pypika/badge/?version=latest\n   :target: http://pypika.readthedocs.io/en/latest/\n.. |PyPi| image:: https://img.shields.io/pypi/v/pypika.svg?style=flat\n   :target: https://pypi.python.org/pypi/pypika\n.. |License| image:: https://img.shields.io/hexpm/l/plug.svg?maxAge=2592000\n   :target: http://www.apache.org/licenses/LICENSE-2.0\n\n.. _available_badges_end:\n",
      "keywords": "pypika python query builder querybuilder sql mysql postgres psql oracle vertica aggregated relational database rdbms business analytics bi data science analysis pandas orm object mapper",
      "platform": [],
      "classifiers": [
        "License :: OSI Approved :: Apache Software License",
        "Development Status :: 5 - Production/Stable",
        "Intended Audience :: Developers",
        "Programming Language :: Python :: 3",
        "Programming Language :: PL/SQL",
        "Topic :: Software Development :: Libraries :: Python Modules",
        "Topic :: Scientific/Engineering :: Information Analysis",
        "Topic :: Scientific/Engineering :: Mathematics",
        "Operating System :: POSIX",
        "Operating System :: MacOS :: MacOS X",
        "Operating System :: Microsoft :: Windows",
        "Environment :: MetaData :: IBM Python Ecosystem"
      ],
      "download_url": "",
      "supported_platform": [],
      "comment": "",
      "provides": [],
      "requires": [],
      "obsoletes": [],
      "project_urls": [],
      "provides_dist": [],
      "obsoletes_dist": [],
      "requires_dist": [],
      "requires_external": [],
      "requires_python": "",
      "description_content_type": "",
      "provides_extras": [],
      "dynamic": [
        "author",
        "author-email",
        "classifier",
        "description",
        "home-page",
        "keywords",
        "license",
        "license-file",
        "summary"
      ],
      "license_expression": "",
      "license_file": [
        "LICENSE.txt"
      ],
      "+links": [
        {
          "rel": "releasefile",
          "hash_spec": "sha256=0835aa102275e1ffae0ecec8d4ec8954cbf65be76d74c59f1dc189ea01ad0e0c",
          "hashes": {
            "sha256": "0835aa102275e1ffae0ecec8d4ec8954cbf65be76d74c59f1dc189ea01ad0e0c"
          },
          "href": "https://wheels.developerfirst.ibm.com/ppc64le/linux/+f/083/5aa102275e1ff/pypika-0.48.9-py2.py3-none-any.whl",
          "log": [
            {
              "what": "upload",
              "who": "ppc64le",
              "when": [
                2026,
                7,
                27,
                13,
                6,
                49
              ],
              "dst": "ppc64le/linux"
            }
          ]
        }
      ]
    },
    "0.49.0+ppc64le1": {
      "name": "PyPika",
      "version": "0.49.0+ppc64le1",
      "metadata_version": "2.4",
      "summary": "A SQL query builder API for Python",
      "home_page": "https://github.com/kayak/pypika",
      "author": "Timothy Heys",
      "author_email": "theys@kayak.com",
      "maintainer": "",
      "maintainer_email": "",
      "license": "Apache License Version 2.0",
      "description": "PyPika - Python Query Builder\n=============================\n\n.. _intro_start:\n\n|BuildStatus|  |CoverageStatus|  |Codacy|  |Docs|  |PyPi|  |License|\n\nAbstract\n--------\n\nWhat is |Brand|?\n\n|Brand| is a Python API for building SQL queries. The motivation behind |Brand| is to provide a simple interface for\nbuilding SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, |Brand|\nleverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also\neasily extended to take full advantage of specific features of SQL database vendors.\n\nWhat are the design goals for |Brand|?\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n|Brand| is a fast, expressive and flexible way to replace handwritten SQL (or even ORM for the courageous souls amongst you).\nValidation of SQL correctness is not an explicit goal of |Brand|. With such a large number of\nSQL 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\nyour SQL database - just as you would have if you were writing SQL yourself.\n\n.. _intro_end:\n\nRead the docs: http://pypika.readthedocs.io/en/latest/\n\nInstallation\n------------\n\n.. _installation_start:\n\n|Brand| supports python ``3.6+``.  It may also work on pypy, cython, and jython, but is not being tested for these versions.\n\nTo install |Brand| run the following command:\n\n.. code-block:: bash\n\n    pip install pypika\n\n\n.. _installation_end:\n\n\nTutorial\n--------\n\n.. _tutorial_start:\n\nThe main classes in pypika are ``pypika.Query``, ``pypika.Table``, and ``pypika.Field``.\n\n.. code-block:: python\n\n    from pypika import Query, Table, Field\n\n\nSelecting Data\n^^^^^^^^^^^^^^\n\nThe entry point for building queries is ``pypika.Query``.  In order to select columns from a table, the table must\nfirst be added to the query.  For simple queries with only one table, tables and columns can be references using\nstrings.  For more sophisticated queries a ``pypika.Table`` must be used.\n\n.. code-block:: python\n\n    q = Query.from_('customers').select('id', 'fname', 'lname', 'phone')\n\nTo convert the query into raw SQL, it can be cast to a string.\n\n.. code-block:: python\n\n    str(q)\n\nAlternatively, you can use the `Query.get_sql()` function:\n\n.. code-block:: python\n\n    q.get_sql()\n\n\nTables, Columns, Schemas, and Databases\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nIn simple queries like the above example, columns in the \"from\" table can be referenced by passing string names into\nthe ``select`` query builder function. In more complex examples, the ``pypika.Table`` class should be used. Columns can be\nreferenced as attributes on instances of ``pypika.Table``.\n\n.. code-block:: python\n\n    from pypika import Table, Query\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)\n\nBoth of the above examples result in the following SQL:\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers\n\nAn alias for the table can be given using the ``.as_`` function on ``pypika.Table``\n\n.. code-block:: sql\n\n    customers = Table('x_view_customers').as_('customers')\n    q = Query.from_(customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM x_view_customers customers\n\nA schema can also be specified. Tables can be referenced as attributes on the schema.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Schema\n\n    views = Schema('views')\n    q = Query.from_(views.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM views.customers\n\nAlso references to databases can be used. Schemas can be referenced as attributes on the database.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Database\n\n    my_db = Database('my_db')\n    q = Query.from_(my_db.analytics.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM my_db.analytics.customers\n\n\nResults can be ordered by using the following syntax:\n\n.. code-block:: python\n\n    from pypika import Order\n    Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)\n\nThis results in the following SQL:\n\n.. code-block:: sql\n\n    SELECT \"id\",\"fname\",\"lname\",\"phone\" FROM \"customers\" ORDER BY \"id\" DESC\n\nArithmetic\n\"\"\"\"\"\"\"\"\"\"\n\nArithmetic expressions can also be constructed using pypika.  Operators such as `+`, `-`, `*`, and `/` are implemented\nby ``pypika.Field`` which can be used simply with a ``pypika.Table`` or directly.\n\n.. code-block:: python\n\n    from pypika import Field\n\n    q = Query.from_('account').select(\n        Field('revenue') - Field('cost')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nUsing ``pypika.Table``\n\n.. code-block:: python\n\n    accounts = Table('accounts')\n    q = Query.from_(accounts).select(\n        accounts.revenue - accounts.cost\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nAn alias can also be used for fields and expressions.\n\n.. code-block:: sql\n\n    q = Query.from_(accounts).select(\n        (accounts.revenue - accounts.cost).as_('profit')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost profit FROM accounts\n\nMore arithmetic examples\n\n.. code-block:: python\n\n    table = Table('table')\n    q = Query.from_(table).select(\n        table.foo + table.bar,\n        table.foo - table.bar,\n        table.foo * table.bar,\n        table.foo / table.bar,\n        (table.foo+table.bar) / table.fiz,\n    )\n\n.. code-block:: sql\n\n    SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table\n\n\nFiltering\n\"\"\"\"\"\"\"\"\"\n\nQueries can be filtered with ``pypika.Criterion`` by using equality or inequality operators\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'\n\nQuery methods such as select, where, groupby, and orderby can be called multiple times.  Multiple calls to the where\nmethod will add additional conditions as\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.fname == 'Max'\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'\n\nFilters such as IN and BETWEEN are also supported\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,customers.fname\n    ).where(\n        customers.age[18:65] & customers.status.isin(['new', 'active'])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')\n\nFiltering with complex criteria can be created using boolean symbols ``&``, ``|``, and ``^``.\n\nAND\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) & (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 AND lname='Mustermann'\n\nOR\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) | (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'\n\nXOR\n\n.. code-block:: python\n\n customers = Table('customers')\n q = Query.from_(customers).select(\n     customers.id, customers.fname, customers.lname, customers.phone\n ).where(\n     (customers.age >= 18) ^ customers.is_registered\n )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered\n\n\nConvenience Methods\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nIn the `Criterion` class, there are the static methods `any` and `all` that allow building chains AND and OR expressions with a list of terms.\n\n.. code-block:: python\n\n    from pypika import Criterion\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname\n    ).where(\n        Criterion.all([\n            customers.is_registered,\n            customers.age >= 18,\n            customers.lname == \"Jones\",\n        ])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE is_registered AND age>=18 AND lname = \"Jones\"\n\n\nGrouping and Aggregating\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nGrouping allows for aggregated results and works similar to ``SELECT`` clauses.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Table('customers')\n    q = Query \\\n        .from_(customers) \\\n        .where(customers.age >= 18) \\\n        .groupby(customers.id) \\\n        .select(customers.id, fn.Sum(customers.revenue))\n\n.. code-block:: sql\n\n    SELECT id,SUM(\"revenue\") FROM \"customers\" WHERE \"age\">=18 GROUP BY \"id\"\n\nAfter adding a ``GROUP BY`` clause to a query, the ``HAVING`` clause becomes available.  The method\n``Query.having()`` takes a ``Criterion`` parameter similar to the method ``Query.where()``.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    payments = Table('payments')\n    q = Query \\\n        .from_(payments) \\\n        .where(payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]) \\\n        .groupby(payments.customer_id) \\\n        .having(fn.Sum(payments.total) >= 1000) \\\n        .select(payments.customer_id, fn.Sum(payments.total))\n\n.. code-block:: sql\n\n    SELECT customer_id,SUM(total) FROM payments\n    WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'\n    GROUP BY customer_id HAVING SUM(total)>=1000\n\n\nJoining Tables and Subqueries\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nTables and subqueries can be joined to any query using the ``Query.join()`` method.  Joins can be performed with either\na ``USING`` or ``ON`` clauses.  The ``USING`` clause can be used when both tables/subqueries contain the same field and\nthe ``ON`` clause can be used with a criterion. To perform a join, ``...join()`` can be chained but then must be\nfollowed immediately by ``...on(<criterion>)`` or ``...using(*field)``.\n\n\nJoin Types\n~~~~~~~~~~\n\nAll join types are supported by |Brand|.\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .join(join_table, JoinType.left)\n        ...\n\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .left_join(join_table) \\\n        .left_outer_join(join_table) \\\n        .right_join(join_table) \\\n        .right_outer_join(join_table) \\\n        .inner_join(join_table) \\\n        .outer_join(join_table) \\\n        .full_outer_join(join_table) \\\n        .cross_join(join_table) \\\n        .hash_join(join_table) \\\n        ...\n\nSee the list of join types here ``pypika.enums.JoinTypes``\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on(history.customer_id == customers.id) \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"id\" WHERE \"customers\".\"id\"=5\n\nAs a shortcut, the ``Query.join().on_field()`` function is provided for joining the (first) table in the ``FROM`` clause\nwith the joined table when the field name(s) are the same in both tables.\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on_field('customer_id', 'group') \\\n        .select(history.star) \\\n        .where(customers.group == 'A')\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"customer_id\" AND \"history\".\"group\"=\"customers\".\"group\" WHERE \"customers\".\"group\"='A'\n\n\nExample of a join using `USING`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .using('customer_id') \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" USING \"customer_id\" WHERE \"customers\".\"id\"=5\n\n\nExample of a correlated subquery in the `SELECT`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    last_purchase_at = Query.from_(history).select(\n        history.purchase_at\n    ).where(history.customer_id==customers.customer_id).orderby(\n        history.purchase_at, order=Order.desc\n    ).limit(1)\n    q = Query.from_(customers).select(\n        customers.id, last_purchase_at.as_('last_purchase_at')\n    )\n\n\n.. code-block:: sql\n\n    SELECT\n      \"id\",\n      (SELECT \"history\".\"purchase_at\"\n       FROM \"history\"\n       WHERE \"history\".\"customer_id\" = \"customers\".\"customer_id\"\n       ORDER BY \"history\".\"purchase_at\" DESC\n       LIMIT 1) \"last_purchase_at\"\n    FROM \"customers\"\n\n\nUnions\n\"\"\"\"\"\"\n\nBoth ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with \"UNION`` so |Brand| does not\nprovide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a union query, use either the ``Query.union()`` method or `+` operator with two query instances. For a\nunion all, use ``Query.union_all()`` or the `*` operator.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) + Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" UNION SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nIntersect\n\"\"\"\"\"\"\"\"\"\n\n``INTERSECT`` is supported. Intersects require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a intersected query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a intersect query, use the ``Query.intersect()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    intersected_query = q.intersect(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" INTERSECT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nMinus\n\"\"\"\"\"\n\n``MINUS`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a minus query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a minus query, use either the ``Query.minus()`` method or `-` operator with two query instances.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.minus(r)\n\n    (or)\n\n    minus_query = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) - Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" MINUS SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nEXCEPT\n\"\"\"\"\"\"\n\n``EXCEPT`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a except query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a except query, use the ``Query.except_of()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.except_of(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" EXCEPT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nDate, Time, and Intervals\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nUsing ``pypika.Interval``, queries can be constructed with date arithmetic.  Any combination of intervals can be\nused except for weeks and quarters, which must be used separately and will ignore any other values if selected.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    fruits = Tables('fruits')\n    q = Query.from_(fruits) \\\n        .select(fruits.id, fruits.name) \\\n        .where(fruits.harvest_date + Interval(months=1) < fn.Now())\n\n.. code-block:: sql\n\n    SELECT id,name FROM fruits WHERE harvest_date+INTERVAL 1 MONTH<NOW()\n\n\nTuples\n\"\"\"\"\"\"\n\nTuples are supported through the class ``pypika.Tuple`` but also through the native python tuple wherever possible.\nTuples can be used with ``pypika.Criterion`` in **WHERE** clauses for pairwise comparisons.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nUsing ``pypika.Tuple`` on both sides of the comparison is redundant and |Brand| supports native python tuples.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nTuples can be used in **IN** clauses.\n\n.. code-block:: python\n\n    Query.from_(self.table_abc) \\\n            .select(self.table_abc.foo, self.table_abc.bar) \\\n            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)]))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\") IN ((1,1),(2,2),(3,3))\n\n\nStrings Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nThere are several string operations and function wrappers included in |Brand|.  Function wrappers can be found in the\n``pypika.functions`` package.  In addition, `LIKE` and `REGEX` queries are supported as well.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.like('Mc%')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname LIKE 'Mc%'\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.regex(r'^[abc][a-zA-Z]+&')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname REGEX '^[abc][a-zA-Z]+&';\n\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        fn.Concat(customers.fname, ' ', customers.lname).as_('full_name'),\n    )\n\n.. code-block:: sql\n\n    SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers\n\n\nCustom Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCustom Functions allows us to use any function on queries, as some functions are not covered by PyPika as default, we can appeal\nto Custom functions.\n\n.. code-block:: python\n\n    from pypika import CustomFunction\n\n    customers = Tables('customers')\n    DateDiff = CustomFunction('DATE_DIFF', ['interval', 'start_date', 'end_date'])\n\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n        DateDiff('day', customers.created_date, customers.updated_date)\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,DATE_DIFF('day',created_date,updated_date) FROM customers\n\nCase Statements\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCase statements allow fow a number of conditions to be checked sequentially and return a value for the first condition\nmet or otherwise a default value.  The Case object can be used to chain conditions together along with their output\nusing the ``when`` method and to set the default value using ``else_``.\n\n\n.. code-block:: python\n\n    from pypika import Case, functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        Case()\n           .when(customers.fname == \"Tom\", \"It was Tom\")\n           .when(customers.fname == \"John\", \"It was John\")\n           .else_(\"It was someone else.\").as_('who_was_it')\n    )\n\n\n.. code-block:: sql\n\n    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\"\n\n\nWith Clause\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nWith clause allows give a sub-query block a name, which can be referenced in several places within the main SQL query.\nThe SQL WITH clause is basically a drop-in replacement to the normal sub-query.\n\n.. code-block:: python\n\n    from pypika import Table, AliasedQuery, Query\n\n    customers = Table('customers')\n\n    sub_query = (Query\n                .from_(customers)\n                .select('*'))\n\n    test_query = (Query\n                .with_(sub_query, \"an_alias\")\n                .from_(AliasedQuery(\"an_alias\"))\n                .select('*'))\n\nYou can use as much as `.with_()` as you want.\n\n.. code-block:: sql\n\n    WITH an_alias AS (SELECT * FROM \"customers\") SELECT * FROM an_alias\n\n\nInserting Data\n^^^^^^^^^^^^^^\n\nData can be inserted into tables either by providing the values in the query or by selecting them through another query.\n\nBy default, data can be inserted by providing values for all columns in the order that they are defined in the table.\n\nInsert with values\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\n.. code-block:: python\n\n    customers =  Table('customers')\n\n    q = customers.insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\nMultiple rows of data can be inserted either by chaining the ``insert`` function or passing multiple tuples as args.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com').insert(2, 'John', 'Doe', 'john@example.com')\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert((1, 'Jane', 'Doe', 'jane@example.com'),\n                                     (2, 'John', 'Doe', 'john@example.com'))\n\nInsert with constraint violation handling\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nMySQL\n~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_ignore())\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY IGNORE\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_update(customers.email, Values(customers.email))\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`)\n\n``.on_duplicate_key_update`` works similar to ``.set`` for updating rows, additionally it provides the ``Values``\nwrapper to update to the value specified in the ``INSERT`` clause.\n\nPostgreSQL\n~~~~~~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_nothing()\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO NOTHING\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_update(customers.email, 'bob@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO UPDATE SET \"email\"='bob@example.com'\n\n\nInsert from a SELECT Sub-query\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')\n\n\nTo specify the columns and the order, use the ``columns`` function.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname').insert(1, 'Jane', 'Doe')\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (1,'Jane','Doe','jane@example.com')\n\n\nInserting data with a query works the same as querying data with the additional call to the ``into`` method in the\nbuilder chain.\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).from_(customers).select('*')\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT * FROM customers\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).columns('id', 'fname', 'lname')\n        .from_(customers).select(customers.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT \"id\", \"fname\", \"lname\" FROM customers\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup')\n\n    q = Query.into(orders_backup).columns('id', 'address', 'customer_fname', 'customer_lname')\n        .from_(customers)\n        .join(orders).on(orders.customer_id == customers.id)\n        .select(orders.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n   INSERT INTO \"orders_backup\" (\"id\",\"address\",\"customer_fname\",\"customer_lname\")\n   SELECT \"orders\".\"id\",\"customers\".\"fname\",\"customers\".\"lname\" FROM \"customers\"\n   JOIN \"orders\" ON \"orders\".\"customer_id\"=\"customers\".\"id\"\n\nUpdating Data\n^^^^^^^^^^^^^^\nPyPika allows update queries to be constructed with or without where clauses.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    Query.update(customers).set(customers.last_login, '2017-01-01 10:00:00')\n\n    Query.update(customers).set(customers.lname, 'smith').where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"last_login\"='2017-01-01 10:00:00'\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, profiles = Tables('customers', 'profiles')\n\n    Query.update(customers)\n         .join(profiles).on(profiles.customer_id == customers.id)\n         .set(customers.lname, profiles.lname)\n\n.. code-block:: sql\n\n   UPDATE \"customers\"\n   JOIN \"profiles\" ON \"profiles\".\"customer_id\"=\"customers\".\"id\"\n   SET \"customers\".\"lname\"=\"profiles\".\"lname\"\n\nUsing ``pypika.Table`` alias to perform the update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nUsing ``limit`` for performing update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .limit(2)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' LIMIT 2\n\n\nParametrized Queries\n^^^^^^^^^^^^^^^^^^^^\n\nPyPika allows you to use ``Parameter(str)`` term as a placeholder for parametrized queries.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname')\n        .insert(Parameter(':1'), Parameter(':2'), Parameter(':3'))\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (:1,:2,:3)\n\nThis allows you to build prepared statements, and/or avoid SQL-injection related risks.\n\nDue to the mix of syntax for parameters, depending on connector/driver, it is required that you specify the\nparameter token explicitly or use one of the specialized Parameter types per [PEP-0249](https://www.python.org/dev/peps/pep-0249/#paramstyle):\n``QmarkParameter()``, ``NumericParameter(int)``,  ``NamedParameter(str)``, ``FormatParameter()``, ``PyformatParameter(str)``\n\nAn example of some common SQL parameter styles used in Python drivers are:\n\nPostgreSQL:\n    ``$number`` OR ``%s`` + ``:name`` (depending on driver)\nMySQL:\n    ``%s``\nSQLite:\n    ``?``\nVertica:\n    ``:name``\nOracle:\n    ``:number`` + ``:name``\nMSSQL:\n    ``%(name)s`` OR ``:name`` + ``:number`` (depending on driver)\n\nYou 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.\n\nTemporal support\n^^^^^^^^^^^^^^^^\n\nTemporal criteria can be added to the tables.\n\nSelect\n\"\"\"\"\"\"\n\nHere is a select using system time.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n\nYou can also use between.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'\n\nYou can also use a period range.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n\nFinally you can select for all times:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.all_())).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME ALL\n\nA user defined period can also be used in the following manner.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(t.valid_period.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR \"valid_period\" BETWEEN '2020-01-01' AND '2020-02-01'\n\nJoins\n\"\"\"\"\"\n\nWith joins, when the table object is used when specifying columns, it is\nimportant to use the table from which the temporal constraint was generated.\nThis is because `Table(\"abc\")` is not the same table as `Table(\"abc\").for_(...)`.\nThe following example demonstrates this.\n\n.. code-block:: python\n\n    t0 = Table(\"abc\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    t1 = Table(\"efg\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    query = (\n        Query.from_(t0)\n        .join(t1)\n        .on(t0.foo == t1.bar)\n        .select(\"*\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    JOIN \"efg\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    ON \"abc\".\"foo\"=\"efg\".\"bar\"\n\nUpdate & Deletes\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nAn update can be written as follows:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.update(\n        t.for_portion(\n            SYSTEM_TIME.from_to('2020-01-01', '2020-02-01')\n        )\n    ).set(\"foo\", \"bar\")\n\nThis produces:\n\n.. code-block:: sql\n\n    UPDATE \"abc\"\n    FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n    SET \"foo\"='bar'\n\nHere is a delete:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_portion(t.valid_period.from_to('2020-01-01', '2020-02-01'))\n    ).delete()\n\nThis produces:\n\n.. code-block:: sql\n\n    DELETE FROM \"abc\"\n    FOR PORTION OF \"valid_period\" FROM '2020-01-01' TO '2020-02-01'\n\nCreating Tables\n^^^^^^^^^^^^^^^\n\nThe entry point for creating tables is ``pypika.Query.create_table``, which is used with the class ``pypika.Column``.\nAs with selecting data, first the table should be specified. This can be either a\nstring or a `pypika.Table`. Then the columns, and constraints. Here's an example\nthat demonstrates much of the functionality.\n\n.. code-block:: python\n\n    stmt = Query \\\n        .create_table(\"person\") \\\n        .columns(\n            Column(\"id\", \"INT\", nullable=False),\n            Column(\"first_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"last_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"phone_number\", \"VARCHAR(20)\", nullable=True),\n            Column(\"status\", \"VARCHAR(20)\", nullable=False, default=ValueWrapper(\"NEW\")),\n            Column(\"date_of_birth\", \"DATETIME\")) \\\n        .unique(\"last_name\", \"first_name\") \\\n        .primary_key(\"id\")\n\nThis produces:\n\n.. code-block:: sql\n\n    CREATE TABLE \"person\" (\n        \"id\" INT NOT NULL,\n        \"first_name\" VARCHAR(100) NOT NULL,\n        \"last_name\" VARCHAR(100) NOT NULL,\n        \"phone_number\" VARCHAR(20) NULL,\n        \"status\" VARCHAR(20) NOT NULL DEFAULT 'NEW',\n        \"date_of_birth\" DATETIME,\n        UNIQUE (\"last_name\",\"first_name\"),\n        PRIMARY KEY (\"id\")\n    )\n\nThere is also support for creating a table from a query.\n\n.. code-block:: python\n\n    stmt = Query.create_table(\"names\").as_select(\n        Query.from_(\"person\").select(\"last_name\", \"first_name\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n        CREATE TABLE \"names\" AS (SELECT \"last_name\",\"first_name\" FROM \"person\")\n\n.. _tutorial_end:\n\n\n.. _license_start:\n\n\nLicense\n-------\n\nCopyright 2020 KAYAK Germany, GmbH\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n    http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n\n\nCrafted with \u2665 in Berlin.\n\n.. _license_end:\n\n\n.. _appendix_start:\n\n.. |Brand| replace:: *PyPika*\n\n.. _appendix_end:\n\n.. _available_badges_start:\n\n.. |BuildStatus| image:: https://github.com/kayak/pypika/workflows/Unit%20Tests/badge.svg\n   :target: https://github.com/kayak/pypika/actions\n.. |CoverageStatus| image:: https://coveralls.io/repos/kayak/pypika/badge.svg?branch=master\n   :target: https://coveralls.io/github/kayak/pypika?branch=master\n.. |Codacy| image:: https://api.codacy.com/project/badge/Grade/6d7e44e5628b4839a23da0bd82eaafcf\n   :target: https://www.codacy.com/app/twheys/pypika\n.. |Docs| image:: https://readthedocs.org/projects/pypika/badge/?version=latest\n   :target: http://pypika.readthedocs.io/en/latest/\n.. |PyPi| image:: https://img.shields.io/pypi/v/pypika.svg?style=flat\n   :target: https://pypi.python.org/pypi/pypika\n.. |License| image:: https://img.shields.io/hexpm/l/plug.svg?maxAge=2592000\n   :target: http://www.apache.org/licenses/LICENSE-2.0\n\n.. _available_badges_end:\n",
      "keywords": "pypika python query builder querybuilder sql mysql postgres psql oracle vertica aggregated relational database rdbms business analytics bi data science analysis pandas orm object mapper",
      "platform": [],
      "classifiers": [
        "License :: OSI Approved :: Apache Software License",
        "Development Status :: 5 - Production/Stable",
        "Intended Audience :: Developers",
        "Programming Language :: Python :: 3",
        "Programming Language :: PL/SQL",
        "Topic :: Software Development :: Libraries :: Python Modules",
        "Topic :: Scientific/Engineering :: Information Analysis",
        "Topic :: Scientific/Engineering :: Mathematics",
        "Operating System :: POSIX",
        "Operating System :: MacOS :: MacOS X",
        "Operating System :: Microsoft :: Windows",
        "Environment :: MetaData :: IBM Python Ecosystem"
      ],
      "download_url": "",
      "supported_platform": [],
      "comment": "",
      "provides": [],
      "requires": [],
      "obsoletes": [],
      "project_urls": [],
      "provides_dist": [],
      "obsoletes_dist": [],
      "requires_dist": [],
      "requires_external": [],
      "requires_python": "",
      "description_content_type": "",
      "provides_extras": "",
      "dynamic": "summary",
      "license_expression": "",
      "license_file": "LICENSE.txt",
      "+links": [
        {
          "rel": "releasefile",
          "hash_spec": "sha256=5300581d4db23dc354c47e7789c5a5d40b04769a4ec74f7b26879f35f094fbfe",
          "href": "https://wheels.developerfirst.ibm.com/ppc64le/linux/+f/530/0581d4db23dc3/pypika-0.49.0+ppc64le1-py2.py3-none-any.whl",
          "log": [
            {
              "what": "upload",
              "who": "ppc64le",
              "when": [
                2026,
                3,
                16,
                9,
                32,
                29
              ],
              "dst": "ppc64le/linux"
            }
          ]
        }
      ]
    },
    "0.48.9+ppc64le1": {
      "name": "PyPika",
      "version": "0.48.9+ppc64le1",
      "metadata_version": "2.4",
      "summary": "A SQL query builder API for Python",
      "home_page": "https://github.com/kayak/pypika",
      "author": "Timothy Heys",
      "author_email": "theys@kayak.com",
      "maintainer": "",
      "maintainer_email": "",
      "license": "Apache License Version 2.0",
      "description": "PyPika - Python Query Builder\n=============================\n\n.. _intro_start:\n\n|BuildStatus|  |CoverageStatus|  |Codacy|  |Docs|  |PyPi|  |License|\n\nAbstract\n--------\n\nWhat is |Brand|?\n\n|Brand| is a Python API for building SQL queries. The motivation behind |Brand| is to provide a simple interface for\nbuilding SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, |Brand|\nleverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also\neasily extended to take full advantage of specific features of SQL database vendors.\n\nWhat are the design goals for |Brand|?\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n|Brand| is a fast, expressive and flexible way to replace handwritten SQL (or even ORM for the courageous souls amongst you).\nValidation of SQL correctness is not an explicit goal of |Brand|. With such a large number of\nSQL 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\nyour SQL database - just as you would have if you were writing SQL yourself.\n\n.. _intro_end:\n\nRead the docs: http://pypika.readthedocs.io/en/latest/\n\nInstallation\n------------\n\n.. _installation_start:\n\n|Brand| supports python ``3.6+``.  It may also work on pypy, cython, and jython, but is not being tested for these versions.\n\nTo install |Brand| run the following command:\n\n.. code-block:: bash\n\n    pip install pypika\n\n\n.. _installation_end:\n\n\nTutorial\n--------\n\n.. _tutorial_start:\n\nThe main classes in pypika are ``pypika.Query``, ``pypika.Table``, and ``pypika.Field``.\n\n.. code-block:: python\n\n    from pypika import Query, Table, Field\n\n\nSelecting Data\n^^^^^^^^^^^^^^\n\nThe entry point for building queries is ``pypika.Query``.  In order to select columns from a table, the table must\nfirst be added to the query.  For simple queries with only one table, tables and columns can be references using\nstrings.  For more sophisticated queries a ``pypika.Table`` must be used.\n\n.. code-block:: python\n\n    q = Query.from_('customers').select('id', 'fname', 'lname', 'phone')\n\nTo convert the query into raw SQL, it can be cast to a string.\n\n.. code-block:: python\n\n    str(q)\n\nAlternatively, you can use the `Query.get_sql()` function:\n\n.. code-block:: python\n\n    q.get_sql()\n\n\nTables, Columns, Schemas, and Databases\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nIn simple queries like the above example, columns in the \"from\" table can be referenced by passing string names into\nthe ``select`` query builder function. In more complex examples, the ``pypika.Table`` class should be used. Columns can be\nreferenced as attributes on instances of ``pypika.Table``.\n\n.. code-block:: python\n\n    from pypika import Table, Query\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)\n\nBoth of the above examples result in the following SQL:\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers\n\nAn alias for the table can be given using the ``.as_`` function on ``pypika.Table``\n\n.. code-block:: sql\n\n    customers = Table('x_view_customers').as_('customers')\n    q = Query.from_(customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM x_view_customers customers\n\nA schema can also be specified. Tables can be referenced as attributes on the schema.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Schema\n\n    views = Schema('views')\n    q = Query.from_(views.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM views.customers\n\nAlso references to databases can be used. Schemas can be referenced as attributes on the database.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Database\n\n    my_db = Database('my_db')\n    q = Query.from_(my_db.analytics.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM my_db.analytics.customers\n\n\nResults can be ordered by using the following syntax:\n\n.. code-block:: python\n\n    from pypika import Order\n    Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)\n\nThis results in the following SQL:\n\n.. code-block:: sql\n\n    SELECT \"id\",\"fname\",\"lname\",\"phone\" FROM \"customers\" ORDER BY \"id\" DESC\n\nArithmetic\n\"\"\"\"\"\"\"\"\"\"\n\nArithmetic expressions can also be constructed using pypika.  Operators such as `+`, `-`, `*`, and `/` are implemented\nby ``pypika.Field`` which can be used simply with a ``pypika.Table`` or directly.\n\n.. code-block:: python\n\n    from pypika import Field\n\n    q = Query.from_('account').select(\n        Field('revenue') - Field('cost')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nUsing ``pypika.Table``\n\n.. code-block:: python\n\n    accounts = Table('accounts')\n    q = Query.from_(accounts).select(\n        accounts.revenue - accounts.cost\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nAn alias can also be used for fields and expressions.\n\n.. code-block:: sql\n\n    q = Query.from_(accounts).select(\n        (accounts.revenue - accounts.cost).as_('profit')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost profit FROM accounts\n\nMore arithmetic examples\n\n.. code-block:: python\n\n    table = Table('table')\n    q = Query.from_(table).select(\n        table.foo + table.bar,\n        table.foo - table.bar,\n        table.foo * table.bar,\n        table.foo / table.bar,\n        (table.foo+table.bar) / table.fiz,\n    )\n\n.. code-block:: sql\n\n    SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table\n\n\nFiltering\n\"\"\"\"\"\"\"\"\"\n\nQueries can be filtered with ``pypika.Criterion`` by using equality or inequality operators\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'\n\nQuery methods such as select, where, groupby, and orderby can be called multiple times.  Multiple calls to the where\nmethod will add additional conditions as\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.fname == 'Max'\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'\n\nFilters such as IN and BETWEEN are also supported\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,customers.fname\n    ).where(\n        customers.age[18:65] & customers.status.isin(['new', 'active'])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')\n\nFiltering with complex criteria can be created using boolean symbols ``&``, ``|``, and ``^``.\n\nAND\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) & (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 AND lname='Mustermann'\n\nOR\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) | (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'\n\nXOR\n\n.. code-block:: python\n\n customers = Table('customers')\n q = Query.from_(customers).select(\n     customers.id, customers.fname, customers.lname, customers.phone\n ).where(\n     (customers.age >= 18) ^ customers.is_registered\n )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered\n\n\nConvenience Methods\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nIn the `Criterion` class, there are the static methods `any` and `all` that allow building chains AND and OR expressions with a list of terms.\n\n.. code-block:: python\n\n    from pypika import Criterion\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname\n    ).where(\n        Criterion.all([\n            customers.is_registered,\n            customers.age >= 18,\n            customers.lname == \"Jones\",\n        ])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE is_registered AND age>=18 AND lname = \"Jones\"\n\n\nGrouping and Aggregating\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nGrouping allows for aggregated results and works similar to ``SELECT`` clauses.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Table('customers')\n    q = Query \\\n        .from_(customers) \\\n        .where(customers.age >= 18) \\\n        .groupby(customers.id) \\\n        .select(customers.id, fn.Sum(customers.revenue))\n\n.. code-block:: sql\n\n    SELECT id,SUM(\"revenue\") FROM \"customers\" WHERE \"age\">=18 GROUP BY \"id\"\n\nAfter adding a ``GROUP BY`` clause to a query, the ``HAVING`` clause becomes available.  The method\n``Query.having()`` takes a ``Criterion`` parameter similar to the method ``Query.where()``.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    payments = Table('payments')\n    q = Query \\\n        .from_(payments) \\\n        .where(payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]) \\\n        .groupby(payments.customer_id) \\\n        .having(fn.Sum(payments.total) >= 1000) \\\n        .select(payments.customer_id, fn.Sum(payments.total))\n\n.. code-block:: sql\n\n    SELECT customer_id,SUM(total) FROM payments\n    WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'\n    GROUP BY customer_id HAVING SUM(total)>=1000\n\n\nJoining Tables and Subqueries\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nTables and subqueries can be joined to any query using the ``Query.join()`` method.  Joins can be performed with either\na ``USING`` or ``ON`` clauses.  The ``USING`` clause can be used when both tables/subqueries contain the same field and\nthe ``ON`` clause can be used with a criterion. To perform a join, ``...join()`` can be chained but then must be\nfollowed immediately by ``...on(<criterion>)`` or ``...using(*field)``.\n\n\nJoin Types\n~~~~~~~~~~\n\nAll join types are supported by |Brand|.\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .join(join_table, JoinType.left)\n        ...\n\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .left_join(join_table) \\\n        .left_outer_join(join_table) \\\n        .right_join(join_table) \\\n        .right_outer_join(join_table) \\\n        .inner_join(join_table) \\\n        .outer_join(join_table) \\\n        .full_outer_join(join_table) \\\n        .cross_join(join_table) \\\n        .hash_join(join_table) \\\n        ...\n\nSee the list of join types here ``pypika.enums.JoinTypes``\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on(history.customer_id == customers.id) \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"id\" WHERE \"customers\".\"id\"=5\n\nAs a shortcut, the ``Query.join().on_field()`` function is provided for joining the (first) table in the ``FROM`` clause\nwith the joined table when the field name(s) are the same in both tables.\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on_field('customer_id', 'group') \\\n        .select(history.star) \\\n        .where(customers.group == 'A')\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"customer_id\" AND \"history\".\"group\"=\"customers\".\"group\" WHERE \"customers\".\"group\"='A'\n\n\nExample of a join using `USING`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .using('customer_id') \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" USING \"customer_id\" WHERE \"customers\".\"id\"=5\n\n\nExample of a correlated subquery in the `SELECT`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    last_purchase_at = Query.from_(history).select(\n        history.purchase_at\n    ).where(history.customer_id==customers.customer_id).orderby(\n        history.purchase_at, order=Order.desc\n    ).limit(1)\n    q = Query.from_(customers).select(\n        customers.id, last_purchase_at.as_('last_purchase_at')\n    )\n\n\n.. code-block:: sql\n\n    SELECT\n      \"id\",\n      (SELECT \"history\".\"purchase_at\"\n       FROM \"history\"\n       WHERE \"history\".\"customer_id\" = \"customers\".\"customer_id\"\n       ORDER BY \"history\".\"purchase_at\" DESC\n       LIMIT 1) \"last_purchase_at\"\n    FROM \"customers\"\n\n\nUnions\n\"\"\"\"\"\"\n\nBoth ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with \"UNION`` so |Brand| does not\nprovide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a union query, use either the ``Query.union()`` method or `+` operator with two query instances. For a\nunion all, use ``Query.union_all()`` or the `*` operator.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) + Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" UNION SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nIntersect\n\"\"\"\"\"\"\"\"\"\n\n``INTERSECT`` is supported. Intersects require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a intersected query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a intersect query, use the ``Query.intersect()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    intersected_query = q.intersect(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" INTERSECT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nMinus\n\"\"\"\"\"\n\n``MINUS`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a minus query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a minus query, use either the ``Query.minus()`` method or `-` operator with two query instances.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.minus(r)\n\n    (or)\n\n    minus_query = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) - Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" MINUS SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nEXCEPT\n\"\"\"\"\"\"\n\n``EXCEPT`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a except query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a except query, use the ``Query.except_of()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.except_of(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" EXCEPT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nDate, Time, and Intervals\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nUsing ``pypika.Interval``, queries can be constructed with date arithmetic.  Any combination of intervals can be\nused except for weeks and quarters, which must be used separately and will ignore any other values if selected.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    fruits = Tables('fruits')\n    q = Query.from_(fruits) \\\n        .select(fruits.id, fruits.name) \\\n        .where(fruits.harvest_date + Interval(months=1) < fn.Now())\n\n.. code-block:: sql\n\n    SELECT id,name FROM fruits WHERE harvest_date+INTERVAL 1 MONTH<NOW()\n\n\nTuples\n\"\"\"\"\"\"\n\nTuples are supported through the class ``pypika.Tuple`` but also through the native python tuple wherever possible.\nTuples can be used with ``pypika.Criterion`` in **WHERE** clauses for pairwise comparisons.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nUsing ``pypika.Tuple`` on both sides of the comparison is redundant and |Brand| supports native python tuples.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nTuples can be used in **IN** clauses.\n\n.. code-block:: python\n\n    Query.from_(self.table_abc) \\\n            .select(self.table_abc.foo, self.table_abc.bar) \\\n            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)]))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\") IN ((1,1),(2,2),(3,3))\n\n\nStrings Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nThere are several string operations and function wrappers included in |Brand|.  Function wrappers can be found in the\n``pypika.functions`` package.  In addition, `LIKE` and `REGEX` queries are supported as well.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.like('Mc%')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname LIKE 'Mc%'\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.regex(r'^[abc][a-zA-Z]+&')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname REGEX '^[abc][a-zA-Z]+&';\n\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        fn.Concat(customers.fname, ' ', customers.lname).as_('full_name'),\n    )\n\n.. code-block:: sql\n\n    SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers\n\n\nCustom Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCustom Functions allows us to use any function on queries, as some functions are not covered by PyPika as default, we can appeal\nto Custom functions.\n\n.. code-block:: python\n\n    from pypika import CustomFunction\n\n    customers = Tables('customers')\n    DateDiff = CustomFunction('DATE_DIFF', ['interval', 'start_date', 'end_date'])\n\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n        DateDiff('day', customers.created_date, customers.updated_date)\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,DATE_DIFF('day',created_date,updated_date) FROM customers\n\nCase Statements\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCase statements allow fow a number of conditions to be checked sequentially and return a value for the first condition\nmet or otherwise a default value.  The Case object can be used to chain conditions together along with their output\nusing the ``when`` method and to set the default value using ``else_``.\n\n\n.. code-block:: python\n\n    from pypika import Case, functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        Case()\n           .when(customers.fname == \"Tom\", \"It was Tom\")\n           .when(customers.fname == \"John\", \"It was John\")\n           .else_(\"It was someone else.\").as_('who_was_it')\n    )\n\n\n.. code-block:: sql\n\n    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\"\n\n\nWith Clause\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nWith clause allows give a sub-query block a name, which can be referenced in several places within the main SQL query.\nThe SQL WITH clause is basically a drop-in replacement to the normal sub-query.\n\n.. code-block:: python\n\n    from pypika import Table, AliasedQuery, Query\n\n    customers = Table('customers')\n\n    sub_query = (Query\n                .from_(customers)\n                .select('*'))\n\n    test_query = (Query\n                .with_(sub_query, \"an_alias\")\n                .from_(AliasedQuery(\"an_alias\"))\n                .select('*'))\n\nYou can use as much as `.with_()` as you want.\n\n.. code-block:: sql\n\n    WITH an_alias AS (SELECT * FROM \"customers\") SELECT * FROM an_alias\n\n\nInserting Data\n^^^^^^^^^^^^^^\n\nData can be inserted into tables either by providing the values in the query or by selecting them through another query.\n\nBy default, data can be inserted by providing values for all columns in the order that they are defined in the table.\n\nInsert with values\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\n.. code-block:: python\n\n    customers =  Table('customers')\n\n    q = customers.insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\nMultiple rows of data can be inserted either by chaining the ``insert`` function or passing multiple tuples as args.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com').insert(2, 'John', 'Doe', 'john@example.com')\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert((1, 'Jane', 'Doe', 'jane@example.com'),\n                                     (2, 'John', 'Doe', 'john@example.com'))\n\nInsert with constraint violation handling\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nMySQL\n~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_ignore())\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY IGNORE\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_update(customers.email, Values(customers.email))\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`)\n\n``.on_duplicate_key_update`` works similar to ``.set`` for updating rows, additionally it provides the ``Values``\nwrapper to update to the value specified in the ``INSERT`` clause.\n\nPostgreSQL\n~~~~~~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_nothing()\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO NOTHING\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_update(customers.email, 'bob@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO UPDATE SET \"email\"='bob@example.com'\n\n\nInsert from a SELECT Sub-query\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')\n\n\nTo specify the columns and the order, use the ``columns`` function.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname').insert(1, 'Jane', 'Doe')\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (1,'Jane','Doe','jane@example.com')\n\n\nInserting data with a query works the same as querying data with the additional call to the ``into`` method in the\nbuilder chain.\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).from_(customers).select('*')\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT * FROM customers\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).columns('id', 'fname', 'lname')\n        .from_(customers).select(customers.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT \"id\", \"fname\", \"lname\" FROM customers\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup')\n\n    q = Query.into(orders_backup).columns('id', 'address', 'customer_fname', 'customer_lname')\n        .from_(customers)\n        .join(orders).on(orders.customer_id == customers.id)\n        .select(orders.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n   INSERT INTO \"orders_backup\" (\"id\",\"address\",\"customer_fname\",\"customer_lname\")\n   SELECT \"orders\".\"id\",\"customers\".\"fname\",\"customers\".\"lname\" FROM \"customers\"\n   JOIN \"orders\" ON \"orders\".\"customer_id\"=\"customers\".\"id\"\n\nUpdating Data\n^^^^^^^^^^^^^^\nPyPika allows update queries to be constructed with or without where clauses.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    Query.update(customers).set(customers.last_login, '2017-01-01 10:00:00')\n\n    Query.update(customers).set(customers.lname, 'smith').where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"last_login\"='2017-01-01 10:00:00'\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, profiles = Tables('customers', 'profiles')\n\n    Query.update(customers)\n         .join(profiles).on(profiles.customer_id == customers.id)\n         .set(customers.lname, profiles.lname)\n\n.. code-block:: sql\n\n   UPDATE \"customers\"\n   JOIN \"profiles\" ON \"profiles\".\"customer_id\"=\"customers\".\"id\"\n   SET \"customers\".\"lname\"=\"profiles\".\"lname\"\n\nUsing ``pypika.Table`` alias to perform the update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nUsing ``limit`` for performing update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .limit(2)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' LIMIT 2\n\n\nParametrized Queries\n^^^^^^^^^^^^^^^^^^^^\n\nPyPika allows you to use ``Parameter(str)`` term as a placeholder for parametrized queries.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname')\n        .insert(Parameter(':1'), Parameter(':2'), Parameter(':3'))\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (:1,:2,:3)\n\nThis allows you to build prepared statements, and/or avoid SQL-injection related risks.\n\nDue to the mix of syntax for parameters, depending on connector/driver, it is required that you specify the\nparameter token explicitly or use one of the specialized Parameter types per [PEP-0249](https://www.python.org/dev/peps/pep-0249/#paramstyle):\n``QmarkParameter()``, ``NumericParameter(int)``,  ``NamedParameter(str)``, ``FormatParameter()``, ``PyformatParameter(str)``\n\nAn example of some common SQL parameter styles used in Python drivers are:\n\nPostgreSQL:\n    ``$number`` OR ``%s`` + ``:name`` (depending on driver)\nMySQL:\n    ``%s``\nSQLite:\n    ``?``\nVertica:\n    ``:name``\nOracle:\n    ``:number`` + ``:name``\nMSSQL:\n    ``%(name)s`` OR ``:name`` + ``:number`` (depending on driver)\n\nYou 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.\n\nTemporal support\n^^^^^^^^^^^^^^^^\n\nTemporal criteria can be added to the tables.\n\nSelect\n\"\"\"\"\"\"\n\nHere is a select using system time.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n\nYou can also use between.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'\n\nYou can also use a period range.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n\nFinally you can select for all times:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.all_())).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME ALL\n\nA user defined period can also be used in the following manner.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(t.valid_period.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR \"valid_period\" BETWEEN '2020-01-01' AND '2020-02-01'\n\nJoins\n\"\"\"\"\"\n\nWith joins, when the table object is used when specifying columns, it is\nimportant to use the table from which the temporal constraint was generated.\nThis is because `Table(\"abc\")` is not the same table as `Table(\"abc\").for_(...)`.\nThe following example demonstrates this.\n\n.. code-block:: python\n\n    t0 = Table(\"abc\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    t1 = Table(\"efg\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    query = (\n        Query.from_(t0)\n        .join(t1)\n        .on(t0.foo == t1.bar)\n        .select(\"*\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    JOIN \"efg\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    ON \"abc\".\"foo\"=\"efg\".\"bar\"\n\nUpdate & Deletes\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nAn update can be written as follows:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.update(\n        t.for_portion(\n            SYSTEM_TIME.from_to('2020-01-01', '2020-02-01')\n        )\n    ).set(\"foo\", \"bar\")\n\nThis produces:\n\n.. code-block:: sql\n\n    UPDATE \"abc\"\n    FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n    SET \"foo\"='bar'\n\nHere is a delete:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_portion(t.valid_period.from_to('2020-01-01', '2020-02-01'))\n    ).delete()\n\nThis produces:\n\n.. code-block:: sql\n\n    DELETE FROM \"abc\"\n    FOR PORTION OF \"valid_period\" FROM '2020-01-01' TO '2020-02-01'\n\nCreating Tables\n^^^^^^^^^^^^^^^\n\nThe entry point for creating tables is ``pypika.Query.create_table``, which is used with the class ``pypika.Column``.\nAs with selecting data, first the table should be specified. This can be either a\nstring or a `pypika.Table`. Then the columns, and constraints. Here's an example\nthat demonstrates much of the functionality.\n\n.. code-block:: python\n\n    stmt = Query \\\n        .create_table(\"person\") \\\n        .columns(\n            Column(\"id\", \"INT\", nullable=False),\n            Column(\"first_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"last_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"phone_number\", \"VARCHAR(20)\", nullable=True),\n            Column(\"status\", \"VARCHAR(20)\", nullable=False, default=ValueWrapper(\"NEW\")),\n            Column(\"date_of_birth\", \"DATETIME\")) \\\n        .unique(\"last_name\", \"first_name\") \\\n        .primary_key(\"id\")\n\nThis produces:\n\n.. code-block:: sql\n\n    CREATE TABLE \"person\" (\n        \"id\" INT NOT NULL,\n        \"first_name\" VARCHAR(100) NOT NULL,\n        \"last_name\" VARCHAR(100) NOT NULL,\n        \"phone_number\" VARCHAR(20) NULL,\n        \"status\" VARCHAR(20) NOT NULL DEFAULT 'NEW',\n        \"date_of_birth\" DATETIME,\n        UNIQUE (\"last_name\",\"first_name\"),\n        PRIMARY KEY (\"id\")\n    )\n\nThere is also support for creating a table from a query.\n\n.. code-block:: python\n\n    stmt = Query.create_table(\"names\").as_select(\n        Query.from_(\"person\").select(\"last_name\", \"first_name\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n        CREATE TABLE \"names\" AS (SELECT \"last_name\",\"first_name\" FROM \"person\")\n\n.. _tutorial_end:\n\n\n.. _license_start:\n\n\nLicense\n-------\n\nCopyright 2020 KAYAK Germany, GmbH\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n    http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n\n\nCrafted with \u2665 in Berlin.\n\n.. _license_end:\n\n\n.. _appendix_start:\n\n.. |Brand| replace:: *PyPika*\n\n.. _appendix_end:\n\n.. _available_badges_start:\n\n.. |BuildStatus| image:: https://github.com/kayak/pypika/workflows/Unit%20Tests/badge.svg\n   :target: https://github.com/kayak/pypika/actions\n.. |CoverageStatus| image:: https://coveralls.io/repos/kayak/pypika/badge.svg?branch=master\n   :target: https://coveralls.io/github/kayak/pypika?branch=master\n.. |Codacy| image:: https://api.codacy.com/project/badge/Grade/6d7e44e5628b4839a23da0bd82eaafcf\n   :target: https://www.codacy.com/app/twheys/pypika\n.. |Docs| image:: https://readthedocs.org/projects/pypika/badge/?version=latest\n   :target: http://pypika.readthedocs.io/en/latest/\n.. |PyPi| image:: https://img.shields.io/pypi/v/pypika.svg?style=flat\n   :target: https://pypi.python.org/pypi/pypika\n.. |License| image:: https://img.shields.io/hexpm/l/plug.svg?maxAge=2592000\n   :target: http://www.apache.org/licenses/LICENSE-2.0\n\n.. _available_badges_end:\n",
      "keywords": "pypika python query builder querybuilder sql mysql postgres psql oracle vertica aggregated relational database rdbms business analytics bi data science analysis pandas orm object mapper",
      "platform": [],
      "classifiers": [
        "License :: OSI Approved :: Apache Software License",
        "Development Status :: 5 - Production/Stable",
        "Intended Audience :: Developers",
        "Programming Language :: Python :: 3",
        "Programming Language :: PL/SQL",
        "Topic :: Software Development :: Libraries :: Python Modules",
        "Topic :: Scientific/Engineering :: Information Analysis",
        "Topic :: Scientific/Engineering :: Mathematics",
        "Operating System :: POSIX",
        "Operating System :: MacOS :: MacOS X",
        "Operating System :: Microsoft :: Windows",
        "Environment :: MetaData :: IBM Python Ecosystem"
      ],
      "download_url": "",
      "supported_platform": [],
      "comment": "",
      "provides": [],
      "requires": [],
      "obsoletes": [],
      "project_urls": [],
      "provides_dist": [],
      "obsoletes_dist": [],
      "requires_dist": [],
      "requires_external": [],
      "requires_python": "",
      "description_content_type": "",
      "provides_extras": "",
      "dynamic": "summary",
      "license_expression": "",
      "license_file": "LICENSE.txt",
      "+links": [
        {
          "rel": "releasefile",
          "hash_spec": "sha256=727f8b22c594ef8c2e443fdf91673c252aa643d816e452723179f7619a29c349",
          "href": "https://wheels.developerfirst.ibm.com/ppc64le/linux/+f/727/f8b22c594ef8c/pypika-0.48.9+ppc64le1-py2.py3-none-any.whl",
          "log": [
            {
              "what": "upload",
              "who": "ppc64le",
              "when": [
                2026,
                3,
                16,
                9,
                32,
                28
              ],
              "dst": "ppc64le/linux"
            }
          ]
        }
      ]
    },
    "0.49.0": {
      "name": "PyPika",
      "version": "0.49.0",
      "metadata_version": "2.4",
      "summary": "A SQL query builder API for Python",
      "home_page": "https://github.com/kayak/pypika",
      "author": "Timothy Heys",
      "author_email": "theys@kayak.com",
      "maintainer": "",
      "maintainer_email": "",
      "license": "Apache License Version 2.0",
      "description": "PyPika - Python Query Builder\n=============================\n\n.. _intro_start:\n\n|BuildStatus|  |CoverageStatus|  |Codacy|  |Docs|  |PyPi|  |License|\n\nAbstract\n--------\n\nWhat is |Brand|?\n\n|Brand| is a Python API for building SQL queries. The motivation behind |Brand| is to provide a simple interface for\nbuilding SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, |Brand|\nleverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also\neasily extended to take full advantage of specific features of SQL database vendors.\n\nWhat are the design goals for |Brand|?\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n|Brand| is a fast, expressive and flexible way to replace handwritten SQL (or even ORM for the courageous souls amongst you).\nValidation of SQL correctness is not an explicit goal of |Brand|. With such a large number of\nSQL 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\nyour SQL database - just as you would have if you were writing SQL yourself.\n\n.. _intro_end:\n\nRead the docs: http://pypika.readthedocs.io/en/latest/\n\nInstallation\n------------\n\n.. _installation_start:\n\n|Brand| supports python ``3.6+``.  It may also work on pypy, cython, and jython, but is not being tested for these versions.\n\nTo install |Brand| run the following command:\n\n.. code-block:: bash\n\n    pip install pypika\n\n\n.. _installation_end:\n\n\nTutorial\n--------\n\n.. _tutorial_start:\n\nThe main classes in pypika are ``pypika.Query``, ``pypika.Table``, and ``pypika.Field``.\n\n.. code-block:: python\n\n    from pypika import Query, Table, Field\n\n\nSelecting Data\n^^^^^^^^^^^^^^\n\nThe entry point for building queries is ``pypika.Query``.  In order to select columns from a table, the table must\nfirst be added to the query.  For simple queries with only one table, tables and columns can be references using\nstrings.  For more sophisticated queries a ``pypika.Table`` must be used.\n\n.. code-block:: python\n\n    q = Query.from_('customers').select('id', 'fname', 'lname', 'phone')\n\nTo convert the query into raw SQL, it can be cast to a string.\n\n.. code-block:: python\n\n    str(q)\n\nAlternatively, you can use the `Query.get_sql()` function:\n\n.. code-block:: python\n\n    q.get_sql()\n\n\nTables, Columns, Schemas, and Databases\n^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nIn simple queries like the above example, columns in the \"from\" table can be referenced by passing string names into\nthe ``select`` query builder function. In more complex examples, the ``pypika.Table`` class should be used. Columns can be\nreferenced as attributes on instances of ``pypika.Table``.\n\n.. code-block:: python\n\n    from pypika import Table, Query\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)\n\nBoth of the above examples result in the following SQL:\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers\n\nAn alias for the table can be given using the ``.as_`` function on ``pypika.Table``\n\n.. code-block:: sql\n\n    customers = Table('x_view_customers').as_('customers')\n    q = Query.from_(customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM x_view_customers customers\n\nA schema can also be specified. Tables can be referenced as attributes on the schema.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Schema\n\n    views = Schema('views')\n    q = Query.from_(views.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM views.customers\n\nAlso references to databases can be used. Schemas can be referenced as attributes on the database.\n\n.. code-block:: sql\n\n    from pypika import Table, Query, Database\n\n    my_db = Database('my_db')\n    q = Query.from_(my_db.analytics.customers).select(customers.id, customers.phone)\n\n.. code-block:: sql\n\n    SELECT id,phone FROM my_db.analytics.customers\n\n\nResults can be ordered by using the following syntax:\n\n.. code-block:: python\n\n    from pypika import Order\n    Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)\n\nThis results in the following SQL:\n\n.. code-block:: sql\n\n    SELECT \"id\",\"fname\",\"lname\",\"phone\" FROM \"customers\" ORDER BY \"id\" DESC\n\nArithmetic\n\"\"\"\"\"\"\"\"\"\"\n\nArithmetic expressions can also be constructed using pypika.  Operators such as `+`, `-`, `*`, and `/` are implemented\nby ``pypika.Field`` which can be used simply with a ``pypika.Table`` or directly.\n\n.. code-block:: python\n\n    from pypika import Field\n\n    q = Query.from_('account').select(\n        Field('revenue') - Field('cost')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nUsing ``pypika.Table``\n\n.. code-block:: python\n\n    accounts = Table('accounts')\n    q = Query.from_(accounts).select(\n        accounts.revenue - accounts.cost\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost FROM accounts\n\nAn alias can also be used for fields and expressions.\n\n.. code-block:: sql\n\n    q = Query.from_(accounts).select(\n        (accounts.revenue - accounts.cost).as_('profit')\n    )\n\n.. code-block:: sql\n\n    SELECT revenue-cost profit FROM accounts\n\nMore arithmetic examples\n\n.. code-block:: python\n\n    table = Table('table')\n    q = Query.from_(table).select(\n        table.foo + table.bar,\n        table.foo - table.bar,\n        table.foo * table.bar,\n        table.foo / table.bar,\n        (table.foo+table.bar) / table.fiz,\n    )\n\n.. code-block:: sql\n\n    SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table\n\n\nFiltering\n\"\"\"\"\"\"\"\"\"\n\nQueries can be filtered with ``pypika.Criterion`` by using equality or inequality operators\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'\n\nQuery methods such as select, where, groupby, and orderby can be called multiple times.  Multiple calls to the where\nmethod will add additional conditions as\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        customers.fname == 'Max'\n    ).where(\n        customers.lname == 'Mustermann'\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'\n\nFilters such as IN and BETWEEN are also supported\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,customers.fname\n    ).where(\n        customers.age[18:65] & customers.status.isin(['new', 'active'])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')\n\nFiltering with complex criteria can be created using boolean symbols ``&``, ``|``, and ``^``.\n\nAND\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) & (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 AND lname='Mustermann'\n\nOR\n\n.. code-block:: python\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id, customers.fname, customers.lname, customers.phone\n    ).where(\n        (customers.age >= 18) | (customers.lname == 'Mustermann')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'\n\nXOR\n\n.. code-block:: python\n\n customers = Table('customers')\n q = Query.from_(customers).select(\n     customers.id, customers.fname, customers.lname, customers.phone\n ).where(\n     (customers.age >= 18) ^ customers.is_registered\n )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered\n\n\nConvenience Methods\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nIn the `Criterion` class, there are the static methods `any` and `all` that allow building chains AND and OR expressions with a list of terms.\n\n.. code-block:: python\n\n    from pypika import Criterion\n\n    customers = Table('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname\n    ).where(\n        Criterion.all([\n            customers.is_registered,\n            customers.age >= 18,\n            customers.lname == \"Jones\",\n        ])\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname FROM customers WHERE is_registered AND age>=18 AND lname = \"Jones\"\n\n\nGrouping and Aggregating\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nGrouping allows for aggregated results and works similar to ``SELECT`` clauses.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Table('customers')\n    q = Query \\\n        .from_(customers) \\\n        .where(customers.age >= 18) \\\n        .groupby(customers.id) \\\n        .select(customers.id, fn.Sum(customers.revenue))\n\n.. code-block:: sql\n\n    SELECT id,SUM(\"revenue\") FROM \"customers\" WHERE \"age\">=18 GROUP BY \"id\"\n\nAfter adding a ``GROUP BY`` clause to a query, the ``HAVING`` clause becomes available.  The method\n``Query.having()`` takes a ``Criterion`` parameter similar to the method ``Query.where()``.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    payments = Table('payments')\n    q = Query \\\n        .from_(payments) \\\n        .where(payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]) \\\n        .groupby(payments.customer_id) \\\n        .having(fn.Sum(payments.total) >= 1000) \\\n        .select(payments.customer_id, fn.Sum(payments.total))\n\n.. code-block:: sql\n\n    SELECT customer_id,SUM(total) FROM payments\n    WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'\n    GROUP BY customer_id HAVING SUM(total)>=1000\n\n\nJoining Tables and Subqueries\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nTables and subqueries can be joined to any query using the ``Query.join()`` method.  Joins can be performed with either\na ``USING`` or ``ON`` clauses.  The ``USING`` clause can be used when both tables/subqueries contain the same field and\nthe ``ON`` clause can be used with a criterion. To perform a join, ``...join()`` can be chained but then must be\nfollowed immediately by ``...on(<criterion>)`` or ``...using(*field)``.\n\n\nJoin Types\n~~~~~~~~~~\n\nAll join types are supported by |Brand|.\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .join(join_table, JoinType.left)\n        ...\n\n\n.. code-block:: python\n\n    Query \\\n        .from_(base_table)\n        ...\n        .left_join(join_table) \\\n        .left_outer_join(join_table) \\\n        .right_join(join_table) \\\n        .right_outer_join(join_table) \\\n        .inner_join(join_table) \\\n        .outer_join(join_table) \\\n        .full_outer_join(join_table) \\\n        .cross_join(join_table) \\\n        .hash_join(join_table) \\\n        ...\n\nSee the list of join types here ``pypika.enums.JoinTypes``\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on(history.customer_id == customers.id) \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"id\" WHERE \"customers\".\"id\"=5\n\nAs a shortcut, the ``Query.join().on_field()`` function is provided for joining the (first) table in the ``FROM`` clause\nwith the joined table when the field name(s) are the same in both tables.\n\nExample of a join using `ON`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .on_field('customer_id', 'group') \\\n        .select(history.star) \\\n        .where(customers.group == 'A')\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" ON \"history\".\"customer_id\"=\"customers\".\"customer_id\" AND \"history\".\"group\"=\"customers\".\"group\" WHERE \"customers\".\"group\"='A'\n\n\nExample of a join using `USING`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    q = Query \\\n        .from_(history) \\\n        .join(customers) \\\n        .using('customer_id') \\\n        .select(history.star) \\\n        .where(customers.id == 5)\n\n\n.. code-block:: sql\n\n    SELECT \"history\".* FROM \"history\" JOIN \"customers\" USING \"customer_id\" WHERE \"customers\".\"id\"=5\n\n\nExample of a correlated subquery in the `SELECT`\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n.. code-block:: python\n\n    history, customers = Tables('history', 'customers')\n    last_purchase_at = Query.from_(history).select(\n        history.purchase_at\n    ).where(history.customer_id==customers.customer_id).orderby(\n        history.purchase_at, order=Order.desc\n    ).limit(1)\n    q = Query.from_(customers).select(\n        customers.id, last_purchase_at.as_('last_purchase_at')\n    )\n\n\n.. code-block:: sql\n\n    SELECT\n      \"id\",\n      (SELECT \"history\".\"purchase_at\"\n       FROM \"history\"\n       WHERE \"history\".\"customer_id\" = \"customers\".\"customer_id\"\n       ORDER BY \"history\".\"purchase_at\" DESC\n       LIMIT 1) \"last_purchase_at\"\n    FROM \"customers\"\n\n\nUnions\n\"\"\"\"\"\"\n\nBoth ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with \"UNION`` so |Brand| does not\nprovide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a union query, use either the ``Query.union()`` method or `+` operator with two query instances. For a\nunion all, use ``Query.union_all()`` or the `*` operator.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) + Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" UNION SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nIntersect\n\"\"\"\"\"\"\"\"\"\n\n``INTERSECT`` is supported. Intersects require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a intersected query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a intersect query, use the ``Query.intersect()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    intersected_query = q.intersect(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" INTERSECT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nMinus\n\"\"\"\"\"\n\n``MINUS`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a minus query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a minus query, use either the ``Query.minus()`` method or `-` operator with two query instances.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.minus(r)\n\n    (or)\n\n    minus_query = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    ) - Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" MINUS SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nEXCEPT\n\"\"\"\"\"\"\n\n``EXCEPT`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so\ntrying to cast a except query to string will throw a ``SetOperationException`` if the column sizes are mismatched.\n\nTo create a except query, use the ``Query.except_of()`` method.\n\n.. code-block:: python\n\n    provider_a, provider_b = Tables('provider_a', 'provider_b')\n    q = Query.from_(provider_a).select(\n        provider_a.created_time, provider_a.foo, provider_a.bar\n    )\n    r = Query.from_(provider_b).select(\n        provider_b.created_time, provider_b.fiz, provider_b.buz\n    )\n    minus_query = q.except_of(r)\n\n.. code-block:: sql\n\n    SELECT \"created_time\",\"foo\",\"bar\" FROM \"provider_a\" EXCEPT SELECT \"created_time\",\"fiz\",\"buz\" FROM \"provider_b\"\n\nDate, Time, and Intervals\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nUsing ``pypika.Interval``, queries can be constructed with date arithmetic.  Any combination of intervals can be\nused except for weeks and quarters, which must be used separately and will ignore any other values if selected.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    fruits = Tables('fruits')\n    q = Query.from_(fruits) \\\n        .select(fruits.id, fruits.name) \\\n        .where(fruits.harvest_date + Interval(months=1) < fn.Now())\n\n.. code-block:: sql\n\n    SELECT id,name FROM fruits WHERE harvest_date+INTERVAL 1 MONTH<NOW()\n\n\nTuples\n\"\"\"\"\"\"\n\nTuples are supported through the class ``pypika.Tuple`` but also through the native python tuple wherever possible.\nTuples can be used with ``pypika.Criterion`` in **WHERE** clauses for pairwise comparisons.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nUsing ``pypika.Tuple`` on both sides of the comparison is redundant and |Brand| supports native python tuples.\n\n.. code-block:: python\n\n    from pypika import Query, Tuple\n\n    q = Query.from_(self.table_abc) \\\n        .select(self.table_abc.foo, self.table_abc.bar) \\\n        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\")=(1,2)\n\nTuples can be used in **IN** clauses.\n\n.. code-block:: python\n\n    Query.from_(self.table_abc) \\\n            .select(self.table_abc.foo, self.table_abc.bar) \\\n            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)]))\n\n.. code-block:: sql\n\n    SELECT \"foo\",\"bar\" FROM \"abc\" WHERE (\"foo\",\"bar\") IN ((1,1),(2,2),(3,3))\n\n\nStrings Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nThere are several string operations and function wrappers included in |Brand|.  Function wrappers can be found in the\n``pypika.functions`` package.  In addition, `LIKE` and `REGEX` queries are supported as well.\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.like('Mc%')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname LIKE 'Mc%'\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n    ).where(\n        customers.lname.regex(r'^[abc][a-zA-Z]+&')\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname FROM customers WHERE lname REGEX '^[abc][a-zA-Z]+&';\n\n\n.. code-block:: python\n\n    from pypika import functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        fn.Concat(customers.fname, ' ', customers.lname).as_('full_name'),\n    )\n\n.. code-block:: sql\n\n    SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers\n\n\nCustom Functions\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCustom Functions allows us to use any function on queries, as some functions are not covered by PyPika as default, we can appeal\nto Custom functions.\n\n.. code-block:: python\n\n    from pypika import CustomFunction\n\n    customers = Tables('customers')\n    DateDiff = CustomFunction('DATE_DIFF', ['interval', 'start_date', 'end_date'])\n\n    q = Query.from_(customers).select(\n        customers.id,\n        customers.fname,\n        customers.lname,\n        DateDiff('day', customers.created_date, customers.updated_date)\n    )\n\n.. code-block:: sql\n\n    SELECT id,fname,lname,DATE_DIFF('day',created_date,updated_date) FROM customers\n\nCase Statements\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nCase statements allow fow a number of conditions to be checked sequentially and return a value for the first condition\nmet or otherwise a default value.  The Case object can be used to chain conditions together along with their output\nusing the ``when`` method and to set the default value using ``else_``.\n\n\n.. code-block:: python\n\n    from pypika import Case, functions as fn\n\n    customers = Tables('customers')\n    q = Query.from_(customers).select(\n        customers.id,\n        Case()\n           .when(customers.fname == \"Tom\", \"It was Tom\")\n           .when(customers.fname == \"John\", \"It was John\")\n           .else_(\"It was someone else.\").as_('who_was_it')\n    )\n\n\n.. code-block:: sql\n\n    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\"\n\n\nWith Clause\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nWith clause allows give a sub-query block a name, which can be referenced in several places within the main SQL query.\nThe SQL WITH clause is basically a drop-in replacement to the normal sub-query.\n\n.. code-block:: python\n\n    from pypika import Table, AliasedQuery, Query\n\n    customers = Table('customers')\n\n    sub_query = (Query\n                .from_(customers)\n                .select('*'))\n\n    test_query = (Query\n                .with_(sub_query, \"an_alias\")\n                .from_(AliasedQuery(\"an_alias\"))\n                .select('*'))\n\nYou can use as much as `.with_()` as you want.\n\n.. code-block:: sql\n\n    WITH an_alias AS (SELECT * FROM \"customers\") SELECT * FROM an_alias\n\n\nInserting Data\n^^^^^^^^^^^^^^\n\nData can be inserted into tables either by providing the values in the query or by selecting them through another query.\n\nBy default, data can be inserted by providing values for all columns in the order that they are defined in the table.\n\nInsert with values\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\n.. code-block:: python\n\n    customers =  Table('customers')\n\n    q = customers.insert(1, 'Jane', 'Doe', 'jane@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')\n\nMultiple rows of data can be inserted either by chaining the ``insert`` function or passing multiple tuples as args.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com').insert(2, 'John', 'Doe', 'john@example.com')\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).insert((1, 'Jane', 'Doe', 'jane@example.com'),\n                                     (2, 'John', 'Doe', 'john@example.com'))\n\nInsert with constraint violation handling\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nMySQL\n~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_ignore())\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY IGNORE\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = MySQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_duplicate_key_update(customers.email, Values(customers.email))\n\n.. code-block:: sql\n\n    INSERT INTO `customers` VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`)\n\n``.on_duplicate_key_update`` works similar to ``.set`` for updating rows, additionally it provides the ``Values``\nwrapper to update to the value specified in the ``INSERT`` clause.\n\nPostgreSQL\n~~~~~~~~~~\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_nothing()\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO NOTHING\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = PostgreSQLQuery.into(customers) \\\n        .insert(1, 'Jane', 'Doe', 'jane@example.com') \\\n        .on_conflict(customers.email) \\\n        .do_update(customers.email, 'bob@example.com')\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT (\"email\") DO UPDATE SET \"email\"='bob@example.com'\n\n\nInsert from a SELECT Sub-query\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\n.. code-block:: sql\n\n    INSERT INTO \"customers\" VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')\n\n\nTo specify the columns and the order, use the ``columns`` function.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname').insert(1, 'Jane', 'Doe')\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (1,'Jane','Doe','jane@example.com')\n\n\nInserting data with a query works the same as querying data with the additional call to the ``into`` method in the\nbuilder chain.\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).from_(customers).select('*')\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT * FROM customers\n\n.. code-block:: python\n\n    customers, customers_backup = Tables('customers', 'customers_backup')\n\n    q = Query.into(customers_backup).columns('id', 'fname', 'lname')\n        .from_(customers).select(customers.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n    INSERT INTO customers_backup SELECT \"id\", \"fname\", \"lname\" FROM customers\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup')\n\n    q = Query.into(orders_backup).columns('id', 'address', 'customer_fname', 'customer_lname')\n        .from_(customers)\n        .join(orders).on(orders.customer_id == customers.id)\n        .select(orders.id, customers.fname, customers.lname)\n\n.. code-block:: sql\n\n   INSERT INTO \"orders_backup\" (\"id\",\"address\",\"customer_fname\",\"customer_lname\")\n   SELECT \"orders\".\"id\",\"customers\".\"fname\",\"customers\".\"lname\" FROM \"customers\"\n   JOIN \"orders\" ON \"orders\".\"customer_id\"=\"customers\".\"id\"\n\nUpdating Data\n^^^^^^^^^^^^^^\nPyPika allows update queries to be constructed with or without where clauses.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    Query.update(customers).set(customers.last_login, '2017-01-01 10:00:00')\n\n    Query.update(customers).set(customers.lname, 'smith').where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"last_login\"='2017-01-01 10:00:00'\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nThe syntax for joining tables is the same as when selecting data\n\n.. code-block:: python\n\n    customers, profiles = Tables('customers', 'profiles')\n\n    Query.update(customers)\n         .join(profiles).on(profiles.customer_id == customers.id)\n         .set(customers.lname, profiles.lname)\n\n.. code-block:: sql\n\n   UPDATE \"customers\"\n   JOIN \"profiles\" ON \"profiles\".\"customer_id\"=\"customers\".\"id\"\n   SET \"customers\".\"lname\"=\"profiles\".\"lname\"\n\nUsing ``pypika.Table`` alias to perform the update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .where(customers.id == 10)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' WHERE \"id\"=10\n\nUsing ``limit`` for performing update\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    customers.update()\n            .set(customers.lname, 'smith')\n            .limit(2)\n\n.. code-block:: sql\n\n    UPDATE \"customers\" SET \"lname\"='smith' LIMIT 2\n\n\nParametrized Queries\n^^^^^^^^^^^^^^^^^^^^\n\nPyPika allows you to use ``Parameter(str)`` term as a placeholder for parametrized queries.\n\n.. code-block:: python\n\n    customers = Table('customers')\n\n    q = Query.into(customers).columns('id', 'fname', 'lname')\n        .insert(Parameter(':1'), Parameter(':2'), Parameter(':3'))\n\n.. code-block:: sql\n\n    INSERT INTO customers (id,fname,lname) VALUES (:1,:2,:3)\n\nThis allows you to build prepared statements, and/or avoid SQL-injection related risks.\n\nDue to the mix of syntax for parameters, depending on connector/driver, it is required that you specify the\nparameter token explicitly or use one of the specialized Parameter types per [PEP-0249](https://www.python.org/dev/peps/pep-0249/#paramstyle):\n``QmarkParameter()``, ``NumericParameter(int)``,  ``NamedParameter(str)``, ``FormatParameter()``, ``PyformatParameter(str)``\n\nAn example of some common SQL parameter styles used in Python drivers are:\n\nPostgreSQL:\n    ``$number`` OR ``%s`` + ``:name`` (depending on driver)\nMySQL:\n    ``%s``\nSQLite:\n    ``?``\nVertica:\n    ``:name``\nOracle:\n    ``:number`` + ``:name``\nMSSQL:\n    ``%(name)s`` OR ``:name`` + ``:number`` (depending on driver)\n\nYou 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.\n\nTemporal support\n^^^^^^^^^^^^^^^^\n\nTemporal criteria can be added to the tables.\n\nSelect\n\"\"\"\"\"\"\n\nHere is a select using system time.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.as_of('2020-01-01'))).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n\nYou can also use between.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME BETWEEN '2020-01-01' AND '2020-02-01'\n\nYou can also use a period range.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(SYSTEM_TIME.from_to('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n\nFinally you can select for all times:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(t.for_(SYSTEM_TIME.all_())).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME ALL\n\nA user defined period can also be used in the following manner.\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_(t.valid_period.between('2020-01-01', '2020-02-01'))\n    ).select(\"*\")\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR \"valid_period\" BETWEEN '2020-01-01' AND '2020-02-01'\n\nJoins\n\"\"\"\"\"\n\nWith joins, when the table object is used when specifying columns, it is\nimportant to use the table from which the temporal constraint was generated.\nThis is because `Table(\"abc\")` is not the same table as `Table(\"abc\").for_(...)`.\nThe following example demonstrates this.\n\n.. code-block:: python\n\n    t0 = Table(\"abc\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    t1 = Table(\"efg\").for_(SYSTEM_TIME.as_of('2020-01-01'))\n    query = (\n        Query.from_(t0)\n        .join(t1)\n        .on(t0.foo == t1.bar)\n        .select(\"*\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n    SELECT * FROM \"abc\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    JOIN \"efg\" FOR SYSTEM_TIME AS OF '2020-01-01'\n    ON \"abc\".\"foo\"=\"efg\".\"bar\"\n\nUpdate & Deletes\n\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\n\nAn update can be written as follows:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.update(\n        t.for_portion(\n            SYSTEM_TIME.from_to('2020-01-01', '2020-02-01')\n        )\n    ).set(\"foo\", \"bar\")\n\nThis produces:\n\n.. code-block:: sql\n\n    UPDATE \"abc\"\n    FOR PORTION OF SYSTEM_TIME FROM '2020-01-01' TO '2020-02-01'\n    SET \"foo\"='bar'\n\nHere is a delete:\n\n.. code-block:: python\n\n    t = Table(\"abc\")\n    q = Query.from_(\n        t.for_portion(t.valid_period.from_to('2020-01-01', '2020-02-01'))\n    ).delete()\n\nThis produces:\n\n.. code-block:: sql\n\n    DELETE FROM \"abc\"\n    FOR PORTION OF \"valid_period\" FROM '2020-01-01' TO '2020-02-01'\n\nCreating Tables\n^^^^^^^^^^^^^^^\n\nThe entry point for creating tables is ``pypika.Query.create_table``, which is used with the class ``pypika.Column``.\nAs with selecting data, first the table should be specified. This can be either a\nstring or a `pypika.Table`. Then the columns, and constraints. Here's an example\nthat demonstrates much of the functionality.\n\n.. code-block:: python\n\n    stmt = Query \\\n        .create_table(\"person\") \\\n        .columns(\n            Column(\"id\", \"INT\", nullable=False),\n            Column(\"first_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"last_name\", \"VARCHAR(100)\", nullable=False),\n            Column(\"phone_number\", \"VARCHAR(20)\", nullable=True),\n            Column(\"status\", \"VARCHAR(20)\", nullable=False, default=ValueWrapper(\"NEW\")),\n            Column(\"date_of_birth\", \"DATETIME\")) \\\n        .unique(\"last_name\", \"first_name\") \\\n        .primary_key(\"id\")\n\nThis produces:\n\n.. code-block:: sql\n\n    CREATE TABLE \"person\" (\n        \"id\" INT NOT NULL,\n        \"first_name\" VARCHAR(100) NOT NULL,\n        \"last_name\" VARCHAR(100) NOT NULL,\n        \"phone_number\" VARCHAR(20) NULL,\n        \"status\" VARCHAR(20) NOT NULL DEFAULT 'NEW',\n        \"date_of_birth\" DATETIME,\n        UNIQUE (\"last_name\",\"first_name\"),\n        PRIMARY KEY (\"id\")\n    )\n\nThere is also support for creating a table from a query.\n\n.. code-block:: python\n\n    stmt = Query.create_table(\"names\").as_select(\n        Query.from_(\"person\").select(\"last_name\", \"first_name\")\n    )\n\nThis produces:\n\n.. code-block:: sql\n\n        CREATE TABLE \"names\" AS (SELECT \"last_name\",\"first_name\" FROM \"person\")\n\n.. _tutorial_end:\n\n\n.. _license_start:\n\n\nLicense\n-------\n\nCopyright 2020 KAYAK Germany, GmbH\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n    http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n\n\nCrafted with \u2665 in Berlin.\n\n.. _license_end:\n\n\n.. _appendix_start:\n\n.. |Brand| replace:: *PyPika*\n\n.. _appendix_end:\n\n.. _available_badges_start:\n\n.. |BuildStatus| image:: https://github.com/kayak/pypika/workflows/Unit%20Tests/badge.svg\n   :target: https://github.com/kayak/pypika/actions\n.. |CoverageStatus| image:: https://coveralls.io/repos/kayak/pypika/badge.svg?branch=master\n   :target: https://coveralls.io/github/kayak/pypika?branch=master\n.. |Codacy| image:: https://api.codacy.com/project/badge/Grade/6d7e44e5628b4839a23da0bd82eaafcf\n   :target: https://www.codacy.com/app/twheys/pypika\n.. |Docs| image:: https://readthedocs.org/projects/pypika/badge/?version=latest\n   :target: http://pypika.readthedocs.io/en/latest/\n.. |PyPi| image:: https://img.shields.io/pypi/v/pypika.svg?style=flat\n   :target: https://pypi.python.org/pypi/pypika\n.. |License| image:: https://img.shields.io/hexpm/l/plug.svg?maxAge=2592000\n   :target: http://www.apache.org/licenses/LICENSE-2.0\n\n.. _available_badges_end:\n",
      "keywords": "pypika python query builder querybuilder sql mysql postgres psql oracle vertica aggregated relational database rdbms business analytics bi data science analysis pandas orm object mapper",
      "platform": [],
      "classifiers": [
        "License :: OSI Approved :: Apache Software License",
        "Development Status :: 5 - Production/Stable",
        "Intended Audience :: Developers",
        "Programming Language :: Python :: 3",
        "Programming Language :: PL/SQL",
        "Topic :: Software Development :: Libraries :: Python Modules",
        "Topic :: Scientific/Engineering :: Information Analysis",
        "Topic :: Scientific/Engineering :: Mathematics",
        "Operating System :: POSIX",
        "Operating System :: MacOS :: MacOS X",
        "Operating System :: Microsoft :: Windows",
        "Environment :: MetaData :: IBM Python Ecosystem"
      ],
      "download_url": "",
      "supported_platform": [],
      "comment": "",
      "provides": [],
      "requires": [],
      "obsoletes": [],
      "project_urls": [],
      "provides_dist": [],
      "obsoletes_dist": [],
      "requires_dist": [],
      "requires_external": [],
      "requires_python": "",
      "description_content_type": "",
      "provides_extras": [],
      "dynamic": [
        "author",
        "author-email",
        "classifier",
        "description",
        "home-page",
        "keywords",
        "license",
        "license-file",
        "summary"
      ],
      "license_expression": "",
      "license_file": [
        "LICENSE.txt"
      ],
      "+links": [
        {
          "rel": "releasefile",
          "hash_spec": "sha256=e936a29922d1b856a137680a2f220deff2ce9c14059d55df6034e7037cadb932",
          "hashes": {
            "sha256": "e936a29922d1b856a137680a2f220deff2ce9c14059d55df6034e7037cadb932"
          },
          "href": "https://wheels.developerfirst.ibm.com/ppc64le/linux/+f/e93/6a29922d1b856/pypika-0.49.0-py2.py3-none-any.whl",
          "log": [
            {
              "what": "upload",
              "who": "ppc64le",
              "when": [
                2026,
                7,
                27,
                13,
                6,
                50
              ],
              "dst": "ppc64le/linux"
            }
          ]
        }
      ]
    }
  },
  "type": "projectconfig"
}