创建pyodbc游标到多个数据库

Question

我正在使用pyodbc处理存储在MS-Access数据库中的事件。每个月都是一个单独的文件/数据库，我想处理多个月的事件。

是否可以为包含多个月（即数据库连接）的视图创建游标？

编辑1：无需编写新数据库？（也许有UNION VIEW之类的东西？）

Answer 1

您需要建立多个连接和游标，但是您应该能够处理数据。比方说，该文件存储为month_1.mdb，month_2.mdb等，C:\access。# Set up each connection, must have a way to access each file's nameconnect_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\access\\month_{}.mdb;"# Assuming that you'll get the same data from each databasesql = "SELECT column_1, column_2 FROM table"# Connect to each fileconnections = [pyodbc.connect(connect_string.format(n)) for n in range(1, 12 + 1)]# Create a cursor for each filecursors = [conn.cursor() for conn in connections]# Query each file and save the datadata = []for cur in cursors:  cur.execute(sql)  data.extend(cur.fetchall())好的，现在您已经拥有了所有数据。您可以使用该sqlite3模块创建内存数据库，然后对其进行查询。import sqlite3# Create your temporary databaseconnection = sqlite3.connect(":memory:")cursor = connection.cursor()# Set up a place to hold the data fetched previously_ = cur.execute("CREATE TABLE t(x INTEGER, y INTEGER)")# Dump all the data into the databasefor column_1, column_2 in data:  _ = cursor.execute("INSERT INTO t VALUES (?, ?)", [column_1, column_2])# Now you can run queries against the new view of your data