How to save to two tables using one SQLAlchemy model

Question

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

Answer 1

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.

• use database trigger to do this job for you
• create a SessionExtension which will create and add to session copy-objects (usually on before_flush). Edit-1: You can take versioning example from SA as a basic; this code is doing even more then you need.
• see SA Versioning example which will not only give you a copy of the object, but the whole version history, which might be what you wish for
• see Reverse mapping from a table to a model in SQLAlchemy question, where the proposed solution is described in the blogpost.

Answer 2

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.