Insert not working for SQLAlchemy database session

Question

Why isn't a record being inserted? There is an id returned but when I check the database there is no new record.

From models.py

from zope.sqlalchemy import ZopeTransactionExtension

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

And views.py

DBSession.execute(text('INSERT INTO (a,b,c) VALUES (\'a\',\'b\',\'c\') RETURNING id'), params=dict(a=a,b=b,c=c))

I've tried committing with transaction.commit() which doesn't get an error but doesn't insert a record. result.fetchone()[0] is getting an id.

And DBSession.commit which gets

assert self.transaction_manager.get().status == ZopeStatus.COMMITTING, "Transaction must be committed using the transaction manager"

Answer 1

This is because you are not using ORM to insert new rows threfore transaction doesn't know it should commit on it's own, because transaction state is not marked as dirty.

Place the following code after you DBSession.execute the query in your views.py.

from zope.sqlalchemy import mark_changed
session = DBSession()
session.execute(...your query...)
mark_changed(session)

At this point transaction should be able to properly commit your query, alternatively use ORM to insert the new row.

Here is a bit more on this subject:

https://pypi.python.org/pypi/zope.sqlalchemy/0.7.4#id15

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.

Answer 2

try

DBSession.flush()

after execute