pymysql callproc()似乎会影响后续选择 [英] pymysql callproc() appears to affect subsequent selects
问题描述
我正在尝试将代码库从使用MySQLdb过渡到pymysql.我遇到以下问题，想知道是否有人看到过类似的东西.
简而言之，如果我通过pymysql游标callproc()方法调用存储过程，则随后使用相同或不同游标的execute()方法进行的后续选择"调用将返回错误的结果.我看到Python 2.7.2和Python 3.2.2的结果相同callproc()方法是否以某种方式锁定了服务器?代码如下所示:
conn = pymysql.connect(host='localhost', user='me', passwd='pwd',db='mydb')
curr = conn.cursor()
rargs = curr.callproc("getInputVar", (args,))
resultSet = curr.fetchone()
print("Result set : {0}".format(resultSet))
# curr.close()
#
# curr = conn.cursor()
curr.execute('select * from my_table')
resultSet = curr.fetchall()
print("Result set len : {0}".format(len(resultSet)))
curr.close()
conn.close()
我可以取消注释上面的close()和游标创建调用，但这不会改变结果.如果我注释掉callproc()调用，则select语句可以正常工作.
我也遇到了类似的问题，即(已提交的)INSERT语句未出现在数据库中. PyMySQL 0.5和Python 3.2和MySQL Community Server 5.5.19.
我为我找到了解决方案:我使用executemany方法代替了execute()方法，有关该方法的模块参考 http://code.google.com/p/pymssql/wiki/PymssqlModuleReference 还有一个指向示例的链接.
更新 过了一会儿，今天，我发现这还不是完整的解决方案. python脚本末尾的exit()太快会使数据丢失在数据库中. 因此，我在关闭连接之前和在退出脚本之前添加了一个time.sleep()，最后所有数据都出现了！ (我也改用了myisam表)
import pymysql
conn = pymysql.connect(host='localhost', user='root', passwd='', db='mydb', charset='utf8')
conn.autocommit(True)
cur = conn.cursor()
# CREATE tables (SQL statements generated by MySQL workbench, and exported with Menu -> Database -> Forward Engineer)
cur.execute("""
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
# […]
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
""")
# Fill lookup tables:
cur.executemany("insert into mydb.number(tagname,name,shortform) values (%s, %s, %s)", [('ЕД','singular','sg'), ('МН','plural','p')] )
cur.executemany("insert into mydb.person(tagname,name,shortform) values (%s, %s, %s)", [('1-Л','first','1st'), ('2-Л','second','2nd'), ('3-Л','third','3rd')] )
cur.executemany("insert into mydb.pos(tagname,name,shortform) values (%s, %s, %s)", [('S','noun','s'), ('A','adjective','a'), ('ADV','adverb','adv'), ('NUM','numeral','num'), ('PR','preposition','pr'), ('COM','composite','com'), ('CONJ','conjunction','conj'), ('PART','particle','part'), ('P','word-clause','p'), ('INTJ','interjection','intj'), ('NID','foreign-named-entity','nid'), ('V','verb','v')] )
#[…]
import time
time.sleep(3)
cur.close()
conn.close()
time.sleep(3)
exit()
我建议论坛/论坛 https://groups.google.com/论坛/#！forum/pymysql-users 与开发人员进行进一步讨论.
I'm attempting to transition a code base from using MySQLdb to pymysql. I'm encountering the following problem and wonder if anyone has seen something similar.
In a nutshell, if I call a stored procedure through the pymysql cursor callproc() method a subsequent 'select' call through the execute() method using the same or a different cursor returns incorrect results. I see the same results for Python 2.7.2 and Python 3.2.2
Is the callproc() method locking up the server somehow? Code is shown below:
conn = pymysql.connect(host='localhost', user='me', passwd='pwd',db='mydb')
curr = conn.cursor()
rargs = curr.callproc("getInputVar", (args,))
resultSet = curr.fetchone()
print("Result set : {0}".format(resultSet))
# curr.close()
#
# curr = conn.cursor()
curr.execute('select * from my_table')
resultSet = curr.fetchall()
print("Result set len : {0}".format(len(resultSet)))
curr.close()
conn.close()
I can uncomment the close() and cursor creation calls above but this doesn't change the result. If I comment out the callproc() invocation the select statement works just fine.
I have a similar problem with (committed) INSERT statements not appearing in the database. PyMySQL 0.5 für Python 3.2 and MySQL Community Server 5.5.19.
I found the solution for me: instead of using the execute() method, I used the executemany method, explained in the module reference on http://code.google.com/p/pymssql/wiki/PymssqlModuleReference There is also a link to examples.
Update A little later, today, I found out that this is not yet the full solution. A too fast exit() at the end of the python script makes the data getting lost in the database. So, I added a time.sleep() before closing the connection and before exit()ing the script, and finally all the data appeared! (I also switched to using a myisam table)
import pymysql
conn = pymysql.connect(host='localhost', user='root', passwd='', db='mydb', charset='utf8')
conn.autocommit(True)
cur = conn.cursor()
# CREATE tables (SQL statements generated by MySQL workbench, and exported with Menu -> Database -> Forward Engineer)
cur.execute("""
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
# […]
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
""")
# Fill lookup tables:
cur.executemany("insert into mydb.number(tagname,name,shortform) values (%s, %s, %s)", [('ЕД','singular','sg'), ('МН','plural','p')] )
cur.executemany("insert into mydb.person(tagname,name,shortform) values (%s, %s, %s)", [('1-Л','first','1st'), ('2-Л','second','2nd'), ('3-Л','third','3rd')] )
cur.executemany("insert into mydb.pos(tagname,name,shortform) values (%s, %s, %s)", [('S','noun','s'), ('A','adjective','a'), ('ADV','adverb','adv'), ('NUM','numeral','num'), ('PR','preposition','pr'), ('COM','composite','com'), ('CONJ','conjunction','conj'), ('PART','particle','part'), ('P','word-clause','p'), ('INTJ','interjection','intj'), ('NID','foreign-named-entity','nid'), ('V','verb','v')] )
#[…]
import time
time.sleep(3)
cur.close()
conn.close()
time.sleep(3)
exit()
I suggest the forum/group https://groups.google.com/forum/#!forum/pymysql-users for further discussion with the developer.
这篇关于pymysql callproc()似乎会影响后续选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
