python csv 批量导入mysql

不得不说python确实很强大，批量150w数据导入mysql只要192s。机器配置腾讯云轻应用2C4G80GSSD。上代码。

import pymysql
import csv
import codecs
import time


def get_conn():
  conn = pymysql.connect(host='localhost', port=3306, user='zlc', passwd='zlc', db='wechat', charset='utf8mb4')
  return conn
def insert(cur, sql, args):
  cur.executemany(sql, args)



def read_csv_to_mysql(filename):
  try:
    with codecs.open(filename=filename, mode='r', encoding='utf-8') as f:
      reader = csv.reader(f)
      filename=filename.rstrip('.csv')
      firstline=next(reader)
      sql='CREATE TABLE `'+filename+'` ('
      lineId=1
      values=''
      for value in firstline:
        sql=sql+'f'+str(lineId) +' varchar(255) DEFAULT NULL,'
        lineId=lineId+1
        values=values+'%s,'
      sql=sql.rstrip(',')
      values=values.rstrip(',')
      sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
      conn = get_conn()
      cur = conn.cursor()
      cur.execute(sql)
      sql = 'insert into '+filename+' values('+values+')'
      usersvalues=[]
      usersvalues.append(tuple(firstline))
      total=0
      for item in reader:
        if item[1] is None or item[1] == '': # item[1]作为唯一键，不能为null
          continue
        args = tuple(item)
        usersvalues.append(args)
        # print(usersvalues)
        if len(usersvalues)==1000:
          insert(cur, sql=sql, args=usersvalues)
          usersvalues=[]
        total=total+1
      print("插入总数",total)
      insert(cur, sql=sql, args=usersvalues)
      conn.commit()
      
  except Exception as e:
    print("发生错误",e)
    conn.rollback()
  finally:
    cur.close()
    conn.close()


if __name__ == '__main__':
  time_start=time.time()
  read_csv_to_mysql('hb_mx_20191231.csv')
  time_end=time.time()
  print('totally cost',time_end-time_start)