SQLAlchemy, scoped_session - raw SQL INSERT doesn't write to DB

Question

I have a Pyramid / SQLAlchemy, MySQL python app. When I execute a raw SQL INSERT query, nothing gets written to the DB. When using ORM, however, I can write to the DB. I read the docs, I read up about the ZopeTransactionExtension, read a good deal of SO questions, all to no avail. What hasn't worked so far:

• transaction.commit() - nothing is written to the DB. I do realize this statement is necessary with ZopeTransactionExtension but it just doesn't do the magic here.
• dbsession().commit - doesn't work since I'm using ZopeTransactionExtension
• dbsession().close() - nothing written
• dbsession().flush() - nothing written
• mark_changed(session) -

  File "/home/dev/.virtualenvs/sc/local/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 198, in join_transaction if session.twophase: AttributeError: 'scoped_session' object has no attribute 'twophase'"

What has worked but is not acceptable because it doesn't use scoped_session:

• engine.execute(...)

I'm looking for how to execute raw SQL with a scoped_session (dbsession() in my code)

Here is my SQLAlchemy setup (models/__init__.py)

def dbsession():
    assert (_dbsession is not None)
    return _dbsession

def init_engines(settings, _testing_workarounds=False):
    import zope.sqlalchemy
    extension = zope.sqlalchemy.ZopeTransactionExtension()
    global _dbsession

    _dbsession = scoped_session(
        sessionmaker(
            autoflush=True,
            expire_on_commit=False,
            extension=extension,
        )
    )

    engine = engine_from_config(settings, 'sqlalchemy.')
    _dbsession.configure(bind=engine)

Here is a python script I wrote to isolate the problem. It resembles the real-world environment of where the problem occurs. All I want is to make the below script insert the data into the DB:

# -*- coding: utf-8 -*-
import sys
import transaction

from pyramid.paster import setup_logging, get_appsettings
from sc.models import init_engines, dbsession
from sqlalchemy.sql.expression import text


def __main__():

if len(sys.argv) < 2:
    raise RuntimeError()
config_uri = sys.argv[1]
setup_logging(config_uri)
aa = init_engines(get_appsettings(config_uri))

session = dbsession()
session.execute(text("""INSERT INTO
         operations (description, generated_description)
         VALUES ('hello2', 'world');"""))

print list(session.execute("""SELECT * from operations""").fetchall()) # prints inserted data
transaction.commit()

print list(session.execute("""SELECT * from operations""").fetchall()) # doesn't print inserted data

if __name__ == '__main__':
    __main__()

What is interesting, if I do:

session = dbsession()
session.execute(text("""INSERT INTO
         operations (description, generated_description)
         VALUES ('hello2', 'world');"""))
op = Operation(generated_description='aa', description='oo')
session.add(op)

then the first print outputs the raw SQL inserted row ('hello2' 'world'), and the second print prints both rows, and in fact both rows are inserted into the DB.

I cannot comprehend why using an ORM insert alongside raw SQL "fixes" it.

I really need to be able to call execute() on a scoped_session to insert data into the DB using raw SQL. Any advice?

Answer 1

It has been a while since I mixed raw sql with sqlalchemy, but whenever you mix them, you need to be aware of what happens behind the scenes with the ORM. First, check the autocommit flag. If the zope transaction is not configured correctly, the ORM insert might be triggering a commit.

Actually, after looking at the zope docs, it seems manual execute statements need an extra step. From their readme:

By default, zope.sqlalchemy puts sessions in an 'active' state when they are first used. ORM write operations automatically move the session into a 'changed' state. This avoids unnecessary database commits. Sometimes it is necessary to interact with the database directly through SQL. It is not possible to guess whether such an operation is a read or a write. Therefore we must manually mark the session as changed when manual SQL statements write to the DB.

>>> session = Session()
>>> conn = session.connection()
>>> users = Base.metadata.tables['test_users']
>>> conn.execute(users.update(users.c.name=='bob'), name='ben')
<sqlalchemy.engine...ResultProxy object at ...>
>>> from zope.sqlalchemy import mark_changed
>>> mark_changed(session)
>>> transaction.commit()
>>> session = Session()
>>> str(session.query(User).all()[0].name)
'ben'
>>> transaction.abort()

It seems you aren't doing that, and so the transaction.commit does nothing.