Read Data from Excel and insert into SQL database using python

In this tutorial we are discussing how to insert data in database using python. First we need to established connection with Database using pyodbc library. Here we are going to insert data in MS SQL.

install and import pyodbc and pandas library

#install the  library
 !pip install pyodbc
 !pip install pandas

import pyodbc
import pandas as pd

Establish the connection with Database. we required database credentials and server name.

server = 'Server Name'
database = 'Database Name'
username = 'User Name'
password = '#########'
#Connection String

connection = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = connection.cursor()

print("connection Established sucessfully")

above code display the ‘conenction established sucessfully’ message if your connection has successfull with database.

In previous post, we already discuss about how to read data from database. Read data from SQL Server in Python – Data Science (learntodatascience.com) Here, we only focus on how to write data in database from python to SQL.

How to read data from excel and CSV using python : Read And Write Data From Excel And Csv From Python

Read the data from source and perform preprocessing operation before insert into Database if needed. You can follow link Data Preprocessing using python – Data Science (learntodatascience.com) for how to preprocess data using python.

df=pd.read_excel('employee_data.xlsx')
df

Now we are going to insert above data in MS SQL Databse.

for index, row in df.iterrows():
        cursor.execute("INSERT INTO TEST_TABLE (ID,ADDRESS,AGE) values(?,?,?)",row.ID, row.ADDRESS,row.AGE)
        connection.commit()
        num_rows = df.count()[0]
print('Number of Rows inserted DataFrame :',num_rows)

For loop is used to insert data one row at a time. we have a TEST_TABLE table in Database with columns id, address and age. cursor.execute function call the insert statement. commit () function commit the insert operation. each time inserted row counted, and print operation displays the how many data inserted into the database.