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.
I am a software Engineer having 4+ Years of Experience in Building full-stack applications.