Had a need to quickly export some large datasets from a MySQL database to CSV file. Decided on using python and the peewee package.
I found the process to be very quick and straightforward to set up my data models and do some simple querying. The tricky part was on how to export the peewee query result set to a CSV file.
Reading the docs mentioned a method for retrieving data as ‘tuples‘ which can then be written to file with the standard python csv package:
myData = Model.select().where(Model.deleted_at == None).tuples()
My other requirement was to define the CSV data headers which we are able to retrieve in proper order from the model’s _meta attribute like so:
headers = [h for h in Plant._meta.sorted_field_names]
With the model’s attribute headers defined we can write a simple python function to export a peewee tuple result including the data headers:
import csv
import time
from peewee import *
# removed peewee model and db definition for brevity
def writeToCsv(data, filename):
print("Writing to csv: {} ...".format(filename))
with open(filename, 'w', newline='') as out:
csvOut = csv.writer(out)
# column headers
headers = [x for x in Model._meta.sorted_field_names]
csvOut.writerow(headers)
# write data rows
for row in data:
csvOut.writerow(row)
# Retrieve data set as tuples
myData = Model.select().where(Model.deleted_at == None).tuples()
# export to csv file
writeToCsv(myData , "myData_{}.csv".format(time.time_ns()))
References:
https://docs.peewee-orm.com/en/latest/peewee/querying.html?highlight=csv#selecting-multiple-records
https://stackoverflow.com/questions/13864940/python-dumping-database-data-with-peewee