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 csv
import sqlite3
connection = sqlite3.connect( 'g4g.db' )
cursor = connection.cursor()
create_table =
cursor.execute(create_table)
file = open ( 'person-records.csv' )
contents = csv.reader( file )
insert_records = "INSERT INTO person (name, age) VALUES(?, ?)"
cursor.executemany(insert_records, contents)
select_all = "SELECT * FROM person"
rows = cursor.execute(select_all).fetchall()
for r in rows:
print (r)
connection.commit()
connection.close()
|
Output:
SQLite:
Last Updated :
28 Oct, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...