buildpg

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:

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:

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:

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:

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:

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.run(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

Operators

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

Usage:

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]
show(V('foo').is_not('true'))
#> sql="foo is not true" params=[]

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

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']