用SQLAlchemy命名数据库函数中的参数 [英] Named parameters in database functions with SQLAlchemy
本文介绍了用SQLAlchemy命名数据库函数中的参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
create function test_f(一个文本默认值' *',b文本默认'+')将文本返回为$$
选择一个|| ''|| b;
$$语言sql;
Postgres允许使用命名参数调用它:
mytest的=>选择test_f('a','b');
test_f
--------
a b
(1 row)
mytest =>选择test_f('a');
test_f
--------
a +
(1 row)
mytest =>选择test_f(b:='a');
test_f
--------
* a
(1 row)
我想用Python做同样的事情,使用 SQLAlchemy的 func
构造,但似乎 func
不符合命名参数:
在[85]中:print(sqlalchemy.func.test_f('a','b'))
test_f :test_f_1,:test_f_2)
在[86]中:print(sqlalchemy.func.test_f('a'))
test_f(:test_f_1)
In [87]: print(sqlalchemy.func.test_f(a ='a'))
test_f()
我是否缺少某些东西,或 func
不支持命名参数?
解决方案
感谢 Michael Bayer的建议,我想出了一个解决我自己的问题:诀窍是使用 SQLAlchemy的编译器,并且一些正确的转义:
from psycopg2.extensions import adapt as sqlescape
import sqlalchemy $ b $ from sqlalchemy import select从sqlalchemy.ext.compiler导入编译
from sqlalchemy.sql.expression import ColumnClause
class MyFunc(ColumnClause):
def __init __(self,* args,** kwargs):
self.kwargs = kwargs
super().__ init __(* args)
@compiles(MyFunc)
def compile_myfunc(element,compiler,** kw ):
s =','。join(%s:=%s%(k,sqlescape(v))for k,v in element.kwargs.items())
return s(%s)%(element.name,s)
def call(engine,func,** kwargs):
return engine.execute(select [ MyFunc(func,** kwargs)]))
engine = sqlalchemy.cre ate_engine('postgresql + psycopg2:// lbolla @ localhost / mytest')
print(call(engine,'test_f',a ='a')。scalar())
print(call ,'test_f',b ='b')。scalar())
I have a function in my database (Postgres) that looks like this:
create function test_f(a text default '*', b text default '+') returns text as $$
select a || ' ' || b;
$$ language sql;
Postgres allows calling it with named parameters:
mytest=> select test_f('a', 'b');
test_f
--------
a b
(1 row)
mytest=> select test_f('a');
test_f
--------
a +
(1 row)
mytest=> select test_f(b:='a');
test_f
--------
* a
(1 row)
I want to do the same from Python, using SQLAlchemy's func
construct, but it seems that func
does not honor named parameters:
In [85]: print(sqlalchemy.func.test_f('a', 'b'))
test_f(:test_f_1, :test_f_2)
In [86]: print(sqlalchemy.func.test_f('a'))
test_f(:test_f_1)
In [87]: print(sqlalchemy.func.test_f(a='a'))
test_f()
Am I missing something, or func
does not support named parameters?
解决方案
Thanks to the suggestion of Michael Bayer, I came up with a solution to my own question: the trick is to use SQLAlchemy's compiler, and some proper escaping:
from psycopg2.extensions import adapt as sqlescape
import sqlalchemy
from sqlalchemy import select
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ColumnClause
class MyFunc(ColumnClause):
def __init__(self, *args, **kwargs):
self.kwargs = kwargs
super().__init__(*args)
@compiles(MyFunc)
def compile_myfunc(element, compiler, **kw):
s = ','.join("%s:=%s" % (k, sqlescape(v)) for k, v in element.kwargs.items())
return "%s(%s)" % (element.name, s)
def call(engine, func, **kwargs):
return engine.execute(select([MyFunc(func, **kwargs)]))
engine = sqlalchemy.create_engine('postgresql+psycopg2://lbolla@localhost/mytest')
print(call(engine, 'test_f', a='a').scalar())
print(call(engine, 'test_f', b='b').scalar())
这篇关于用SQLAlchemy命名数据库函数中的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文