Excel 逐条导入Mysql(数据更新)

其实,我的业务程是, 先读取excel/csv -> pandas 数据清洗 -> 导入Mysql, 一般是做一个表append 或者是 if exist -> replace的操作

逐行来添加数据其实更加灵活和方便.

这里用的驱动是pymysql的一个包, 其实本质就是一个客户端, 服务端已经和mysql进行匹配了, 只是使用client罢了.

还是直接上代码吧, 还是蛮简单的.

1 #!/usr/bin/env python
2 # coding: utf-8
3 # author: chenjieyouge@gmail.com
4
5 '''
6 应用场景:
7 将excel等存储的数据 逐条 导入到 Mysql, 即 拼接sql 语句insert into.
8
9 特点:
10 1. 更适用于有主键约束的的表, 如花名册管理, 直接将新花名册逐条插入, 如有门店重复,则 替换 掉,保持最新.
11 2. to_sql 则更适用用于无主键约束, 大批量导入数据的场景.
12
13 原理:
14 连接: 用Python提供的pymysql驱动,直接拼接SQL去执行
15 数据: 将每条数据进行"insert into 或 replace into 表名 values (), (), (), ()...每个()放一条数据.
16 过程:
17 1. 创建连接对象 con 名字取通俗易懂哈
18 2. 创建游标对象cursor (通俗理解为连接对象的一个小弟, 用来执行sql, 获取数据..)
19 3. 执行sql语句cursor.execute() 或 executemany()....
20 4. 提交命令(事务) con.commit()
21 5. 查询或提取数据 cursor.fetchall()
22 6. 关闭con, cursor
23
24 '''
25
26 import pandas as pd
27 import pymysql
28
29
30 # 配置
31 主机 = "192.168.6.81"
32 用户 = "xxx"
33 密码 = 123456
34 库名 = "new_house"
35
36
37 # 连接
38
39 # 1. 创建连接对象, 游标对象
40 con = pymysql.connect(主机, 用户, 密码, 库名)
41 cursor = con.cursor()
42
43 def 连接测试(cursor):
44 try:
45 # 3. 连接测试: 执行sql, 提交, 查询
46 cursor.execute("show tables;")
47 # 4. 提交
48 con.commit()
49 # 5. 获取数据
50 print(cursor.fetchall())
51 # 6. 关闭连接
52 cursor.close(); con.close()
53 print("连接测试成功!")
54 except Exception as e:
55 print("连接测试失败!", e)
56
57
58 def 处理数据(path, sheet=None):
59 """"后续可以写更复杂的数据处理逻辑"""
60 table = pd.read_excel(path, sheet)
61 # 将缺失值用 None 填充
62 data = table.where(pd.notnull(table), None)
63 return table
64
65
66 def 导入数据(cursor, table, to_table, method="replace"):
67
68 len_cols = table.columns.size # 字段的个数
69
70 # 拼接sql语句: => insert into 表名 (字段1, 字段2 ...字段n) values (a,b,c), (d,e,f)....
71 if method not in ("insert", "replace"):
72 print("input error!")
73
74 insert_sql = "%s into %s values (%s)" % (method,to_table, "%s,"*(len_cols-1) + "%s")
75
76 # 变量每行数据, 组成生成器对象 ( (),(),(),(),(),()... ), 每个元组表示一条记录
77 args = (tuple(row) for _, row in table.iterrows())
78 try:
79 _ = cursor.executemany(insert_sql, args)
80 con.commit()
81 print("successfully!")
82 except Exception as e:
83 print("fail",e)
84 finally:
85 cursor.close()
86 con.close()
87
88
89 if __name__ == '__main__':
90
91 连接测试()
92
93 # table = 处理数据("C:\\Users\\beike\\Desktop\\8月带看明细8.15.xls")
94 #
95 # 导入数据(cursor, table, "数据库有的表", "replace")

耐心和恒心, 总会获得回报的.