I just finished a basic Python script for a client that I’d like to share with you. He needed an easy means of moving data back and forth between MySQL and Excel, and sometimes he needed to do a bit of manipulation between along the way. In the past I may have relied solely on VBA for this, but I have found it to be much easier with Python. In this post and the accompanying video, I show just part of the project - importing data from Excel into MySQL via Python. Let’s get started.
Be sure to check out the accompanying video!
Download the dependencies
Assuming you have Python installed (I’m using version 2.7), download and install the xlrd library and MySQLdb module-
Develop the script
Then tailor the following script to fit your needs:
import xlrd import MySQLdb # Open the workbook and define the worksheet book = xlrd.open_workbook("pytest.xls") sheet = book.sheet_by_name("source") # Establish a MySQL connection database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython") # Get the cursor, which is used to traverse the database, line by line cursor = database.cursor() # Create the INSERT INTO sql query query = """INSERT INTO orders (product, customer_type, rep, date, actual, expected, open_opportunities, closed_opportunities, city, state, zip, population, region) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %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): product = sheet.cell(r,).value customer = sheet.cell(r,1).value rep = sheet.cell(r,2).value date = sheet.cell(r,3).value actual = sheet.cell(r,4).value expected = sheet.cell(r,5).value open = sheet.cell(r,6).value closed = sheet.cell(r,7).value city = sheet.cell(r,8).value state = sheet.cell(r,9).value zip = sheet.cell(r,10).value pop = sheet.cell(r,11).value region = sheet.cell(r,12).value # Assign values from each row values = (product, customer, rep, date, actual, expected, open, closed, city, state, zip, pop, region) # 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 "All Done! Bye, for now." print "" columns = str(sheet.ncols) rows = str(sheet.nrows) print "I just imported " %2B columns %2B " columns and " %2B rows %2B " rows to MySQL!"
Hope this is useful. More to come!