5

It is possible export sqlite3 table to csv or xls format? I'm using python 2.7 and sqlite3.

Kara
  • 6,115
  • 16
  • 50
  • 57
Risino
  • 361
  • 1
  • 4
  • 10
  • By the way look at this post (is about import from python but it may be ussefull http://stackoverflow.com/questions/2887878/importing-a-csv-file-into-a-sqlite3-database-table-using-python – cristian Nov 24 '10 at 07:42

3 Answers3

9

I knocked this very basic script together using a slightly modified example class from the docs; it simply exports an entire table to a CSV file:

import sqlite3
import csv, codecs, cStringIO

class UnicodeWriter:
    """
    A CSV writer which will write rows to CSV file "f", 
    which is encoded in the given encoding.
    """

    def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
        # Redirect output to a queue
        self.queue = cStringIO.StringIO()
        self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
        self.stream = f
        self.encoder = codecs.getincrementalencoder(encoding)()

    def writerow(self, row):
        self.writer.writerow([unicode(s).encode("utf-8") for s in row])
        # Fetch UTF-8 output from the queue ...
        data = self.queue.getvalue()
        data = data.decode("utf-8")
        # ... and reencode it into the target encoding
        data = self.encoder.encode(data)
        # write to the target stream
        self.stream.write(data)
        # empty queue
        self.queue.truncate(0)

    def writerows(self, rows):
        for row in rows:
            self.writerow(row)

conn = sqlite3.connect('yourdb.sqlite')

c = conn.cursor()
c.execute('select * from yourtable')

writer = UnicodeWriter(open("export.csv", "wb"))

writer.writerows(c)

Hope this helps!

Edit: If you want headers in the CSV, the quick way is to manually add another row before you write the data from the database, e.g:

# Select whichever rows you want in whatever order you like
c.execute('select id, forename, surname, email from contacts')

writer = UnicodeWriter(open("export.csv", "wb"))

# Make sure the list of column headers you pass in are in the same order as your SELECT
writer.writerow(["ID", "Forename", "Surname", "Email"])
writer.writerows(c)

Edit 2: To output pipe-separated columns, register a custom CSV dialect and pass that into the writer, like so:

csv.register_dialect('pipeseparated', delimiter = '|')

writer = UnicodeWriter(open("export.csv", "wb"), dialect='pipeseparated')

Here's a list of the various formatting parameters you can use with a custom dialect.

Mark Bell
  • 28,985
  • 26
  • 118
  • 145
  • Yes, this is helpful. But all data is in one column. For Example I had 4 columns and 12 rows in database (sqlite3). It is possible divide it? Maybe create table in csv with headers? Thx – Risino Nov 24 '10 at 09:44
  • I don't quite understand—this code already outputs the selected columns separated by commas (or at least it does when I run it). Can you post an example row from the CSV file you generated? – Mark Bell Nov 24 '10 at 13:25
  • Yes, you're right. Yes, you're right. This code separate by commas: ID, Forename, Surname, Email, but if it is possible I want separate by columns. E.g.: | ID | Forename | Surname | Email | – Risino Nov 25 '10 at 07:14
  • Ok, I've edited the answer again, but you should really be more specific in your questions; after all the original question never mentioned pipe characters at all. Please mark the answer as accepted if it solves your problem. – Mark Bell Nov 25 '10 at 07:44
  • Yes, this is true, but in this case a pipeserator means a new column in excel table. – Risino Nov 25 '10 at 08:09
  • Problem resolved. As delimiter I use semicolon. csv.register_dialect('separator', delimiter = ';') – Risino Nov 25 '10 at 09:29
1

Yes.Read here sqlitebrowser

  • Import and export records as text
  • Import and export tables from/to CSV files
  • Import and export databases from/to SQL dump files
cristian
  • 8,676
  • 3
  • 38
  • 44
0

External programs: see documentation for sqlite3 for details. You can do it from shell/command line.

On the fly: csv module will help you to handle CSV file format correctly

Eir Nym
  • 1,515
  • 19
  • 30