mysql – How do I create a CSV file from database in Python?

mysql – How do I create a CSV file from database in Python?

import csv
import sqlite3

from glob import glob; from os.path import expanduser
conn = sqlite3.connect( # open places.sqlite from one of the Firefox profiles
    glob(expanduser(~/.mozilla/firefox/*/places.sqlite))[0]
)
cursor = conn.cursor()
cursor.execute(select * from moz_places;)
with open(out.csv, w, newline=) as csv_file:  # Python 3 version    
#with open(out.csv, wb) as csv_file:              # Python 2 version
    csv_writer = csv.writer(csv_file)
    csv_writer.writerow([i[0] for i in cursor.description]) # write headers
    csv_writer.writerows(cursor)

PEP 249 (DB API 2.0) has more information about cursor.description.

Using the csv module is very straight forward and made for this task.

import csv
writer = csv.writer(open(out.csv, w))
writer.writerow([name, address, phone, etc])
writer.writerow([bob, 2 main st, 703, yada])
writer.writerow([mary, 3 main st, 704, yada])

Creates exactly the format youre expecting.

mysql – How do I create a CSV file from database in Python?

You can easily create it manually, writing a file with a chosen separator. You can also use csv module.

If its from database you can alo just use a query from your sqlite client :

sqlite <db params> < queryfile.sql > output.csv

Which will create a csv file with tab separator.

Leave a Reply

Your email address will not be published. Required fields are marked *