Python sqlalchemy calling stored procedure

Question

I am trying to call a stored procedure from python using sqlalchemy. The code I have is as follows:

@database_connection_wrapper
def get_adv(connection):
    ''' get the adv using a stored proc from the database '''

    connection.callproc("GetAdvMedianTrailing30Days")
    results = list(connection.fetchall())
    print(results)

I get the following error: AttributeError: 'pyodbc.Connection' object has no attribute 'callproc'

I followed the instructions on the official documentation but that did not make a difference.

I am on python 3.5

Is there a different way to call stored procedures?

Gord Thompson · Accepted Answer · 2017-08-02 22:48:24Z

5

From https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures, pyodbc does not implement .callproc() method on the connection object. You must execute the sp using a SQL call (.execute("{CALL GetAdvMedianTrailing30Days}")).

(This isn't really a SQLAlchemy question since you are leaking the abstraction down to make a DB-API call using pyodbc)

edited Aug 2, 2017 at 22:48

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

answered Aug 2, 2017 at 20:27

cowbert

3,4833 gold badges27 silver badges37 bronze badges

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

6 Comments

PYA Over a year ago

Thanks! I get an error that

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The EXECUTE permission was denied on the object 'GetAdvMedianTrailing30Days', database 'XXX', schema 'dbo'. (229) (SQLExecDirectW)")

. How can I resolve this?

cowbert Over a year ago

Does the user you are using sqlalchemy to log into the server with have the necessary permissions to execute GetAdvMedianTrailing30Days in that database?

PYA Over a year ago

Will have to ask my team :P . The github link you posted is really helpful, since my stored proc returns values I will have to convert it into a SQL statement I think. Thanks again!

cowbert Over a year ago

The error you are getting implies the problem is permissions on the db server, not related to python.

PYA Over a year ago

yeah, i thought so too. I will have to look into SQL permissions for the user I am using.

|

Collectives™ on Stack Overflow

Python sqlalchemy calling stored procedure

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related