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

Answer 1

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()