In this post, we will see how to import CSV file data into an SQLite database table using Python.

Now, For solving this requirement we will be using two modules: sqlite3 and pandas. So let’s discuss these first.

sqlite3 module is used to access SQLite databases in Python. SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. It is the most used database engine on the world wide web.

Pandas is an open-source library that is built on top of the NumPy library. It is a Python package that offers various data structures and operations for manipulating numerical data and time series. It is mainly popular for importing and analyzing data much easier. Pandas is fast and it has high performance & productivity for users.

Now, Let’s see the approach to import CSV file data into an SQLite database table using Python.
Approach:

• Import Required Modules.

Syntax: import moduleName

• Make a connection object with the existing database or create a new database and connect it using connect() class of sqlite3 module.

Syntax: sqlite3.connect(‘databaseName.db’)

• Create a cursor object using the cursor() method of a connection object.

Syntax: connection_object.cursor()

• Create a new table by executing create SQL query using execute() method of cursor object.

Syntax: cursor_object.execute("Sql Query")

• Import CSV file using read_csv() method of pandas.

Syntax: pandas.read_csv(‘file_name.csv’)

• Write the contents to a new table using to_sql() method of dataframe.

The function to_sql() creates a new table from records of the dataframe. Pass the table name and connection object inside this function. The column names of the table are the same as the header of the CSV file. By default, the dataframe index is written as a column. Simply toggle the index parameter to False in order to remove this column. Additionally, the if_exists parameter specifies the behavior in case the table name is already being used. It can either raise an error (fail), append new values, or replace the existing table.

Syntax: DataFrame.to_sql(table_name, connection_object, if_exists, index)

• Close the connection using clase() method of connection object.

Syntax: connection_object.close()

Now, let’s look into Python code:

# Import sqlite3 module into
# this program as sq
import sqlite3 as sq

# Import pandas module into
# this program as pd
import pandas as pd
  
# Create a connection object,
# Make a new db if not exist already 
# and connect it, if exist then connect.
connection = sq.connect('information.db')

# Create a cursor object
curs = connection.cursor()

# Run create table sql query
curs.execute("create table if not exists studentInfo" + 
             " (name text, gender text, age integer,course text, branch text)")

# Load CSV data into Pandas DataFrame
student = pd.read_csv('student.csv')

# Write the data to a sqlite db table
student.to_sql('studentInfo', connection, if_exists='replace', index=False)
  
# Run select sql query
curs.execute('select * from studentInfo')

# Fetch all records
# as list of tuples
records = curs.fetchall()

# Display result 
for row in records:
    # show row
    print(row)
    
# Close connection to SQLite database
connection.close()

Output:

You can access the CSV file Here.