buildpg

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