python读取csv指定列数据批量插入到mysql的指定字段中

测试中经常要将线上数据导入到测试环境中测试，此时就需要用到脚本，将数据批量写入到mysql中。有时我们可能只需要导入部分字段，下面就编写了通用脚本，方便不同数据库导入

csv文件字段：

分三步

1、读取Excel

def read_col(file,excel_row_name):
    if excel_row_name:
        col_list = excel_row_name
        df = pd.read_csv(file, usecols=col_list)
    else:
        df = pd.read_csv(file)
    row_list=[]
    length = len(df)
    for i in range(0, length):
        row_value = list(df.loc[i])
        #将list转换为带括号的字符串
        row_value_str=str(tuple([s for s in row_value ]))
        row_list.append(row_value_str)
    row_list_str = ','.join(row_list)
    return row_list_str

2、写入表

# 批量向f分表中插入数据
def insert_db(start_time, end_time,db,table_row_name,row_value, env=''):
    msg = []
    sql_connect = MySql.mysql(db, "ip", port, "username", "password")
    date_list = date_range(start_time, end_time)
    """向table中插入数据"""
     # logging.warning('开始插入数据')
    for statistics_date in date_list:
        table = 'XX_' + statistics_date
        insert_sql = "insert into {}{} values {}".format(
                table,table_row_name, row_value)
        print (insert_sql)
        try:
            sql_connect.operate(insert_sql)
        except pymysql.err.IntegrityError:
                # logging.warning(insert_sql)
                # logging.warning("this data is already exist")
             msg.append("数据已存在")
             return False, msg
        msg.append(table + '表数据插入完毕')
    sql_connect.close()
     # logging.warning('数据插入完毕')
 

3、封装外层方法，传入数据库名称、excel中表头字段，数据库中表头字段

def excel_to_sql(file,excel_row_name,db,table_row_name):
    row_list_str=read_col(file,excel_row_name)
    insert_db('20220911','20220911',db,table_row_name,row_list_str)