Using SQL Server stored procedures from Python (pyodbc)

Question

I'm have a stored procedure, code:

DECLARE @RC int 
DECLARE @id varchar(13) 
DECLARE @pw varchar(13) 
DECLARE @depart varchar(32) 
DECLARE @class varchar(12) 
DECLARE @name varchar(12) 
DECLARE @birthday varchar(10) 
DECLARE @grade int 
DECLARE @subgrade int 
SELECT @id = 'test' 
SELECT @pw = '12345' 
SELECT @depart = 'none' 
SELECT @class = 'GM' 
SELECT @name = 'name' 
SELECT @birthday = 'None' 
SELECT @grade = 3 
SELECT @subgrade = 2 
EXEC @RC = [my_database].[dbo].[my_table] @id, @pw, @depart, @class, @name, @birthday, @grade, @subgrade 
DECLARE @PrnLine nvarchar(4000) 
PRINT 'Stored Procedure: my_database.dbo.my_table' 
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)

How i can make a raw sql query to create account using this procedure? I'm using flask and pyodbc.

score 32 · Accepted Answer · edited Jun 20 '20 at 09:12

32

From the pyodbc documentation

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')}")

So to call your procedure

id_ = 'test' 
pw = '12345' 
depart = 'none' 
class_ = 'GM' 
name = 'name' 
birthday = 'None' 
grade = 3 
subgrade = 2 

sql = 'exec [my_database].[dbo].[my_table](?, ?, ?, ?, ?, ?, ?, ?)'
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)

cursor.execute(sql, (values))

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 20 '15 at 19:00

dirn

19,454
5
69
74

3

here a problem: sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)") 'exe my_procedure(?, ?, ?)' ('5215125', '125151', '31231') – bartezr Feb 20 '15 at 19:19
That looks like a syntax error in the stored procedure. Is `@P1` a part of the real one? – dirn Feb 20 '15 at 19:27
3

Thank you, sql = 'exec [my_database].[dbo].[my_table] ?, ?, ?, ?, ?, ?, ?, ?' working fine(without brackets). – bartezr Feb 21 '15 at 08:35
1

Without brackets works if you don't have spaces in the names, if you have spaces you need the brackets..... – M T Head Sep 12 '16 at 20:48
Hello everybody please, How to get the return value if my stored procedure has a SELECT IDENT_CURRENT('TABLE') as ID, I want to get the ID. – GSandro_Strongs Oct 21 '20 at 13:23

Gord Thompson · Answer 2 · 2021-03-15T15:26:28.443

30

The accepted answer does not address the issue of capturing the return value from the stored procedure, which can be done like this:

id_ = 'test' 
pw = '12345' 
depart = 'none' 
class_ = 'GM' 
name = 'name' 
birthday = 'None' 
grade = 3 
subgrade = 2 

sql = """\
SET NOCOUNT ON;
DECLARE @RC int;
EXEC @RC = [my_database].[dbo].[my_sp] ?, ?, ?, ?, ?, ?, ?, ?;
SELECT @RC AS rc;
"""
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)
cursor.execute(sql, values)
rc = cursor.fetchval()  # pyodbc convenience method similar to cursor.fetchone()[0]

edited Mar 15 '21 at 15:26

answered Feb 02 '17 at 18:00

Gord Thompson

116,920
32
215
418

