CODEWITHSUNDEEP
pythonsqlite
xZise
xZise

Reputation: 2379

Accessing a sqlite database with multiple connections

I want to access the same SQLite database from multiple instances.

I tried that from two Python shells but didn't get really consistent results in showing me new entries on the other connection. Does this actually work or was is simply a fluke (or a misunderstanding on my side)?

I was using the following code snippets:

>>> import sqlite3
>>> conn = sqlite3.connect("test.db")
>>> conn.cursor().execute("SELECT * from foo").fetchall()
>>> conn.execute("INSERT INTO foo VALUES (1, 2)")

Of course I wasn't always adding new entries.

Upvotes: 2

Views: 13793

Answers (2)

Drewness
Drewness

Reputation: 5072

It's not a fluke, just a misunderstanding of how the connections are being handled. From the docs:

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

In order to see the changes on other connection you will have to commit() the changes from your execute() command. Again, from the docs:

If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

Upvotes: 13

rogue-one
rogue-one

Reputation: 11587

You should also include commit after any DML statements. if the autocommit property of your connection string is set to false

>>> import sqlite3
>>> conn = sqlite3.connect("test.db")
>>> conn.cursor().execute("SELECT * from foo").fetchall()
>>> conn.execute("INSERT INTO foo VALUES (1, 2)")
>>> conn.commit()

Upvotes: 2

Related Questions