Well dang. I don't think I've used .executescript() before, but that is definitely broken, yes.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Tuesday, November 20, 2018 3:30 PM To: SQLite mailing list Subject: Re: [sqlite] Creating and dropping tables with multiple connections Change the executed SQL to be executed using .executescript(...) and make sure that the executed SQL is a multi-statement batch consisting of BEGIN TRANSACTION; <stmt> COMMIT; that is cur1.execute(createSQL) -> cur1.executescript('BEGIN TRANSACTION; ' + createSQL + ' COMMIT;') and see what happens ... With isolation_level=None there should be no difference since you have theoretically turned off the wrapper magic, but there is. I do not know what the sqlite3 wrapper is doing that causes it to fail. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of David Raymond >Sent: Tuesday, 20 November, 2018 12:57 >To: SQLite mailing list >Subject: Re: [sqlite] Creating and dropping tables with multiple >connections > >As an aside, it works properly written in python using apsw. It does >not work when using Pythons sqlite3 wrapper (but then, since that >wrapper "buggers about" with no way to tell what it is doing, I find >that unsurprising). > > >Works fine for me anyway. What I wrote for an equivalent is below. > > >import contextlib >import os >import sqlite3 >import sys > >print("Python version: {0}".format(sys.version)) >print("SQLite3 module version: {0}".format(sqlite3.version)) >print("SQLite3 DLL version: {0}\n".format(sqlite3.sqlite_version)) > >createSQL = """CREATE TABLE MyTable( > component TEXT not null, > key TEXT not null, > value INTEGER not null, > primary key (component, key) >);""" >dropSQL = "DROP TABLE MyTable;" > >dbFi = r"D:\Temp\DeleteMe.sqlite" >if os.path.isfile(dbFi): > os.remove(dbFi) > >with contextlib.closing(sqlite3.connect(dbFi, isolation_level = >None)) as conn1, \ > contextlib.closing(sqlite3.connect(dbFi, isolation_level = >None)) as conn2: > with contextlib.closing(conn1.cursor()) as cur1, \ > contextlib.closing(conn2.cursor()) as cur2: > for roundNo in range(2): > print(f"Round: {roundNo:,d}") > > print("Create on conn1") > try: > cur1.execute(createSQL) > except sqlite3.Error as err: > print("ERROR: {0!s}".format(err)) > > print("Delete on conn2") > try: > cur2.execute(dropSQL) > except sqlite3.Error as err: > print("ERROR: {0!s}".format(err)) > print() > print("Executing an extra drop on conn2. This should fail") > try: > cur2.execute(dropSQL) > except sqlite3.Error as err: > print("ERROR: {0!s}".format(err)) > >if os.path.isfile(dbFi): > os.remove(dbFi) > >print("\nDone.") > >Running that gives me: > >Python version: 3.7.1 (v3.7.1:260ec2c36a, Oct 20 2018, 14:57:15) [MSC >v.1915 64 bit (AMD64)] >SQLite3 module version: 2.6.0 >SQLite3 DLL version: 3.25.3 > >Round: 0 >Create on conn1 >Delete on conn2 > >Round: 1 >Create on conn1 >Delete on conn2 > >Executing an extra drop on conn2. This should fail >ERROR: no such table: MyTable > >Done. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users