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

user_428862 0
Viewed 85611

I have a Sqlite 3 and/or MySQL table named "clients"..

Using python 2.6, How do I create a csv file named Clients100914.csv with headers? excel dialect...

The Sql execute: select * only gives table data, but I would like complete table with headers.

How do I create a record set to get table headers. The table headers should come directly from sql not written in python. 

w = csv.writer(open(Fn,'wb'),dialect='excel')
#w.writelines("header_row")
#Fetch into sqld
w.writerows(sqld)

This code leaves me with file open and no headers. Also cant get figure out how to use file as log.

csv file-io sqlite mysql python
8 Answers

It can be easily done using pandas and sqlite3. In extension to the answer from Cristian Ciupitu.

import sqlite3
from glob import glob; from os.path import expanduser
conn = sqlite3.connect(glob(expanduser('data/clients_data.sqlite'))[0])
cursor = conn.cursor()

Now use pandas to read the table and write to csv.

clients = pd.read_sql('SELECT * FROM clients' ,conn)
clients.to_csv('data/Clients100914.csv', index=False)

This is more direct and works all the time.

user_92789820

The below code works for Oracle with Python 3.6 :

import cx_Oracle
import csv

# Create tns
dsn_tns=cx_Oracle.makedsn('<host>', '<port>', service_name='<service_name>')

# Connect to DB using user, password and tns settings
conn=cx_Oracle.connect(user='<user>', password='<pass>',dsn=dsn_tns)
c=conn.cursor()

#Execute the Query
c.execute("select * from <table>")

# Write results into CSV file
with open("<file>.csv", "w", newline='') as csv_file:
    csv_writer = csv.writer(csv_file)
    csv_writer.writerow([i[0] for i in c.description]) # write headers
    csv_writer.writerows(c)
user_45397420
Related