How do you get your CSV data into a SQLite database? Here is an article from Towards data Science. To do this we are going to go through Python. Cool. The article is called Turn Your Excel Workbook Into a SQLite Database Transforming spreadsheets into quarriable database tables.

Below is the text that is in the CSV text file that we will be importing into SQLite. Go ahead and copy the data to your own file.

ind,planet,type,radius_km,moons,distance_AU
0,Mercury,rock,2440,0,0.39
1,Venus,rock,6052,0,0.72
2,Earth,rock,6371,1,1
3,Mars,rock,3390,2,1.52
4,Jupiter,gas,69911,80,5.2
5,Saturn,gas,58232,83,9.54
6,Uranus,ice,25362,27,19.8
7,Neptune,ice,24622,14,30.06

In Python, I have created a project using Anaconda Navigator (which I installed on my computer). Feel free to copy any of this code and adopt it to your own needs. That’s the whole point of my website.

import pandas as pd
import sqlite3
df_planets = pd.read_csv(r'D:\MyData\Research\Datasets\Planets\planets1.csv')
df_planets

Here’s what the output looks like in Jupyter Notebook.

# create a SQLite database file in the specified location
db_conn = sqlite3.connect("D:\MyData\Research\Datasets\Planets\planets1.db")
# i guess I don't need the r in front?
#
c = db_conn.cursor() # create a connection to the database
#
df_planets.columns  # check the column names; need this when we create the table

Here’s the output.

Index(['ind', 'planet', 'type', 'radius_km', 'moons', 'distance_AU'], dtype='object')

Drop (delete) the table tbl_planets if it exists.

# drop the table if it exists.
c.execute(
    """
    DROP TABLE if exists tbl_planets; 
    """
)

Create a table in the new SQLite database.

# create a table in the new database
c.execute(
    """
    CREATE TABLE if not exists tbl_planets (
        ind INTEGER NOT NULL,
        planet TEXT NOT NULL,
        type TEXT NOT NULL,
        radius_km INTEGER NOT NULL,
        moons TEXT NOT NULL,
        distance_AU INTEGER NOT NULL
        );
    """
)  

Now we can insert the data into the table.

# take the dataframe df_planets and insert it into the table tbl_planets
df_planets.to_sql('tbl_planets', db_conn, if_exists='append',index=False)

Now all we need to do is to check that the data went in by running a simple SELECT statement against the table.

pd.read_sql("SELECT * FROM tbl_planets", db_conn)

Here is what it looks like in Jupyter Notebook run from Anaconda Navigator.

I created a Python project called CSV to SQLite Nov 2024 to do this example.

From SQLite to CSV