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: You need to _commit_ your transaction.

You can do this explicitly (by calling `DBSession.commit()` or by using the
[`pyramid_tm`
middleware](http://docs.pylonsproject.org/projects/pyramid_tm/en/latest/); 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()
    

