在 python 中将准备好的语句与 mysql 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27649759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using prepared statements with mysql in python
提问by horse hair
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:
我正在尝试在 Python 中将 SQL 与准备好的语句一起使用。Python 没有自己的机制，所以我尝试直接使用 SQL：
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?
这是怎么回事？
采纳答案by Alex Martelli
Using prepared statements with MySQL in Python is explained e.g at http://zetcode.com/db/mysqlpython/-- look within that page for Prepared statements.
例如在http://zetcode.com/db/mysqlpython/ 中解释了在 Python 中使用 MySQL 的准备好的语句- 在该页面中查找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 MySQLdbmodule does the prepare and execute parts on your behalf (and may cache things to avoid repeating work needlessly, etc, etc).
没有进一步的字符串格式——MySQLdb模块代表您准备和执行部分（并且可能会缓存内容以避免不必要地重复工作等）。
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).
请考虑，根据您提到的“循环”的性质，单个调用.execute_many（以元组序列作为第二个参数）可能会取代整个循环（除非您需要在其中进行更多处理）循环不仅仅是将数据插入数据库）。
Added: a better alternative nowadays may be to use mysql's own Connector/Pythonand the explicit prepare=Trueoption 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" isafter all one of the principles in "The Zen of Python" (import thisfrom an interactive prompt to read all those principles). mysqldbdoing things implicitly (and it seems the current open-source version doesn'tuse prepared statements) can't be as good an architecture as Connector/Python's more explicit one.
补充：现在更好的选择可能是使用 mysql 自己的Connector/Python和工厂中的显式prepare=True选项.cursor()- 请参阅http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html。这使您可以拥有一个特定的游标，用于准备哪些语句（根据 mysql.com 页面，“比使用 PREPARE 和 EXECUTE 二进制协议更有效”）和另一个用于最好不准备的语句的游标；“显式胜于隐”是在“巨蟒之禅”的原则，所有的一个（在import this交互模式提示符的读取所有这些原则）。 mysqldb隐含地做事（似乎当前的开源版本没有使用准备好的语句) 不能像Connector/Python更明确的架构那样好。
回答by Kevin
Python does support prepared statements:
Python 确实支持准备好的语句：
sql = "INSERT INTO {} (date, time, tag, power) VALUES (%s, %s, %s, %s);"
sql = sql.format(self.db_scan_table)
self.cursor.execute(sql, (d, t, tag, power))
(You should ensure self.db_scan_tableis not vulnerable to SQL injection)
（您应该确保self.db_scan_table不会受到 SQL 注入的影响）
This assumes your paramstyleis 'format', which it should be for MySQL.
这假设你paramstyle是'format'，它应该是 MySQL。
回答by Eyasu
import mysql.connector
db_con=mysql.connector.connect(host='',
database='',
user='',
password='')
cursor = db_con.cursor(prepared=True,)
#cursor = db_con.cursor(prepared=True)#IT MAY HAVE PROBLEM
sql = """INSERT INTO table (xy,zy) VALUES (%s, %s)"""
input=(1,2)
cursor.execute(sql , input)
db_con.commit()
SELECT STMT
选择STMT
sql = """SELECT * FROM TABLE WHERE XY=%s ORDER BY id DESC LIMIT 1 """
ID=1
input=(ID,)
#input=(ID)# IT MAY HAS PROBLEM
cursor.execute(sql, input)
data = cursor.fetchall()
rowsNumber=cursor.rowcount
