CODEWITHSUNDEEP
pythonsqlalchemy
ATK7474
ATK7474

Reputation: 349

SQLAlchemy - Copy records, update one column, and save as new records

Using SQLAlchemy, I am simply trying to query a set of records such as follows

session.query(MyTable).filter_by(foreign_id=413).all()

Then, I just need to make a copy of these records, change the foreign_id, and save them back to the same table as new records. I can't think of an efficient way to do this right now. The only thing that I have come up with is looping through the result set, creating new records that share all the same properties besides foreign_id and then bulk saving these new records.

It is important that I keep the original records in tact, so simply updating the rows is not an option.

If it helps, here is essentially the MyTable object

class MyTable(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    col_a = Column(String(64))
    col_b = Column(String(64))
    foreign_id = Column(Integer, ForeignKey('other_table.id'))

In this example, I would want to keep col_a and col_b the same, but update the foreign_id and id columns.

Upvotes: 2

Views: 1841

Answers (1)

CodeMantle
CodeMantle

Reputation: 1416

If you were using Django ORM, you could simply set the id field to None and save it, creating a copied record, assuming the primary id field was auto-generated.

With SQLAlchemy, this will not work, nor does it make sense in pure SQL terms. An efficient and extensible way to achieve this is:

from sqlalchemy.orm import class_mapper

recs = session.query(MyTable).filter_by(foreign_id=413).all()
for rec in recs:
    newrec = MyTable()
    for item in [p.key for p in class_mapper(MyTable).iterate_properties]:
        if item not in ['id', 'foreign_id']:
            setattr(newrec, item, getattr(rec, item))
    # assign foreign field as appropriate...
    session.add(newrec)
    session.commit()

Upvotes: 2

Related Questions