SQLAlchemy won't update my database

Question

I'm making a Pyramid app using SQLAlchemy-0.7.8. I'm using 64bit Python3.2.

The question is, why does the following function not commit anything to the database?

def create_card(sText,sCard):
    """
    create a wildcard instance if all is well (ie,sCard match in sText)
    return 
        oCard, dCard
    otherwise return False,False
    """
    oMatch = re.search(sCard,sText)
    if oMatch:
        oCard = WildCard()
        #set up some stuff about the WildCard

        DBSession.add(oCard)
        DBSession.flush()
        dCard = {
                    'id'            : oCard.id,
                    'span'          : oMatch.span(),
                    'card'          : oCard.card_string,
                            }
        return oCard,dCard
    return False,False

I import DBSession form another script. it is defined as follows:

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

Here's some background info:

The app I'm making is to be used to characterize large blocks of HTML through use of regular expressions. If the app gets stuck and thinks there should be a wilcard match for a piece of text then the user is given a little form to fill in. Once the form is committed create_card is called. If the wildcard is matched against the string then a WildCard instance is created.

The WildCard class is nothing special, it just stores a string and a few integers. If I print out dCard it looks like the WildCard was sucessfully committed because it has an integer id. If I don't call flush on the database session then dCard['id'] is None.

the id field looks like:

id = Column(Integer,Sequence('wild_seq'), primary_key=True)

The add and flush lines cause the following console output:

2012-09-16 12:30:34,845 INFO  [sqlalchemy.engine.base.Engine][Dummy-2] INSERT INTO wildcard_wildcards (card_string, range_id, brand_id, category_id, group_cat_map_id, heading_group_id, heading_to_grp_map_id, heading_id, value_map_id, igneore_match) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2012-09-16 12:30:34,845 INFO  [sqlalchemy.engine.base.Engine][Dummy-2] ('sCard contents', None, None, None, None, None, None, None, None, 0)

So up until this point everything is behaving pretty as is expected.

Here's the problem: Even though the WildCard instance looks like it has been committed to the database, and no Exceptions are raised, direct examination of the database shows that no changes are made.

replacing flush() with commit() raises the following exception:

AssertionError: Transaction must be committed using the transaction manager

Martijn Pieters · Accepted Answer · 2016-07-06T06:58:04.800

24

You need to commit your transaction.

You can do this explicitly (by calling DBSession.commit() or by using the pyramid_tm middleware; the latter commits transactions automatically on successful responses (with a 2xx HTTP response).

The latter only commits transactions for SQLAlchemy if you use the ZopeTransactionExtension extension with your session maker:

from zope.sqlalchemy import ZopeTransactionExtension

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

If you are already using the ZopeTransactionExtension and want to explicitly commit your transactions, you need to use the transaction package: import transaction

transaction.commit()

edited Jul 06 '16 at 06:58

answered Sep 16 '12 at 10:36

Martijn Pieters

1,048,767
296
4,058
3,343

3

that gives me: AssertionError: Transaction must be committed using the transaction manager. – Sheena Sep 16 '12 at 10:42
@Sheena: then you are already using the ZopeTransactionExtension; updated my answer. – Martijn Pieters Sep 16 '12 at 10:46
@Sheena: What, you use `transaction.commit()` and you get the same assertion error? – Martijn Pieters Sep 16 '12 at 11:37
1

That's the only time i get the assertion error. I can commit things fine in other places though (specifically, in my views), just not in that file – Sheena Sep 16 '12 at 11:43
You need to remove all `DBSession.commit()` and `DBSession.flush()` calls and *only* use `transaction.commit()`. – Martijn Pieters Sep 16 '12 at 13:48
Theres a lot of code to edit... do you know why DBSession.commit() would only work some of the time and not others? there must be a way to get it to behave consistently – Sheena Sep 16 '12 at 16:05
@Sheena: When you use the ZopeTransactionExtension you *rarely* need to commit explicitly. When you do you *must* use the `transaction` module, because transaction management has been delegated to it. Normally, the `pyramid_tm` tween then takes care of the transaction commit. In other words, if `DBSession.commit()` worked elsewhere, then that was a coincidence and luck. – Martijn Pieters Sep 16 '12 at 17:17
using transaction.commit() means I get sqlalchemy.orm.exc.DetachedInstanceError when I try try to make use of oCard.id. The WildCard is committed to the database though so that's great – Sheena Sep 17 '12 at 11:08
Thanks for all the help. Your advice didn't quite work but was close led me to the correct answer. I'll write about what I did a little later. Cheers – Sheena Sep 17 '12 at 13:26
2

Using threads you might get a DetachedInstanceError on calling transaction.commit(). This might be because you don't have your transaction code within the "with transaction.manager:" block, as was my problem. – ericso Dec 26 '13 at 15:54
Doesn't seem like `bulk_save_objects` works with zope transaction manager. – coler-j Aug 18 '22 at 20:14

SQLAlchemy won't update my database

1 Answers1

Linked