本文共 5199 字，大约阅读时间需要 17 分钟。
问题描述
博主在做课设的时候需要将一个数据库的表转移到另一个远程数据库中，但是由于两个数据库的mysql版本不同，出现了Incorrect string value: '\xF0\x9F\xA7\xA0 O...' for column 'game_name' at row 1，由于我不想损失爬取的数据，我想了一个办法，将插入失败的语句调出来，删除原数据表中的数据 我将插入失败语句存入了一个txt文档中
插入不成功语句部分展示INSERT INTO `time_task_tb_game` VALUES (32396, 'Valkeala Software Bundle', '526', '-52%', '254', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/bundles/2952/7d80ea457d2bb1rs/capsule_sm_120.jpg?t=1603194436', '2021-01-08 16:50:00.000000');INSERT INTO `time_task_tb_game` VALUES (32397, 'Vampire: The Masquerade – Bloodlines 2', '163.18', 'no discount', '163.18', 'epic', 'https://cdn1.epicgames.com/nemesia/offer/EGS_VampireTheMasqueradeBloodlines2_HardsuitLabs_S2-1200x1600-29b9f030725c7871fa0c379c269958a6.jpg?h=854&resize=1&w=640', '2021-01-08 16:46:00.000000');INSERT INTO `time_task_tb_game` VALUES (32398, 'Vampyr', '143.59', 'no discount', '143.59', 'epic', 'https://cdn1.epicgames.com/epic/offer/Vampyr_PortraitPromoImage_1280x1420-1280x1420-301b40bd64a8217ef00324d59b1ce28b.jpg?h=854&resize=1&w=640', '2021-01-08 16:46:00.000000');INSERT INTO `time_task_tb_game` VALUES (32399, 'Vector Velocity', '6', '-50%', '3', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/751480/capsule_sm_120.jpg?t=1609434617', '2021-01-08 16:51:00.000000');INSERT INTO `time_task_tb_game` VALUES (32400, 'Victoriana - Steampunk Text Adventure', '15', '-73%', '4', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/1164040/capsule_sm_120.jpg?t=1607944138', '2021-01-08 16:51:00.000000');INSERT INTO `time_task_tb_game` VALUES (32401, 'Vigilantes', '50', '-50%', '25', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/545600/capsule_sm_120.jpg?t=1588229211', '2021-01-08 16:50:00.000000');INSERT INTO `time_task_tb_game` VALUES (32402, 'Villa\'s Blinds', '50', '-30%', '35', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/1333800/capsule_sm_120.jpg?t=1609754082', '2021-01-08 16:51:00.000000');INSERT INTO `time_task_tb_game` VALUES (32403, 'Viviette', '39', '-51%', '19', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/883070/capsule_sm_120.jpg?t=1606327442', '2021-01-08 16:49:00.000000');INSERT INTO `time_task_tb_game` VALUES (32404, 'Vocabulary & Hangul', '42', '-33%', '28', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/bundles/14151/1254ffogtjtvpdb8/capsule_sm_120.jpg?t=1586975955', '2021-01-08 16:51:00.000000');INSERT INTO `time_task_tb_game` VALUES (32405, 'Vocabulary, Hiragana & Katakana', '62', '-39%', '38', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/bundles/12912/js0p1h5fg8m6yliy/capsule_sm_120.jpg?t=1577179396', '2021-01-08 16:50:00.000000');INSERT INTO `time_task_tb_game` VALUES (32406, 'Voxel Bot', '11', '-45%', '6', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/1095370/capsule_sm_120.jpg?t=1609498833', '2021-01-08 16:51:00.000000');INSERT INTO `time_task_tb_game` VALUES (32407, 'W R G bundle', '108', '-56%', '48', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/bundles/7397/ezqg3qh2j2h01218/capsule_sm_120.jpg?t=1528026723', '2021-01-08 16:50:00.000000');INSERT INTO `time_task_tb_game` VALUES (32408, 'WARIUM', '6', '-50%', '3', 'steam', 'https://media.st.dl.pinyuncloud.com/steam/apps/713010/capsule_sm_120.jpg?t=1520071584', '2021-01-08 16:50:00.000000');INSERT INTO `time_task_tb_game` VALUES (32409, 'WHAT THE GOLF?', '65.23', 'no discount', '65.23', 'epic', 'https://cdn1.epicgames.com/epic/offer/EGS_Triband_WHATTHEGOLF_S2-1280x1440-57e28856824bd25450d8c3c108aebc14.jpg?h=854&resize=1&w=640', '2021-01-08 16:46:00.000000');INSERT INTO `time_task_tb_game` VALUES (32410, 'WRC 8 FIA World Rally Championship', '195.83', 'no discount', '195.83', 'epic', 'https://cdn1.epicgames.com/f6dcd5bf17c0469789292d1166bf91a1/offer/CodeRedemption_WRC8-340x440-5d61091860d976273aeefd283524e5d7-340x440-5d61091860d976273aeefd283524e5d7.jpg?h=854&resize=1&w=640', '2021-01-08 16:46:00.000000');INSERT INTO `time_task_tb_game` VALUES (32411, 'WRC 9 FIA World Rally Championship', '228.48', 'no discount', '228.48', 'epic', 'https://cdn1.epicgames.com/9cf250606722448887fa1447b1d9da06/offer/EGS_WRC9FIAWorldRallyChampionship_KTRacing_S2-1200x1600-2e92991d2eb3440be53f4c41a53d1b3b.jpg?h=854&resize=1&w=640', '2021-01-08 16:46:00.000000');INSERT INTO `time_task_tb_game` VALUES [ERR] 1366 - Incorrect string value: '\xF0\x9F\xA7\xA0 O...' for column 'game_name' at row 1
可以看到我只需要把Values后的(到数字到,的字符取出来就可以了，这里我用到了正则表达式
# 存储要删除表中数据的id wrond_data_id = []; # 打开错误文件列表 with open("wrongdate.txt", "r",encoding='utf-8') as f: # 打开文件 data = f.read() # 读取文件 # 正则表达式提取字符 ans = re.findall(r"\([0-9]*,", data); print(len(ans)) for i in range(0,len(ans)): # data_id = re.find(r"[0-9]*",ans[i]); print(ans[i][1:-1]); #加入list wrond_data_id.append(ans[i][1:-1]); # wrond_data_id.append(data_id) # 删除数据表中的数据 CollectDataToMysql.deleteWrongData(wrond_data_id)def deleteWrongData(wrong_id_list): db = connectMysql() cur = db.cursor(); sql = "delete from time_task_tb_game where game_id = %s" data = (wrong_id_list[i] for i in range(0,len(wrong_id_list))) cur.executemany(sql,data); db.commit(); cur.close(); db.close(); 转载地址：http://fjaen.baihongyu.com/