Results from previous queries persist after execute

[Cody Baker]

While PEP248/PEP249 don't explicitly say that result sets should be cleared between execute calls, other PEP248 libraries (sqlite3 and MySQLdb) discard these results (or issue and error). If statement A is executed, followed by statement B, a fetchall on the cursor will return rows from both statements A and B. The result is the same if the results are retrieved individually with fetch().

The language in the nextset() documentation makes it sound as if the results should be cleared between execute calls.
"An Error (or subclass) exception is raised if the previous call to .execute*() did not produce any result set or no call was issued yet." - http://www.python.org/dev/peps/pep-0249/#nextset

>>> import oursql
>>> conn = oursql.connect(user='root', db='test')
>>> c = conn.cursor()
>>> c.execute('CREATE TABLE t1 (c1 text, c2 text)');
>>> c.execute('INSERT INTO t1 VALUES (?,?)',('a','b'))
>>> c.execute('INSERT INTO t1 VALUES (?,?)',('c','d'))
>>> c.execute('CREATE TABLE t2 (c1 text, c2 text)');
>>> c.execute('INSERT INTO t2 VALUES (?,?)',('y','z'))
>>> c.execute('INSERT INTO t2 VALUES (?,?)',('w','x'))
>>> c.execute('SELECT * FROM t1')
>>> c.fetchone()
(u'a', u'b')
>>> c.execute('SELECT * FROM t2')
>>> c.fetchone()
(u'c', u'd')
>>> c.fetchone()
>>> c.fetchone()
(u'y', u'z')
>>> c.fetchone()
(u'w', u'x')
>>> c.fetchone()

Here is documentation of sqlite3 and MySQLdb's behavior.

>>> import sqlite3
>>> conn = sqlite3.connect('blah.db')
>>> c = conn.cursor()
>>> c.execute('CREATE TABLE t1 (c1 text, c2 text)');
<sqlite3.Cursor object at 0x108c983b0>
>>> c.execute('INSERT INTO t1 VALUES (?,?)',('a','b'))
<sqlite3.Cursor object at 0x108c983b0>
>>> c.execute('INSERT INTO t1 VALUES (?,?)',('c','d'))
<sqlite3.Cursor object at 0x108c983b0>
>>> c.execute('CREATE TABLE t2 (c1 text, c2 text)');
<sqlite3.Cursor object at 0x108c983b0>
>>> c.execute('INSERT INTO t2 VALUES (?,?)',('y','z'))
<sqlite3.Cursor object at 0x108c983b0>
>>> c.execute('INSERT INTO t2 VALUES (?,?)',('w','x'))
<sqlite3.Cursor object at 0x108c983b0>
>>> c.execute('SELECT * FROM t1')
<sqlite3.Cursor object at 0x108c983b0>
>>> c.fetchone()
(u'a', u'b')
>>> c.execute('SELECT * FROM t2')
<sqlite3.Cursor object at 0x108c983b0>
>>> c.fetchone()
(u'y', u'z')
>>> c.fetchone()
(u'w', u'x')
>>> c.fetchone()

>>> import MySQLdb
>>> conn = MySQLdb.connect(user='root', db='test')
>>> c = conn.cursor()
>>> c.execute('CREATE TABLE t1 (c1 text, c2 text)');
0L
>>> c.execute('INSERT INTO t1 VALUES (%s,%s)',('a','b'))
1L
>>> c.execute('INSERT INTO t1 VALUES (%s,%s)',('c','d'))
1L
>>> c.execute('CREATE TABLE t2 (c1 text, c2 text)');
0L
>>> c.execute('INSERT INTO t2 VALUES (%s,%s)',('y','z'))
1L
>>> c.execute('INSERT INTO t2 VALUES (%s,%s)',('w','x'))
1L
>>> c.execute('SELECT * FROM t1')
2L
>>> c.fetchone()
('a', 'b')
>>> c.execute('SELECT * FROM t2')
2L
>>> c.fetchone()
('y', 'z')
>>> c.fetchone()
('w', 'x')
>>> c.fetchone()

[Cody Baker (jcodybaker)]

--- cursor.pyx 2013-01-02 18:56:10.000000000 -0800
+++ cursor.pyx.orig 2013-01-02 18:56:23.000000000 -0800
@@ -96,7 +96,6 @@
         """
         self._check_closed()
         self._do_autoping()
- self._statements.clear()
         # This, on the other hand, is just plain stupid.
         if plain_query:
             if params:
@@ -131,7 +130,6 @@
         """
         self._check_closed()
         self._do_autoping()
- self._statements.clear()
         # No plain queries because they can't be parameterized anyway.
         stmt = self._new_statement()
         stmt.prepare(query)

[Cody Baker (jcodybaker)]

Err. This time patched in the right direction.

--- cursor.pyx.orig 2013-01-02 18:56:23.000000000 -0800
+++ cursor.pyx 2013-01-02 18:56:10.000000000 -0800
@@ -96,6 +96,7 @@
         """
         self._check_closed()
         self._do_autoping()
+ self._statements.clear()
         # This, on the other hand, is just plain stupid.
         if plain_query:
             if params:
@@ -130,6 +131,7 @@
         """
         self._check_closed()
         self._do_autoping()
+ self._statements.clear()
         # No plain queries because they can't be parameterized anyway.
         stmt = self._new_statement()
         stmt.prepare(query)