Re-using a sqlalchemy ORM object to insert multiple records

Question

This seemed like something simple to do, but I can't seem to figure out how;

I have a transactionlog table in my project which stores financial transactions. In most cases I have to write a number of these transactions in a row, all of them share a lot of properties.

So what I would like to do is instantiate one Transactionlog object, fill out the common properties and then keep adding copies of this original object to the session.

My current code is like this (simplified, it is part of a larger class-method):

    t = Transactionlog()
    t.tlog_newData = origin
    t.tlog_ppl_id = self._contract.member.ppl_id
    t.tlog_datetime = period.period_start
    t.tlog_shift_datetime = period.period_end
    t.tlog_artc_id = revenue_article_id
    t.tlog_club_id = self._contract.member.ppl_club_id
    t.tlog_ppl_mshp_id = self._contract.ppl_mshp_id


    periodlabel = "{0} to {1}".format(period.period_start, period.period_end)

    # linked periodical articles AB
    for linkedarticle in self._contract.linkedarticles:
        if linkedarticle.pmar_periodical:
            if linkedarticle.pmar_free:
                t.price = 0
            else:
                t.price = linkedarticle.article.artc_price
            t.tlog_artc_id = linkedarticle.artc_id
            t.tlog_comment = "{0}: {1}".format(periodlabel, linkedarticle.article.artc_description)
            t.tlog_evtt_id = 'ab'
            t.tlog_code = 'member_linked_article'
            db_session.add(t)
            # counterbook SIS
            t2 = t
            t2.tlog_evtt_id = 'sis'
            t2.price = t.price * -1
            t2.link(t)
            db_session.add(t2)
            t.tlog_code = None

    db_session.commit()

What you see is the instantiation of the initial object t. Under the linked articles I loop through a bunch of articles and (try to) book a new transactionlog line, of type AB for each article. Every booking also has a counter booking SIS.

In the database I -do- see three records appearing, but all have the same properties, they all have tlog_evtt_id 'sis' and all have price -1. So it seems that they all get the most recently set properties.

I figured that adding to the SQLAlchemy session would generate an INSERT with the current data, and then editing the existing object and adding it again would generate a second INSERT with the new data.

So in short, what is the SQLAlchemy way to insert copies of an existing object into the database?

javex · Accepted Answer · 2013-08-28 10:40:25Z

1

According to this answer you want a copy constructor:

class Transactionlog(Base):
    ...
    @classmethod
    def copy(cls, t):
        t_new = cls()
        t_new.tlog_newData = t.tlog_newData
        ...

Another idea that you could use is with the help of functools.partial. My example assumes you have the default SQLAlchemy constructor:

data = {'tlog_newData': origin,
        'tlog_ppl_id': self._contract.member.ppl_id,
        ...
       }
make_log = functools.partial(Transactionlog, **data)

# linked periodical articles AB
for linkedarticle in self._contract.linkedarticles:
    if linkedarticle.pmar_periodical:
        t = make_log()
        ...

I would say this is actually the clean way as it really creates a new instance for each object you want to add - that is exactly what you want. Yes, there is overhead, but there is also overhead when retrieving those objects from the database later on: That's the price of using an ORM.

edited Aug 28, 2013 at 10:40

answered Aug 28, 2013 at 8:26

javex

7,6247 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Erik Oosterwaal Over a year ago

Thanks, javex, I have seen this forum post, but the given solution is to work around SQLalchemy. Also it's 5 years old, I figure this is such a common problem SQLAlchemy would have a solution for this implemented. I'd like to wait for others to chime in, before accepting that a workaround is the only solution.

javex Over a year ago

I think the problem here is that it's not an issue with SQLAlchemy, as that by itself only does what makes sense: Identify objects by their identity, i.e. instance-wise. I added another idea to my post, that you could also use.

Erik Oosterwaal Over a year ago

I think I might have a solution using: docs.sqlalchemy.org/en/rel_0_8/orm/… I'm going to test and update this question.

javex Over a year ago

This sounds like a really hacky solution that might become unstable as I think the transient thing is thought for different things than that. But try it out - it might work.

zzzeek Over a year ago

TransactionLog here is serving as a template for new rows, but yea, the copy constructor is still the normal way this would be done. If you want SQLAlchemy to do it, you can use the make_transient function to convert an object into a transient again. On my end, that feels like the more hacky solution, but maybe it's not.

|

pdepmcp · Accepted Answer · 2015-09-09 20:53:56Z

The make_transient solution is actually working for me in a similar situation, but it behaves a little differently from how it's documented.

Documentation states: "This will remove its association with any session and additionally will remove its “identity key”, such that it’s as though the object were newly constructed, except retaining its values."

It's true that the object is detached, but the primary key is not reset. To get it work I had to clean it by myself with a method like this:

def cloneAndDetach (self):
    session.make_transient(self)
    self.id = None
    return self

please also note that scoped_session has not the make_transient method, so you really need to use a session instance.

Collectives™ on Stack Overflow

Re-using a sqlalchemy ORM object to insert multiple records

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related