CSV to SQLite3

Story: Assume you work for the Finxter Academy. Your boss asks you to import an existing CSV file containing fictitious users to SQLite3.

What is SQLite3, you ask!

SQLite3 is an ANSI-C source-code file that offers a light-weight database with the following qualities:

• Easy to compile and incorporate into projects.
• No external dependencies.
• Supports cross-platforms.
• Easy to port to other systems.

💡 Note: To follow along, click here to download the Finxters CSV file. Then, move this file to the current working directory.

Related Article:

• SQLite Developer – Income and Opportunity

Preparation

Before any data manipulation can occur, one (1) new library will require installation.

• The Pandas library enables access to/from a CSV file.

To install this library, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

💡 Note: The SQLite3 library is built-in to Python and does not require installation. This library contains methods to import and select data to/from the database.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

If the installation was successful, a message displays in the terminal indicating the same.

Feel free to view the PyCharm installation guide for the required library.

• How to install Pandas on PyCharm

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd 
import csv
from csv import reader
import sqlite3

Create SQLite3 Database

The first step is to write a function that creates a SQLite3 database.

Navigate to an IDE and perform the following steps:

• Create a new Python file (example: users.py).
• Paste the code below (remember to add the Required Starter Code).
• Save and run the file.

def connect_db(db_name):
    try:
        con = sqlite3.connect(db_name)
    except:
        con = None
    return con

con = connect_db('users.db')

try:
    cur = con.cursor() 
    print(con)
    print(cur)
except:
    print('An error occurred.')

con.close()

• Line [1] defines the function connect_db with one (1) argument: a database name.
  • Line [2] executes the try statement.
    • The code on Line [3] executes. If the connection is successful, a connection object saves to con.
  • Line [4] executes if the connection fails.
    • The code on Line [5] executes. The variable con contains None.
  • Line [6] returns con from the connect_db function.
• Line [7] calls the connect_db function and saves the return value to con.
• Line [8] executes the try statement.
  • Line [9] creates a cursor class (see below) and assigns this value to cur.
  • Lines [10-11] output the values of con and cur to the terminal.
• Lines [12-13] execute if the try statement fails.
• Line [14] closes the open connection.

💡 Note: conn.cursor() allows Python to execute commands against the SQLite3 database for the duration of the script (or until the connection is closed).

Output

💡 Note: You may want to remove Lines [10-11] before continuing.

Create a Table

The next step is to write a function that creates a SQLite3 table.

The query below contains five (5) fields. These match the number of columns inside the CSV file.

💡 Note: We matched the table field names to the CSV column names. However, you can use your own table field names.

def connect_db(db_name):
    try:
        con = sqlite3.connect(db_name)
    except:
        con = None
    return con

def create_table(table_name):
    query = f'''CREATE TABLE IF NOT EXISTS {table_name} 
               (FID INTEGER, fname VARCHAR(50), lname VARCHAR(50),
                rank VARCHAR(20), solved INTEGER);'''
    cur.execute(query)   

con = connect_db('users.db')

try:
    cur = con.cursor() 
    create_table('finxters')
except:
    print('An error occurred.')

con.close()

• Line [1] defines a function create_table with one (1) argument: a table.
  • Line [2] creates a multi-line query.
  • Line [3] executes the query.
• Line [4] calls the connect_db function and saves the return value to con.
• Line [5] executes the try statement.
  • Line [6] creates a cursor class (see above) and assigns this value to cur.
  • Line [7] creates the finxters table if it does not exist.
• Lines [8-9] execute if the try statement fails.
• Line [10] closes the open connection.

After running this code (barring any unforeseen errors), a SQLite3 database containing one (1) table should now reside in the current working directory.

Inserting Records

The Finxter CSV file contains 50 records (plus a header row). Let’s add these records to the finxters table.

To ensure that these records insert only once, we need to write a function ok_to_insert that checks if the table is empty.

Then another function insert_recs is written to insert rows from the CSV file into the table.

Finally, let’s put it all together for a functioning Python script.

💡 Note: Remember to add the Required Start Code at the top.
The lines in yellow will be addressed below.

def connect_db(db_name):
    try:
        con = sqlite3.connect(db_name)
    except:
        con = None
    return con

def create_table(table_name):
    query = f'''CREATE TABLE IF NOT EXISTS {table_name} 
               (FID INTEGER, first_name VARCHAR(50), last_name VARCHAR(50),
                rank VARCHAR(20), solved INTEGER);'''
    cur.execute(query)    

def ok_to_insert(table_name):
    cur.execute(f'SELECT COUNT(*) from {table_name}')
    rows = int(cur.fetchone()[0])
    return True if rows==0 else False

def insert_recs(csv_file, table_name):    
    with open(csv_file) as f:
        reader = csv.reader(f)
        for field in reader:
            cur.execute(f'INSERT INTO {table_name} VALUES (?,?,?,?,?);', field)

con = connect_db('users.db')

try:
    cur = con.cursor() 
    create_table('finxters')

    if ok_to_insert('finxters'):
         insert_recs('finxters-sample.csv', 'finxters')
except:
    print('An error occurred.')

con.close()

• Line [1] creates a function ok_to_insert with one (1) argument: a table.
  • Line [2] creates and executes a query to determine how many records are in the table.
  • Line [3] uses slicing to return the total number of rows in the table.
  • Line [4] returns the appropriate value.
• Line [5] creates a function insert_recs with two (2) arguments: file, table.
  • Lines [6-9] loop through the CSV file, adding each CSV row to the database table.
    
    For each column (field) to insert, the code expects a ‘?' character. This character lets SQLite3 you know what to expect. Here we retrieve five (5) columns. Therefore we must have five (5) '?' characters separated by commas (,).
• Line [10] executes the try statement.
  • Line [11] creates a cursor class (see above) and assigns this value to cur.
  • Line [12] creates the table if it does not exist.
  • Line [13] returns either True/False from the ok_to_insert function.
    • Line [14] executes and inserts the records into the table.
• Lines [15-16] execute if an error occurs.
• Line [17] closes the open connection.

Fetching Data

Now that the sqlite3 table contains data let’s fetch some records!

results = 'SELECT * FROM finxters WHERE solved > 1500;'
rows = cur.execute(results).fetchall()
 
for r in rows:
    print(r)  

con.close()

• Line [1] creates a query to fetch all records that meet the stated criteria. This saves to the results variable.
• Line [2] executes the query created on Line [1] and returns Tuples.
• Lines [3-4] loop through and output the rows that match the criteria (1 record/line).

Output (snippet)

🧩 Challenge: The Finxter Challenge is to write additional code to format the output as a table.