excel读取数据写入mysql

excel------mysql(20万添加一次)

path(文件路径) tablename(数据库表名)

def add_data(path,tablename):
    from datetime import datetime
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mysql', port=3306,
                           db='test')
    cur = conn.cursor()
    with open(path,'r') as f:
        contexts = f.readlines()
    src = ''
    num = 1
    insert_sql = f" insert into {tablename} (src,tgt,create_tm) values "
    for i in contexts:
        # print(i)
        time = datetime.now()
        if num % 2 == 0:
            src = pymysql.escape_string(src)
            tgt = pymysql.escape_string(i.replace("\r\n", ""))
            insert_sql += f"('{src}','{tgt}','{time}'),"
            if num % 200000 == 0:
                insert_sql = insert_sql[0:-1] + ";"
                # print(insert_sql, '00--11')
                try:
                    cur.execute(insert_sql)
                    conn.commit()
                    print('ok............................')
                    insert_sql = f" insert into {tablename} (src,tgt,create_tm) values "
                except Exception as e:
                    print(e, "insert--------------")
                    conn.rollback()
                    conn.close()
                    return 'erro............'
            src = ''
        else:
            print(src)
            src = i.replace("\r\n", "")
        num += 1

if __name__ == '__main__':
    add_data('1m.txt','test')