在python中使用带有MySQL的预准备语句 [英] Using prepared statements with mysql in python

问题描述

我正在尝试将SQL与Python中准备好的语句一起使用. Python对此没有自己的机制，因此我尝试直接使用SQL:

sql = "PREPARE stmt FROM ' INSERT INTO {} (date, time, tag, power) VALUES (?, ?, ?, ?)'".format(self.db_scan_table)
self.cursor.execute(sql)

然后，在循环中:

sql = "EXECUTE stmt USING \'{}\', \'{}\', {}, {};".format(d, t, tag, power)
self.cursor.execute(sql)

然后在循环中我得到:

MySQL Error [1064]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2014-12-25', '12:31:46', 88000000, -6.64' at line 1

这是怎么回事?

解决方案

例如，在

self.cursor.execute(sql, (d, t, tag, power))

无需进一步的字符串格式设置-MySQLdb模块代表您准备和执行部件(并且可以缓存内容以避免不必要的重复工作等).

根据您提到的循环"的性质，请考虑对.execute_many的单个调用(以元组序列作为第二个参数)可能会代替整个循环(除非您不仅需要在数据库中插入数据，还需要在该循环中进行更多处理.

添加:如今更好的替代方法可能是使用mysql自己的Connector/Python和.cursor()工厂中的显式prepare=True选项-请参见import this读取所有这些原则)之后，显式胜于隐式"是 is . mysqldb隐式地做事(似乎当前的开放源代码版本没有使用准备好的语句)不能像Connector/Python的显式架构那样好. >

I am trying to use SQL with prepared statements in Python. Python doesn't have its own mechanism for this so I try to use SQL directly:

sql = "PREPARE stmt FROM ' INSERT INTO {} (date, time, tag, power) VALUES (?, ?, ?, ?)'".format(self.db_scan_table)
self.cursor.execute(sql)

Then later, in the loop:

sql = "EXECUTE stmt USING \'{}\', \'{}\', {}, {};".format(d, t, tag, power)
self.cursor.execute(sql)

And in the loop I get:

MySQL Error [1064]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2014-12-25', '12:31:46', 88000000, -6.64' at line 1

What's going on?

解决方案

Using prepared statements with MySQL in Python is explained e.g at http://zetcode.com/db/mysqlpython/ -- look within that page for Prepared statements.

In your case, that would be, e.g:

sql = ('INSERT INTO {} (date, time, tag, power) VALUES '
       '(%s, %s, %s, %s)'.format(self.db_scan_table))

and later, "in the loop" as you put it:

self.cursor.execute(sql, (d, t, tag, power))

with no further string formatting -- the MySQLdb module does the prepare and execute parts on your behalf (and may cache things to avoid repeating work needlessly, etc, etc).

Do consider, depending on the nature of "the loop" you mention, that it's possible that a single call to .execute_many (with a sequence of tuples as the second argument) could take the place of the whole loop (unless you need more processing within that loop beyond just the insertion of data into the DB).

Added: a better alternative nowadays may be to use mysql's own Connector/Python and the explicit prepare=True option in the .cursor() factory -- see http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html . This lets you have a specific cursor on which statements are prepared (with the "more efficient than using PREPARE and EXECUTE" binary protocol, according to that mysql.com page) and another one for statements that are better not prepared; "explicit is better than implicit" is after all one of the principles in "The Zen of Python" (import this from an interactive prompt to read all those principles). mysqldb doing things implicitly (and it seems the current open-source version doesn't use prepared statements) can't be as good an architecture as Connector/Python's more explicit one.

这篇关于在python中使用带有MySQL的预准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！