In this article, we are going to discuss how to import a CSV file content into an SQLite database table using Python.
Approach:
- At first, we import csv module (to work with csv file) and sqlite3 module (to populate the database table).
- Then we connect to our Lazyroar database using the sqlite3.connect() method.
- At this point, we create a cursor object to handle queries on the database table.
- We first create our person table and create a csv file with the contents inside which we will be inserting into our table.
- We open the above-created csv file using the open() function.
- We extract all the contents of the csv file into our contents variable through csv.reader() method.
- Then we insert our row-wise contents of csv file into our database through executemany() method which will replace (?,?) with the next two comma-separated data of the csv file and insert it as a record into the person table.
- Finally, we verify that the data of the csv file has been successfully inserted into our table with the SELECT statement and commit the changes and close the database connection.
Below is the implementation.
For the purpose of implementation, we will be creating a person table in our Lazyroar.db database. We are going to insert the content of the person_records.csv in our person table. Below is the CSV file we are going to use:
Below is the complete program based on the above approach:
Python3
# Import required modulesimport csvimport sqlite3# Connecting to the Lazyroar databaseconnection = sqlite3.connect('g4g.db')# Creating a cursor object to execute# SQL queries on a database tablecursor = connection.cursor()# Table Definitioncreate_table = '''CREATE TABLE person( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL); '''# Creating the table into our# databasecursor.execute(create_table)# Opening the person-records.csv filefile = open('person-records.csv')# Reading the contents of the# person-records.csv filecontents = csv.reader(file)# SQL query to insert data into the# person tableinsert_records = "INSERT INTO person (name, age) VALUES(?, ?)"# Importing the contents of the file# into our person tablecursor.executemany(insert_records, contents)# SQL query to retrieve all data from# the person table To verify that the# data of the csv file has been successfully# inserted into the tableselect_all = "SELECT * FROM person"rows = cursor.execute(select_all).fetchall()# Output to the console screenfor r in rows: print(r)# Committing the changesconnection.commit()# closing the database connectionconnection.close() |
Output:
SQLite:
