Clone a sqlalchemy db object with new primary key

By: Varghese Chacko 1 year ago

At many points, a developer may want to clone a row of data for multiple reason. TheSQL Alchemy makeit easy to do in 5 steps. For example we have a table products and clone the product with id 25.

1. Select the record

product = Product.query.get(25)

2. Expunge the object from session

db.session.expunge(product)

3. Make the product trancient

make_transient(product)

For more details, read http://docs.sqlalchemy.org/en/rel_1_1/orm/session_api.html#sqlalchemy.orm.session.make_transient

4. Change any attribute(column in database table) by assigning new data. For example, ifyou want to changename, just assign it.

product.name = "New Name"

 The issue with this method is that the primary key will also be copid. So to create a newrecord, assign None to the primary key. It will trigger the auto generation function and will have next primary key.

5. Add the object to session and commit

db.session.add(product)
db.session.commit()

Putting all together

from sqlalchemy.orm.session import make_transient

product = Product.query.get(25)
db.session.expunge(product)
make_transient(product)

product.name = "New Name"
product.id = None

db.session.add(product)
db.session.commit()