How to create and restore a backup from SqlAlchemy?

Question

I'm writing a Pylons app, and am trying to create a simple backup system where every table is serialized and tarred up into a single file for an administrator to download, and use to restore the app should something bad happen.

I can serialize my table data just fine using the SqlAlchemy serializer, and I can deserialize it fine as well, but I can't figure out how to commit those changes back to the database.

In order to serialize my data I am doing this:

from myproject.model.meta import Session
from sqlalchemy.ext.serializer import loads, dumps
q = Session.query(MyTable)
serialized_data = dumps(q.all())

In order to test things out, I go ahead and truncation MyTable, and then attempt to restore using serialized_data:

from myproject.model import meta
restore_q = loads(serialized_data, meta.metadata, Session)

This doesn't seem to do anything... I've tried calling a Session.commit after the fact, individually walking through all the objects in restore_q and adding them, but nothing seems to work.

What am I missing? Or is there a better way to do what I'm aiming for? I don't want to shell out and directly touch the database, since SqlAlchemy supports different database engines.

Answer 1

You have to use Session.merge() method instead of Session.add() to put deserialized object back into the session.