Convert a .csv file into SQLite table in Python

CSV files which stand for ‘Comma Separated Values’ contains values separated by commas. It is a common format for storing data in tables form. It is just like spreadsheet for us. With Python you can convert a csv files into SQLite table using the Python sqlite3 module.

What is a CSV File?

A csv file is a file format having values, each separated by comma. When you are working in Python, it is more suitable for us to work with csv files instead of other file format. This is the best format to store comma separated values, like storing table or storing lists.

Why we use CSV file in Python?

CSV files is used so much frequent that python has a built-in module for reading and writting a csv files. CSV files can store huge amount of data. Large dataset that are used in the process of data analytics and other application are often in the form of CSV format. So it is better you know how to read and write a csv file.

The following a Sample CSV File Contents. Copy these values and put them in a file and name it ‘csvfile.csv’.

Series_reference,Period,Data_value,Suppressed,STATUS,UNITS,Magnitude
BDCQ.SF1AA2CA,2016.06,1116.386,,F,Dollars,6
BDCQ.SF1AA2CA,2016.09,1070.874,,F,Dollars,6
BDCQ.SF1AA2CA,2016.12,1054.408,,F,Dollars,6
BDCQ.SF1AA2CA,2017.03,1010.665,,F,Dollars,6
BDCQ.SF1AA2CA,2017.06,1233.7,,F,Dollars,6
BDCQ.SF1AA2CA,2017.09,1282.436,,F,Dollars,6
BDCQ.SF1AA2CA,2017.12,1290.82,,F,Dollars,6
BDCQ.SF1AA2CA,2018.03,1412.007,,F,Dollars,6
BDCQ.SF1AA2CA,2018.06,1488.055,,F,Dollars,6
BDCQ.SF1AA2CA,2018.09,1497.678,,F,Dollars,6
BDCQ.SF1AA2CA,2018.12,1570.507,,F,Dollars,6
BDCQ.SF1AA2CA,2019.03,1393.749,,F,Dollars,6
BDCQ.SF1AA2CA,2019.06,1517.143,,F,Dollars,6
BDCQ.SF1AA2CA,2019.09,1381.514,,F,Dollars,6
BDCQ.SF1AA2CA,2019.12,1370.985,,F,Dollars,6
BDCQ.SF1AA2CA,2020.03,1073.017,,F,Dollars,6
BDCQ.SF1AA2CA,2020.06,1131.445,,F,Dollars,6
BDCQ.SF1AA2CA,2020.09,1440.101,,F,Dollars,6
BDCQ.SF1AA2CA,2020.12,1489.979,,F,Dollars,6

Reading a CSV file in Python?

I suppose you have the above contents in a ‘csvfile.csv’ file. If you want to read this csv file you can use the python built-in module ‘csv’.To read a csv file in Python use the following steps:

import the csv module
open the file with context manager
create the csv.reader() object
read the rows from the csv.reader()

Following is the python code for reading csv files

import csv

# open the file
with open('csvfile.csv' , 'r') as csvfile:
    # create the object of csv.reader()
    csv_file_reader = csv.reader(csvfile,delimiter=',')
    for row in csv_file_reader:
        print(row)

Output of the above python code:

