CODEWITHSUNDEEP
sqlparameterspyodbc
CT00
CT00

Reputation: 77

Passing a parameter to a sql query using pyodbc failing

I have read dozens of similar posts and tried everything but I still get an error message when trying to pass a parameter to a simple query using pyodbc. Apologies if there is an answer to this elsewhere but I cannot find it

I have a very simple table:

select * from Test

yields

a
b
c

This works fine:

import pyodbc
import pandas
connection = pyodbc.connect('DSN=HyperCube SYSTEST',autocommit=True)
result = pandas.read_sql("""select * from Test where value = 'a'""",connection,params=None)
print(result)

result:

  value
0     a

However if I try to do the where clause with a parameter it fails

result = pandas.read_sql("""select * from Test where value = ?""",connection,params='a')

yields

Error: ('01S02', '[01S02] Unknown column/parameter value (9001) (SQLPrepare)')

I also tried this

cursor = connection.cursor()
cursor.execute("""select * from Test where value = ?""",['a'])
pyodbcResults = cursor.fetchall()

and still received the same error

Does anyone know what is going on? Could it be an issue with the database I am querying?

PS. I looked at the following post and the syntax there in the first part of answer 9 where dates are passed by strings looks identical to what I am doing

pyodbc the sql contains 0 parameter markers but 1 parameters were supplied' 'hy000'

Thanks

Upvotes: 3

Views: 11631

Answers (1)

Adam Silenko
Adam Silenko

Reputation: 3108

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)[https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html]

params : list, tuple or dict, optional, default: None

example:

cursor.execute("select * from Test where value = %s",['a'])

or Named arguments example:

result = pandas.read_sql(('select * from Test where value = %(par)s'),
               db,params={"par":'p'})

in pyodbc write parms directly after sql parameter:

cursor.execute(sql, *parameters)

for example:

onepar = 'a'
cursor.execute("select * from Test where value = ?", onepar)

cursor.execute("select a from tbl where b=? and c=?", x, y)

Upvotes: 3

Related Questions