My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
StoredProcedures  
Using stored procedures
Updated Jul 28, 2011 by mkleehammer

Calling

The Python DB API specifies that Cursor objects should have a callproc method for calling stored procedures. This hasn't been implemented yet in pyodbc because there is no way for it to know which parameters are input and which are output.

Since there won't be a way anytime soon, I'll probably implement callproc but make all of the parameters input-only.

To call a stored procedure right now, pass the call to the execute method using either a format your database recognizes or using the ODBC call escape format. (The ODBC driver will then reformat the call for you to match the given database.)

For SQL Server you would use something like this:

# SQL Server format
cursor.execute("exec sp_dosomething(123, 'abc')")

# ODBC format
cursor.execute("{call sp_dosomething(123, 'abc')}")

Results

Since we can't use output parameters at this point, you'll need to return results in a result set. Usually this means just ending your stored procedure with a SELECT statement.

Tips

Some databases will return results for everything you do, including the row counts of any inserts or deletes. If you have these, you can "step over" them using Cursor.nextset().

If you are using SQL Server, you can use SET NOCOUNT ON to disable this. (Much cleaner)

Comment by syash...@gmail.com, Apr 22, 2010

using mysql: code work fine so far for select statements. But for stored proc. it just hangs.

>>> c.execute('call sp.mysp') <pyodbc.cursor object at 0x0221---->

The pre-tested and using store proc. does not work. Is the a bug or user error

Comment by m...@thedonahues.com, Nov 16, 2010

using mssql,

cursor.execute('exec GetData? "startIndex" "endIndex" "field1;field2;foo;bar;spam;eggs;...."')

works fine to call a stored procedure called GetData?, but only when the last argument is less than 128 characters.

I believe this is because pyodbc thinks the final "field1;field2;foo;bar;spam;eggs;...." is a single field rather than the stored procedure knowing it's a concatenated string of field names. Is there a way to override pyodbc throwing an error for thinking i'm asking for a field name longer than the allowed length? using RODBC in R with the same string returns the expected results, so I know for a fact it's not the query.... Thanks.

Comment by OGd...@googlemail.com, Jan 5, 2011

Using MSSQL,

Calling stored procs this way using pyodbc causes Python to hang for a while before returning the result. The proc and what goes on inside it is not the issue, since it is fast when executed using sqlcmd. The proc is not terminated by a "direct" SELECT statement but rather: EXEC sp_executesql <dynamic_query> <params>. Could that be the issue? When I generate <dynamic_query_and_params> within Python and use pyodbc to execute it is as fast as you would expect.

Comment by sameh.na...@gmail.com, Jan 26, 2012

This format is the the one that worked for me on SQL server

# SQL Server format cursor.execute("exec sp_dosomething 123, 'abc'")

Comment by pradyot....@gmail.com, Feb 28, 2012

I am trying to do the following, sp_helptext sp_copyAlertSettings where "sp_copyAlertSettings" is a procedure. this sort of does what I want, but stores the returned text , along with an assorted escape characters. I am wondering if there exists a better way to do this. As an example: printing the cursor values returned. (u'\nCREATE procedure dbo?.sp_copyAlertSettings?(\r\n', ) (u'\t@sourceNTUsername varchar(255),\r\n', ) (u'\t@sourceProfilename varchar(255),\r\n', ) (u'\t@targetNTUsername varchar(255),\r\n', ) (u'\t@targetProfilename varchar(255),\r\n', ) (u'\t@luversion varchar(512),\r\n', ) (u'\t@luuserid int,\r\n', ) (u'\t@luowningsite varchar(50)\r\n', )

Comment by g...@western.kiev.ua, Jun 24, 2012

f your stored procedure returns more than one table, you can move to next table (set of rows) using command cursor.nextset()

Comment by webtour...@gmail.com, Feb 10, 2013

hi if the StoredProc? changes data is commit() required ?

Comment by jediso...@gmail.com, Jul 12, 2013

My python application is on Linux environment accessing a remote MS SQL Server for data, using a stored procedure. What works for me is like what sameh.na...@gmail.com, Jan 26, 2012 above said. My python statement:

cursor.execute("exec OID_getFacultyClassInfo 803381007, '12S'") <--- this works, however if I change the hard coded input as variables, it errors:

cursor.execute("exec OID_getFacultyClassInfo @uid, @term")

pyodbc.ProgrammingError?: ('42000', '42000? FreeTDS?Server?Must declare the scalar variable "@uid". (137) (SQLExecDirectW)')

What should be the correct syntax?

Comment by zbert...@gmail.com, Aug 19, 2013

cursor.execute("declare @uid=<value_1>, @term=<value_2>; exec OID_getFacultyClassInfo @uid, @term")

you can replace the <value_1> and <value_2> with real values from within the python code dynalically

Comment by aaron.fr...@gmail.com, Nov 27, 2013

Quote: Since we can't use output parameters at this point, you'll need to return results in a result set. Usually this means just ending your stored procedure with a SELECT statement.

Example? Or is this just a case of having the stored procedure end up putting data into a table then reading that table?

Comment by shahz...@gmail.com, Apr 24, 2014
The format by Sameh worked for me:
SQL Server format:
cursor.execute("exec sp_dosomething 123, 'abc'")
Comment by debi...@gmail.com, Jul 24, 2014

When I run: cursor.execute("""{call myStoredProcedure('abc')}""") I get this 'myStoredProcedure' is not a macro.

Why is Teradata thinking that myStoredProcedure is macro and not a stored procedure?

I am able to run the usual select commands on the database.

Comment by webguy...@gmail.com, Oct 10, 2014

I was able to use SP's against MSSQL using cursor.execute("{call dbo.my_procedure('some variable')}")

Powered by Google Project Hosting