python3--DB--优化insert数据时间

如图， 之前插入的时间是207s， 优化后执行时间为58s

主要是之前做的比较粗， 直接插入一条， commit一次；
这样效率太低；
我之前写了一个类， 操作sqlite和mysql；
类如下:

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
import pymysql
import sqlite3

def dict_factory(cursor, row):
  d = {}
  for idx, col in enumerate(cursor.description):
    d[col[0]] = row[idx]
  return d
class MySqlTools:
  def __init__(self, dbtype, **db_conf):
    self.dbtype = dbtype
    self.db_conf = db_conf
    if dbtype == 'mysql':
      db = db_conf.get('db', None)
      passwd = db_conf.get('passwd', None)
      if db == None or passwd == None:
        print('mysql need define db, passwd!')
        sys.exit(1)
      host = db_conf.get('host', 'localhost')
      user = db_conf.get('user', 'root')
      port = db_conf.get('port', 3306)
      charset = db_conf.get('cherset', 'utf8')
      conn = pymysql.connect(host=host,\
             user=user,\
             passwd=passwd,\
             db=db,\
             port=port, \
             charset=charset)
    elif dbtype == 'sqlite':
      db = db_conf.get('db', None)
      if db == None:
        print('sqlite need define db!')
        sys.exit(1)
      conn = sqlite3.connect(db)
      conn.text_factory = str
      conn.row_factory = dict_factory
    self.conn = conn
  def select(self, t_sql):
    if self.dbtype == 'mysql':
      cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
    elif self.dbtype == 'sqlite':
      cur = self.conn.cursor()
    try:
      cur.execute(t_sql)
    except Exception as e:
      print(e)
      return 'fail'
    result_of_sql = cur.fetchall()
    cur.close()
    return result_of_sql
  def exec(self, t_sql):
    if self.dbtype == 'mysql':
      cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
    elif self.dbtype == 'sqlite':
      cur = self.conn.cursor()
    try:
      res = cur.execute(t_sql)
      self.conn.commit()
    except Exception as e:
      self.conn.rollback()
      print(e)
      return 'fail'
    cur.close()
    return ('success %s' % res)
  def insert(self, sql_list, commit=1):
    if self.dbtype == 'mysql':
      cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
    elif self.dbtype == 'sqlite':
      cur = self.conn.cursor()
    while sql_list:
      range_times = commit
      if len(sql_list) < range_times:
        range_times = len(sql_list)
      for i in range(range_times):
        t_sql = sql_list.pop(0)
        try:
          cur.execute(t_sql)
        except Exception as e:
          print(e)
          cur.close()
          return 'fail'
      try:
        self.conn.commit()
      except Exception as e:
        print(e)
        self.conn.rollback()
        cur.close()
        return 'fail'
    cur.close()

  def close(self):
    self.conn.close()

目前把insert 单独写了一个方法， 之前用的是exec方法；
insert方法两个外部参数， sql语句列表和执行多少条再提交；
当然根据测试， 1000条数据一次提交和300条数据一次提交， 速度并无太大差别；
这个还是要多测试才能找到合适的。。
测试实例如下;

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
import yaml
import sys, os, time
from package.mytool import MySqlTools

def init_conf(file_name):
  f = open(file_name, 'r', encoding="utf-8")
  data = yaml.load(f, Loader=yaml.FullLoader)
  f.close()
  return data

def insert_value(table_name, data):
  sql_list = []
  for i in data:
    t_sql = "insert into %s(%s) values(%s);"
    v1, v2 = '', ''
    for j in i:
      if not v1 and not v2:
        v1, v2 = str(j), '"'+str(i[j])+'"'
      else:
        v1, v2 = v1+','+str(j), v2+','+'"'+str(i[j])+'"'
    t_sql = (t_sql % (table_name, v1, v2))
    sql_list.append(t_sql)
  return sql_list

if __name__ == '__main__':
  base_dir = sys.path[0]
  os.chdir(base_dir)
  data = init_conf('config.yml')
  mysql_conf = data.get('mysql', None)
  mysql_conn = MySqlTools('mysql', **mysql_conf)
  sqlite_conn = MySqlTools('sqlite', db='app-info.db')
  '''初始化
  t_sql = "create table if not exists app_info \
          (id INTEGER PRIMARY KEY AUTO_INCREMENT,busniess_name VARCHAR(100),\
          app_name VARCHAR(100),app_cluster VARCHAR(100),\
          app_ip VARCHAR(100),data_src VARCHAR(100),app_nameid VARCHAR(100),app_pgm VARCHAR(100));"
  mysql_conn.exec(t_sql)
  '''
  t_sql = "select busniess_name, app_name, app_cluster, app_ip, data_src, \
          app_nameid, app_pgm from app_info;"
  res = sqlite_conn.select(t_sql)
  sql_list = insert_value('app_info', res)
  start_time = time.time()
  mysql_conn.insert(sql_list, 200)
  end_time = time.time()
  exec_time = end_time - start_time
  print('exec time == > %s \n' % (exec_time))