buildpg 0.4

Query building for the postgresql prepared statements and asyncpg.

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