SqlAlchemy: export table to new database

Question

Only 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, query a table (all fields, only a filter on some fields), and write the results to a local SQLite database.

(The object is to write a data adapter: perform some queries on the SQLite database before exporting the results to another database. Writing to temporary table in the target database is also possible.)

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

This 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 has?

Thanks!

CL. · Accepted Answer · 2016-11-28T07:33:57.407

4

See Creating and Dropping Database Tables:

Creating … individual tables can be done via the create() … method of Table.

For reading the source structure, see Reflecting Database Objects:

A Table object can be instructed to load information about itself from the corresponding database schema object already existing within the database.
[…]
The reflection system can also reflect views.

edited Nov 28 '16 at 07:33

answered Sep 19 '12 at 13:35

CL.

173,858
17
217
259

I knew about the creation of tables, but reflection - that's the bit I was looking for, thanks! – Jerry Sep 19 '12 at 13:56
That link doesn't work any more; I'm guessing this is the new one: http://docs.sqlalchemy.org/en/latest/core/reflection.html, To quote, "In the most simple case you need only specify the table name, a MetaData object, and the autoload=True flag." – Racing Tadpole Nov 27 '16 at 23:58

score 3 · Answer 2 · answered Feb 10 '14 at 13:01

Test 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()

SqlAlchemy: export table to new database

2 Answers2

Linked