Executing stored procedure with multiple parameters in Python using different parameter sets

Question

I need to execute a stored procedure having 2 date parameters from Python. I want to change parameters and execute procedure multiple times in a loop using parameters set which are in a dataframe rows. My code is as below;

def _get_dataset(date_param1, data_param2):
    sql="exec [dbo].[SP_Name] (%s,%s)" % (date_param1, 
    date_param2)
    cnxn = pyodbc.connect('DRIVER={SQL 
    Server};SERVER=xxx.xxx.xx.xx;DATABASE=db;UID=userid;PWD=password')
    cursor = cnxn.cursor()
    data = pd.read_sql(sql,cnxn)
    return data 

for i in range(len(dataframe)):
    first_date = df.iloc[i][0]
    second_date = df.iloc[i][1]
    _get_dataset(str(first), str(second))

The error I am getting;

DatabaseError: Execution failed on sql 'exec [dbo].[SP_name] (2019-06-25,2019-06-24)': ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '2019'. (102) (SQLExecDirectW)")

What is wrong in the code? Thanks in advance.

Try single quotes around the dates on the sql string: "exec [dbo].[SP_Name] ('%s', '%s')" - I don't have a MS SQLServer around for testing. — Paulo Scardine
– Paulo Scardine, Commented Jun 26, 2019 at 10:17
@LajosArpad The actual query is supposed to be like 'exec SP_name '2019-06-24' '2019-06-25'''. — gulsahaha
– gulsahaha, Commented Jun 26, 2019 at 10:24
If you execute that directly in SQL Server, without Python, is it working? — Lajos Arpad
– Lajos Arpad, Commented Jun 26, 2019 at 10:25

Paulo Scardine · Accepted Answer · 2019-06-26 11:25:28Z

1

I don't have an SQL Server around for testing, but it is better to pass the parameters using read_sql as it will use the underlying database driver to perform the string interpolation in a safe way:

def _get_dataset(date_param1, data_param2):
    sql = "exec [dbo].[SP_Name] (?, ?)" 
    cnxn = pyodbc.connect(
       'DRIVER={SQL Server};SERVER=xxx.xxx.xx.xx;DATABASE=db;UID=userid;PWD=password'
    )
    cursor = cnxn.cursor()
    data = pd.read_sql(sql, cnxn, params=(date_param1, data_param2))
    return data

You can also use an ORM like SQLAlchemy to pass parameters in a driver-neutral way, so your code is not tied to a specific driver syntax:

In [559]: import sqlalchemy as sa

In [560]: pd.read_sql(sa.text('SELECT * FROM data where Col_1=:col1'), 
   .....:             engine, params={'col1': 'X'})
   .....: 
Out[560]: 
   index  id                        Date Col_1  Col_2  Col_3
   0      0  26  2010-10-18 00:00:00.000000     X   27.5      1

edited Jun 26, 2019 at 11:25

answered Jun 26, 2019 at 10:27

Paulo Scardine

78.2k12 gold badges134 silver badges153 bronze badges

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

7 Comments

gulsahaha Over a year ago

I didn't work. This time I get error as below; DatabaseError: Execution failed on sql 'exec [dbo].[SP_Name] (?, ?)': ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

Paulo Scardine Over a year ago

Check your database driver documentation for which of the five syntax styles, described in PEP 249’s paramstyle, is supported. I'm not sure it is ? or %(name)s for SQL Server. Also, I don't know what type the parameters are - if they are dates, you may want to convert them to a string in a format you are sure SQL Server will understand.

Paulo Scardine Over a year ago

Wanna zoom? I have a few minutes to help you before my first commitments today.

gulsahaha Over a year ago

Thanks @PauloScardine, but now I am not able to do zoom.

Paulo Scardine Over a year ago

Check my update about using SQLAlchemy to iron out differences between database drivers. @GulsahAyhan

|

Collectives™ on Stack Overflow

Executing stored procedure with multiple parameters in Python using different parameter sets

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related