SQLAlchemy session query with INSERT IGNORE

Question

Hi,

I'm trying to do a bulk insert/update with SQLAlchemy. Here's a snippet:

for od in clist:
    where = and_(Offer.network_id==od['network_id'],
                 Offer.external_id==od['external_id'])
    o = session.query(Offer).filter(where).first()
    if not o:
        o = Offer()
    o.network_id = od['network_id']
    o.external_id = od['external_id']
    o.title = od['title']
    o.updated = datetime.datetime.now()
    payout = od['payout']
    countrylist = od['countries']
    session.add(o)
    session.flush()

    for country in countrylist:
        c = session.query(Country).filter(Country.name==country).first()
        where = and_(OfferPayout.offer_id==o.id, 
                     OfferPayout.country_name==country)
        opayout = session.query(OfferPayout).filter(where).first()
        if not opayout:
            opayout = OfferPayout()
        opayout.offer_id = o.id
        opayout.payout = od['payout']
        if c:
            opayout.country_id = c.id
            opayout.country_name = country
        else:
            opayout.country_id = 0 
            opayout.country_name = country
        session.add(opayout)

    session.flush()

It looks like my issue was touched on here, http://www.mail-archive.com/[email protected]/msg05983.html, but I don't know how to use "textual clauses" with session query objects and couldn't find much (though admittedly I haven't had as much time as I'd like to search).

I'm new to SQLAlchemy and I'd imagine there's some issues in the code besides the fact that it throws an exception on a duplicate key. For example, doing a flush after every iteration of clist (but I don't know how else to get an the o.id value that is used in the subsequent OfferPayout inserts).

Guidance on any of these issues is very appreciated.

Answer 1

The way you should be doing these things is with session.merge().

You should also be using your objects relation properties. So the o above should have o.offerpayout and this a list (of objects) and your offerpayout has offerpayout.country property which is the related countries object.

So the above would look something like

for od in clist:

    o = Offer()
    o.network_id = od['network_id']
    o.external_id = od['external_id']
    o.title = od['title']
    o.updated = datetime.datetime.now()
    payout = od['payout']
    countrylist = od['countries']

    for country in countrylist:
        opayout = OfferPayout()
        opayout.payout = od['payout']
        country_obj = Country()
        country_obj.name = country
        opayout.country = country_obj

        o.offerpayout.append(opayout)

     session.merge(o)
     session.flush()

This should work as long as all the primary keys are correct (i.e the country table has a primary key of name). Merge essentially checks the primary keys and if they are there merges your object with one in the database (it will also cascade down the joins).