SqlAlchemy: export table to new database

Question

Just recently started using python and I like it! However, I am stuck with SqlAlchemy.

I am trying to write a script that reads an MS SQL database, queries a table (all fields, only a filter in some fields) and writes the results to a local SQLite database.

(The goal is to write a data adapter: execute some queries in the SQLite database before exporting the results to another database. It is also possible to write to a temporary table in the target database.)

I can establish a connection and get the query results - I can print them so that I know that this part works. But how can I create a new table based on the structure of the query results from the source SQL Server?

It works:

import sqlalchemy esd = sqlalchemy.create_engine( 'mssql+pyodbc://username:passwordSservername/dbname' ) for row in esd.execute( 'select * from ticket_actions where log_dt > \'2012-09-01\''): print( row.eFolderID ) 

This also works:

 import pyodbc cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=username;PWD=password') cursor = cnxn.cursor() for row in cursor.execute( 'select * from ticket_actions where log_dt > \'2012-09-01\''): print( row.eFolderID ) 

Any ideas on how to create a new table with the same structure as the query were?

Thanks!

Answer 1

See Creating and deleting database tables :

Creating ... individual tables can be done using the create() ... Table method.

To read the source structure, see Reflecting Database Objects :

The A Table object can be instructed to load information about itself from the database database object that already exists in the database.
[...]
A reflection system can also reflect representations.

Answer 2

Check this:

 def copy_table(src_session, src_class, dst_session, dst_class): r=src_session.query(src_class).all() for i in r: j=dst_class() [setattr(j, col.name, getattr(i, col.name)) for col in i.__table__.columns] dst_session.add(j) se1=db1.Session() se2=db2.Session() copy_table(se1, db1.Book, se2, db2.Book) se2.commit()