我正在使用pyodbc處理存儲(chǔ)在MS-Access數(shù)據(jù)庫中的事件。每個(gè)月都是一個(gè)單獨(dú)的文件/數(shù)據(jù)庫，我想處理多個(gè)月的事件。是否可以為包含多個(gè)月（即數(shù)據(jù)庫連接）的視圖創(chuàng)建游標(biāo)？編輯1：無需編寫新數(shù)據(jù)庫？（也許有UNION VIEW之類的東西？）
1 回答
蕭十郎
TA貢獻(xiàn)1815條經(jīng)驗(yàn) 獲得超13個(gè)贊
您需要建立多個(gè)連接和游標(biāo)，但是您應(yīng)該能夠處理數(shù)據(jù)。
比方說，該文件存儲(chǔ)為month_1.mdb，month_2.mdb等，C:\access。
# Set up each connection, must have a way to access each file's name
connect_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\access\\month_{}.mdb;"
# Assuming that you'll get the same data from each database
sql = "SELECT column_1, column_2 FROM table"
# Connect to each file
connections = [pyodbc.connect(connect_string.format(n)) for n in range(1, 12 + 1)]
# Create a cursor for each file
cursors = [conn.cursor() for conn in connections]
# Query each file and save the data
data = []
for cur in cursors:
cur.execute(sql)
data.extend(cur.fetchall())
好的，現(xiàn)在您已經(jīng)擁有了所有數(shù)據(jù)。您可以使用該sqlite3模塊創(chuàng)建內(nèi)存數(shù)據(jù)庫，然后對(duì)其進(jìn)行查詢。
import sqlite3
# Create your temporary database
connection = 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 database
for 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
添加回答
舉報(bào)
0/150
提交
取消