字符串插值导致 MysqlDB 错误
我正在尝试使用 mysqlDB，但遇到了一个对我来说根本没有意义的问题。
我的问题集中在以下执行语句中的表名。
这有效：
c.execute(u'''insert into AIN0 (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
(str(date), int(raw), int(formula), 0.1))
这些都不起作用：
c.execute(u'''insert into %s (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
(str("AIN0"), str(date), int(raw), int(formula), 0.1))
c.execute(u'''insert into 'AIN0' (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
(str(date), int(raw), int(formula), 0.1))
问题是，两个无效的结果都会导致相同的错误消息：
编程错误：（1064，“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，以获取在附近使用的正确语法” ''AIN0'（Date_Info、Raw_Data、Calculation_Formula、Calculated_Data）值（第 1 行的“2011”）
请注意，在错误消息中，AIN0 被单引号括起来，即使在我使用字符串插值运算符插入它的情况下也是如此可以肯定的是，我在工作案例中故意犯了一个语法错误，以便查看 MySQL 正在查看的内容。
编程错误：（1064，“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本对应的手册，以了解要在附近使用的正确语法”）） AIN0（Date_Info、Raw_Data、Calculation_Formula、Calculated_Data）值（第 1 行的“201”）
果然，在 MySQL 正常工作的值中，AIN0 没有被单引号包围，这让我相信这就是导致错误。
当我使用字符串插值时，为什么在字符串周围插入单引号以及如何让它们停止？
I'm trying to use mysqlDB, but I've run into a problem that doesn't make sense to me at all.
My problem is centered around the table name in the following execute statements.
This works:
c.execute(u'''insert into AIN0 (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
(str(date), int(raw), int(formula), 0.1))
Neither of these work:
c.execute(u'''insert into %s (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
(str("AIN0"), str(date), int(raw), int(formula), 0.1))
c.execute(u'''insert into 'AIN0' (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
(str(date), int(raw), int(formula), 0.1))
The thing is, both of the non-working ones result in the same error message:
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
''AIN0' (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values ('2011' at line 1")
Notice that in the error message, AIN0 is surrounded by single quotes, even in the case where I inserted it using the string interpolation operator. Just to be sure, I made an intentional syntax error in the working case in order to see what MySQL was looking at.
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'))
AIN0 (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values ('201' at line 1")
Sure enough, in the one that MySQL works correctly with, AIN0 is not surrounded by single quotes leading me to believe that that is what is causing the error.
Why are single quotes being inserted around my strings when I use string interpolation and how do I get them to stop?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果“字符串插值”是指 Python 的字符串格式化，那么这不是您正在使用的。
cursor.execute()执行 SQL 参数插入，它有自己的一套规则。发生这种混乱的原因是某些数据库接口（包括 mysqlDB）使用%s作为占位符...其他数据库接口使用例如?当您使用参数化 SQL 时，占位符可以仅出现在 SQL 中可以出现表达式的位置。表名不是表达式。当您的参数是字符串时，它会用单引号括起来，以便在生成的 SQL 中它看起来像 SQL 字符串文字。这涉及到双单引号，例如，在 Python 中显示为字符串文字的内容，如
"O'Brien"在 SQL 中变成'O''Brien'。因此，您无法阻止它用单引号引起来。您需要执行两个步骤：(1) 使用 Python 字符串格式构建一个 SQL 字符串，在您想要的位置插入
AIN0。 (2) 在cursor.execute()中使用该 SQL 字符串If by "string interpolation" you mean like Python's string formatting, that's NOT what you are using.
cursor.execute()does SQL parameter insertion, which has its own set of rules. The confusion happens because some database interfaces (including mysqlDB) use%sas a placeholder ... other database interfaces use e.g.?When you use parameterised SQL, the place holders can appear only where an expression can appear in SQL. A table name is not an expression. When your argument is a string, it is wrapped in single quotes so that in the resultant SQL it looks like an SQL string literal. This involves doubling single quotes e.g. what in Python would appear as a string literal like
"O'Brien"becomes'O''Brien'in SQL.So, you can't stop it from wrapping single quotes arround it. You need to do two steps: (1) build an SQL string that inserts
AIN0where you want it, using Python string formatting. (2) Use that SQL string in yourcursor.execute()