SQLAlchemy, scoped_session - raw SQL INSERT does not write to DB
I have a Pyramid / SQLAlchemy application, MySQL python. When I execute a raw SQL INSERT query, nothing is written to the DB. However, when using an ORM, I can write to the DB. I read the docs, I read about ZopeTransactionExtension, read many SO questions, all to no avail. What hasn't worked so far:
-
transaction.commit()
- nothing is written to the database. I do understand that this assertion is needed with the ZopeTransactionExtension, but it just doesn't do the magic here. -
dbsession().commit
- doesn't work as I am using ZopeTransactionExtension -
dbsession().close()
- nothing is written -
dbsession().flush()
- nothing is 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 is not has attribute 'twophase' "
-
engine.execute(...)
I am looking for how to execute raw SQL with scoped_session
( dbsession()
in my code)
Here is my SQLAlchemy ( models/__init__.py
) setup
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 environment in which the problem arises. All I want is to do below script to insert data into 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's interesting if I do this:
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 prints the original line inserted by SQL ('hello2' '' world ') and the second print prints the lines as , and in fact both lines are inserted into the DB .
I can't figure out why using ORM insertion along with raw SQL "fixes" this.
I really need to be able to call execute () on scoped_session to insert data into the DB using raw SQL. Any advice?
source to share
It's been a while since I've been mixing raw sql with sqlalchemy, but whenever you mix them you should know what's going on behind the scenes with the ORM. First, check the autocommit flag . If the zope transaction is not configured correctly, the ORM insert can initiate a commit.
In fact, looking at the zope docs, it seems that the manual instructions require an extra step. From the readme :
By default, zope.sqlalchemy puts sessions in an "active" state when they are first used. ORM write operations automatically move the session to the "changed" state. This avoids unnecessary database transactions. Sometimes it is necessary to interact with the database directly through SQL. It is not possible to guess whether such an operation is read or write. Therefore, we have to manually mark the session as modified when writing manual SQL statements to the database.
>>> 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()
You don't seem to be doing this, and therefore transaction.commit does nothing.
source to share