pymysql callproc() appears to affect subsequent selects
Question: 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.


Answer: 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.

