CODEWITHSUNDEEP
pythonmysqlsqlitefile-iocsv
Merlin
Merlin

Reputation: 25719

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

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.

Upvotes: 31

Views: 96991

Answers (8)

Cristian Ciupitu
Cristian Ciupitu

Reputation: 20930

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.

Upvotes: 73

Bahubali Patil
Bahubali Patil

Reputation: 1135

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)

Upvotes: 0

goyuiitv
goyuiitv

Reputation: 13

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.

Upvotes: 1

Francis Kessie
Francis Kessie

Reputation: 51

This is simple and works fine for me.

Lets say you have already connected to your database table and also got a cursor object. So following on on from that point.

import csv
curs = conn.cursor()
curs.execute("select * from oders")
m_dict = list(curs.fetchall())

with open("mycsvfile.csv", "wb") as f:
    w = csv.DictWriter(f, m_dict[0].keys())
    w.writerow(dict((fn,fn) for fn in m_dict[0].keys()))
    w.writerows(m_dict)

Upvotes: 2

John Machin
John Machin

Reputation: 83032

How to extract the column headings from an existing table:

You don't need to parse an SQL "create table" statement. This is fortunate, as the "create table" syntax is neither nice nor clean, it is warthog-ugly.

You can use the table_info pragma. It gives you useful information about each column in a table, including the name of the column.

Example:

>>> #coding: ascii
... import sqlite3
>>>
>>> def get_col_names(cursor, table_name):
...     results = cursor.execute("PRAGMA table_info(%s);" % table_name)
...     return [row[1] for row in results]
...
>>> def wrong_way(cur, table):
...     import re
...     cur.execute("SELECT sql FROM sqlite_master WHERE name=?;", (table, ))
...     sql = cur.fetchone()[0]
...     column_defs = re.findall("[(](.*)[)]", sql)[0]
...     first_words = (line.split()[0].strip() for line in column_defs.split(','))
...     columns = [word for word in first_words if word.upper() != "CONSTRAINT"]
...     return columns
...
>>> conn = sqlite3.connect(":memory:")
>>> curs = conn.cursor()
>>> _ignored = curs.execute(
...     "create table foo (id integer, name text, [haha gotcha] text);"
...     )
>>> print get_col_names(curs, "foo")
[u'id', u'name', u'haha gotcha']
>>> print wrong_way(curs, "foo")
[u'id', u'name', u'[haha'] <<<<<===== WHOOPS!
>>>

Other problems with the now-deleted "parse the create table SQL" answer:

  1. Stuffs up with e.g. create table test (id1 text, id2 int, msg text, primary key(id1, id2)) ... needs to ignore not only CONSTRAINT, but also keywords PRIMARY, UNIQUE, CHECK and FOREIGN (see the create table docs).

  2. Needs to specify re.DOTALL in case there are newlines in the SQL.

  3. In line.split()[0].strip() the strip is redundant.

Upvotes: 2

Stephen Johnson
Stephen Johnson

Reputation: 467

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 you're expecting.

Upvotes: 8

user39178
user39178

Reputation:

unless i'm missing something, you just want to do something like so...

f = open("somefile.csv")
f.writelines("header_row")

logic to write lines to file (you may need to organize values and add comms or pipes etc...)

f.close()

Upvotes: -1

Guillaume Lebourgeois
Guillaume Lebourgeois

Reputation: 3873

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

If it's 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.

Upvotes: 5

Related Questions