Public bug reported: 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() ** Affects: oursql Importance: Undecided Status: New -- You received this bug notification because you are a member of Agesys Team, which is subscribed to oursql. https://bugs.launchpad.net/bugs/1095510 Title: Results from previous queries persist after execute Status in oursql: New Bug description: 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() To manage notifications about this bug go to: https://bugs.launchpad.net/oursql/+bug/1095510/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~agesys-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~agesys-dev More help : https://help.launchpad.net/ListHelp
