Named Parameters In Database Functions With Sqlalchemy
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; $$ lan
Solution 1:
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())
Post a Comment for "Named Parameters In Database Functions With Sqlalchemy"