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.
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']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_bexecutemany_bfetch_bfetchval_bfetchrow_bcursor_b
| 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=[]| 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']
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent