How to export MS Access table into a csv file in Python using e.g. pypyodbc

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

Also, is there a documentation on all functions in pypyodbc which I have failed to find so far?

Solution

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

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