Metadata-Version: 2.1
Name: buildpg
Version: 0.2
Summary: Query building for the postgresql prepared statements and asyncpg.
Home-page: https://github.com/samuelcolvin/buildpg
Author: Samuel Colvin
Author-email: s@muelcolvin.com
License: MIT
Platform: UNKNOWN
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.6
Classifier: Programming Language :: Python :: 3.7
Classifier: Programming Language :: SQL
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Information Technology
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: Unix
Classifier: Operating System :: POSIX :: Linux
Classifier: Environment :: MacOS X
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Topic :: Internet
Requires-Python: >=3.6

buildpg
=======

|BuildStatus| |Coverage| |pypi|

Query building for the postgresql prepared statements and asyncpg.

Lots of more powerful features, including full clause construction, multiple values, logic functions,
query pretty-printing and different variable substitution - below is just a very quick summary.
Please check the code and tests for examples.

Building Queries
................

Simple variable substitution:

.. code-block:: python

   from buildpg import render

   render('select * from mytable where x=:foo and y=:bar', foo=123, bar='whatever')
   >> 'select * from mytable where x=$1 and y=$2', [123, 'whatever']


Use of ``V`` to substitute constants:

.. code-block:: python

   from buildpg import V, render

   render('select * from mytable where :col=:foo', col=V('x'), foo=456)
   >> 'select * from mytable where x=$1', [456]


Complex logic:

.. code-block:: python

   from buildpg import V, funcs, render

   where_logic = V('foo.bar') == 123
   if spam_value:
       where_logic &= V('foo.spam') <= spam_value

   if exclude_cake:
       where_logic &= funcs.not_(V('foo.cake').in_([1, 2, 3]))

   render('select * from foo :where', where=where_logic)
   >> 'select * from foo foo.bar = $1 AND foo.spam <= $2 AND not(foo.cake in $3)', [123, 123, ['x', 'y']]


Values usage:

.. code-block:: python

   from buildpg import Values, render

   render('insert into the_table (:values__names) values :values', values=Values(a=123, b=456, c='hello'))
   >> 'insert into the_table (a, b, c) values ($1, $2, $3)', [123, 456, 'hello']


With asyncpg
............


As a wrapper around *asyncpg*:

.. code-block:: python

   import asyncio
   from buildpg import asyncpg

   async def main():
       async with asyncpg.create_pool_b('postgres://postgres@localhost:5432/db') as pool:
           await pool.fetchval_b('select spam from mytable where x=:foo and y=:bar', foo=123, bar='whatever')
           >> 42

   asyncio.get_event_loop().run_until_complete(main())


Both the pool and connections have ``*_b`` variants of all common query methods:

- ``execute_b``
- ``executemany_b``
- ``fetch_b``
- ``fetchval_b``
- ``fetchrow_b``
- ``cursor_b``

.. |BuildStatus| image:: https://travis-ci.com/samuelcolvin/buildpg.svg?branch=master
   :target: https://travis-ci.com/samuelcolvin/buildpg
.. |Coverage| image:: https://codecov.io/gh/samuelcolvin/buildpg/branch/master/graph/badge.svg
   :target: https://codecov.io/gh/samuelcolvin/buildpg/
.. |pypi| image:: https://img.shields.io/pypi/v/buildpg.svg
   :target: https://pypi.org/project/buildpg/


Operators
.........

========================  ==================
Python operator/function   SQL operator
========================  ==================
``&``                     ``AND``
``|``                     ``OR``
``=``                     ``=``
``!=``                    ``!=``
``<``                     ``<``
``<=``                    ``<=``
``>``                     ``>``
``>=``                    ``>=``
``+``                     ``+``
``-``                     ``-``
``*``                     ``*``
``/``                     ``/``
``%``                     ``%``
``**``                    ``^``
``-``                     ``-``
``~``                     ``not(...)``
``sqrt``                  ``|/;``
``abs``                   ``@``
``contains``              ``@>``
``contained_by``          ``<@``
``overlap``               ``&&``
``like``                  ``LIKE``
``cat``                   ``||``
``in_``                   ``in``
``from_``                 ``from``
``at_time_zone``          ``AT TIME ZONE``
``matches``               ``@@``
``for_``                  ``for``
``factorial``             ``!``
``cast``                  ``::``
``asc``                   `` ASC``
``desc``                  ``DESC``
``comma``                 ``, ;``
``on``                    ``ON``
``as_``                   ``AS``
========================  ==================

Usage:

.. code-block:: python

   from buildpg import V, S, render

   def show(component):
       sql, params = render(':c', c=component)
       print(f'sql="{sql}" params={params}')

   show(V('foobar').contains([1, 2, 3]))
   #> sql="foobar @> $1" params=[[1, 2, 3]]
   show(V('foobar') == 4)
   #> sql="foobar = $1" params=[4]
   show(~V('foobar'))
   #> sql="not(foobar)" params=[]
   show(S(625).sqrt())
   #> sql="|/ $1" params=[625]


Functions
.........

===========================================  =============
Python function                              SQL function
===========================================  =============
``AND(*args)``                               ``<arg1> and <arg2> ...``
``OR(*args)``                                ``<arg1> or <arg2> ...``
``NOT(arg)``                                 ``not(<arg>)``
``comma_sep(*args)``                         ``<arg1>, <arg2>, ...;``
``count(expr)``                              ``count(expr)``
``any(arg)``                                 ``any(<arg1>)``
``now()``                                    ``now()``
``cast(v, cast_type)``                       ``<v>::<cast_type>``
``upper(string)``                            ``upper(<string>)``
``lower(string)``                            ``lower(<string>)``
``length(string)``                           ``length(<string>)``
``left(string, n)``                          ``left(<string>, <n>)``
``right(string, n)``                         ``right(<string>, <n>)``
``extract(expr)``                            ``extract(<expr>)``
``sqrt(n)``                                  ``|/ <n>``
``abs(n)``                                   ``@ <n>``
``factorial(n)``                             ``!<n>;``
``position(substring, string)``              ``position(<substring> in <st``...
``substring(string, pattern, escape=None)``  ``substring(<string> from <pa``...
``to_tsvector(arg1, document=None)``         ``to_tsvector(<arg1>)``
``to_tsquery(arg1, text=None)``              ``to_tsquery(<arg1>)``
===========================================  =============

Usage:

.. code-block:: python

   from buildpg import V, render, funcs

   def show(component):
      sql, params = render(':c', c=component)
      print(f'sql="{sql}" params={params}')

   show(funcs.AND(V('x') == 4, V('y') > 6))
   #> sql="x = $1 AND y > $2" params=[4, 6]
   show(funcs.position('foo', 'this has foo in it'))
   #> sql="position($1 in $2)" params=['foo', 'this has foo in it']


