Python提高更新数据库数据的速率的一种方法

常规更新数据库数据方法

我一开始用的更新数据库数据的方式是使用UPDATE更新，这段代码如下：

for i in ids:
	sql = "UPDATE " + tables + " SET " + tables + " =%s where id =%s" % (values[i],i)
    cursor.execute(sql)
#提交到数据库执行
db.commit()

更新速率如下（以更新8192个数据为例）：

更新速率极其慢，一开始居然用了198s，对于需要绘制动态图的我来讲，这更新速度远远不够，于是我就开始不断尝试其他更新方法，经过好几天的探索，终于找到了新的方法。

提高数据库更新速率的方法

最终我选择了通过INSERT的方式更新数据库数据，直接上代码：

#SQL插入语句
sql = "DELETE " + tables + " FROM " + tables
cursor.execute(sql)
sql = "insert into " + tables + " values(%s,%s)"
cursor.executemany(sql, [(values[i],i) for i in ids ])
#提交到数据库执行
db.commit()

更新速率如下（以更新8192个数据为例）：

第二种方式相比于第一种方式，速率得到大幅的提升，0.14s VS 198s。
下面给出常规方法的完整代码：

import pymysql
import numpy as np
import time
import random
import data_updata
import threading 
#打开数据库连接
#---------数据库中的频谱数据先插入8192个数据，以防更新无数据导致失败-------
db = pymysql.connect(
    host = 'localhost',
    user = 'root',  password = '',
    database = 'speech',
    charset = "utf8")
#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#清空数据库中频谱数据
sql = "set GLOBAL max_connections=32000"
cursor.execute(sql.encode('utf8'))
sql = "DELETE freqplot_data1 FROM freqplot_data1"
cursor.execute(sql.encode('utf8'))
print('数据库中频谱数据已清空')
#插入8192个索引
for i in range(8192):
    sql = "INSERT INTO freqplot_data1(id) VALUES(%s)"%(i)
    cursor.execute(sql.encode('utf8'))
print('数据库中频谱数据索引已建立')
db.commit()
db.close()
#----------------------------------------------------------------------------------------
def data_updata(tables, values, ids):
    db = pymysql.connect(
        host = 'localhost',
        user = 'root',  password = '',
        database = 'speech',
        charset = "utf8")
    cursor = db.cursor()
    #SQL插入语句
    #sql = "DELETE " + tables + " FROM " + tables
    #cursor.execute(sql)
    for i in ids:
        sql = "UPDATE " + tables + " SET " + tables + " =%s where id =%s" % (values[i],i)
        cursor.execute(sql)
        #提交到数据库执行
    db.commit()
    cursor.close()
    db.close()
def main():
    x = []
    for i in range(8192):
        l = random.uniform(88.5,108.5)
        l = round(l,1)
        x.append(l)
    data_updata('freqplot_data1', x, range(8192))
while 1:
    start_time = time.time()
    main()
    end_time = time.time()
    print(end_time-start_time)
    print('-------')

提高后的完整代码：

import pymysql
import numpy as np
import time
import random
import data_updata
import threading 
#打开数据库连接
#---------数据库中的频谱数据先插入8192个数据，以防更新无数据导致失败-------
db = pymysql.connect(
    host = 'localhost',
    user = 'root',  password = '',
    database = 'speech',
    charset = "utf8")
#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#清空数据库中频谱数据
sql = "set GLOBAL max_connections=32000"
cursor.execute(sql.encode('utf8'))
sql = "DELETE freqplot_data1 FROM freqplot_data1"
cursor.execute(sql.encode('utf8'))
print('数据库中频谱数据已清空')
#插入8192个索引
for i in range(8192):
    sql = "INSERT INTO freqplot_data1(id) VALUES(%s)"%(i)
    cursor.execute(sql.encode('utf8'))
print('数据库中频谱数据索引已建立')
db.commit()
db.close()
#----------------------------------------------------------------------------------------
def data_updata(tables, values, ids):
    db = pymysql.connect(
        host = 'localhost',
        user = 'root',  password = '',
        database = 'speech',
        charset = "utf8")
    cursor = db.cursor()
    #SQL插入语句
    sql = "DELETE " + tables + " FROM " + tables
    cursor.execute(sql)
    sql = "insert into " + tables + " values(%s,%s)"
    cursor.executemany(sql, [(values[i],i) for i in ids ])
        #提交到数据库执行
    db.commit()
    cursor.close()
    db.close()
def main():
    x = []
    for i in range(8192):
        l = random.uniform(88.5,108.5)
        l = round(l,1)
        x.append(l)
    print(x[3])
    try:
        data_updata('freqplot_data1', x, range(8192))
    except:
        print('数据上传失败')
while 1:
    start_time = time.time()
    main()
    end_time = time.time()
    print(end_time-start_time)
    print('-------')