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.
Submitted by Pranit Sharma, on September 15, 2022

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.

Inserting a pandas dataframe to an already existing table in a database

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.

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.

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

Table:

Insert a dataframe to a table (Table 1)

Let us understand with the help of an example,

Python code 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:

Insert a dataframe to a table (Program Output)

Updated table:

Insert a dataframe to a table (Table 2)

Python Pandas Programs »



ADVERTISEMENT
ADVERTISEMENT




Comments and Discussions!




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.