How to write CSV data to a SQLite database with Python

By | May 22, 2017

sqlite3In this short post I will show the syntax for writing data to a SQL database with the Python library, sqlite3. We will be using Python 3+. A SQLite database is exactly what it sounds like – a lightweight implementation of the popular SQL relational database management system, making it perfect for browsers, embedded systems, or small scale data storage/analysis (Wikipedia). On to the code, let’s do it in less than 20 lines.

For sake of example, let’s say you scraped some linguistic data like this (but chose to output it in .csv format instead). Then you have three columns: context, response, and score. Each of these will be entered as text fields (though we may want to change score to “real” type later on). Here’s the data I tested this code with:

context, response, score
"Raj: I don't like bugs, okay. They freak me out.", Sheldon: Interesting. You're afraid of insects and women. Ladybugs must render you catatonic., 5
"Sheldon: (3 knocks) Penny! (3 knocks) Penny! (3 knocks) Penny!Bernadette: What happens if I say come in?Penny: Well, find out.Bernadette: Come in!Sheldon: (silence)(3 knocks) Bernadette! (3 knocks) Bernadette! (3 knocks) Bernadette!Penny: Come in! Sheldon: Keep it up. I've got nowhere else to be.Bernadette: Just come in.","Sheldon: For future reference, if I want to watch Mean Girls, I'll just stream it on Netflix.", 5
Sheldon: Why are you crying? Penny: Because I'm stupid. ,"Sheldon: That's no reason to cry. One cries because one is sad. For example, I cry because others are stupid, and that makes me sad.", 5

And here’s the script that will populate a table “utterances” with the data in the CSV file and save it as “example.db”.

import sqlite3
import csv

f=open('sheldon_quotes.csv','r') # open the csv data file
next(f, None) # skip the header row
reader = csv.reader(f)

sql = sqlite3.connect('example.db')
cur = sql.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS utterances
            (context text, response text, score real)''') # create the table if it doesn't already exist
for row in reader:
	cur.execute("INSERT INTO utterances VALUES (?, ?, ?)", row)

Simple example. “sqlite3.connect()” creates a new database or connects to an existing one of the same name. “sql.cursor” creates the cursor object that you use to execute the actual database commands (values are substituted in wherever “?” exists. It takes care of figuring out that index 0 of the row goes where the first question mark is placed, index 1 goes where the second question mark is, and so on. You can take a look at the data by adding two lines like this:

for row in cur.execute('SELECT * FROM utterances'):

There’s also some good documentation on the docs page. I recommend taking a look at commands like .fetchone(), .fetchall(), .keys(), create_function,  and the example shell here which is a short script that lets you interactively experiment with commands.

Leave a Reply

Your email address will not be published.