SqlAlchemy:将表导出到新数据库 [英] SqlAlchemy: export table to new database

问题描述

最近才开始使用python，我喜欢它！但是，我坚持使用 SqlAlchemy.

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

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

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.

(目的是写一个数据适配器:在将结果导出到另一个数据库之前对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.)

我可以建立连接并获取查询结果 - 我可以打印它们，以便我知道该部分有效.但是如何根据源 SQL Server 的查询结果的结构创建新表?

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?

这有效:

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 )

这也有效:

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?

谢谢！

推荐答案

参见 创建和删除数据库表:

创建……单独的表可以通过create()……Table的方法来完成.

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

要阅读源代码结构，请参阅反映数据库对象:

For reading the source structure, see Reflecting Database Objects:

可以指示 Table 对象从数据库中已经存在的相应数据库架构对象加载有关其自身的信息.
[…]
反射系统也可以反射视图.

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.

这篇关于SqlAlchemy:将表导出到新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！