I have a pyodbc select where I wish to pass multiple parameterized variables. When one of the variables is passed in its native format with only a single parameter variable, the select statement completes successfully. However, when I pass two parameters, that same variable seemingly results in a not found situation. When I then hardcode a variable and pass THAT into the two-variable query, it works.
Here's the one where I use the variable natively. The variable comes from a prior select statement into a dictionary. I then assign the dictionary value to a variable. It maintains the type of "pyodbc.row". I did another run where I assigned a variable to that as "str(this_incid)" and the results were identical. And I should stress that in other parts of the code, where I pass the pyodbc.type to a single-parameter query, it works just fine. I could also post those results, but I think this is long enough already.
auth_type = 'OC'
print("This incid ", this_incid, "type", type(this_incid))
print(str(this_incid))
print("Auth type ", auth_type, "type", type(auth_type))
bhfc_cursor.execute("""\
select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH
INNER JOIN DBO.INC_MAIN ON inc_auth.inci_id = inc_main.inci_id
where inc_auth.auth_type = (?) and inc_main.inci_id = (?) """, str(auth_type), str(this_incid))
oic_details = bhfc_cursor.fetchone()
print("oic details: " ,oic_details)
This incid ('_53N0TYDNM', ) type <class 'pyodbc.Row'>
('_53N0TYDNM', )
temp incid _53M07J2D6 type <class 'str'>
Auth type OC type <class 'str'>
oic details: None
Here's the one where I just do a hard assignment of the second variable:
auth_type = 'OC'
temp_incid = '_53M07J2D6'
print("temp incid ", temp_incid, "type", type(temp_incid))
print("Auth type ", auth_type, "type", type(auth_type))
bhfc_cursor.execute("""\
select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH
INNER JOIN DBO.INC_MAIN ON inc_auth.inci_id = inc_main.inci_id
where inc_auth.auth_type = (?) and inc_main.inci_id = (?) """, str(auth_type), str(temp_incid))
oic_details = bhfc_cursor.fetchone()
print("oic details: " ,oic_details)
and that one works!
temp incid _53M07J2D6 type <class 'str'>
Auth type OC type <class 'str'>
oic details: ('29-2190 ', 'FC ', ' ', datetime.datetime(2018, 1, 6, 8, 48))
I do see the difference in my print statements where in the one that fails it shows surrounded by single ticks/apostrophes, whereas in the hard assignment scenario it does not. I'm not sure what to make of that.
Ah, what the heck- here's one of the single-parameter queries which succeeds with that variable in its native pyodbc.row type:
print("Unit Record this_incid", this_incid, "type ", type(this_incid))
bhfc_cursor.execute("""\
select INC_UNIT.* from INC_UNIT
inner join INC_MAIN on inc_unit.inci_no = inc_main.inci_no
where inc_main.INCI_ID = (?)""""", this_incid)
unit_details = bhfc_cursor.fetchall()
print(unit_details)
Unit Record this_incid ('_53N0TYDNM', ) type <class 'pyodbc.Row'>
unit details: [('_53N0TYEZE', '46132', datetime.datetime(2018, 1, 4, 0, 0), '1800511 ', 'CMD29 (snip)
You'll note that I didn't "typecast" the variables in this one (Not sure what that means, other than wrapping them in "str()". I did that because when I didn't do that with multiple variables I received a sql bind parameter error, and the alleged solution was to "type cast" the variables as posited in this answer: python: Invalid data type (0) (SQLBindParameter)
Anybody have any ideas/suggestions? Thanks.
The Python DBAPI Spec v2.0, a.k.a. "PEP 249", specifies that for the .execute() method:
Parameters may be provided as sequence or mapping and will be bound to variables in the operation.
That is, strict adherence to the spec requires that the parameter values be a single sequence (list, tuple, or range), or a mapping (dict), depending on the specific implementation of the software layer. With pyodbc that would be something like
sql = "SELECT * FROM my_table WHERE city = ? AND state = ?"
params = ("Nashville", "TN") # tuple
crsr.execute(sql, params)
For convenience, pyodbc offers an extension to the DBAPI spec allowing us to pass multiple parameters as individual values, e.g.,
sql = "SELECT * FROM my_table WHERE city = ? AND state = ?"
crsr.execute(sql, "Nashville", "TN")
In your case if you have a single value in a pyodbc.Row object (which acts like a sequence), both methods are equivalent
my_state = crsr.execute("SELECT 'TN'").fetchone()
print(type(my_state)) # <class 'pyodbc.Row'>
print(my_state) # ('TN', )
sql = "SELECT * FROM my_table WHERE state = ?"
crsr.execute(sql, my_state)
# which is equivalent to
# crsr.execute(sql, ('TN', ))
crsr.execute(sql, my_state[0])
# which is equivalent to
# crsr.execute(sql, 'TN')
However, it gets more complicated when we have more than one parameter. This query won't work correctly …
sql = "SELECT * FROM my_table WHERE city = ? AND state = ?"
crsr.execute(sql, "Nashville", my_state)
# which is equivalent to
# crsr.execute(sql, "Nashville", ('TN', ))
… because the parameters are now an inconsistent mix of scalar ("Nashville") and sequence (('TN', )) values. You can fix that by either combining the values into a tuple
params = ("Nashville", ) + tuple(my_state)
print(type(params)) # <class 'tuple'>
print(params) # ('Nashville', 'TN')
crsr.execute(sql, params)
or by pulling the individual values out of the pyodbc.Row object using *
crsr.execute(sql, "Nashville", *my_state)
# which is equivalent to
# crsr.execute(sql, "Nashville", "TN")