SQLAlchemy User Defined Function Error

Question

I'm attempting to call a user defined function (table function) using SQLAlchemy. Here is my code:

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker

db = create_engine('mssql+pymssql://User:Password@Server/Database')

metadata = MetaData(db)
Session = sessionmaker(bind=db)
session = Session()

results = session.query(func.MyFunctionName('Value')).all()

When this executes I get the following error:

sqlalchemy.exc.OperationalError: (OperationalError) (195, "'MyFunctionName' is not a recognized built-in function name.DB-Lib error message 195, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") 'SELECT MyFunctionName(%(MyFunctionName_2)s) AS [MyFunctionName_1]' {'MyFunctionName_2': 'Value'}

When I look at SQL Profiler, I get the following:

SELECT MyFunctionName('Value') AS [MyFunctionName_1]

Which tells me that it is not adding the SELECT * FROM MyFunctionName in the query.

What do I need to do to get my session.query to add the * FROM in the query for execution?

If you need more information, please leave a comment.

CodeLikeBeaker · Accepted Answer · 2013-07-15 14:40:38Z

2

a table based function you need to turn into a select() object first, using the pattern described at http://docs.sqlalchemy.org/en/latest/core/tutorial.html#functions

myfunc = select([column('x'), column('y')]).select_from(func.myfunction())
session.execute(myfunc).fetchall()

edited Jul 15, 2013 at 14:40

CodeLikeBeaker

21.4k14 gold badges83 silver badges114 bronze badges

answered Jul 13, 2013 at 18:13

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CodeLikeBeaker Over a year ago

This worked with tweaking. I had to change session.query to session.execute(myfunc).fetchall(). I edited your response with the updated code.

zzzeek Over a year ago

passing the select() to query() should work as well, it will wrap it in an extra SELECT but you'll still get the same result. what was your output/version of SQLAlchemy ?

CodeLikeBeaker Over a year ago

It appears I need an alias for my wrapped sub select. So "select column from (select column from myfunc()) as A

Collectives™ on Stack Overflow

SQLAlchemy User Defined Function Error

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related