To convert a Microsoft Acces table (
.accdb) to a CSV file in Python, use the following four steps:
- Establish database connection,
- Run the SQL query to select data,
- Store the data in the CSV using basic Python file I/O and the
- Close the database connection.
Here’s a specific example with additional annotations and simplified for clarity:
import pyodbc import csv # 1. Example establish database connection connection = pyodbc.connect("your connection string") # 2. Run SQL query cursor = connection.cursor() cursor.execute('select * from XXX;') # 3. Store the contents in "cursor" in the CSV using file I/O with open('my_file.csv','w') as f: writer = csv.writer(f) writer.writerows([x for x in cursor.description]) writer.writerows(cursor) # 4. Close the database connection cursor.close() conn.close()
Basically, as soon as you have mastered the art of pulling data into the
cursor variable using the
pyodbc module, you can use all different ways to write that data into the CSV using basic Python file I/O — such as the
open() function or context managers — or even Pandas function to write DataFrames to CSV files.
If you’ve landed on this article, you’ll probably struggle with one or both of those issues:
- Understanding basic Python features and functions—or knowing about them in the first place. In that case, join my free email academy for an infinite stream of learning content.
- Understanding the database handling provided by Python’s Access interface. In that case, check out our in-depth tutorial on
pyodbcon the Finxter blog.
I hope you learned something out of today’s tutorial, my friend! Here’s a joke to round things out:
While working as a researcher in distributed systems, Dr. Christian Mayer found his love for teaching computer science students.
To help students reach higher levels of Python success, he founded the programming education website Finxter.com. He’s author of the popular programming book Python One-Liners (NoStarch 2020), coauthor of the Coffee Break Python series of self-published books, computer science enthusiast, freelancer, and owner of one of the top 10 largest Python blogs worldwide.
His passions are writing, reading, and coding. But his greatest passion is to serve aspiring coders through Finxter and help them to boost their skills. You can join his free email academy here.