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

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

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

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

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

 1 #!/usr/bin/env python
 2 # coding: utf-8
 3 # author: [email protected]
 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")