Jan 3, 2022 · May 10, 2021 · May 19, 2021 · May 19, 2021 · Jun 18, 2021 · Jun 24, 2021
diff --git a/Doc/whatsnew/3.11.rst b/Doc/whatsnew/3.11.rst
  (Contributed by Aviv Palivoda, Daniel Shahaf, and Erlend E. Aasland in
  :issue:`16379`.)

 * Fetch across rollback no longer raises :exc:`~sqlite3.InterfaceError`.
  Instead we leave it to the SQLite library to handle these cases.
  (Contributed by Erlend E. Aasland in :issue:`44092`.)


 threading
 ---------

diff --git a/Lib/test/test_sqlite3/test_regression.py b/Lib/test/test_sqlite3/test_regression.py
        with self.assertRaises(sqlite.ProgrammingError):
            cur = con.cursor()

    def test_cursor_registration(self):
        """
        Verifies that subclassed cursor classes are correctly registered with
        the connection object, too.  (fetch-across-rollback problem)
        """
        class Connection(sqlite.Connection):
            def cursor(self):
                return Cursor(self)

        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                sqlite.Cursor.__init__(self, con)

        con = Connection(":memory:")
        cur = con.cursor()
        cur.execute("create table foo(x)")
        cur.executemany("insert into foo(x) values (?)", [(3,), (4,), (5,)])
        cur.execute("select x from foo")
        con.rollback()
        with self.assertRaises(sqlite.InterfaceError):
            cur.fetchall()

    def test_auto_commit(self):
        """
        Verifies that creating a connection in autocommit mode works.
diff --git a/Lib/test/test_sqlite3/test_transactions.py b/Lib/test/test_sqlite3/test_transactions.py
        self.con1.commit()

    def test_rollback_cursor_consistency(self):
        """
        Checks if cursors on the connection are set into a "reset" state
        when a rollback is done on the connection.
        """
        """Check that cursors behave correctly after rollback."""
        con = sqlite.connect(":memory:")
        cur = con.cursor()
        cur.execute("create table test(x)")
        cur.execute("insert into test(x) values (5)")
        cur.execute("select 1 union select 2 union select 3")

        con.rollback()
        with self.assertRaises(sqlite.InterfaceError):
            cur.fetchall()
        self.assertEqual(cur.fetchall(), [(1,), (2,), (3,)])


 class RollbackTests(unittest.TestCase):
    """bpo-44092: sqlite3 now leaves it to SQLite to resolve rollback issues"""

    def setUp(self):
        self.con = sqlite.connect(":memory:")
        self.cur1 = self.con.cursor()
        self.cur2 = self.con.cursor()
        with self.con:
            self.con.execute("create table t(c)");
            self.con.executemany("insert into t values(?)", [(0,), (1,), (2,)])
        self.cur1.execute("begin transaction")
        select = "select c from t"
        self.cur1.execute(select)
        self.con.rollback()
        self.res = self.cur2.execute(select)  # Reusing stmt from cache

    def tearDown(self):
        self.con.close()

    def _check_rows(self):
        for i, row in enumerate(self.res):
            self.assertEqual(row[0], i)

    def test_no_duplicate_rows_after_rollback_del_cursor(self):
        del self.cur1
        self._check_rows()

    def test_no_duplicate_rows_after_rollback_close_cursor(self):
        self.cur1.close()
        self._check_rows()

    def test_no_duplicate_rows_after_rollback_new_query(self):
        self.cur1.execute("select c from t where c = 1")
        self._check_rows()


 class SpecialCommandTests(unittest.TestCase):
    def setUp(self):
diff --git a/Misc/NEWS.d/next/Library/2021-05-19-12-35-49.bpo-44092.hiSlI5.rst b/Misc/NEWS.d/next/Library/2021-05-19-12-35-49.bpo-44092.hiSlI5.rst
 Fetch across rollback no longer raises :exc:`~sqlite3.InterfaceError`. Instead
 we leave it to the SQLite library to handle these cases.
 Patch by Erlend E. Aasland.
diff --git a/Modules/_sqlite/connection.c b/Modules/_sqlite/connection.c
    return 0;
 }

 static void
 pysqlite_do_all_statements(pysqlite_Connection *self)
 {
    // Reset all statements
    sqlite3_stmt *stmt = NULL;
    while ((stmt = sqlite3_next_stmt(self->db, stmt))) {
        if (sqlite3_stmt_busy(stmt)) {
            (void)sqlite3_reset(stmt);
        }
    }

    // Reset all cursors
    for (int i = 0; i < PyList_Size(self->cursors); i++) {
        PyObject *weakref = PyList_GetItem(self->cursors, i);
        PyObject *object = PyWeakref_GetObject(weakref);
        if (object != Py_None) {
            pysqlite_Cursor *cursor = (pysqlite_Cursor *)object;
            cursor->reset = 1;
        }
    }
 }

 #define VISIT_CALLBACK_CONTEXT(ctx) \
 do {                                \
    if (ctx) {                      \
    }

    if (!sqlite3_get_autocommit(self->db)) {
        pysqlite_do_all_statements(self);

        int rc;

        Py_BEGIN_ALLOW_THREADS
Original file line number	Diff line number	Diff line change
Expand Up		@@ -248,6 +248,11 @@ sqlite3
		(Contributed by Aviv Palivoda, Daniel Shahaf, and Erlend E. Aasland in
		:issue:`16379`.)

		* Fetch across rollback no longer raises :exc:`~sqlite3.InterfaceError`.
		Instead we leave it to the SQLite library to handle these cases.
		(Contributed by Erlend E. Aasland in :issue:`44092`.)


		threading
		---------

Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -230,28 +230,6 @@ def __init__(self, name):
		with self.assertRaises(sqlite.ProgrammingError):
		cur = con.cursor()

		def test_cursor_registration(self):
		"""
		Verifies that subclassed cursor classes are correctly registered with
		the connection object, too. (fetch-across-rollback problem)
		"""
		class Connection(sqlite.Connection):
		def cursor(self):
		return Cursor(self)

		class Cursor(sqlite.Cursor):
		def __init__(self, con):
		sqlite.Cursor.__init__(self, con)

		con = Connection(":memory:")
		cur = con.cursor()
		cur.execute("create table foo(x)")
		cur.executemany("insert into foo(x) values (?)", [(3,), (4,), (5,)])
		cur.execute("select x from foo")
		con.rollback()
		with self.assertRaises(sqlite.InterfaceError):
		cur.fetchall()

		def test_auto_commit(self):
		"""
		Verifies that creating a connection in autocommit mode works.
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -129,19 +129,52 @@ def test_locking(self):
		self.con1.commit()

		def test_rollback_cursor_consistency(self):
		"""
		Checks if cursors on the connection are set into a "reset" state
		when a rollback is done on the connection.
		"""
		"""Check that cursors behave correctly after rollback."""
		con = sqlite.connect(":memory:")
		cur = con.cursor()
		cur.execute("create table test(x)")
		cur.execute("insert into test(x) values (5)")
		cur.execute("select 1 union select 2 union select 3")

		con.rollback()
		with self.assertRaises(sqlite.InterfaceError):
		cur.fetchall()
		self.assertEqual(cur.fetchall(), [(1,), (2,), (3,)])


		class RollbackTests(unittest.TestCase):
		"""bpo-44092: sqlite3 now leaves it to SQLite to resolve rollback issues"""

		def setUp(self):
		self.con = sqlite.connect(":memory:")
		self.cur1 = self.con.cursor()
		self.cur2 = self.con.cursor()
		with self.con:
		self.con.execute("create table t(c)");
		self.con.executemany("insert into t values(?)", [(0,), (1,), (2,)])
		self.cur1.execute("begin transaction")
		select = "select c from t"
		self.cur1.execute(select)
		self.con.rollback()
		self.res = self.cur2.execute(select) # Reusing stmt from cache

		def tearDown(self):
		self.con.close()

		def _check_rows(self):
		for i, row in enumerate(self.res):
		self.assertEqual(row[0], i)

		def test_no_duplicate_rows_after_rollback_del_cursor(self):
		del self.cur1
		self._check_rows()

		def test_no_duplicate_rows_after_rollback_close_cursor(self):
		self.cur1.close()
		self._check_rows()

		def test_no_duplicate_rows_after_rollback_new_query(self):
		self.cur1.execute("select c from t where c = 1")
		self._check_rows()


		class SpecialCommandTests(unittest.TestCase):
		def setUp(self):
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,3 @@
		Fetch across rollback no longer raises :exc:`~sqlite3.InterfaceError`. Instead
		we leave it to the SQLite library to handle these cases.
		Patch by Erlend E. Aasland.
