simonw
Execute statements not committed unless a utlity function is ran
Hello,
This might totally be an operator error but it surprised me and took some tracking down to figure out what was wrong. I searched docs for "commit" but maybe not very hard
I'm playing around a datasette project and using a mixture of sqlite-utils (v3.37) functions like .upsert(), .query(), and .execute()
All is good. Fun framework. Thanks for it
However, I recently swapped an .upsert() for an .execute() so that the .execute() was the last action before the application exits. This meant that the writes in the execute were not committed. Here's a simple script that shows the issue...
from sqlite_utils import Database
def create_table(db: Database):
db["artist_details"].create({
"id": str,
"name": str,
"discovered": int,
}, pk="id", if_not_exists=True)
def save_artist_listen(db: Database, artist_listen):
db.execute(
"insert into artist_details (id, name, discovered)"
"values (lower(:artist), :artist, :uts_timestamp)"
"on conflict(id)"
"do update set discovered = min(:uts_timestamp, discovered)",
artist_listen
)
database = Database("test.db")
create_table(database)
listens = [{
"artist": "The Cabs",
"uts_timestamp": 3456789012,
},{
"artist": "The Cabs",
"uts_timestamp": 2345678901,
},{
"artist": "The Cabs",
"uts_timestamp": 1234567890,
},{
"artist": "Another Band",
"uts_timestamp": 1234567890,
}]
for listen in listens:
save_artist_listen_date(database, listen)
if False:
database["artist_details"].upsert({
"id": "Try Science".lower(),
"name": "Try Science",
"discovered": 1230000000,
}, pk="id")
if False:
database.conn.commit()
Run the script with:
$ python simple-case.py
$ sqlite-utils query test.db "select * from artist_details" # check contents of table, it will show an empty array
If I change either of those Falses to True then the transactions are committed and everything works as expected.
Easy enough to workaround, but like I say, a little surprising.
Other things that work include the with database.conn: trick from the .delete_where example, or setting database.conn.isolation_level = None at the start of the script