Loading data into SQLite

Different approaches to load data into a SQLite database using the following Python libraries:

Pandas
csvkit
sqlite-utils

Pandas

Using the pandas library (alongside click, and uv) to load a csv file into a sqlite database.

This approach is the most involved, but has the added benefits of being able to change to load different kinds of files such as excel and json. Can also be modified for perculiar csv files which may have different delimeters or header and footer rows that need to be ignored.

uv run load_sqlite_database.py

Which executes the following python script:

# /// script
# requires-python = "==3.12"
# dependencies = [
#   "pandas",
#   "click",
# ]
# ///

import pandas as pd
import sqlite3
import click

@click.command()
@click.option('--csv_file', type=str, required=True, help='Path to the CSV file to load.')
@click.option('--db_file', type=str, required=True, help='Path to the SQLite database file.')
@click.option('--table_name', type=str, required=True, help='Name of the table to create or append data to.')

# Function to load data from a CSV file into a SQLite database
def load_csv_to_sqlite(csv_file, db_file, table_name):
    """
    Load data from a CSV file into a SQLite database table.

    :param csv_file: Path to the CSV file.
    :param db_file: Path to the SQLite database file.
    :param table_name: Name of the table to create or append data to.
    """
    # Create a connection to the SQLite database
    conn = sqlite3.connect(db_file)

    # check if the CSV file exists
    if not pd.io.common.file_exists(csv_file):
        raise FileNotFoundError(f"The file {csv_file} does not exist.")
    
    # check if it is a valid CSV file
    if not csv_file.endswith('.csv'):
        raise ValueError(f"The file {csv_file} is not a valid CSV file.")
    
    # Load the CSV file into a DataFrame
    df = pd.read_csv(csv_file)

    # 
    # load excel file into a DataFrame
    # df = pd.read_excel(excel_file, sheet_name=sheet_name)
    
    # Write the DataFrame to the SQLite table
    df.to_sql(table_name, conn, if_exists='replace', index=False)
    
    # Close the connection
    conn.close()

if __name__ == '__main__':
    load_csv_to_sqlite()

csvkit

Use the very versatile csvkit library to load data into the SQLite database.

pip install csvkit

# csvsql --db sqlite:///base_path.db --tables table_name --insert file_to_load.csv
csvsql --db sqlite:///data.db --tables iris --insert iris.csv

sqlite-utils

Use the sqlite-utils python library to load a csv file into a SQLite database

pip install sqlite-utils

# sqlite-utils insert database_file.db table_name csv_file --csv
sqlite-utils insert data.db iris iris.csv --csv

MarquinSmith