Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Write to CSV from sqlite3 database in python

Tags:

python

sqlite

csv

Ok, So I have a database called cars.db which has a table == inventory,

Inventory essentially contains

    ('Ford', 'Hiluz', 2),
    ('Ford', 'Tek', 6),
    ('Ford', 'Outlander', 9),
    ('Honda', 'Dualis', 3),
    ('Honday', 'Elantre', 4)

I then wrote this which is meant to edit that to the csv, however, I can't seem to work this out, in some cases I get stuff to print but its not right, and when I try and fix that, nothing prints. Any suggestions to get me on track?

#write table to csv

import sqlite3
import csv

with sqlite3.connect("cars.db") as connection:
    csvWriter = csv.writer(open("output.csv", "w"))
    c = connection.cursor()

    rows = c.fetchall()

    for x in rows:
        csvWriter.writerows(x)
like image 442
SScode Avatar asked Sep 16 '13 11:09

SScode


People also ask

How do I convert a SQLite database to a CSV file?

First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.

How fetch data from sqlite3 database in Python?

SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.


2 Answers

You should just do:

rows = c.fetchall()
csvWriter.writerows(rows)

If the reason you iterate through the rows is because you wan't to preprocess them before writing them to the file, then use the writerow method:

rows = c.fetchall()
for row in rows:
    # do your stuff
    csvWriter.writerow(row)
like image 50
Viktor Kerkez Avatar answered Sep 22 '22 23:09

Viktor Kerkez


In order to put tittles in first row, dictionary approach is suggested for table inventory in cars.db

import sqlite3
import csv
import os.path
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(BASE_DIR, "cars.db")
conn = sqlite3.connect(db_path)
c = conn.cursor()
c.execute("SELECT rowid, * FROM inventory")    
columns = [column[0] for column in c.description]
results = []
for row in c.fetchall():
    results.append(dict(zip(columns, row)))
with open("output.csv", "w", newline='') as new_file:
    fieldnames = columns
    writer = csv.DictWriter(new_file,fieldnames=fieldnames)
    writer.writeheader()
    for line in results:
        writer.writerow(line)
conn.close()
like image 21
GERMAN RODRIGUEZ Avatar answered Sep 24 '22 23:09

GERMAN RODRIGUEZ