Python - SQL语句中的单引号PyODBC
问题描述:
我已经尝试了几个驱动程序:pymsql,pyobdc,并且在SQL中仍然有格式单引号的问题。下面的代码实例:Python - SQL语句中的单引号PyODBC
CASE 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"
CASE 2.
UPDATE_SQL3 = """
UPDATE STATION
SET
STATION_NAME = %s,
STATION_TITLE = %s,
ACTIVE = %s
WHERE
STATION_ID = %s
"""
我赶错误:
('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The'. (102) (SQLExecDirectW)")
一次title = u'102.7 The Fan'
CASE 3.
UPDATE_SQL3 = """
UPDATE STATION
SET
STATION_NAME = '%s',
STATION_TITLE = '%s',
ACTIVE = %s
WHERE
STATION_ID = %s
"""
错误:
('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 's'. (102) (SQLExecDirectW)")
其中name = u'Power Michiana\\'s Hits and Hip Hop'
什么是处理它的正确方法?
答
您的“CASE 1”基本上是正确的,但您没有正确地将参数传递给conn.execute
。而不是试图用字符串格式化(通过%
运营商),只需将元组的第二个参数传递给.execute
,就像这样:
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.")
的情况下,3名做= u'Power Michiana \的点击和嘻哈' – akalikin
我如何正确地做到这一点?我试图替换字符--' title = title.replace(“\\”,“”)',但我仍然看到 - 'title = u'Power Michiana \''Hits and Hip Hop'' – SpanishBoy