当遇到nan值无法写入mysql数据库,如果加上
df[pd.isna(df)]=None
又碰到"Cannot do inplace boolean setting on mixed-types with a non np.nan value"。可以乖乖认怂。认怂方式之一:
def importExcelToMysql(cur, objt):
query = """REPLACE INTO origContactInfo (deviceRecordKey, fax, phone, email, importTime, updateTime) VALUES (%s, %s, %s, %s, %s, %s)"""
for parent, dirnames, filenames in os.walk(objt.path_url):
for filename in filenames:
print(filename)
excel_path = os.path.join(objt.path_url, filename)
# 读取excel文件
workbook = read_excel_with_file_name(excel_path)
worksheet = workbook[objt.contact_sheet_name]
row_count = len(worksheet)
# 将表中每一行数据读到sqlstr数组中
for i in range(1, row_count):
current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
valuestr = worksheet.loc[i].values.tolist()
#########################认怂大法在此###################################
for find_nan in range(0,len(valuestr)):
if pd.isna(valuestr[find_nan]):
valuestr[find_nan]=""
######################################################################
valuestr.append(current_time)
valuestr.append(current_time)
valuestr = tuple(valuestr)
# 将每行数据存到数据库中
cur.execute(query, valuestr)
print(filename + " finished writing to DB!")