Home » Python » Python Programs

How to insert a pandas dataframe to an already existing table in a database?

Given a table in database, we have to insert a pandas dataframe in it. By Pranit Sharma Last updated : September 30, 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

Suppose we have a pre-defined table with columns "Name" and "City", we are going to replace the entire table with our new DataFrame.

Inserting a pandas dataframe to an already existing table in a 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 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.

Table:

Let us understand with the help of an example,

Python program to insert a pandas dataframe to an already existing table in a 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 = {
    'sports':['Football','cricket',
               'basketball','volleyball',
               'rugby','baseball',
               'badminton','hockey'],
    'no_of_people_like':[33,33,29,12,28,28,28,12]
}

# 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:

Updated table:

Python Pandas Programs »