Write to CSV from sqlite3 database in python

Question

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)

.writerows as name suggests writes all rows and you want .writerow (no s at the end) which writes a single row. Also see Viktor's answer. — freakish
– freakish, Commented Sep 16, 2013 at 11:49

Viktor Kerkez · Accepted Answer · 2013-09-16 11:41:22Z

10

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)

answered Sep 16, 2013 at 11:41

Viktor Kerkez

46.8k13 gold badges109 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SScode Over a year ago

Great. That definitely helped. I also forgot that I needed to add a SELECT method in order to grab the data i.e. code c.execute("SELECT * FROM inventory") Then in combination with your suggestion it worked. Thanks

GERMAN RODRIGUEZ · Accepted Answer · 2020-06-24 19:34:03Z

1

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()

edited Jun 24, 2020 at 19:34

answered Jun 24, 2020 at 17:39

GERMAN RODRIGUEZ

5151 gold badge5 silver badges10 bronze badges

Comments

Powers · Accepted Answer · 2020-09-18 20:35:54Z

Using Pandas should be more performant and requires less code. You can save the data from a sqlite table to a Pandas DataFrame and then use Pandas to write the CSV file.

df = pd.read_sql('SELECT * from cars', conn)
df.to_csv('cars.csv')

Here's the full code that creates your sqlite table with fake data:

import pandas as pd
import sqlite3

# create Pandas DataFrame
data = [('Toyota', 'Hilux', 2),
    ('Ford', 'Tek', 6),
    ('Ford', 'Outlander', 9),
    ('Honda', 'Dualis', 3),
    ('Honday', 'Elantre', 4)]
df = pd.DataFrame.from_records(data, columns=['make', 'model', 'age'])

# establish sqlite connection
conn = sqlite3.connect('../tmp/cars.db')
c = conn.cursor()

# create sqlite table
c.execute('''CREATE TABLE cars (make text, model text, age int)''')

# add data to sqlite table
df.to_sql('cars', conn, if_exists='append', index = False)

# write sqlite table out as a CSV file
df = pd.read_sql('SELECT * from cars', conn)
df.to_csv('../tmp/cars.csv')

Here's code to write out all the tables in a sqlite database as CSV files with a single command:

for table in c.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall():
    t = table[0]
    df = pd.read_sql('SELECT * from ' + t, conn)
    df.to_parquet('../tmp/' + t + '.csv')

See here for more info.

Collectives™ on Stack Overflow

Write to CSV from sqlite3 database in python

3 Answers 3

1 Comment

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

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

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

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related