How to Import a CSV file into a SQLite database Table using Python?

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 geeks 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 geeks.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 modules
import csv
import sqlite3

# Connecting to the geeks database
connection = sqlite3.connect('g4g.db')

# Creating a cursor object to execute
# SQL queries on a database table
cursor = connection.cursor()

# Table Definition
create_table = '''CREATE TABLE person(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER NOT NULL);
                '''

# Creating the table into our
# database
cursor.execute(create_table)

# Opening the person-records.csv file
file = open('person-records.csv')

# Reading the contents of the
# person-records.csv file
contents = csv.reader(file)

# SQL query to insert data into the
# person table
insert_records = "INSERT INTO person (name, age) VALUES(?, ?)"

# Importing the contents of the file
# into our person table
cursor.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 table
select_all = "SELECT * FROM person"
rows = cursor.execute(select_all).fetchall()

# Output to the console screen
for r in rows:
    print(r)

# Committing the changes
connection.commit()

# closing the database connection
connection.close()

Output:

SQLite: