Python3 多线程(连接池)操作MySQL插入数据

# coding:utf-8
import re,os,sys
import  datetime
import uuid
import pymysql
from queue import Queue
from DBUtils.PooledDB import PooledDB

import threading

import time



def mysql_connection():
    maxconnections = 15  # 最大连接数
    pool = PooledDB(
        pymysql,
        maxconnections,
        host='ip',
        user='root',
        port=3306,
        passwd='123456',
        db='xiancheng')
    return pool

def mysql_insert(*args):
    #print(args)
    con = mysql_connection().connection()
    cur = con.cursor()

    POINT_ID = uuid.uuid4().hex
    DRP = 1.0
    now_str = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    PUBLISH_TIME = datetime.datetime.strptime(now_str, '%Y-%m-%d %H:%M:%S')
    FORECAST_TIME = args[0]
    TYPE = args[1]
    LGTD = args[2][0]
    LTTD = args[2][1]
    sql = "INSERT INTO hw_table(point_id,drp,publish_time,forecast_time,lgtd,lttd,typed) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (POINT_ID,DRP,PUBLISH_TIME,FORECAST_TIME,LGTD,LTTD,TYPE)
    # print(sql)

    try:
        cur.execute(sql)

        con.commit()
    except Exception as e:
        con.rollback()  # 事务回滚
        print('SQL执行有误,原因:', e)
    finally:
        cur.close()
        con.close()

def task(FORECAST_TIME,TYPE, data):
    q = Queue(maxsize=10)  # 设定最大队列数和线程数
    st = time.time()
    while data:
        content = data.pop()
        t = threading.Thread(target=mysql_insert, args=(FORECAST_TIME,TYPE, content))
        q.put(t)
        if (q.full() == True) or (len(data)) == 0:
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()
    print("数据插入完成.==>> 耗时:{}'s".format(round(time.time() - st, 3)))

def main():
    '1.读取所有文件数据'
    path = './whi_22110418.000'
    FORECAST_TIME,TYPE,xy_rain_list = extractData(path)
    print(FORECAST_TIME)
    print(TYPE)
    print(len(xy_rain_list))
    data = xy_rain_list

    # 1. 使用队列和多线程
    task(FORECAST_TIME,TYPE, data)

    # 2. 单纯插入数据
    # 就是将
    # insertDataToOracle(FORECAST_TIME, TYPE, xy_rain_list)




if __name__ == '__main__':
    main()

总结：

将单纯的依次插入数据改成，多线程和队列结合的插入方式，发现效率更是地下，也许是插入的方式的改变，我将多插入改成单插入，如果只是这个原因，那么我们仅仅需要的是多插入和多线程的方式，自已可以根据更改，没必要完全按照原文操作。

参考文章