Pyodbc Calling a stored procedure with parameters

Question

I've been trying to call this stored procedure with parameters to no avail. I think I've figured out the parameter part, but now I think I'm just plain calling the stored procedure wrong. I tried to document my different approaches as best possible with their error messages (below). How do I call a stored procedure with parameters and write to a dataframe?

Try A: Using a placeholder join with params in dictionary and %s for variable

params1 = {'mo1_start_date' : '12/30/2019', 'mo1_end_date' : '01/26/2020'}
placeholders = ','.join('?' for i in range(len(params1.values())))  # '?,?'

interconnectmo1 = """exec TNT.dbo.abc \
    @start_date = %(mo1_start_date)s  -- datetime \
  , @end_date = %(mo1_end_date)s    -- datetime \
  , @syscode = NULL           -- varchar(8000)  \
  , @estimate_ids = NULL       -- varchar(8000)""" %placeholders

sqlmo1 = pd.read_sql(interconnectmo1, conn, params1)

Error A: TypeError: format requires a mapping

Try B: No placeholder join with params in dictionary and ? for variable

params1 = {'mo1_start_date' : '12/30/2019', 'mo1_end_date' : '01/26/2020'}

interconnectmo1 = """exec TNT.dbo.abc\
    @start_date = ? -- datetime \
  , @end_date = ?    -- datetime \
  , @syscode = NULL           -- varchar(8000)  \
  , @estimate_ids = NULL       -- varchar(8000)""" 

sqlmo1 = pd.read_sql(interconnectmo1, conn, params1)

Error B:[ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)

Try C: Using a placeholder join with params in list and %s for variable

params1 = {'mo1_start_date' : '12/30/2019', 'mo1_end_date' : '01/26/2020'}
paramsmo1=list(params1.values())
placeholders = ','.join('?' for i in range(len(paramsmo1)))  # '?,?'

interconnectmo1 = """exec TNT.dbo.abc\
    @start_date = %(mo1_start_date)s -- datetime \
  , @end_date = %(mo1_end_date)s    -- datetime \
  , @syscode = NULL           -- varchar(8000)  \
  , @estimate_ids = NULL       -- varchar(8000)""" %placeholders

sqlmo1 = pd.read_sql(interconnectmo1, conn, paramsmo1)

Error C: TypeError: format requires a mapping

Try D: No placeholder join with params in dictionary and %s for variable

params1 = {'mo1_start_date' : '12/30/2019', 'mo1_end_date' : '01/26/2020'}
interconnectmo1 = """exec TNT.dbo.abc\
    @start_date = "%(mo1_start_date)s" -- datetime \
  , @end_date =  "%(mo1_end_date)s"   -- datetime \
  , @syscode = NULL           -- varchar(8000)  \
  , @estimate_ids = NULL       -- varchar(8000)""" % (params1)

sqlmo1 = pd.read_sql(interconnectmo1, conn, params1)

Error D: Procedure or function expects parameter '@end_date', which was not supplied.

Try E: Trying with cursor..

params1 = {'mo1_start_date' : '12/30/2019', 'mo1_end_date' : '01/26/2020'}
csr = conn.cursor()
interconnectmo1 = csr.execute("""exec TNT.dbo.abc \
    @start_date = "%(mo1_start_date)s" -- datetime \
  , @end_date =  "%(mo1_end_date)s"   -- datetime \
  , @syscode = NULL           -- varchar(8000)  \
  , @estimate_ids = NULL       -- varchar(8000)""" % (params1))

csr.execute(interconnectmo1, (params1))

Error E: Procedure or function expects parameter '@end_date', which was not supplied.

score 2 · Accepted Answer · answered Apr 27 '20 at 18:48

I finally got this to work. There were two issues with this code.

The data type comment callouts. -- datetime and -- varchar(8000) were a problem. Even when my code recognized the first parameter, it could not find the second parameter because of this disruption.
The dictionary input as a parameter just didn't work. I'm not sure if it's not a capability or if I just never got the correct syntax to get it to work, but it didn't work. I looked into binding the parameters too- No cigar. I even tried list input and was not successful.

mo1_start_date='12/30/2019' 
mo1_end_date='1/26/2020' 
interconnectmo1 = """exec TNT.dbo.abc
@start_date = ? , @end_date =  ? , @syscode = NULL , @estimate_ids = NULL""" 
sqlmo1 = pd.read_sql_query(interconnectmo1, conn, params=(mo1_start_date, mo1_end_date))

The most straightforward way won out. There was NO need to complicate this at all. If you have a dictionary and cannot get your parameters in this format, then I guess you're forced to find a solution for dictionaries. But for this problem, I was the one creating the dictionary unnecessarily. I just moved to a simpler way of defining my params and viola.

I hope this helps someone, because it took me days and SEVERAL code boards to finally back out of my prior approach to find this winning one!

Pyodbc Calling a stored procedure with parameters

1 Answers1