Skip to content

Lesson 2 of 6

Load the CSV into SQLite with Python

You write a Python script that reads your CSV file and creates an SQLite database table, then checks the row count.

7 minData Analysis

After this lesson you will be able to load a CSV file into an SQLite database using Python, create a table that matches the data, and check how many rows were loaded.

Prepare the Python script

Create a new file called load_data.py in the same folder as your sales.csv file from the previous lesson. This script will open the CSV, create a database, and copy the rows into a table.

Start with the imports you need. The csv module reads CSV files, and sqlite3 gives Python access to SQLite databases.

import csv
import sqlite3

Connect to a new database

SQLite stores a whole database in one file. When you connect to a file that does not exist yet, SQLite creates it. Add this line to create a database file called sales.db.

conn = sqlite3.connect('sales.db')

The variable conn is your connection to the database. You will use it to send commands to SQLite.

Create a table

A table holds rows of data. Each column has a name and a type. Your CSV has four columns: date, item, quantity, and unit_price. Create a table with the same column names and suitable types.

conn.execute('''
CREATE TABLE IF NOT EXISTS sales (
    date TEXT,
    item TEXT,
    quantity INTEGER,
    unit_price REAL
)
''')

The CREATE TABLE IF NOT EXISTS command makes a table only if it is not already there. The types are TEXT for dates and item names, INTEGER for whole numbers, and REAL for numbers with a decimal point.

Read the CSV and insert rows

Open sales.csv in read mode. The csv.DictReader reads each row as a dictionary where the keys are the column names from the first line.

with open('sales.csv', 'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        conn.execute(
            'INSERT INTO sales (date, item, quantity, unit_price) VALUES (?, ?, ?, ?)',
            (row['date'], row['item'], int(row['quantity']), float(row['unit_price']))
        )

The INSERT INTO statement adds one row. The question marks are placeholders. SQLite replaces them with the values in the tuple that follows. This is safer than putting values directly into the SQL string, because it handles special characters correctly.

The values from the CSV are strings. Convert quantity to an integer and unit_price to a float so they match the table types.

Save and check the row count

After the loop, commit the changes to save them to the database file. Then run a query to count the rows.

conn.commit()

count = conn.execute('SELECT COUNT(*) FROM sales').fetchone()[0]
print(f'Loaded {count} rows into sales.db')

fetchone() returns the first row of the result as a tuple. The count is the first item in that tuple. The print line shows how many rows were loaded.

Close the connection when you are done.

conn.close()

The complete load_data.py file looks like this:

import csv
import sqlite3

conn = sqlite3.connect('sales.db')

conn.execute('''
CREATE TABLE IF NOT EXISTS sales (
    date TEXT,
    item TEXT,
    quantity INTEGER,
    unit_price REAL
)
''')

with open('sales.csv', 'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        conn.execute(
            'INSERT INTO sales (date, item, quantity, unit_price) VALUES (?, ?, ?, ?)',
            (row['date'], row['item'], int(row['quantity']), float(row['unit_price']))
        )

conn.commit()

count = conn.execute('SELECT COUNT(*) FROM sales').fetchone()[0]
print(f'Loaded {count} rows into sales.db')

conn.close()

Run the script

In your terminal, run the script from the folder that contains load_data.py and sales.csv.

python load_data.py

The script prints the number of rows it loaded. If your sales.csv has the five data rows from the example, it prints Loaded 5 rows into sales.db.

If you run the script again, the table already exists, so the CREATE TABLE IF NOT EXISTS does nothing. The INSERT INTO statements add the same rows again, so the count doubles. To avoid this, delete the sales.db file before running the script again, or change the script to remove old rows first. For now, delete the file if you want a clean load.

Check the database directly

You can look at the database without Python. In your terminal, run:

sqlite3 sales.db

Then type .tables and press Enter to see the table names. Type SELECT COUNT(*) FROM sales; and press Enter to see the row count. Type .quit to leave. This is a quick way to check that the data is there.

Key takeaways

  • SQLite stores a database in a single file, and Python connects to it with the sqlite3 module.
  • Use CREATE TABLE IF NOT EXISTS to define columns with types that match your data.
  • Read CSV rows with csv.DictReader and insert them using parameterised queries with placeholders.
  • Call commit() to save changes, and use SELECT COUNT(*) to verify how many rows were loaded.

Try it

Modify load_data.py so that it deletes all existing rows from the sales table before inserting the CSV data. Run the script twice in a row. After the second run, the printed count should be the same as after the first run, not double. Check the count with sqlite3 sales.db and the query SELECT COUNT(*) FROM sales; to confirm. A good result is a script that always loads the same number of rows as there are data lines in sales.csv, no matter how many times it runs.

Official documentation

Ready to use this in a job?

Rolebound applies to matching roles for you.

Start now

Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.

    Cookies on Rolebound

    We use necessary cookies to sign you in and keep the site working — those cannot be turned off. We would also like to set analytics and advertising cookies so we can see which pages help people get interviews and which adverts brought them here. Nothing optional is set unless you say yes, and you can change your mind at any time from “Cookie settings” in the footer. Read our Privacy Policy.

    Load the CSV into SQLite with Python | Ask and Answer a Data Question with SQL and Python | Rolebound