CODEWITHSUNDEEP
python-2.7sqlalchemyuser-defined-functions
CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

SQLAlchemy User Defined Function Error

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.

Upvotes: 1

Views: 4216

Answers (1)

zzzeek
zzzeek

Reputation: 75217

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()

Upvotes: 2

Related Questions