Inserting Pandas DataFrames Into Database using INSERT

Published in

Analytics Vidhya

2 min readOct 23, 2019

When working with data in Python, we make use of pandas, and we’ve often got our data stored as a pandas DataFrame. Thankfully, we don’t need to do any conversions if we want to use SQL with our DataFrames; we can directly insert a pandas DataFrame into a MySQL database using INSERT.

Below are steps for the same :

Step 1 : We can here create our own DataFrame using a dictionary. We could also import data from a CSV file or create a DataFrame in any number of other ways, but for the purposes of this example, we’re just going to import the movies data as shown :

import pandas as pd 
movies = pd.read_csv(‘http://bit.ly/imdbratings')
movies.head()

Step 2: Create a table in our MySQL database.

Before inserting data into MySQL, we’re going to to create a movies table in MySQL to hold our data. If such a table already existed, we could skip this step.

Note: A table is created by using the CREATE TABLE statement, followed by table name and then list each column along with its datatype. For eg.

                  CREATE TABLE movies_details(
                               star_rating float(2,1),
                               title varchar2(100),
                               content_rating varchar2(10),
                               ……);

Step 4: Next, we’ll create a column list and insert our dataframe rows one by one into the database by iterating through each row and using INSERT INTO to insert that row’s values into the database.

# creating column list for insertion 
cols = “`,`”.join([str(i) for i in data.columns.tolist()])
# Insert DataFrame records one by one. 
for i,row in data.iterrows():
    sql = “INSERT INTO `movies_details` (`” +cols + “`) VALUES (“ + “%s,”*(len(row)-1) + “%s)” 
    cursor.execute(sql, tuple(row)) 
# the connection is not autocommitted by default, so we must commit to save our # changes 
    connection.commit()

Step 5: Again, let’s query the database to make sure that our inserted data has been saved correctly.

# Execute query 
sql = “SELECT * FROM `movies_details`” 
cursor.execute(sql) # Fetch all the records 
result = cursor.fetchall() 
for i in result: 
    print(i)

Output :

(9.3, ‘The Shawshank Redemption’,’R’, ‘Crime’, 142, ‘[u’Tim Robbins’, u’Morgan Freeman’, u’Bob Gunt…’) (9.2,’The Godfather’, ‘R’,’Crime’,175, ‘[u’Marlon Brando’, u’Al Pacino’, u’James Caan’]’ (9.1,’The Godfather: Part II’ , ‘R’, ‘Crime’,200, ‘[u’Al Pacino’, u’Robert De Niro’, u’Robert Duv…’) 
…….......

Once we’re satisfied that everything looks right, we can close the connection.

connection.close()

In the next article, we will learn about to_sql() method to insert Pandas DataFrames into the MySQL database.

Inserting Pandas DataFrames Into Database using INSERT

Written by Kumar Brar