25

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,

Share a link to this question
CC BY-SA 3.0
Improve this question
30

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', 'wb') as f:
    writer = csv.writer(f)
    writer.writerow(['Column 1', 'Column 2', ...])
    writer.writerows(data)
Share a link to this answer
CC BY-SA 3.0
Improve this answer
2
  • @Dougal What is 'column 1'. Are these the database column's or are these the csv columns. Thanks.
    – Adam Azam
    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
13

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'

Share a link to this answer
CC BY-SA 4.0
Improve this answer
2
8

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/

Share a link to this answer
CC BY-SA 4.0
Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.