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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

37

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 20, 2015 at 19:00

dirn

21.1k7 gold badges75 silver badges76 bronze badges

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

5 Comments

bartezr Over a year ago

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

dirn Over a year ago

That looks like a syntax error in the stored procedure. Is @P1 a part of the real one?

bartezr Over a year ago

Thank you, sql = 'exec [my_database].[dbo].[my_table] ?, ?, ?, ?, ?, ?, ?, ?' working fine(without brackets).

M T Head Over a year ago

Without brackets works if you don't have spaces in the names, if you have spaces you need the brackets.....

GSandro_Strongs Over a year ago

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.

Gord Thompson · Accepted Answer · 2021-03-15 15:26:28Z

32

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, 2021 at 15:26

answered Feb 2, 2017 at 18:00

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

6 Comments

pcnate Over a year ago

this seems to cause SQL Server to recompile the query for every execution rather than reusing execution plans causing high compiles and recompiles

pythondumb Over a year ago

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?

Gord Thompson Over a year ago

@pythondumb - Did you remember to call .commit()?

pythondumb Over a year ago

Yes I did. I used conn.commit()

Gord Thompson Over a year ago

@pythondumb - Then please ask a new question and include a minimal reproducible example.

|

Eman4real · Accepted Answer · 2018-03-23 19:39:46Z

18

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

answered Mar 23, 2018 at 19:39

Eman4real

6186 silver badges13 bronze badges

1 Comment

cah1r Over a year ago

This actually helped us deal with a problem of SP not working. Still I don't see how this is a factor

Viggos · Accepted Answer · 2018-06-05 09:16:41Z

8

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

answered Jun 5, 2018 at 9:16

Viggos

3693 silver badges6 bronze badges

Comments

manuelnegrete107 · Accepted Answer · 2020-11-03 19:25:57Z

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 3, 2020 at 19:25

manuelnegrete107

591 silver badge3 bronze badges

3 Comments

Gord Thompson Over a year ago

By using dynamic SQL you are leaving yourself open to SQL injection vulnerabilities.

manuelnegrete107 Over a year ago

Thanks. What would be a better approach?

Gord Thompson Over a year ago

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.

Leonardo Pascual · Accepted Answer · 2019-07-30 22:19:38Z

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, 2019 at 22:19

Leonardo Pascual

573 bronze badges

Comments

Julien · Accepted Answer · 2017-02-02 00:04:01Z

-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 2, 2017 at 0:04

Julien

15.3k6 gold badges33 silver badges58 bronze badges

answered Feb 1, 2017 at 23:06

anonmyous1234

1

Collectives™ on Stack Overflow

Using SQL Server stored procedures from Python (pyodbc)

7 Answers 7

5 Comments

6 Comments

1 Comment

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

5 Comments

6 Comments

1 Comment

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related