Writing Excel Data to MySQL using Pandas

admin

Install packages
As we are going to work with an excel using pandas and mysql database, first we need to install the required packages.

1. pip install pandas
   
2. pip install openpyxl
   
3. pip install sqlalchemy
   
4. pip install pymysql

複製代碼

openpyxl is required to work with .xlsx files. sqlalchemy and pymysql is required for connecting with mysql.

Be ready with a sample .xlsx file
I have created a sample employee file 'employee.xlsx' having employee's first name, last name, age and profession.

Make a connection to your mysql
You should have your MySQL database ready. create_engine() lets you create an engine instance, basically a starting point of sqlalchemy application. You have to pass here a URL string that indicates database dialect and connection parameters. Replace username, password, host, port, database with your corresponding values.

1. from sqlalchemy import create_engineengine = create_engine("mysql+pymysql://" + "username" + ":" + "password" + "@" + "host" + ":" + "port" + "/" + "database" + "?" + "charset=utf8mb4")

複製代碼

After creating the instance, now you should establish a connection to the database using connect().

1. conn = engine.connect()

複製代碼

Now that we have our connection established, it's time to work a little with Pandas.

Reading an excel file using Pandas
First create an excel file obj using pandas ExcelFile() and pass your filename inside.

1. import pandas as pdexcel_file = pd.ExcelFile('employee.xlsx')

複製代碼

After creating the excel file obj, now it's time to parse the excel sheet using parse() function. Pass the sheet name that you want to process.

1. excel_dataframe = excel_file.parse(sheet_name="employee_data")

複製代碼

And yes! The data has been copied to a dataframe.
Let's move on to the last step where we load this into our mysql table.

Load the data into mysql
To load our excel_dataframe to mysql, we use to_sql() with table name and sql connection arguments. Here I have given table name as employee_data. The parameter if_exists checks if the table already exists in the database. If yes, in this case it will append the data.

1. excel_dataframe.to_sql("employee_data", conn, if_exists="append")

複製代碼

Don't worry if you don't have the table already. A table gets created with the name that you pass into to_sql().

Now go and query for the table results. You will see the data inserted.

method2 script

1. import xlrd
   
2. import MySQLdb
   
3. 
   
4. # Open the workbook and define the worksheet
   
5. book = xlrd.open_workbook("pytest.xls")
   
6. sheet = book.sheet_by_name("source")
   
7. 
   
8. # Establish a MySQL connection
   
9. database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython")
   
10. 
    
11. # Get the cursor, which is used to traverse the database, line by line
    
12. cursor = database.cursor()
    
13. 
    
14. # Create the INSERT INTO sql query
    
15. 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)"""
    
16. 
    
17. # Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
    
18. for r in range(1, sheet.nrows):
    
19.                 product               = sheet.cell(r,).value
    
20.                 customer             = sheet.cell(r,1).value
    
21.                 rep                     = sheet.cell(r,2).value
    
22.                 date                   = sheet.cell(r,3).value
    
23.                 actual                 = sheet.cell(r,4).value
    
24.                 expected            = sheet.cell(r,5).value
    
25.                 open                  = sheet.cell(r,6).value
    
26.                 closed                = sheet.cell(r,7).value
    
27.                 city                   = sheet.cell(r,8).value
    
28.                 state                 = sheet.cell(r,9).value
    
29.                 zip                    = sheet.cell(r,10).value
    
30.                 pop                  = sheet.cell(r,11).value
    
31.                 region              = sheet.cell(r,12).value
    
32. 
    
33.                 # Assign values from each row
    
34.                 values = (product, customer, rep, date, actual, expected, open, closed, city, state, zip, pop, region)
    
35. 
    
36.                 # Execute sql Query
    
37.                 cursor.execute(query, values)
    
38. 
    
39. # Close the cursor
    
40. cursor.close()
    
41. 
    
42. # Commit the transaction
    
43. database.commit()
    
44. 
    
45. # Close the database connection
    
46. database.close()
    
47. 
    
48. # Print results
    
49. print ""
    
50. print "All Done! Bye, for now."
    
51. print ""
    
52. columns = str(sheet.ncols)
    
53. rows = str(sheet.nrows)
    
54. print "I just imported " %2B columns %2B " columns and " %2B rows %2B " rows to MySQL!"

複製代碼

method3

1. import pymysql
   
2. import pandas
   
3. file = r'D:\Python\finreport\tablebasicinfo.xlsx'    ## 文件
   
4. df = pandas.read_excel(file) ## 读文件
   
5. print(df)
   
6. infodata = pandas.DataFrame(df).values
   
7. db = pymysql.connect(host='localhost',
   
8.                      user='root',
   
9.                      password='***',
   
10.                      port=3307,
    
11.                      database='reportdb')
    
12. cursor = db.cursor()
    
13. for i in range(0, len(infodata)):  #
    
14.     data_each = []
    
15.     data_each = infodata[i]
    
16.     print(data_each)
    
17.     sql = "insert into table_basic_info values {}".format(tuple(data_each))
    
18.     try:
    
19.         cursor.execute(sql)
    
20.         db.commit()
    
21.         print(i)
    
22.     except:
    
23.         db.rollback()
    
24. 
    
25. db.close  # 关闭数据库连接

複製代碼