Python 快速读取 Excel 内容写入 Mysql

admin

前置准备：

四个文件分别如下：

1. testdata.xls文件

2. 数据库配置信息

3. mysql数据库操作

4. excel读取

1. 数据库配置信息
   
2. 
   
3. PageCount=10
   
4. 
   
5. dbhost="10.10.1.248"
   
6. dbport=3306
   
7. dbuser="root"
   
8. dbpasswd="123456"
   
9. dbname="testdb"

複製代碼

mysqldb.py文件内容

1. # 数据库操作
   
2. 
   
3. import pymysql
   
4. from configs import dbhost,dbpasswd,dbport,dbuser,dbname
   
5. 
   
6. def get_cur():
   
7.     conn = pymysql.connect(host=dbhost, port=dbport, user=dbuser, passwd=dbpasswd, database=dbname)
   
8.     cur = conn.cursor()
   
9.     return cur,conn
   
10. 
    
11. def get_count(sql):
    
12.     cur,conn = get_cur()
    
13.     cur.execute(sql)
    
14.     count = cur.fetchall()
    
15.     cur.close()
    
16.     conn.close()
    
17.     return count[0][0]
    
18. 
    
19. 
    
20. def get_table_colum():
    
21.     cur,conn = get_cur()
    
22.     cur.execute("select * from xxljob_info")
    
23.     col_name_list = [tuple[0] for tuple in cur.description]
    
24.     cur.close()
    
25.     conn.close()
    
26.     return col_name_list
    
27. 
    
28. def get_data(sql1):
    
29.     cur,conn = get_cur()
    
30.     cur.execute(sql1)
    
31.     results = cur.fetchall()
    
32.     cloumn = get_table_colum()
    
33.     res = {}
    
34.     reslist = []
    
35.     for r in range(len(list(results))):
    
36.         for m in range(len(list(cloumn))):
    
37.             res[str(list(cloumn)[m])] = str(list(results)[r][m])
    
38.         reslist.append(res)
    
39.         res = {}
    
40.     cur.close()
    
41.     conn.close()
    
42.     return reslist
    
43. 
    
44. def execute_sql(sql):
    
45.     cur, conn = get_cur()
    
46.     cur.execute(sql)
    
47.     cur.close()
    
48.     conn.commit()
    
49.     conn.close()
    
50. 
    
51. if __name__ == '__main__':
    
52.     get_table_colum()
    
53.     sql = "insert into xxljob_info (executer,describes,router,block,cron,is_repeater,leader,status,mark,create_time) VALUES ('testabc','testabc','第一个','单机串行','testabc','否','testabc','testabc','testabc','2022-06-16 13:14:19')"
    
54. 
    
55.     for i in range(55):
    
56.         execute_sql(sql)
    
57.       # get_count("select count(*) from xxljob_info")

複製代碼

readExcel.py文件内容

1. # 读取excel表数据,并执行sql
   
2. 
   
3. import xlrd
   
4. from mysqldb import execute_sql
   
5. file = xlrd.open_workbook("testdata.xls")
   
6. table1 = file.sheet_by_name("数据表")
   
7. sql = "insert into testdata values "
   
8. 
   
9. for row in range(table1.nrows - 1):
   
10.     list1 = table1.row_values(row + 1)
    
11.     list1 = [row + 1] + list1
    
12.     execute_sql(sql + str(tuple(list1)))

複製代碼

上 4 个文件即可通过 Python 实现 Excel 内容自动读取并入库 Mysql。