Original file line number	Diff line number	Diff line change
Expand Up		@@ -234,28 +234,6 @@ pysqlite_connection_init_impl(pysqlite_Connection *self,
		return 0;
		}

		static void
		pysqlite_do_all_statements(pysqlite_Connection *self)
		{
		// Reset all statements
		sqlite3_stmt *stmt = NULL;
		while ((stmt = sqlite3_next_stmt(self->db, stmt))) {
		if (sqlite3_stmt_busy(stmt)) {
		(void)sqlite3_reset(stmt);
		}
		}

		// Reset all cursors
		for (int i = 0; i < PyList_Size(self->cursors); i++) {
		PyObject *weakref = PyList_GetItem(self->cursors, i);
		PyObject *object = PyWeakref_GetObject(weakref);
		if (object != Py_None) {
		pysqlite_Cursor cursor = (pysqlite_Cursor )object;
		cursor->reset = 1;
		}
		}
		}

		#define VISIT_CALLBACK_CONTEXT(ctx) \
		do { \
		if (ctx) { \
Expand DownExpand Up		@@ -506,8 +484,6 @@ pysqlite_connection_rollback_impl(pysqlite_Connection *self)
		}

		if (!sqlite3_get_autocommit(self->db)) {
		pysqlite_do_all_statements(self);

		int rc;

		Py_BEGIN_ALLOW_THREADS
Expand Down