使用pyodbc将参数传递给SQL查询失败 [英] Passing a parameter to a sql query using pyodbc failing

问题描述

我已经阅读了数十篇类似的文章，并尝试了所有方法，但是当尝试使用pyodbc将参数传递给简单查询时，我仍然收到错误消息.抱歉，在其他地方找不到答案

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

收益

a
b
c

这很好:

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)

结果:

  value
0     a

但是，如果我尝试使用参数执行where子句，则会失败

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')

收益

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

我也尝试过

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.我看了下面的帖子，答案9的第一部分那里的语法，其中通过字符串传递日期看起来与我正在做的事情

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，该sql包含0个参数标记，但提供了1个参数''hy000'

谢谢

推荐答案

pandas.read_sql(sql，con，index_col = None，coerce_float = True，params = None，parse_dates = None，column = None，chunksize = None)[ params:列表，元组或字典，可选，默认值:无

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

示例:

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

或命名参数示例:

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

在pyodbc中，在sql参数后直接编写parms:

in pyodbc write parms directly after sql parameter:

cursor.execute(sql，* parameters)

cursor.execute(sql, *parameters)

例如:

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

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

这篇关于使用pyodbc将参数传递给SQL查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！