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-
>[email protected]] 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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
