{"id":345,"date":"2013-11-25T14:39:00","date_gmt":"2013-11-25T19:39:00","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=345"},"modified":"2013-11-25T14:39:00","modified_gmt":"2013-11-25T19:39:00","slug":"import-data-from-excel-into-mysql-using-python","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/11\/25\/import-data-from-excel-into-mysql-using-python\/","title":{"rendered":"Import Data From Excel Into MySQL Using Python"},"content":{"rendered":"<div>\n<p>I just finished a basic Python script for a client that I\u2019d 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 &#8211; importing data from Excel into MySQL via Python. Let\u2019s get started.<\/p>\n<p>Assuming you have Python installed (I\u2019m using version 2.7), download and install the xlrd library and MySQLdb module-<\/p>\n<ul>\n<li><a href=\"http:\/\/pypi.python.org\/pypi\/xlrd\">http:\/\/pypi.python.org\/pypi\/xlrd<\/a><\/li>\n<li><a href=\"http:\/\/sourceforge.net\/projects\/mysql-python\/\">http:\/\/sourceforge.net\/projects\/mysql-python\/<\/a><\/li>\n<\/ul>\n<p>Then tailor the following script to fit your needs:<\/p>\n<figure><figcaption><\/figcaption><div>\n<table>\n<tbody>\n<tr>\n<td>\n<pre><code>import xlrd\nimport MySQLdb\n\n# Open the workbook and define the worksheet\nbook = xlrd.open_workbook(\"pytest.xls\")\nsheet = book.sheet_by_name(\"source\")\n\n# Establish a MySQL connection\ndatabase = MySQLdb.connect (host=\"localhost\", user = \"root\", passwd = \"\", db = \"mysqlPython\")\n\n# Get the cursor, which is used to traverse the database, line by line\ncursor = database.cursor()\n\n# Create the INSERT INTO sql query\nquery = \"\"\"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)\"\"\"\n\n# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers\nfor r in range(1, sheet.nrows):\n      product      = sheet.cell(r,).value\n      customer = sheet.cell(r,1).value\n      rep          = sheet.cell(r,2).value\n      date     = sheet.cell(r,3).value\n      actual       = sheet.cell(r,4).value\n      expected = sheet.cell(r,5).value\n      open        = sheet.cell(r,6).value\n      closed       = sheet.cell(r,7).value\n      city     = sheet.cell(r,8).value\n      state        = sheet.cell(r,9).value\n      zip         = sheet.cell(r,10).value\n      pop          = sheet.cell(r,11).value\n      region   = sheet.cell(r,12).value\n\n      # Assign values from each row\n      values = (product, customer, rep, date, actual, expected, open, closed, city, state, zip, pop, region)\n\n      # Execute sql Query\n      cursor.execute(query, values)\n\n# Close the cursor\ncursor.close()\n\n# Commit the transaction\ndatabase.commit()\n\n# Close the database connection\ndatabase.close()\n\n# Print results\nprint \"\"\nprint \"All Done! Bye, for now.\"\nprint \"\"\ncolumns = str(sheet.ncols)\nrows = str(sheet.nrows)\nprint \"I just imported \" %2B columns %2B \" columns and \" %2B rows %2B \" rows to MySQL!\"\n\nHope this is useful. More to come!\n<\/code><\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/figure>\n<hr \/>\n<div><iframe loading=\"lazy\" src=\"http:\/\/www.youtube.com\/embed\/YLXFEQLCogM\" height=\"240\" width=\"320\"><\/iframe><\/div>\n<\/div>\n<footer>Posted by\u00a0Michael Herman\u00a0<time datetime=\"2012-09-30T13:13:00-06:00\" data-updated=\"true\">Sep 30th, 2012<\/time>\u00a0\u00a0<a href=\"http:\/\/mherman.org\/blog\/categories\/excel\/\">excel<\/a><\/p>\n<\/footer>\n","protected":false},"excerpt":{"rendered":"<p>I just finished a basic Python script for a client that I\u2019d like to share with you. He needed an easy means of moving data&hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-345","post","type-post","status-publish","format-standard","hentry","category-python"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=345"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/345\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=345"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}