Python MySQLdb 转义字符 : query works in MySQL but not in python MySQLdb

Question

我正在尝试通过 Python 的 MySQLdb 库将数据从 Pandas(从 CSV 导入)传递到 MySQL 数据库。当文字反斜杠发挥作用时，我遇到了麻烦。我从原始输入中转义了单个反斜杠，因此 Python 知道它们是文字反斜杠，而不是对后续字符的转义。但是当我尝试执行 INSERT 查询时，MySQLdb 说存在语法错误。但这是令人困惑和沮丧的部分:如果我将确切的字符串复制/粘贴到 MySQL 中，它会毫无问题地执行。

我已尝试使数据和结构尽可能接近实际数据，但为了保护隐私对其进行了更改。请注意，在第一行的 SourceSystemID 列的末尾和第二行的 MiddleInitial 列的末尾有两个类似的违规值。

In [39]: test
Out[39]: 

  ehrSystemID SourceSystemID LastName FirstName MiddleInitial  Sex  
0   fakePlace           ABC\      NaN       NaN           NaN  NaN   
1   fakePlace            XYZ    Smith      John             \    M   
          npi  deaNumber LicenseNumber ProvSpecialty  dob  
0  1234567890  AB1234567       !123456      Internal  NaN  
1         NaN        NaN       B123456      Internal  NaN  

将这些行的值转换为字符串以附加到 INSERT 语句的末尾(请注意，所有 MySQL 列都是 varchar，因此所有值都用单引号引起来)

In [40]: testVals
Out[40]: "('fakePlace', 'ABC\\', '', '', '', '', '1234567890', 'AB1234567', '!123456', 'Internal', ''), ('fakePlace', 'XYZ', 'Smith', 'John', '\\', 'M', '', '', 'B123456', 'Internal', '')"

我传递给 MySQLdb 的命令和产生的错误:

In [41]: testCmd1
Out[41]: "INSERT INTO source_providers (ehrSourceID, sourceSystemID, nameLast, nameFirst, nameMiddle, sex, npiRaw, dea, licenseNumber, specialty1, dobRaw) VALUES ('fakePlace', 'ABC\\', '', '', '', '', '1234567890', 'AB1234567', '!123456', 'Internal', ''), ('fakePlace', 'XYZ', 'Smith', 'John', '\\', 'M', '', '', 'B123456', 'Internal', '')"

In [42]: db.Cur.execute(testCmd1)
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-42-32fe62e740d8> in <module>()
----> 1 db.Cur.execute(testCmd1)

/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.8-intel.egg/MySQLdb/cursors.pyc in execute(self, query, args)
    200             del tb
    201             self.messages.append((exc, value))
--> 202             self.errorhandler(self, exc, value)
    203         self._executed = query
    204         if not self._defer_warnings: self._warning_check()

/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.8-intel.egg/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)
     34     del cursor
     35     del connection
---> 36     raise errorclass, errorvalue
     37 
     38 re_numeric_part = re.compile(r"^(\d+)")

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1234567890', 'AB1234567', '!123456', 'Internal', ''), ('fakePlace', 'XYZ', 'Smit' at line 1")

直接在MySQL中成功执行确切的命令:

mysql> INSERT INTO source_providers (ehrSourceID, sourceSystemID, nameLast, nameFirst, nameMiddle, sex, npiRaw, dea, licenseNumber, specialty1, dobRaw) VALUES ('fakePlace', 'ABC\\', '', '', '', '', '1234567890', 'AB1234567', '!123456', 'Internal', ''), ('fakePlace', 'XYZ', 'Smith', 'John', '\\', 'M', '', '', 'B123456', 'Internal', '');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

将字符串传递给 MySQL API 时会发生什么事情吗？如何避免过滤掉有问题的行并单独手动插入它们？这涉及数万行，因此我专注于自动化流程。

谢谢。

Answer 1

事实证明，我的困惑在于 Python 如何打印查询字符串，再加上我自己的代码中存在错误。首先，错误:我错误地使用 str.replace() 将单个文字反斜杠替换为转义双斜杠:

sqlCmd.replace('\\', '\\\\') 

因此，当 Python 在打印字符串时显示双斜杠时，我认为它已经成功替换了单斜杠。正确的代码(以防读到这里的人犯同样的错误)是:

sqlCmd = sqlCmd.replace('\\', '\\\\')

第二个混淆来源是 Python 解释器使用 __repr__()，它自动插入一个转义反斜杠用于显示，但这个转义反斜杠 实际上不是在原始字符串中。 Python 假定您足够聪明，知道这一点。结果我不是。 ;-)

__repr__() 和反斜杠的简短附加说明可在另一个 stackoverflow 问题的答案中找到 here .