Pyramid + SQLAlchemy + Zope App returns wrong results with raw SQL

Question

I have a Pyramid 2.X + SQLAlchemy + Zope App created using the official CookieCutter.

There is a table called "schema_b.table_a" with 0 records.

In the below view count(*) should be more than 0 but it returns 0

@view_config(route_name='home', renderer='myproject:templates/home.jinja2')
def my_view(request):
    # Call external REST API. This uses HTTP requests. The API inserts in schema_b.table_a
    call_thirdparty_api()
    mark_changed(request.dbsession)
    sql = "SELECT count(*) FROM schema_b.table_a"
    total = request.dbsession.execute(sql).fetchone()
    print(total)  # Total is 0
    return {}

On the other hand, the following code returns the correct count(*):

@view_config(route_name='home', renderer='myproject:templates/home.jinja2')
def my_view(request):
    engine = create_engine(request.registry.settings.get("sqlalchemy.url"), poolclass=NullPool)
    connection = engine.connect()

    # Call external REST API. This uses HTTP requests. The API inserts in table_a
    call_thirdparty_api()

    sql = "SELECT count(*) FROM schema_b.table_a"
    total = connection.execute(sql).fetchone()
    print(total)  # Total is not 0
    connection.invalidate()
    engine.dispose()
    return {}

It seems that request.session is not able to see the data inserted by the external REST API but it is not clear to me why or how to correct it.

Answer 1

Pyramid and Zope provide transaction managers that extend transactions to far beyond databases. In your example I think a transaction was started in mysql when the request was received on the server by the pyramid_tm package, their documentation states:

"At the beginning of a request a new transaction is started using the request.tm.begin() function." https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/index.html

Because mysql supports consistent nonblocking reads on the transaction you join when calling request.dbsession.execute you query a snapshot of the database made at the start of the transaction. When you use the normal SQLAlchemy function to execute the query a new transaction is created and the expected result is returned.

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

This is very confusing in this situation. But I must admit it's impressive how well it seems to work.