在python中转义MySQL的引号 [英] Escaping quotes for MySQL in python

问题描述

遵循关于此线程的建议，我将我的 list 作为 string 类型存储在 MySQL 数据库 中，但是，我遇到了这个错误:

Following the advice on this thread, I am storing my list as string type in MySQL database, but, I'm facing this error:

_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 \'foo_bar" but I can\\\'t bar.\', u\'high\', 0]]")\' at line 1')

在一些像这样的列表条目上:

on some list entries like this one:

var1 = 'Name'
var2 = 'Surname'
var3 = 15
var4 = [u'The Meter', [[u'Black', u'foo foo bar bar "foo_bar" but I can\'t bar', u'high', 0]]]

我认为这是因为 foo_bar 的开头有一个双引号，我正在使用以下代码在数据库中创建条目:

I figured that's because there's a double quote at the beginning of foo_bar and I am using the following code to make entries in the database:

SQL = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES ("{}", "{}", "{}", "{}")'.format(var1, var2, var3, var4)
cursor.execute(SQL)

并且双引号没有被转义.如果我从以下位置删除双引号:
("{}", "{}", "{}", "{}")，我得到同样的错误，我尝试使用不同的字符串格式(使用 %s)，但这也不起作用.
有什么想法吗?

And the double quotes are not being escaped. If i remove the double quotes from:
("{}", "{}", "{}", "{}"), I get the same error, I tried using a different string formatting, (using %s) but that didn't work either.
Any ideas?

推荐答案

不要使用字符串格式来插入 SQL 值.使用 SQL 参数:

Do not use string formatting to interpolate SQL values. Use SQL parameters:

SQL = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES (%s, %s, %s, %s)'
cursor.execute(SQL, (var1, var2, var3, var4))

这里的%s是SQL参数；然后数据库会为您转义这些值(作为`cursor.execute 的第二个参数传入).

Here the %s are SQL parameters; the database then takes care of escaping the values (passed in as the 2nd argument to `cursor.execute) for you.

您需要使用的确切语法取决于您的数据库适配器；有些使用 %s，有些使用 ? 作为占位符.

Exactly what syntax you need to use depends on your database adapter; some use %s, others use ? for the placeholders.

您不能以其他方式将 Python 容器(如列表)用于这些参数.您必须先将其序列化为字符串格式；您可以为此使用 JSON，但是您还必须记住在查询数据库时再次将 JSON 解码为 Python 字符串.这就是另一个问题的答案试图传达的内容.

You can't otherwise use Python containers, like a list, for these parameters. You'd have to serialise that to a string format first; you could use JSON for that, but then you'd also have to remember to decode the JSON into a Python string again when you query the database. That's what the answers to the other question tried to convey.

例如，如果 var4 是列表，您可以使用:

For example, if var4 is the list, you could use:

cursor.execute(SQL, (var1, var2, var3, json.dumps(var4)))

这篇关于在python中转义MySQL的引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！