python 利用正则表达式删除在将一个数据库的表复制到另一个数据库出现insert失败删除原数据库数据

最新推荐文章于 2021-02-10 05:15:39 发布

萧瑟1

最新推荐文章于 2021-02-10 05:15:39 发布

阅读量202

点赞数

分类专栏： python 文章标签： python

本文链接：https://blog.csdn.net/qq_41410799/article/details/112555130

版权

python 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

问题描述
博主在做课设的时候需要将一个数据库的表转移到另一个远程数据库中，但是由于两个数据库的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();