Jerry Jerry - 1 year ago 118
Python Question

SqlAlchemy: export table to new database

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?


Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download