{ "info": { "author": "Timothy Heys", "author_email": "theys@kayak.com", "bugtrack_url": null, "classifiers": [ "Development Status :: 5 - Production/Stable", "Intended Audience :: Developers", "License :: OSI Approved :: Apache Software License", "Operating System :: MacOS :: MacOS X", "Operating System :: Microsoft :: Windows", "Operating System :: POSIX", "Programming Language :: PL/SQL", "Programming Language :: Python :: 3", "Topic :: Scientific/Engineering :: Information Analysis", "Topic :: Scientific/Engineering :: Mathematics", "Topic :: Software Development :: Libraries :: Python Modules" ], "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()`` 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