Python_tips-批量更新数据表

发表于 | 分类于 | | 阅读量 次

万壑松风知客来，摇扇抚琴待留声

1. 简介

在计算机领域做开发，或多或少你都会涉及到数据的处理，那么和数据库打交道则是稀疏平常的事，区别只是用的多或用的少/用的深或用的浅。

回忆一下，最开始接触数据库是在大二上学期，自学 Python 后出于兴趣开始简单接触 MySQL，之后在下学期有了《数据库原理》这门课，那时候虽然平时有练习，也都只是简单使用，较深的地方学了没用也就忘了，所以总体上也就是基础水平。（简单回忆下想起了好多事~~）目前的工作经常和数据库打交道所以偶尔也会遇到问题，对于这类问题现在就需要认真对待了，写一篇文章做记录。

这里的问题是什么呢？由于我需要处理大量的数据，并将处理后的数据根据条件更新到数据库中，最开始数据少倒没什么影响，后续数据过大导致更新时间过长，有时数据库也会连接失败。

2. 最初处理方式

最容易想到的一种更新方式就是将处理后的数据通过 for 循环逐条更新，当然数据少时这并没有问题。

少量样例数据构建如下：

1
2
3
4
5
6
7
8
9
print(df)

#      a    b  area_a  area_b algo_code atm_code   pred_date
# 0  100  400     500     300     ml_rf     9000  2019-01-01
# 1  200  400     600     200     ml_rf     9001  2019-01-02
# 2  100  500     600     400     ml_rf     9003  2019-01-04
# 3  200  500     700     300     ml_rf     9006  2019-01-07
# 4  300  300     600       0     ml_rf     9000  2019-01-08
# 5  100  300     400     200     ml_rf     9001  2019-01-09

简化过程，可以这样来构建代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
connect = pymysql.connect(user='root', password='', host='', port=3306)
con = connect.cursor()
con.execute('use database')

for i in range(df.shape[0]):
    a = df.ix[i,'a']
    b = df.ix[i,'b']
    algo_code = df.ix[i,'algo_code']
    atm_code = df.ix[i,'atm_code']
    pred_date = df.ix[i,'pred_date']
    update_sql = "update table_name set a={0},b={1} where algo_code={2},atm_code={3},pred_date={4}".format(a,b,algo_code,atm_code,pred_date)
    con.execute(update_sql)
connect.commit()

容易理解的一种数据表更新的方式。

3. 尝试批量更新

上面的方式容易理解也容易实现，但仅限于数据量在一定范围，或时间可接受范围内。其实可以做实验查看只更新一条数据需要耗费多少时间，我这里查看下大概在 0.5s 左右（居然这么长），更新量大岂不是会等到天亮。

由此提高更新效率是当务之急，通过查阅发现 pymysql 库提供批量更新的函数 executemany，通过传入两个参数——更新 sql 和参数元组。通过 executemany 便可以完成批量更新的方式。

简化过程，构建代码和上面相似：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 外部构建sql语句，留出参数位置，循环内将需要更新的参数和条件添加到元组，达到一定数量后批量提交

update_sql = '''update table_name set a = %s,b = %s  where algo_code = %s,atm_code = %s,pred_date = %s;'''
condition = []

for i in range(df.shape[0]):
    a = df.ix[i,'a']
    b = df.ix[i,'b']
    algo_code = df.ix[i,'algo_code']
    atm_code = df.ix[i,'atm_code']
    pred_date = df.ix[i,'pred_date']
    condition.append((a, b, algo_code, atm_code, pred_date))
    if i%6000 == 0:
        con.executemany(update_sql, condition)
        condition = []
# 剩余不足6000条更新
con.executemay(update_sql, condition)
connect.commit()

按理说应该可以了吧，运行程序后让人头疼的是竟然效率没有提高，你这是批量的是什么鬼？

然后去查看了 executemany 函数的源代码，失望的发现虽然是批量提交，但内部还是使用的 execute 函数逐条更新，查表真的耗时间。

4. 批量更新

这时想到了数据表的索引，有了索引更新数据就像翻书一样有了目录。我想设置这个索引，但无奈本人能力有限实现并不容易。观察表结构后发现了，在建表时给了一列自增长的数据，这个值对于每条数据都是唯一的，相当于索引，其实上面的所有 where 条件判断到的位置可以认为就是这个自增长 id，所以换换这个 update_sql 语句再试试。

修改 update_sql 语句，然后再使用 executemany 批量提交更新数据。相对于上面的改变是减少了 where 的判断条件，使用 id 一个条件来确定数据位置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
update_sql = '''update table_name set a = %s,b = %s  where id = %s;'''
condition = []

for i in range(df.shape[0]):
    a = df.ix[i,'a']
    b = df.ix[i,'b']
    id = df.ix[i,'id']
    condition.append((a, b, id))
    if i%6000 == 0:
        con.executemany(update_sql, condition)
        condition = []
# 剩余不足6000条更新
con.executemay(update_sql, condition)
connect.commit()

运行程序，10s 之内就能完成，你会发现时间复杂度下降不止 1000 倍。

具体原因我不能确定，目前猜测有两个：1、where 条件过多会造成查表慢。2、自增长 id 相当于目录索引。这两个条件容易验证（我木有做~~太懒了，没时间）。

5. 结语

到此算是完成了目标，大数据量更新并且效率高。但是里面还有很多可以优化的地方，在后续的使用中我会进行处理，然后更新文章。