Python: Sqlite3 query output to .csv file

Question

I would like to execute this query:

select datetime(date/1000,'unixepoch','localtime') as DATE, address as RECEIVED, body as BODY from sms;

And save it's output to a .csv file in a specified directory. Usually in Ubuntu terminal it is far more easy to manually give commands to save the output of the above query to a file. But i am not familiar with Python-sqlite3. I would like to know how do i execute this query and save it's output to custom directory in a .csv file. Please help me out !

Start with this solution: http://stackoverflow.com/q/17556450/1290420 — daedalus, Jan 21 '16 at 03:44
Thanks that worked. But had to add encoding as UTF-8. That worked quite well. I used pandas.io.sql module to write the query into csv file. — Ashwin Joshi, Jan 21 '16 at 04:49

score 0 · Answer 1 · answered Jan 21 '16 at 03:48

Quick and dirty:

import sqlite

db = sqlite.connect('database_file')
cursor = db.cursor()

cursor.execute("SELECT ...")
rows = cursor.fetchall() 

# Itereate rows and write your CSV

cursor.close()
db.close()

Rows will be a list with all matching records, which you can then iterate and manipulate into your csv file.

If you just want to make a csv file, look at the csv module. The following page should get you going https://docs.python.org/2/library/csv.html

You can also look at the pandas module to help create the file.

Python: Sqlite3 query output to .csv file

1 Answers1