解决python存数据库速度太慢的问题

问题

在项目中遇到一个问题,需要从文本中读取三万条数据写入mysql数据库,文件中为用@分割的sql语句,但是在读取的过程中发现速度过慢,三万八千条数据需要220秒,问题代码片段如下:

def read_to_mysql(filecata, targetDir):
 '''
 用来写入数据库,写入后会剪贴掉文件
 filecata 为保存有文件地址的list,已去掉尾部的空格
 :param filecata: 文件目录
 :param targetDir: 要复制的目标目录
 :return:
 '''
 root_dir = os.path.abspath(os.path.join(os.getcwd(), "./"))
 config = configparser.ConfigParser()
 config.read(root_dir + "/config.ini")
 __host = config.get("DatabaseOfWRT", "host")
 __database_name = config.get("DatabaseOfWRT", "database")
 __user_name = config.get("DatabaseOfWRT", "username")
 __user_passwaord = config.get("DatabaseOfWRT", "password")
 __charset = config.get("DatabaseOfWRT", "charset")
 conn = pymysql.connect(
  host=__host,
  user=__user_name, password=__user_passwaord,
  database=__database_name,
  charset=__charset
 )
 cursor = conn.cursor()
 with open(filecata, "r", encoding='utf-8') as f:
  data = f.read() # 读取文件
  data_list = data.split('@')
  del data_list[-1]
  starttime = int(time.time())
  for data_str in data_list:
   data_str = str(data_str)
   sql = data_str + ';'
   cursor.execute(sql)
   conn.commit()
   print(flag)
 copy_del_file(filecata, targetDir) # 用来剪切的函数,此处不影响,因而省略
 cursor.close()
 conn.close()

解决方案

经测试发现,影响速度的主要原因是commit(),因为没过几秒提交一次即可,但是因为提交的字符长度有限制,所以要设置一个合理的时间读取,代码修改如下:

def read_to_mysql(filecata, targetDir):
 '''
 用来写入数据库,写入后会剪贴掉文件
 filecata 为保存有文件地址的list,已去掉尾部的空格
 :param filecata:
 :param targetDir: 要复制的目标目录
 :return:
 '''
 root_dir = os.path.abspath(os.path.join(os.getcwd(), "./"))
 config = configparser.ConfigParser()
 config.read(root_dir + "/config.ini")
 __host = config.get("DatabaseOfWRT", "host")
 __database_name = config.get("DatabaseOfWRT", "database")
 __user_name = config.get("DatabaseOfWRT", "username")
 __user_passwaord = config.get("DatabaseOfWRT", "password")
 __charset = config.get("DatabaseOfWRT", "charset")
 conn = pymysql.connect(
  host=__host,
  user=__user_name, password=__user_passwaord,
  database=__database_name,
  charset=__charset
 )
 cursor = conn.cursor()
 with open(filecata, "r", encoding='utf-8') as f:
  data = f.read() # 读取文件
  data_list = data.split('@')
  del data_list[-1]
  starttime = int(time.time())
  for data_str in data_list:
   endtime = int(time.time())
   data_str = str(data_str)
   sql = data_str + ';'
   cursor.execute(sql)
   if endtime - starttime ==10: # 每过十秒提交一次
    starttime = int(time.time())
    conn.commit()
 conn.commit()
 copy_del_file(filecata, targetDir)
 cursor.close()
 conn.close()
 return flag

此时写入三万八千条数据需要9秒

补充:python 连数据库cursur.fetchall ()速度慢的解决方案

解决游标遍历慢的方法:

一行一行去遍历,而不是一下全部读取出来

将cursur.fetchall()更改为for i in cursur:

补充:python 读取文件时速度的问题

"""举例 读取文件"""
# 第一种方式
with open('test.txt', 'r', encoding='utf-8') as f:
 info = f.readlines()
 for line in info:
 pass
# 第二种方式
with open('test.txt', 'r', encoding='utf-8') as f:
 for line in f:
 pass

对于以上两种方式读取文件,各自有各自的用途,当用两种方式都可以时,第二种方式的效率是第一种的几个量级, readlines()将文件内容读取到内存的list中,操作虽然方便,但是消耗内存,运行效率慢。

原生的f是将文件内容读到生成器中, 当需要操作时,从生成器中循环出来,速度很快,操作大文件时建议用第二种方式!

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。

你可能感兴趣的