yeah in fact that engine.begin() here is using a brand new SQLite connection each time as the default pool for SQlite files is NullPool, as it is a very fast connecting database and it is the safest option for concurrency. So in your first example, you have a brand new file connection and all of your WAL setup code is running each tme. In the second example, it runs only once.
background on pooling: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#threading-pooling-behavior if you use the SingletonThreadPool instead of the default NullPool, you will again get the same performance with engine.begin(). however, there is no need to change existing code that uses engine.connect() / connection.begin(). On Fri, Mar 5, 2021, at 10:29 AM, Mike Bayer wrote: > OK this is not hard to understand, in one case you are connecting fresh from > the connection pool each time and in the other you are reusing the same > Connection which will be much more performant. The second example that > states "runtime 3.1 seconds" is perfectly acceptable code for SQLAlhcemy 2.0. > Can I ask what documentation led you to believe that engine.connect() was > somehow being removed ? > > > > On Fri, Mar 5, 2021, at 12:02 AM, Jonathan Brandmeyer wrote: >> >> >> On Thu, Mar 4, 2021 at 5:00 PM Mike Bayer <[email protected]> wrote: >>> 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. >> >> I agree that the `echo`ed trace is identical in both cases. But the >> throughput and filesystem effects certainly look as if something is >> triggering a checkpoint every time the connection is returned to the pool. >> >>> >>> 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. >> >> Thanks for the heads-up. >> >>> >>> 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. >> >> Here is a demo that illustrates what I'm seeing with the WAL. See the >> commentary inline. >> >> 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/CA%2BXzfkqB_qr3Lgthda3ySkog9RgdbE8eB5z2Lhx3BcLxmLjiXw%40mail.gmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/CA%2BXzfkqB_qr3Lgthda3ySkog9RgdbE8eB5z2Lhx3BcLxmLjiXw%40mail.gmail.com?utm_medium=email&utm_source=footer>. >> >> >> *Attachments:* >> * benchmark_sqlite_transactions.py > > > -- > 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/985daffb-abcf-43cb-bd74-47bfba9f0d97%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/985daffb-abcf-43cb-bd74-47bfba9f0d97%40www.fastmail.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/db5ba4b9-db8a-471d-8475-587bba1448c3%40www.fastmail.com.
