33

Here is what I currently have:

conn = sqlite3.connect(dbfile)
conn.text_factory = str ## my current (failed) attempt to resolve this
cur = conn.cursor()
data = cur.execute("SELECT * FROM mytable")

f = open('output.csv', 'w')
print >> f, "Column1, Column2, Column3, Etc."
for row in data:
  print >> f, row
f.close()

It creates a CSV file with output that looks like this:

Column1, Column2, Column3, Etc.
(1, u'2011-05-05 23:42:29',298776684,1448052234,463564768,-1130996322, None, u'2011-05-06 04:44:41')

I don't want the rows to be in parentheses nor have quotes nor the 'u' before strings. How do I get it to write the rows to csv without all of this? Thanks,

Dan
  • 4,488
  • 5
  • 48
  • 75

4 Answers4

32

What you're currently doing is printing out the python string representation of a tuple, i.e. the return value of str(row). That includes the quotes and 'u's and parentheses and so on.

Instead, you want the data formatted properly for a CSV file. Well, try the csv module. It knows how to format things for CSV files, unsurprisingly enough.

with open('output.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(['Column 1', 'Column 2', ...])
    writer.writerows(data)

The newline='' is apparently needed for correct escaping of newlines inside quoted fields.

Danica
  • 28,423
  • 6
  • 90
  • 122
  • @Dougal What is 'column 1'. Are these the database column's or are these the csv columns. Thanks. – Barry Allen Apr 29 '18 at 18:40
  • @AdamAzam That's just literally printed out to the CSV file as the first (header) row. This snippet doesn't say anything about the database, just assumes it's gathered in a variable `data` in the order you want it. – Danica Apr 29 '18 at 19:49
  • `writer.writerow(['Column 1', 'Column 2'])` gives me error:`TypeError: a bytes-like object is required, not 'str' `. It is not the case when I open the file with `w` instead of `wb`. I commented out `conn.text_factory = str` in my code - I'm not sure whether it is relevant to the error I get. – John Smith Mar 22 '23 at 09:33
  • 1
    Yes, sorry @JohnSmith, maybe this changed in the past 11 years or maybe I was wrong then – according to current docs it should be opened in text mode, not binary mode. – Danica Mar 27 '23 at 01:53
21

my version that works without issues with just a couple of lines.

import pandas as pd

conn = sqlite3.connect(db_file, isolation_level=None,
                       detect_types=sqlite3.PARSE_COLNAMES)
db_df = pd.read_sql_query("SELECT * FROM error_log", conn)
db_df.to_csv('database.csv', index=False)

If you want a table separated file, change the .csv extension to .tsv and add this sep='\t'

Louis Maddox
  • 5,226
  • 5
  • 36
  • 66
Gopinath S
  • 511
  • 6
  • 20
  • 3
    This is the best answer. For modern versions of Pandas, it's best to use `read_sql`, so you don't need to worry about [lower level details](https://stackoverflow.com/questions/48171611/difference-between-pandas-read-sql-query-and-read-sql-table). See [here](https://mungingdata.com/sqlite/export-tables-queries-to-csv-parquet/) for more info. – Powers Sep 18 '20 at 20:55
  • @Powers it's fine until you have so much data that you need to stream it to the csv. – t3chb0t Oct 20 '21 at 13:03
  • If the table is too big, this may cause system to hang due to memory insufficient. –  Hoo Feb 11 '22 at 04:46
12

Converting an sqlite database table to csv file can also be done directly using sqlite3 tools: 

>sqlite3 c:/sqlite/chinook.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .output data.csv
sqlite> SELECT customerid,
   ...>        firstname,
   ...>        lastname,
   ...>        company
   ...>   FROM customers;
sqlite> .quit

The above sqlite3 commands will will create a csv file called data.csv in your current directory (of course this file can be named whatever you choose). More details are available here: http://www.sqlitetutorial.net/sqlite-export-csv/

Hula_Zell
  • 1,190
  • 13
  • 13
0

I used a silly but easier one if only one table exists!

First I converted the database to a Pandas DataFrame

conn = sqlite3.connect(database file)
df = pd.read_sql_query("select * from TABLE", conn)

Then convert df to a CSV file

df.to_csv(r'...\test_file.csv')

So a CSV file is creating under name of test_file.CSV

eatsfood
  • 950
  • 2
  • 21
  • 31
MJ Hessami
  • 1