> Is there a complete list that defines what are "the statements that > modify the database"? What is the difference between "the statements > that modify the database" and "Data Modification Language (DML) > statement"? Are they the same?
If you dig into their code you can attempt to find what it is actually doing, but the documentation is somewhat ambiguous. (Keith included his findings from their code in one of his responses) > Whether or not they are the same or not, providing a complete list of > such statements for each case should be far less confusing. Fully agreed. You're basically discovering for yourself firsthand why we recommend using autocommit/setting isolation_level = None when opening a connection in Python. It basically comes down to "ambiguity is frustrating and prone to error, so do it yourself explicitly to be clear and safe." > BTW, who is maintaining the python doc? Is it somebody from the > sqlite3 community or the python community? I hope that someone will go > over the document and resolve all the inconsistencies and excessive > "referring to other places references". That's the Python folks, since it's their wrapper. You'll have to dig a little to find whichever is the correct mailing list to raise your concerns on over there. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Peng Yu Sent: Friday, January 31, 2020 9:11 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Default isolation_level for sqlite3.connect? The following is still very confusing. https://docs.python.org/3.8/library/sqlite3.html#controlling-transactions """ autocommit mode means that statements that modify the database take effect immediately." ... The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE). """ Is there a complete list that defines what are "the statements that modify the database"? What is the difference between "the statements that modify the database" and "Data Modification Language (DML) statement"? Are they the same? Whether or not they are the same or not, providing a complete list of such statements for each case should be far less confusing. BTW, who is maintaining the python doc? Is it somebody from the sqlite3 community or the python community? I hope that someone will go over the document and resolve all the inconsistencies and excessive "referring to other places references". > This operates in "auto-commit" mode. When a statement is executed that > is not already inside an EXPLICIT (=user-created) transaction, then it > is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore: > > INSERT INTO TABLE_A ... > BEGIN <whatever> > INSERT INTO TABLE_B ... > DELETE FROM TABLE_C ... > COMMIT > SELECT ... FROM TABLE_D > > will essentially be turned into: > > BEGIN > INSERT INTO TABLE_A ... > COMMIT > BEGIN <whatever> > INSERT INTO TABLE_B ... > DELETE FROM TABLE_C ... > COMMIT > BEGIN > SELECT ... FROM TABLE_D > COMMIT > > where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED > (the SQLite engine's default if you just use BEGIN). -- Regards, Peng _______________________________________________ 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
