SqlAlchemy：将表导出到新数据库-SqlAlchemy:exporttabletonewdatabase

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

最近才开始使用python，我喜欢它！但是，我坚持使用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.

我正在尝试编写一个脚本来读取MS SQL数据库，查询表（所有字段，只对某些字段进行过滤），并将结果写入本地SQLite数据库。

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

（目标是编写数据适配器：在将结果导出到另一个数据库之前，在SQLite数据库上执行一些查询。也可以写入目标数据库中的临时表。）

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?

我可以建立连接并获得查询结果 - 我可以打印它们，所以我知道该部分有效。但是，如何根据源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!

谢谢！

2 个解决方案

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