Home » Python » Python Programs

Python - How to insert pandas dataframe into database?

Given a Pandas DataFrame, we have to insert it into database. By Pranit Sharma Last updated : September 27, 2023

Pandas is a special tool that allows us to perform complex manipulations of data effectively and efficiently. Inside pandas, we mostly deal with a dataset in the form of DataFrame. DataFrames are 2-dimensional data structures in pandas. DataFrames consist of rows, columns, and data.

A database is an organized and structured collection of data stored in a computer system or any cloud storage. Small databases can be stored on a file system. We usually manage a large amount of data by using four operations, create, read, update, and delete and this is called CRUD management of database management.

There are several software in the market that is used to store and manage a large amount of data. Before storing the DataFrame into the database we need to establish the connection between our working environment and the database in which we will store our data.

Problem statement

Given a Pandas DataFrame, we have to insert it into database.

Inserting pandas dataframe into database

In Python, we have multiple libraries to make the database connection, here we are going to use the sqlalchemy library to establish a connection to the database. We will use the MySql database for this purpose.

Once the connection is established, we will use the pandas.DataFrame.to_sql() method, inside which we will pass the connection to the database as a parameter and we will also the table name of the database.

Let us understand with the help of an example,

Python program to insert pandas dataframe into database

# Importing pandas package
import pandas as pd

# Importing sqlalchemy library
import sqlalchemy

# Setting up the connection to the database
db = sqlalchemy.create_engine('mysql://root:1234@localhost/includehelp')

# Creating dictionary
d = {
    'Name':['Ayush','Ashutosh','Bobby','Bhavya','Chetan','Chinki','Dhruv','Esha'],
    'City':['Surat','Vadodra','Chandigarh','Ahemdabad','Bhopal','Delhi','Mumbai','Mumbai']
}

# Creating DataFrame
df = pd.DataFrame(d)

# Display Original DataFrames
print("Created DataFrame:\n",df,"\n")

# Converting the df to sql
df.to_sql(con=db, name='dataframe_1', if_exists='replace')

# Display a message that data has been inserted
print("Your data has been inserted to sql table")

Output

The output of the above program is:

Records in table after inserting:

Python Pandas Programs »