Creating and dropping tables with multiple connections

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Creating and dropping tables with multiple connections

Albert Banaszkiewicz
Hi everybody,


I have a question regarding multiple connections to the same DB from the same process.

I was expecting that modifying a DB via a certain connection should also be visible for all other opened connections. Is my assumption incorrect ? (both operations being transactions).


In my test example, I am creating a table via connection no. 1 and drop it via connection no. 2. Table is created correctly and then correctly dropped in the first iteration. However, if I try to run this one more time I am receiving 'table already exists' error while trying to create it (connection no. 1) and then 'no such table' for connection no. 2 while trying to drop it.

Is it intended behavior ?


Moreover, if I open connections with SQLITE_OPEN_SHAREDCACHE flag, everything works as expected.


The code I use:


static const char* KCreateTable =
"CREATE TABLE MyTable("
" component TEXT not null,"
" key TEXT not null,"
" value INTEGER not null,"
" primary key (component, key)"
");";

static const char* KDropTable =
"DROP TABLE MyTable;";


void ExecuteInTransaction(sqlite3* aDb, const std::string& aQuery)
{
std::string finalQuery = "BEGIN TRANSACTION;" + aQuery + "COMMIT;";

EXPECT_EQ(SQLITE_OK, sqlite3_exec(aDb, finalQuery.c_str(), nullptr, nullptr, nullptr)) << sqlite3_errmsg(aDb);
}

TEST_F(CEncryptedStreamTest, DroppingTablesTest)
{
sqlite3* writeDb1 = nullptr;
sqlite3* writeDb2 = nullptr;

EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", &writeDb1, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", &writeDb2, SQLITE_OPEN_READWRITE, nullptr));

for (int i = 0; i < 2; ++i)
{
ExecuteInTransaction(writeDb1, KCreateTable);

ExecuteInTransaction(writeDb2, KDropTable);
}

sqlite3_close_v2(writeDb1);
sqlite3_close_v2(writeDb2);
}


Cheers,

Albert
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Simon Slavin-3
On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <[hidden email]> wrote:

> ExecuteInTransaction(writeDb1, KCreateTable);

I can't answer your question but the above line shows a misunderstanding of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE, DELETE.  Commands which modify the database schema fall outside the scope of transactions and cannot be handled within the framework of COMMIT and ROLLBACK.

So don't use BEGIN and COMMIT for anything except INSERT, UPDATE, DELETE .  Perhaps you could make that change to your code and see whether the problem remains.

> Moreover, if I open connections with SQLITE_OPEN_SHAREDCACHE flag, everything works as expected.

The result of shared cache is the same as if both connections are the same connection.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Dominique Devienne
On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin <[hidden email]> wrote:

> On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <
> [hidden email]> wrote:
>
> > ExecuteInTransaction(writeDb1, KCreateTable);
>
> I can't answer your question but the above line shows a misunderstanding
> of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE,
> DELETE.  Commands which modify the database schema fall outside the scope
> of transactions and cannot be handled within the framework of COMMIT and
> ROLLBACK.
>

Hmmm, DDL is transactional in SQLite, AFAIK... --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Jay Kreibich
In reply to this post by Simon Slavin-3

> On Nov 20, 2018, at 10:27 AM, Simon Slavin <[hidden email]> wrote:
> On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <[hidden email]> wrote:
>
>> ExecuteInTransaction(writeDb1, KCreateTable);
>
> I can't answer your question but the above line shows a misunderstanding of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE, DELETE.  Commands which modify the database schema fall outside the scope of transactions and cannot be handled within the framework of COMMIT and ROLLBACK.
>
> So don't use BEGIN and COMMIT for anything except INSERT, UPDATE, DELETE .  Perhaps you could make that change to your code and see whether the problem remains.

While that is true in many RDBMS products, in SQLite DDL commands are transactional.

  -j



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Simon Slavin-3
In reply to this post by Dominique Devienne
On 20 Nov 2018, at 4:32pm, Dominique Devienne <[hidden email]> wrote:

> Hmmm, DDL is transactional in SQLite, AFAIK... --DD

I was wrong.  Thanks for the correction.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Igor Korot
In reply to this post by Dominique Devienne
Hi,

On Tue, Nov 20, 2018 at 10:32 AM Dominique Devienne <[hidden email]> wrote:

>
> On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin <[hidden email]> wrote:
>
> > On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <
> > [hidden email]> wrote:
> >
> > > ExecuteInTransaction(writeDb1, KCreateTable);
> >
> > I can't answer your question but the above line shows a misunderstanding
> > of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE,
> > DELETE.  Commands which modify the database schema fall outside the scope
> > of transactions and cannot be handled within the framework of COMMIT and
> > ROLLBACK.
> >
>
> Hmmm, DDL is transactional in SQLite, AFAIK... --DD

