Michael Herman

Software Developer

Import Data From Excel Into MySQL Using Python

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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!

Comments