this seems to cause SQL Server to recompile the query for every execution rather than reusing execution plans causing high compiles and recompiles – pcnate Nov 01 '22 at 14:29
I tried your answer with a SP that `INSERT` records in a table. I used `pyodbc`. In that `sql` string, I had used `sql=""" SET NOCOUNT ON; EXEC [schema].[sp_name] ?,?,?,?,?""" ; cur.execute( sql,values)`. The python code did not throw any error. But when I checked in the table, No data was inserted. Why so? – pythondumb Nov 29 '22 at 15:22
@pythondumb - Did you remember to call [.commit()](https://github.com/mkleehammer/pyodbc/wiki/Cursor#commit)? – Gord Thompson Nov 29 '22 at 15:27
Yes I did. I used conn.commit() – pythondumb Nov 29 '22 at 15:56
@pythondumb - Then please [ask a new question](https://stackoverflow.com/questions/ask) and include a [mcve]. – Gord Thompson Nov 29 '22 at 15:59
Here it goes. https://stackoverflow.com/questions/74615773/issue-in-running-sql-server-stored-proc-using-pyodbc – pythondumb Nov 29 '22 at 16:46

score 18 · Answer 3 · answered Mar 23 '18 at 19:39

18

Don't forget SET NOCOUNT ON in your stored procedure.

answered Mar 23 '18 at 19:39

Eman4real

538
5
12

1

This actually helped us deal with a problem of SP not working. Still I don't see how this is a factor – cah1r Sep 14 '18 at 12:08

score 8 · Answer 4 · answered Jun 05 '18 at 09:16

Another flavour of Gord's answer is using OUTPUT and named parameters (to be defined within the Stored procedure) for clarity.

id_ = 'test' 
pw = '12345' 
depart = 'none' 
class_ = 'GM' 
name = 'name' 
birthday = 'None' 
grade = 3 
subgrade = 2 

sql = """\
DECLARE @RC int;
EXEC [my_database].[dbo].[my_sp] @RC OUTPUT, @id_=?, @pw=?, @depart=?, @class_=?, @name=?, @birthday=?, @grade=?, @subgrade=?;
SELECT @RC AS rc;
"""
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)
cursor.execute(sql, values)
rc = cursor.fetchval()

score 4 · Answer 5 · answered Nov 03 '20 at 19:25

4

After searching everywhere for this solution, i couldnt find a simplified version. All results seem to overcomplicate this that should be so easy to do. Heres my solution.

 import pyodbc
 import pandas as pd
 import datetime as d


  conn = pyodbc.connect('Driver=;'
                  'Server=;'
                  'Database=;'
                  'UID=;'
                  'PWD=;')


     # define parameters to be passed in and out

     quarter_date = d.date(year=2020, month=10, day=1)

     SQL = r'exec TERRITORIES_SP @quarterStart = ' + "'" + str(quarter_date) + "'"

     print(SQL)

     try:
         cursor = conn.cursor()
          cursor.execute(SQL)
          cursor.close()
          conn.commit()
    finally:
          conn.close()

answered Nov 03 '20 at 19:25

manuelnegrete107

51
3

1

By using dynamic SQL you are leaving yourself open to SQL injection vulnerabilities. – Gord Thompson Nov 03 '20 at 20:57
Thanks. What would be a better approach? – manuelnegrete107 Nov 03 '20 at 23:45
Something like `sql = r'exec TERRITORIES_SP @quarterStart = ?'` followed by `cursor.execute(sql, str(quarter_date))`. I'll admit that in this case the SQL injection risk is low, but it's still bad form to promote dynamic SQL as a "simplified" solution. – Gord Thompson Nov 04 '20 at 01:15

score 3 · Answer 6 · answered Jul 30 '19 at 22:19

3

With a cursor initialized by your connection, the sp can be called directly as follow

sql = " exec your_SP @codemp = ?, @fecha = ? "
prm = (dict['param1'], dict['param2'])
cursor.execute(qry, params)

answered Jul 30 '19 at 22:19

Leonardo Pascual

57
3

score -6 · Answer 7 · edited Feb 02 '17 at 00:04

-6

For MSSQL the correct format is this:

SQL = 'exec sp_UpdateUserGoogleAuthenticated ''?'', ''?'''

Try running the Stored Procedure in MSSQL in the SQL Query window and it will fail every time with () surrounding the ? marks. If you escape the single quotes it will allow for variables with spaces in them.

edited Feb 02 '17 at 00:04

Julien

13,986
5
29
53

answered Feb 01 '17 at 23:06

anonmyous1234

1

Using SQL Server stored procedures from Python (pyodbc)

7 Answers7

Linked

Related