How to import CSV file in SQLite database using Python ?

In this article, we’ll learn how to import data from a CSV file and store it in a table in the SQLite database using Python. You can download the CSV file from here which contains sample data on the name and age of a few students.

Contents of the CSV file

Approach:

• Importing necessary modules
• Read data from CSV file DictReader()
• Establish a connection with the database.

sqliteConnection = sqlite3.connect('sql.db')
cursor = sqliteConnection.cursor()

• Create the student table and execute the query using execute() method.
• Inserting data into the table

cursor.executemany("insert into student (name, age) VALUES (?, ?);", student_info)

• Read data from the table
• And close the database.

Below is the implementation:

Python3

import csv
import sqlite3


try:

    # Import csv and extract data
    with open('student_info.csv', 'r') as fin:
        dr = csv.DictReader(fin)
        student_info = [(i['NAME'], i['AGE']) for i in dr]
        print(student_info)

    # Connect to SQLite
    sqliteConnection = sqlite3.connect('sql.db')
    cursor = sqliteConnection.cursor()

    # Create student table
    cursor.execute('create table student(name varchar2(10), age int);')

    # Insert data into table
    cursor.executemany(
        "insert into student (name, age) VALUES (?, ?);", student_info)

    # Show student table
    cursor.execute('select * from student;')

    # View result
    result = cursor.fetchall()
    print(result)

    # Commit work and close connection
    sqliteConnection.commit()
    cursor.close()

except sqlite3.Error as error:
    print('Error occurred - ', error)

finally:
    if sqliteConnection:
        sqliteConnection.close()
        print('SQLite Connection closed')

Output: