Inserting Pandas DataFrames Into Database using INSERT

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')  ## Reading a Dataframe

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 3: We can once again create a connection to the database from Python using pymysql(as discussed in previous article).

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                         user='username',
                         password='*****',
                         db='movies')

# create cursor
cursor=connection.cursor()

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 recrds 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.