为PostgreSQL准备的语句和AsyncPg建立查询。
buildpg的Python项目详细描述
为PostgreSQL准备的语句和AsyncPg建立查询。
很多更强大的特性，包括全子句构造、多值、逻辑函数， 查询漂亮的打印和不同的变量替换-下面只是一个非常快速的总结。 请检查代码和测试以获取示例。
生成查询
简单变量替换：
frombuildpgimportrenderrender('select * from mytable where x=:foo and y=:bar',foo=123,bar='whatever')>>'select * from mytable where x=$1 and y=$2',[123,'whatever']
使用V替换常量：
frombuildpgimportV,renderrender('select * from mytable where :col=:foo',col=V('x'),foo=456)>>'select * from mytable where x=$1',[456]
复杂逻辑：
frombuildpgimportV,funcs,renderwhere_logic=V('foo.bar')==123ifspam_value:where_logic&=V('foo.spam')<=spam_valueifexclude_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']]
值用法：
frombuildpgimportValues,renderrender('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']
使用asyncpg
作为asyncpg的包装：
importasynciofrombuildpgimportasyncpgasyncdefmain():asyncwithasyncpg.create_pool_b('postgres://postgres@localhost:5432/db')aspool:awaitpool.fetchval_b('select spam from mytable where x=:foo and y=:bar',foo=123,bar='whatever')>>42asyncio.get_event_loop().run_until_complete(main())
池和连接都有所有常用查询方法的*_b变体：
- execute_b
- executemany_b
- fetch_b
- fetchval_b
- fetchrow_b
- cursor_b
运算符
| Python operator/function | SQL operator |
|---|---|
| ^{tt9}$ | ^{tt10}$ |
| ^{tt11}$ | ^{tt12}$ |
| ^{tt13}$ | ^{tt13}$ |
| ^{tt15}$ | ^{tt15}$ |
| ^{tt17}$ | ^{tt17}$ |
| ^{tt19}$ | ^{tt19}$ |
| ^{tt21}$ | ^{tt21}$ |
| ^{tt23}$ | ^{tt23}$ |
| ^{tt25}$ | ^{tt25}$ |
| ^{tt27}$ | ^{tt27}$ |
| ^{tt29}$ | ^{tt29}$ |
| ^{tt31}$ | ^{tt31}$ |
| ^{tt33}$ | ^{tt33}$ |
| ^{tt35}$ | ^{tt36}$ |
| ^{tt27}$ | ^{tt27}$ |
| ^{tt39}$ | ^{tt40}$ |
| ^{tt41}$ | ^{tt42}$ |
| ^{tt43}$ | ^{tt44}$ |
| ^{tt45}$ | ^{tt46}$ |
| ^{tt47}$ | ^{tt48}$ |
| ^{tt49}$ | ^{tt50}$ |
| ^{tt51}$ | ^{tt52}$ |
| ^{tt53}$ | ^{tt54}$ |
| ^{tt55}$ | ^{tt56}$ |
| ^{tt57}$ | ^{tt58}$ |
| ^{tt59}$ | ^{tt60}$ |
| ^{tt61}$ | ^{tt62}$ |
| ^{tt63}$ | ^{tt64}$ |
| ^{tt65}$ | ^{tt66}$ |
| ^{tt67}$ | ^{tt68}$ |
| ^{tt69}$ | ^{tt70}$ |
| ^{tt71}$ | ^{tt72}$ |
| ^{tt73}$ | ^{tt74}$ |
| ^{tt75}$ | ^{tt76}$ |
| ^{tt77}$ | ^{tt78}$ |
| ^{tt79}$ | ^{tt80}$ |
| ^{tt81}$ | ^{tt82}$ |
用法：
frombuildpgimportV,S,renderdefshow(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 |
|---|---|
| ^{tt83}$ | ^{tt84}$ |
| ^{tt85}$ | ^{tt86}$ |
| ^{tt87}$ | ^{tt88}$ |
| ^{tt89}$ | ^{tt90}$ |
| ^{tt91}$ | ^{tt91}$ |
| ^{tt93}$ | ^{tt94}$ |
| ^{tt95}$ | ^{tt95}$ |
| ^{tt97}$ | ^{tt98}$ |
| ^{tt99}$ | ^{tt100}$ |
| ^{tt101}$ | ^{tt102}$ |
| ^{tt103}$ | ^{tt104}$ |
| ^{tt105}$ | ^{tt106}$ |
| ^{tt107}$ | ^{tt108}$ |
| ^{tt109}$ | ^{tt110}$ |
| ^{tt111}$ | ^{tt112}$ |
| ^{tt113}$ | ^{tt114}$ |
| ^{tt115}$ | ^{tt116}$ |
| ^{tt117}$ | ^{tt118}$… |
| ^{tt119}$ | ^{tt120}$… |
| ^{tt121}$ | ^{tt122}$ |
| ^{tt123}$ | ^{tt124}$ |
用法：
frombuildpgimportV,render,funcsdefshow(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']
欢迎加入QQ群-->： 979659372
