hey there- engine.begin() does not do anything to the SQLite connection at all as there is no begin() method in the Python DBAPI. this is why in your logging you will see a line that says "BEGIN (implicit)". nothing happened. the pysqlite driver controls the scope of the actual BEGIN on the sqlite3 library implcitly and there should be no difference at all in 2.0 vs. 1.x in how this occurs. There is also no difference between calling engine.begin() or connection.begin(), assuming "connection" here is the SQLAlchemy connection- engine.begin() simply calls connection.begin() after procuring the connection.
note that pysqlite's implicit control of transactions is often insufficient for some scenarios where fine-grained control of transactions scope is desired, which is where the recipe at https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl comes from in order to circumvent pysqlite's assumptions. if you can please provide specifics that illustrate the difference in behavior as well as how you detect the WAL condition (as I am unfamiliar with this aspect of sqlite) that would be appreciated. On Thu, Mar 4, 2021, at 3:51 PM, Jonathan Brandmeyer wrote: > We are currently on v1.3, but we've noticed the big announcements about the > 2.0 API and are reviewing our practices with future migration in mind. > > One of our applications is INSERT-heavy and also uses SQLite. Standard > performance guidance in this situation SQLite is to use the write-ahead log. > We found that the baseline guidance in 2.0 to use engine.begin() as the > transaction's context manager doesn't cooperate well with the WAL. The WAL > is getting checkpointed and deleted at every transaction. The behavior is > consistent with failing to cache the connection. > > Using connection.begin() as the transaction's context manager restores full > use of the WAL. > > Is this a known pitfall when using 2.0-style transactions in the 1.3 release? > Or are we misunderstanding the connection pool? > > Thanks, > Jonathan Brandmeyer > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/bd49f8f6-d930-4726-8c59-6eea6ed06825n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/bd49f8f6-d930-4726-8c59-6eea6ed06825n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/10f89156-3a05-460d-897c-22b425d1e216%40www.fastmail.com.
