CSV to SQLite CSV Library


In this post I am going to read a CSV (comma separated values) file in Python and put the data into a SQLite file. It is also possible to use the pandas library to read the csv file into a pandas DataFrame and then send that out to the SQLite database. Please see From CSV to SQLite. I am going to import a CSV file into SQLite using the CSV Library of Python inn a Jupyter Notebook.

To do this I created a project in Jupyter Notebook using Anaconda Navigator that is called CSV to SQLite CSV Library.

The CSV file is called friends.csv. The database we are creating is called friends.db. The table inside the database is called tbl_friends. The column names of this table are called number, firstname and amount. Here’s the Python code.

import sqlite3
import csv

# create the database friends.db if it doesn't exist
connection = sqlite3.connect(r'D:\Test\friends.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS tbl_friends  
            (number INT, firstname TEXT, amount INT)''')
# I used number because I cannot use index

file = open(r'D:\Test\friends.csv')
friends_data = csv.reader(file)
cursor.executemany('''INSERT INTO tbl_friends VALUES (?,?,?)''', friends_data)

connection.commit()
connection.close()

Below is a screenshot of the results in the database.

Leave a Reply