CODEWITHSUNDEEP
pythonsql-serverpyodbc
gulsahaha
gulsahaha

Reputation: 13

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

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.

Upvotes: 0

Views: 1473

Answers (1)

Paulo Scardine
Paulo Scardine

Reputation: 77251

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

Upvotes: 1

Related Questions