Hi, Here's what I wrote to create PostgreSQL's data backup in SQLite: (yes, I know pg_dump, I just want to create/use it in SQLAlchemy)
| new_engine = sqlalchemy.create_engine("sqlite:///offline.db")
| metadata.create_all(bind=new_engine)
|
| # SQLite doesn't care about foreign keys much so we can just copy the data
| for table in metadata.sorted_tables:
| if table.name == 'posters':
| continue # see below
| data = table.select(bind=session.bind).execute().fetchall()
| if data:
| table.insert(bind=new_engine).execute(data)
|
| # posters table - "data" column is BLOB!
| for poster in metadata.tables['posters'].select(bind=session.bind).execute():
| metadata.tables['posters'].insert(bind=new_engine).\
| execute(md5sum=poster.md5sum, data=StringIO(poster.data).read())
(metadata and session are already created in app. and are pointing to
PostgreSQL's stuff)
Questions:
1) can it be done nicer? Specially the part with tables containing BLOB
columns ("posters" in above example)
2) is there an easy way to do this the opposite way (from SQLite to
PostgreSQL)?
--
-=[ Piotr Ożarowski ]=-
-=[ http://www.ozarowski.pl ]=-
pgpmlMSC0oK3Y.pgp
Description: PGP signature
