How To Import Excel File Into Database Using Python

pip install PyMySQL

pip install xlrd

import xlrd
import MySQLdb

# Open the workbook and define the worksheet
book = xlrd.open_workbook("myfile.xls")
sheet = book.sheet_by_name()

# Establish a MySQL connection
database = MySQLdb.connect (host="localhost", user = "root", passwd = "root", db = "mysql")

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

# Create Table
product_details_table = ("CREATE TABLE IF NOT EXISTS product_details(id int,product_id varchar(255) NOT NULL,product_name text,product_price varchar(255),product_rating BLOB,product_star_rating float,product_url LONGTEXT, PRIMARY KEY (product_id))")

# Execute create table query
cursor.execute(product_details_table)

# Create the INSERT INTO SQL query
query = "INSERT INTO product_details (product_id,product_name,product_price,product_rating,product_star_rating,product_url) VALUES (%s,%s,%s,%s,%s,%s)"

# Create a For loop to iterate through each row in the XLS file

    for r in range(1,sheet.nrows):
        product_id = sheet.cell(r,0).value

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

        product_price = sheet.cell(r,2).value
     
        product_rating = sheet.cell(r,3).value
      
        product_star_rating = sheet.cell(r,4).value
        
        product_url = sheet.cell(r,5).value

# Assign values from each row        
        values = (product_id,product_name,product_price,product_rating,product_star_rating,product_url)

		# Execute sql Query
		cursor.execute(query, values)

# Close the cursor
cursor.close()

# Commit the transaction
database.commit()

# Close the database connection
database.close()

# Print results
print ""
print "Done!"