python导出csv文件进mysql

# __*__coding:utf8__*__
import datetime
import pymysql
# 字段分隔符为\x03 行分隔符为\n
# 测试下来拼接后分段执行效率更高，200万数据35秒左右，executemany方式仅仅拼接sql语句进入列表就1分钟多

conn = pymysql.connect(host="192.168.3.100", port=3307, user="root", password="***", db="test1")
start_time = datetime.datetime.now()


def use_executemany():
    print(start_time)
    cursor = conn.cursor()
    val_list = []
    file = open("a.txt", 'r', encoding='utf8')
    lines = file.readlines()
    first_line = lines[0].replace('\n', '').replace('\x03', ',')
    sql_head = 'insert into test(' + first_line + ') values (%s,%s) '
    for i in range(1, len(lines)):
        val_list.append(eval('("' + lines[i].replace('\n', '').replace('\x03', '","')))
        # 此处在往列表中插入元组时需要加eval转换，否组append进列表的不是元组而是字符串
    cursor.executemany(sql_head, val_list)
    val_list.clear()
    file.close()
    cursor.close()
    conn.close()
    end_time = datetime.datetime.now()
    print(end_time - start_time)


def use_concat():
    print(start_time)
    cursor = conn.cursor()
    val_list = []
    file = open("a.txt", 'r', encoding='utf8')
    lines = file.readlines()
    first_line = lines[0].replace('\n', '').replace('\x03', ',')
    sql_head = 'insert into test(' + first_line + ') values '
    val = '("'
    for i in range(2, len(lines)):
        if i % 2000 != 0:
            val = val + lines[i - 1].replace('\n', '').replace('\x03', '","')
        else:
            val = val + lines[i - 1].replace('\n', '').replace('\x03', '","')
            sql_str = sql_head + val.rstrip('\n("')
            cursor.execute(sql_str)
            val = '("'
            sql_str = ''
        val = val + lines[i].replace('\n', '').replace('\x03', '","')
        # 处理最后一段的数据
        sql_str = sql_head + val.rstrip('\n("')
        cursor.execute(sql_str)
        conn.commit()
        file.close()
        cursor.close()
        conn.close()
        end_time = datetime.datetime.now()
        print(end_time - start_time)


if __name__ == '__main__':
    use_concat()
    use_executemany()