Read the excel and insert data into My SQL using Python

Install xlrd package

Code-

import xlrd

import mysql.connector

from mysql import connector

# Open the workbook and define the worksheet

book = xlrd.open_workbook(r'C:\Users\xyz\Python_Local\File1.xls')

sheet = book.sheet_by_name("Sheet1")

# Establish a MySQL connection

conn = mysql.connector.connect(host='127.0.0.1',database='firstschema',user='root',password='2511')

# Get the cursor, which is used to traverse the database, line by line

cursor = conn.cursor()

# Create the INSERT INTO sql query

query = """INSERT INTO firstschema.tbl1 (name, mobile, location) VALUES (%s, %s, %s)"""

# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers

for r in range(1, sheet.nrows):

name = sheet.cell(r,0).value

mobile = sheet.cell(r,1).value

location = sheet.cell(r,2).value

# Assign values from each row

values = (name, mobile, location)

# Execute sql Query

cursor.execute(query, values)

# Close the cursor

cursor.close()

# Commit the transaction

conn.commit()

# Close the database connection

conn.close()

# Print results

columns = str(sheet.ncols)

rows = str(sheet.nrows-1)

print("I just imported " + columns + " columns and " + rows + " rows to MySQL!")

Techno Exception