Python and sqlite

Updated: 06 July 2024

Use Python to read a csv to sqlite, then write out a new csv

import csv, sqlite3, os

# input csv file something like this
"""
1,maths,smith,sally
9,history,blogs,john
4,art,roberts,mike
"""

# a handy function to drop and recreate a database
def newDatabase():
    if os.path.exists('db.sqlite'):
        os.remove('db.sqlite')
    try:
        conn = sqlite3.connect('db.sqlite')
        return conn
    except Error as e:
        print(e)

conn = newDatabase()
cur = conn.cursor()

sql = '''CREATE TABLE students (
    id INTEGER PRIMAY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
    );'''

# create a table in our database
cur.execute(sql)

# populate table with a few of the columns in the csv file
with open('students_raw.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
    for row in readCSV:
        cur.execute('INSERT INTO students(id, first_name, last_name) VALUES(?,?,?)', [row[0], row[3], row[2]])

conn.commit()

cur.execute('SELECT id, first_name, last_name FROM students')
rows = cur.fetchall()

conn.close()

# write the csv file
with open('out_file.csv', mode='w') as out_file:
    out_writer = csv.writer(out_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    for r in rows:
        out_writer.writerow( [r[0], r[1]] )