CODEWITHSUNDEEP
pythonsqlmysqlsqlalchemy
0atman
0atman

Reputation: 3385

How to save to two tables using one SQLAlchemy model

I have an SQLAlchemy ORM class, linked to MySQL, which works great at saving the data I need down to the underlying table. However, I would like to also save the identical data to a second archive table.

Here's some psudocode to try and explain what I mean

my_data = Data() #An ORM Class
my_data.name = "foo"

#This saves just to the 'data' table
session.add(my_data)

#This will save it to the identical 'backup_data' table
my_data_archive = my_data
my_data_archive.__tablename__ = 'backup_data'
session.add(my_data_archive)

#And commits them both
session.commit()

Just a heads up, I am not interested in mapping a class to a JOIN, as in: http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables

Upvotes: 1

Views: 2423

Answers (2)

van
van

Reputation: 77082

I list some options below. I would go for the DB trigger if you do not need to work on those objects in your model.

Upvotes: 3

Denis Otkidach
Denis Otkidach

Reputation: 33250

Create 2 identical models: one mapped to main table and another mapped to archive table. Create a MapperExtension with redefined method after_insert() (depending on your demands you might also need after_update() and after_delete()). This method should copy data from main model to archive and add it to the session. There are some tricks to copy all columns and many-to-many relations automagically.

Note, that you'll have to flush() session twice to store both objects since unit of work is computed before mapper extension adds new object to the session. You can redefine Session.flush() to take care of this problem. Also auto-incremented fields are assigned when the object is flushed, so you'll have to delay copying if you need them too.

It is one possible scenario which is proved to work. I'd like to know if there is a better way.

Upvotes: 1

Related Questions