Python - 在 SQL 语句 PyODBC 中格式化单引号 [英] Python - format single quote in SQL statement PyODBC
问题描述
我已经尝试了几个驱动程序:pymsql、pyobdc,但仍然存在 SQL 中的格式单引号问题.以下代码示例:
I already tried a couple of drivers: pymsql, pyobdc and still have issue with format single quote in SQL. Examples of code below:
案例 1.
import pyodbc
UPDATE_SQL3 = """
UPDATE STATION
SET
STATION_NAME = ?,
STATION_TITLE = ?,
ACTIVE = ?
WHERE
STATION_ID = ?
"""
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=local;DATABASE=DB;UID=me;PWD=pass')
cursor = conn.cursor()
cursor.execute(UPDATE_SQL3 %
(name,
title,
active,
id
))
此代码无法编译:
并非所有参数都在字符串格式化期间转换"
"not all arguments converted during string formatting"
案例 2.
UPDATE_SQL3 = """
UPDATE STATION
SET
STATION_NAME = %s,
STATION_TITLE = %s,
ACTIVE = %s
WHERE
STATION_ID = %s
"""
我发现错误:
('42000', "[42000] [Microsoft][ODBC SQL Server 驱动程序][SQL服务器]'The' 附近的语法不正确.(102) (SQLExecDirectW)")
('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The'. (102) (SQLExecDirectW)")
曾经title = u'102.7 The Fan'
案例 3.
UPDATE_SQL3 = """
UPDATE STATION
SET
STATION_NAME = '%s',
STATION_TITLE = '%s',
ACTIVE = %s
WHERE
STATION_ID = %s
"""
错误:
('42000', "[42000] [Microsoft][ODBC SQL Server 驱动程序][SQL服务器]'s' 附近的语法不正确.(102) (SQLExecDirectW)")
('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 's'. (102) (SQLExecDirectW)")
where name = u'Power Michiana\\'s Hits and Hip Hop'
处理它的正确方法是什么?
What is a correct approach to handling it?
推荐答案
您的CASE 1"基本上是正确的,但是您没有正确地将参数传递给 conn.execute
.不要尝试使用字符串格式(通过 %
运算符),只需将元组作为第二个参数传递给 .execute
,如下所示:
Your "CASE 1" is essentially correct, but you are not passing the parameters to conn.execute
properly. Instead of trying to use string formatting (via the %
operator), simply pass the tuple as the second argument to the .execute
, like this:
import pyodbc
# test data
name = u"Power Michiana's Hits and Hip Hop"
title = u"(some title)"
active = False
id = 1
conn_str = "DSN=myDb_SQLEXPRESS"
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
UPDATE_SQL3 = """\
UPDATE STATION
SET
STATION_NAME = ?,
STATION_TITLE = ?,
ACTIVE = ?
WHERE
STATION_ID = ?
"""
cursor.execute(UPDATE_SQL3, (name, title, active, id))
conn.commit()
conn.close()
print("Done.")
这篇关于Python - 在 SQL 语句 PyODBC 中格式化单引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!