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.
