For some reason, I want to dump a table from a database (sqlite3) in the form of a csv file. I'm using a python script with elixir (based on sqlalchemy) to modify the database. I was wondering if there is any way to dump the table I use to csv.
I've seen sqlalchemy serializer but it doesn't seem to be what I want. Am I doing it wrong? Should I call the sqlite3 python module after closing my sqlalchemy session to dump to a file instead? Or should I use something homemade?
There are numerous ways to achieve this, including a simple os.system() call to the sqlite3 utility if you have that installed, but here's roughly what I'd do from Python:
import sqlite3
import csv
con = sqlite3.connect('mydatabase.db')
outfile = open('mydump.csv', 'w', newline='') # before Py 3.1 use 'wb' **
outcsv = csv.writer(outfile)
cursor = con.execute('select * from mytable')
# dump column titles (optional)
outcsv.writerow(x[0] for x in cursor.description)
# dump rows
outcsv.writerows(cursor.fetchall())
outfile.close()
** Note that this code was originally written for Python 2.x and 3.0. With the release of Python 3.1 the docs for csv writer say to open the file with newline='' instead of using mode wb.