PYODBC 不喜欢 %,“SQL 包含 2 个参数标记,但提供了 1 个参数." [英] PYODBC does not like %, "The SQL contains 2 parameter markers, but 1 parameters were supplied."
问题描述
所以我目前正在将 Python 与 SQL 联系起来以提取客户信息.不幸的是,我遇到了一些关于 SQL 的错误.我正在尝试使用 LIKE 运算符和 % 通配符,但由于 Python 不喜欢 %.结果,它假装 %s 之间的变量不存在.这就是我的意思:
So I'm currently linking Python with SQL to pull out customer information. Unfortunately, I'm getting some errors with regards to SQL. I am trying to use the LIKE operator, and the % wildcard, but I keep getting errors because Python does not like %. As a result, it pretends that variable between the %s do not exist. Here's what I mean:
SELECT custnbr,
firstname,
middleint,
lastname
FROM lqppcusmst
WHERE custnbr = ? AND firstname LIKE ?
现在,我只是在测试它,所以我只使用客户编号和名字.我给它一个值:
Right now, I'm just testing it out, so I'm just using the customer number, and the first name. I give it a value:
remote_system_account_number = request.DATA['remote_system_account_number']
remote_system_first_name = request.DATA['remote_system_first_name']
因为我写的是在数据库中搜索客户,所以可能会有空白条目,所以我是这样的:
Since what I'm writing is for searching customers within the database, there's a chance there could be blank entries, so I have it like such:
if remote_system_account_number != '':
SQL_where += ' custnbr = ? '
parameters += "remote_system_account_number"
if remote_system_first_name != '':
SQL_where += ' AND firstname LIKE ? '
parameters += ", %remote_system_first_name%"
所以我认为这会奏效,但没有.当我像这样执行它时:
So I thought this would work, but it didn't. When I execute it like such:
database_cursor.execute(customer_information_SQLString + SQL_where, parameters)
我明白了:
ProgrammingError: ('The SQL contains 2 parameter markers, but 1 parameters were supplied', 'HY000')
有人知道怎么处理吗?
推荐答案
parameters
不应该是逗号分隔的字符串,它应该是具有多个值匹配的可枚举(列表或类似)SQL 中占位符的数量.例如:
parameters
should not be a comma separated string, it should be an enumerable (a list or similar) with a number of values matching the number of placeholders in your SQL. For instance:
parameters = []
if remote_system_account_number != '':
SQL_where += ' custnbr = ? '
parameters.append("remote_system_account_number")
if remote_system_first_name != '':
SQL_where += ' AND firstname LIKE ? '
parameters.append("%remote_system_first_name%")
这篇关于PYODBC 不喜欢 %,“SQL 包含 2 个参数标记,但提供了 1 个参数."的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!