如何防止 PyMySQL 转义标识符名称? [英] How to prevent PyMySQL from escaping identifier names?

问题描述

我在 Python 2.7 中使用 PyMySQL，并尝试执行以下语句:

I am utilizing PyMySQL with Python 2.7 and try to execute the following statement:

'INSERT INTO %s (%s, %s) VALUES (%s, %s) ON DUPLICATE KEY UPDATE %s = %s'

使用以下参数:

('artikel', 'REC_ID', 'odoo_created', u'48094', '2014-12-23 10:00:00', 'odoo_modified', '2014-12-23 10:00:00')

总是导致:

{ProgrammingError}(1064, u"您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册在artikel"(REC_ID"、odoo_created")附近使用的语法\n
值 ('48094', '2014-12-' 在第 1 行")

{ProgrammingError}(1064, u"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 ''artikel' ('REC_ID', 'odoo_created')\n
VALUES ('48094', '2014-12-' at line 1")

在我看来，PyMySQL 在格式化过程中会转义所有字符串.如何防止数据库标识符(如表名和列名)出现这种情况?它破坏了语句，因为与表 (SELECT * FROM table) 相比，字符串文字 (SELECT * FROM "table") 中的 SELECT 是不可能的代码>).

Which seems to me like PyMySQL is escaping all strings during formatting. How can I prevent that for database identifiers like table and column names? It corrupts the statement, since SELECTs from string literals (SELECT * FROM "table") are not possible in comparison to tables (SELECT * FROM table).

推荐答案

这就是 DB API 替换的重点.它转义值.您真的不需要转义表/列名称.

That's the point of DB API's substitution. It escapes values. You really shouldn't need to escape table/column names.

我会使用类似的东西:

execute('''INSERT INTO {} ({}, {}) VALUES (%s, %s) ON DUPLICATE KEY UPDATE {} = %s'''
    .format('artikel', 'REC_ID', 'odoo_created', 'odoo_modified'),
    (u'48094', '2014-12-23 10:00:00', '2014-12-23 10:00:00')
)

或者，你知道，直接写名字.

Or, you know, just write the names directly.

这篇关于如何防止 PyMySQL 转义标识符名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！