如何使用例如将MS Access表导出到Python中的csv文件中pypyodbc [英] How to export MS Access table into a csv file in Python using e.g. pypyodbc
问题描述
我一直在尝试使用pypydobc将 MS Access数据库中的表导出到 csv 文件中-使用 fetchone 功能会花费很多时间,例如200,000行需要大约5分钟的时间来打印.如果 fetchone 更快,我可以将结果打印到一个csv文件中,但是它花费的时间太长了.这是我到目前为止尝试过的:
I have been trying to export a table from MS Access database into a csv file using pypydobc - using fetchone function is taking forever e.g. 200,000 rows are taking about 5 minutes to print. If fetchone was quicker I could have just printed the results into a csv file but it's taking too long. This is what I tried so far:
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
r"Dbq=C:\temp\Temp_DB.accdb;")
cur = conn.cursor()
cur.execute("SELECT Column1, Column2, FROM Table1");
Col1 = []
Col2 = []
row = cur.fetchone()
while row is not None:
print(row)
row = cur.fetchone()
Col1.append(row.get("Column1"))
Col2.append(row.get("Column2"))
cur.close()
conn.close()
此外,到目前为止我还找不到关于pypyodbc中所有功能的文档吗?
Also, is there a documentation on all functions in pypyodbc which I have failed to find so far?
推荐答案
考虑使用cur.fetchall()
和csv
模块直接输出查询结果,而无需附加到单个列表(并考虑避免使用print
保存)处理时间):
Consider using cur.fetchall()
and the csv
module to directly output query results without needing to append to individual lists (and consider refraining from print
to save process time):
import pypyodbc
import csv
# MS ACCESS DB CONNECTION
pypyodbc.lowercase = False
conn = pypyodbc.connect(
r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
r"Dbq=C:\temp\Temp_DB.accdb;")
# OPEN CURSOR AND EXECUTE SQL
cur = conn.cursor()
cur.execute("SELECT Column1, Column2, FROM Table1");
# OPEN CSV AND ITERATE THROUGH RESULTS
with open('Output.csv', 'w', newline='') as f:
writer = csv.writer(f)
for row in cur.fetchall() :
writer.writerow(row)
cur.close()
conn.close()
此外-在上面,您正在连接到Jet/ACE SQL引擎(MSAccess.exe的对象,并且不仅限于该程序,但可用于所有Office/Windows程序-认为MS Access是数据库,但实际上是一个GUI控制台..另外,对于csv导出,您可以与GUI应用程序进行交互并运行Access的
Aside - above you are connecting to the Jet/ACE SQL Engine (an object of the MSAccess.exe and not restricted to the program but available to all Office/Windows programs -the misnomer to think MS Access is a database but is actually a GUI console to one. Alternatively for your csv export, you can interact with the GUI application and run Access' TransferText() method to export tables/queries to text delimited files. And Python can open the database and call the export method with win32com
module:
import win32com.client
# OPEN ACCESS APP AND DATABASE
oApp = win32com.client.Dispatch("Access.Application")
oApp.OpenCurrentDatabase('C:\temp\Temp_DB.accdb')
# EXPORT TABLE TO CSV
acExportDelim = 2
oApp.DoCmd.TransferText(acExportDelim, None, "Table1", 'Output.csv'), True)
oApp.DoCmd.CloseDatabase
oApp.Quit
oApp = None
这篇关于如何使用例如将MS Access表导出到Python中的csv文件中pypyodbc的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!