python - 使用python将带有特殊字符的值插入mysql表时出错

Question

使用动态列表将值插入 mysql 表时出现问题。我一直遇到问题，引号指向的是表而不是值。

这是代码:

lst = ['Pid', 'Base', 'Size', 'LoadCount', 'Path','Casename']
lst2 =['888', '1213726720', '61440', '65535', '\\SystemRoot\\System32\\smss.exe', 'wean']

table_name ="test"
insert_intotable = "INSERT INTO "  + table_name + "(" + ",".join(lst) + ") VALUES (" + ",".join(lst2) + ")"
print insert_intotable
c.execute(insert_intotable)
conn.commit()
c.close()
conn.close()

这会导致以下错误:

> Traceback (most recent call last):   File "pp.py", line 53, in
> <module>
>     c.execute(insert_intotable)   File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in
> execute
>     self.errorhandler(self, exc, value)   File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in
> defaulterrorhandler
>     raise errorclass, errorvalue
> _mysql_exceptions.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
> '\\SystemRoot\\System32\\smss.exe,test)' at line 1")

是什么导致了这个语法问题？

Answer 1

插入字符串值时使用“单引号”

更新:

将第二行更改为此

 lst2 =['888', '1213726720', '61440', '65535', '\'\\SystemRoot\\System32\\smss.exe\'', '\'wean\'']

因为，这是错误的SQL查询:

 INSERT INTO test (Path) VALUES(\SystemRoot\System32\smss.exe)

您的代码会生成这样的查询。

您应该引用 varchar 值:

 INSERT INTO test (Path) VALUES('\SystemRoot\System32\smss.exe')