[Sqlalchemy-users] Interaction between SQLAlchemy and stored procedures; insert without commit ...

[Qvx 3000]

I'm in the process of converting some of my code to use SQLAlchemy. Most natural thing was to create a class/mapper for each table. This is very convenient since I'm mostly doing selects and the ability to have most of data pulled automatically is very nice. Combined with custom properties and utility functions, those objects are very useful. However, in occasions when I have to insert data into database or process data from inside database, there are problems. Maybe not so much with SA itself.

For example. This is how I used to do things before SA:

==========
def create_campaign(**kw):
    sql = "BEGIN :id := cc_util.create_campaign(...); END;"
    # ... execute procedure via cursor.execute (), and fetch result ID back
    return campaign_id

# creates and processes campaign from existing import
def import_data_into_campaign(import_id, filename):
    # import is database table(s) holding column information, import data itself, dates, paths, statuses and such

    campaign_id = create_campaign(...) # this is now visible inside database, although it is not yet committed

    # populate import data
    for data in csv(filename):
        cursor.execute("INSERT INTO import_text ...", **data)
    # we are done with preparing data for stored procedure which will take from here

    # now, let us invoke database procedure to do it's work (data and IDs are available)
    sql = '''
        BEGIN
          SAVEPOINT sp_pci;
          cc_util.process_campaign_import(:campaign_id, :import_id);
          :result := 0;
        EXCEPTION
          WHEN OTHERS THEN
            :result := sqlcode;
            ROLLBACK TO sp_pci; /* Not quite necessary, but I like to enclose my processing procedures inside SP */
        END;
    '''
    cursor.execute(sql,...)
    return sqlcode # gotten from execute

def web_action():
    # ... checking and stuff
    result = import_data_into_campaign(import_id, filename)
    if result==0:
        commit()
    else:
        rollback()
    # ... generate page based on result
=========

Now I'm having several problems with converting this to SQLAlchemy:

1. If I replace all those INSERTs and create_campaign with SA objects nothing is written to database prior to calling cc_util.process_campaign_import. The matter is even worse because I need ID's for processing but they are not yet available. If I call commit to make sure objects are in database (inserted) and that IDs are available, I cannot ROLLBACK this in case of error. The real question is maybe: How to do insert without database COMMIT?

2. Creating objects. Sometimes to create an object is nothing more than inserting it into database, but sometimes it takes much more. This is the reason that for most of my objects I have stored procedures that create them. You might say use triggers, but this system is accessed not only from this Python (semi-experimental) app. My typical response to this is similar to create_campaign function: I call store procedure, take back ID, fetch object via Campaign.get(id), return object. Can I somehow move this into __init__ or do it better in some other way? Those create_x functions are annoying.

3. Calling stored procedures in not so verbose and explicit way.

Maybe there are some answers in docs, but I've already read it once top to bottom (not remembering everything). Now I'm just doing searches with no luck...

Regards,
Tvrtko