['Series_reference', 'Period', 'Data_value', 'Suppressed', 'STATUS', 'UNITS', 'Magnitude']
['BDCQ.SF1AA2CA', '2016.06', '1116.386', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2016.09', '1070.874', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2016.12', '1054.408', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2017.03', '1010.665', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2017.06', '1233.7', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2017.09', '1282.436', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2017.12', '1290.82', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2018.03', '1412.007', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2018.06', '1488.055', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2018.09', '1497.678', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2018.12', '1570.507', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2019.03', '1393.749', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2019.06', '1517.143', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2019.09', '1381.514', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2019.12', '1370.985', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2020.03', '1073.017', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2020.06', '1131.445', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2020.09', '1440.101', '', 'F', 'Dollars', '6']
['BDCQ.SF1AA2CA', '2020.12', '1489.979', '', 'F', 'Dollars', '6']

This is the whole data available in the csv file. We just read that file and output these values. Each line has a list in it. Each row is a list type.

Create a Database table in Python

To create a database table in python we need to use the sqlite3 module in python. This module will help us work with SQLite database and execute query.To create a database table in Python follow these steps:

create the database table and with sqlite3.connect()
create the cursor for the database to execute query
create the SQL query for creating the required table
execute the query using the cursor object and execute() method.

Remember we have the ‘csv’ data in a csvfile.csv file. We have 7 columns in this csv file. We need to create a SQLite database table with 7 columns.

To create a query for creating a table with 7 columns we have to use the SQL statements.

SQL statement for creating a table with 7 columns

Table_Query = 'CREATE TABLE csv_data '
'(Series_reference TEXT,Period TEXT,Data_value TEXT,'
'Suppressed TEXT,STATUS TEXT,UNITS TEXT,Magnitude REAL)'

The above SQL command is used for creating a table with 7 columns in it with the specified name.

Inserting CSV data into database Table in Python

Now we have create a table with 7 columns with python sqlite3 module. The next step is to use the insert command and enter the data from the csv file to SQLite database table. We have already learned how to parse csv file and get the values.

We now have to create the insert Statement in SQL and execute this query using the python sqlite3 module.

The Insert statement to enter the cvs data into 7 columns of the database table. Remember we have 7 columns in our csv file as well.

SQL insert statement to insert data into Sqlite Database table.


InsertQuery=f'''INSERT INTO csv_data VALUES 
('{sr,}{period}','{data_value}',
'{suppressed},{status},{units},{magnitude}')'''

Now we are done with the insert command and the table creation command. Now we just need to grab the values from the csv file and insert values to the table.

Insert data from a csv file to sqlite database with Python

The following python program is the composition of what we did so far. To insert the data from a csv file into database table use the following steps.

Read the csv file
parse the csv file
create the database table
use the insert statement to insert the data into SQL table

Below is the python program that insert data from the csv file into a sqlite database using Python.


# importing csv module
import csv
# importing sqlite3 module
import sqlite3

# read the csv file
with open('csvfile.csv' , 'r') as csvfile:
    # create the object of csv.reader()
    csv_file_reader = csv.reader(csvfile,delimiter=',')
    # skip the header 
    next(csv_file_reader,None)
    # create fileds 
    sr =''
    period=''
    data_value=''
    suppressed=''
    status = ''
    units= ''
    magnitude =''
    
    ##### create a database table using sqlite3###
    
    # 1. create query    
    Table_Query = '''CREATE TABLE if not Exists csv_data 
    (Series_reference TEXT,Period TEXT,Data_value TEXT,
    Suppressed TEXT,STATUS TEXT,UNITS TEXT,Magnitude REAL)'''
    
    # 2. create database
    connection=sqlite3.connect('db_csv.db')
    curosr=connection.cursor()
    # 3. execute table query to create table
    curosr.execute(Table_Query)

    # 4. pase csv data
    for row in csv_file_reader:
        # skip the first row
        for i in range(len(row)):
            # assign each field its value
            sr=row[0]
            period=row[1]
            data_value=row[2]
            suppressed=row[3]
            status = row[4]
            units= row[5]
            magnitude = row[6]
        
        # 5. create insert query
        InsertQuery=f"INSERT INTO csv_data VALUES ('{sr}','{period}','{data_value}','{suppressed}','{status}','{units}','{magnitude}')"
        # 6. Execute query
        curosr.execute(InsertQuery)
    # 7. commit changes
    connection.commit()
    # 8. close connection
    connection.close()

A database table with the name csv_data.db was creted with 7 columns have all the data in it after the successful executio fo this program.

This is how your parse csv file and insert it into Database table using Python. If you have any quesiont please let me know in the comment section.

Summary and Conclusion:

Following are the key concepts that we have learned in this article.

How to read csv file with Python?
How to create a database table using python
How to insert csv data into Sqlite database in python

This was it about this article. Read the more articles If you are into Python.

AlixaProDev

I am a software Engineer having 4+ Years of Experience in Building full-stack applications.