Unless you work with mySQL. ;-)

Thank you.

> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Richard Hipp-3
In reply to this post by Albert Banaszkiewicz
On 11/20/18, Albert Banaszkiewicz <[hidden email]> wrote:
> I was expecting that modifying a DB via a certain connection should also be
> visible for all other opened connections.

That is correct.

>
>
> In my test example, I am creating a table via connection no. 1 and drop it
> via connection no. 2.

The sqlite3_prepare_v2() interface does not look at the database file.
It simply compiles the SQL statement into byte-code.  So on the second
interation sqlite3_prepare_v2() does not yet know that the table has
been DROP-ed by the other connection during the first iteration.  It
thinks the table still exists, and thus throws the error.

If you proceed the CREATE TABLE by some other SQL that does require
reading the database files (example: "SELECT 1 FROM sqlite_master
LIMIT 1") then sqlite3_prepare_v2() will know that the table has been
deleted and will work as you expect.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Keith Medcalf
In reply to this post by Albert Banaszkiewicz

after adding in the obviously missing #include's for <sqlite3.h> and <string> the provided code does not compile.  It is looking for an undefined something called EXPECT_EQ ...

Kind of hard to reproduce if incomplete code is presented.

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).

>I was expecting that modifying a DB via a certain connection should
>also be visible for all other opened connections. Is my assumption
>incorrect ? (both operations being transactions).

You are correct. Committed transactions on connection A should be visible to connection B provided that the transaction committed on A before the transaction commenced on connection B.

---
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-
>[hidden email]] On Behalf Of Albert Banaszkiewicz
>Sent: Tuesday, 20 November, 2018 08:35
>To: [hidden email]
>Subject: [sqlite] Creating and dropping tables with multiple
>connections
>
>Hi everybody,
>
>
>I have a question regarding multiple connections to the same DB from
>the same process.
>
>I was expecting that modifying a DB via a certain connection should
>also be visible for all other opened connections. Is my assumption
>incorrect ? (both operations being transactions).
>
>
>In my test example, I am creating a table via connection no. 1 and
>drop it via connection no. 2. Table is created correctly and then
>correctly dropped in the first iteration. However, if I try to run
>this one more time I am receiving 'table already exists' error while
>trying to create it (connection no. 1) and then 'no such table' for
>connection no. 2 while trying to drop it.
>
>Is it intended behavior ?
>
>
>Moreover, if I open connections with SQLITE_OPEN_SHAREDCACHE flag,
>everything works as expected.
>
>
>The code I use:
>
>
>static const char* KCreateTable =
>"CREATE TABLE MyTable("
>" component TEXT not null,"
>" key TEXT not null,"
>" value INTEGER not null,"
>" primary key (component, key)"
>");";
>
>static const char* KDropTable =
>"DROP TABLE MyTable;";
>
>
>void ExecuteInTransaction(sqlite3* aDb, const std::string& aQuery)
>{
>std::string finalQuery = "BEGIN TRANSACTION;" + aQuery + "COMMIT;";
>
>EXPECT_EQ(SQLITE_OK, sqlite3_exec(aDb, finalQuery.c_str(), nullptr,
>nullptr, nullptr)) << sqlite3_errmsg(aDb);
>}
>
>TEST_F(CEncryptedStreamTest, DroppingTablesTest)
>{
>sqlite3* writeDb1 = nullptr;
>sqlite3* writeDb2 = nullptr;
>
>EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", &writeDb1,
>SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
>EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", &writeDb2,
>SQLITE_OPEN_READWRITE, nullptr));
>
>for (int i = 0; i < 2; ++i)
>{
>ExecuteInTransaction(writeDb1, KCreateTable);
>
>ExecuteInTransaction(writeDb2, KDropTable);
>}
>
>sqlite3_close_v2(writeDb1);
>sqlite3_close_v2(writeDb2);
>}
>
>
>Cheers,
>
>Albert
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

David Raymond
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Creating and dropping tables with multiple connections

David Raymond
Well dang. I don't think I've used .executescript() before, but that is definitely broken, yes.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Odp.: Creating and dropping tables with multiple connections

Albert Banaszkiewicz
In reply to this post by Keith Medcalf
First of all, sorry, for posting the code that did not compile. My intention was to simply show whats going on rather than provide copy-paste-able example.


@RichardHipp Thank you for your suggestion. It works as You described.

@All Thanks for Your input


Regards,

Albert

________________________________
Od: sqlite-users <[hidden email]> w imieniu użytkownika Keith Medcalf <[hidden email]>
Wysłane: wtorek, 20 listopada 2018 21:29:49
Do: SQLite mailing list
Temat: 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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users