python代码读取excel表并写入mysql数据库实例 

 读取mac桌面text.xlsx文件内容后，将需要到字段信息（name,department,phonenumber,address,officeplace）存入mysql数据库实例，最终记得要关数据库和连接哦！

1 # 读取excel表的内容然后写入数据库
 2 import xlrd,pymysql
 3 # 创建数据库连接
 4 conn = pymysql.connect(host='localhost', user="root", password='nihao12345'
 5                            , database='db1', charset='utf8')
 6 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 7 sql = "insert into parasaga (name,department,phonenumber,address,officeplace) values (%s,%s,%s,%s,%s)"
 8 #打开文件
 9 file = xlrd.open_workbook("/Users/lurj/Desktop/text.xlsx")
10 sheet_1 = file.sheet_by_index(0) #根据sheet页的排序选取sheet
11 row_content = sheet_1.row_values(0) #获取指定行的数据，返回列表，排序自0开始
12 row_number = sheet_1.nrows #获取有数据的最大行数
14 for i in range(2,row_number):
15     name = sheet_1.cell(i,0).value
16     department = sheet_1.cell(i,1).value
17     phonenumber = sheet_1.cell(i,2).value
18     address = sheet_1.cell(i,3).value
19     officeplace = sheet_1.cell(i,4).value
20     values = (name,department,phonenumber,address,officeplace)
21 #执行sql语句插入数据
22     cursor.execute(sql,values)
23     conn.commit()
24 cursor.close()
25 conn.close()

 

posted @ 2020-02-21 10:06  卢掌柜的  阅读(3557)  评论(0)    收藏  举报