René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"SQLite rowcount is corrupted when combining UPDATE RETURNING w/ table that is dropped and recreated ","articleBody":"version info:\r\n\r\n```\r\n$ python\r\nPython 3.10.0 (default, Nov  5 2021, 17:23:47) [GCC 11.2.1 20210728 (Red Hat 11.2.1-1)] on linux\r\nType \"help\", \"copyright\", \"credits\" or \"license\" for more information.\r\n\u003e\u003e\u003e import sqlite3\r\n\u003e\u003e\u003e sqlite3.sqlite_version\r\n'3.36.0'\r\n```\r\n\r\n\r\nwe have a test suite that creates a table, runs some SQL, then drops it.   if multiple tests run that each perform this task, if the same SQLite connection is used, rowcount starts returning \"0\".   Seems to also require RETURNING to be used.   Full demonstration:\r\n\r\n```py\r\nimport os\r\nimport sqlite3\r\n\r\n\r\n\r\ndef go():\r\n    \"\"\"function creates a new table, runs INSERT/UPDATE, drops table,\r\n    commits connection.\r\n\r\n    \"\"\"\r\n\r\n    # create table\r\n    cursor = conn.cursor()\r\n    cursor.execute(\r\n        \"\"\"CREATE TABLE some_table (\r\n        id INTEGER NOT NULL,\r\n        value VARCHAR(40) NOT NULL,\r\n        PRIMARY KEY (id)\r\n    )\r\n    \"\"\"\r\n    )\r\n    cursor.close()\r\n    conn.commit()\r\n\r\n    # run operation\r\n    cursor = conn.cursor()\r\n    cursor.execute(\r\n        \"INSERT INTO some_table (id, value) VALUES (1, 'v1')\"\r\n    )\r\n    ident = 1\r\n\r\n    cursor.execute(\r\n        \"UPDATE some_table SET value='v2' \"\r\n        \"WHERE id=? RETURNING id\",\r\n        (ident,),\r\n    )\r\n    new_ident = cursor.fetchone()[0]\r\n    assert ident == new_ident\r\n    assert cursor.rowcount == 1, cursor.rowcount\r\n    cursor.close()\r\n\r\n    # drop table\r\n    cursor = conn.cursor()\r\n    cursor.execute(\"DROP TABLE some_table\")\r\n    cursor.close()\r\n\r\n    conn.commit()\r\n\r\nif os.path.exists(\"file.db\"):\r\n    os.unlink(\"file.db\")\r\n\r\n# passes\r\nconn = sqlite3.connect(\"file.db\")\r\ngo()\r\n\r\n# run again w/ new connection (same DB), passes\r\nconn = sqlite3.connect(\"file.db\")\r\ngo()\r\n\r\nprint(\"FAILURE NOW OCCURS\")\r\n# run again w/ same connection, fails\r\ngo()\r\n```\r\n\r\non the third run, where we ran the \"test\" on the same connection twice, it fails:\r\n\r\n```\r\n$  python test3.py \r\nFAILURE NOW OCCURS\r\nTraceback (most recent call last):\r\n  File \"/home/classic/dev/sqlalchemy/test3.py\", line 62, in \u003cmodule\u003e\r\n    go()\r\n  File \"/home/classic/dev/sqlalchemy/test3.py\", line 39, in go\r\n    assert cursor.rowcount == 1, cursor.rowcount\r\nAssertionError: 0\r\n```\r\n\r\nit would appear there's some internal caching of table state that needs to be cleared.","author":{"url":"https://github.com/zzzeek","@type":"Person","name":"zzzeek"},"datePublished":"2022-06-01T22:50:28.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":26},"url":"https://github.com/93421/cpython/issues/93421"}