同时操作多个表_nodejs 同时操作两张表-CSDN博客

本文链接：https://blog.csdn.net/CoolScript/article/details/88174419

某次数据处理时候，需要把一张大表切分到多个小表中。

import pymysql

db = pymysql.connect("xx.xx.xx.xx", "root", "root", "backup3")
cursor = db.cursor()


#　对原来存在的很多小表批量操作
db_name = 'backup3'
sql_get_all_tables = f"select table_name from information_schema.tables where table_schema = '{db_name}'"
cursor.execute(sql_get_all_tables)
tables_list = [i[0] for i in cursor.fetchall()]

for table in tables_list:
    # 删掉无用数据

    # sql = f"delete from  {table} WHERE (email not LIKE '%@%' and phone not REGEXP '^1[3456789][0-9]{{9}}$');"
    # sql = f"update {table} set email='0' WHERE email not LIKE '%@%';"
    # sql = f"update {table} set phone='0' WHERE phone not REGEXP '^1[3456789][0-9]{{9}}$';"
    #sql = f"delete from {table} where(phone='0' and email='0')"
    # sql = f"DELETE FROM {table} WHERE id NOT IN ( SELECT temp.min_id FROM ( SELECT MIN(id) min_id FROM {table} GROUP BY phone,email )AS temp )"
    # 联合约束
    # sql = f"alter table {table} add unique(phone,email)"
    # sql = f"alter table {table} drop column id"
    sql = f"ALTER TABLE {table} ADD id INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST"
    cursor.execute(sql)
    db.commit()
#

# ===========================================================================================


#　把清洗过的一张大表的数据 插入到 很多表中
# cursor.execute('select distinct city_id from all_temp')
# city_id_list = [i[0] for i in cursor.fetchall()]

# for city_id in city_id_list:
#     if city_id != 0:
#         try:
#             # sql = f'INSERT INTO f_{city_id}(`name`,) SELECT * FROM all_temp where city_id={city_id}'
#             sql = f"insert ignore INTO f_{city_id}(`name`,birthday,address,phone,email,age,province,province_id,city,city_id,area,area_id) SELECT `name`,birthday,address,phone,email,age,province,province_id,city,city_id,area,area_id from all_temp where all_temp.city_id={city_id}"
#             cursor.execute(sql)
#             db.commit()
#         except pymysql.err.ProgrammingError as e:
#             with open('log.log', 'a+', encoding='utf8') as ff:
#                 ff.write(str(e))
#                 ff.write('\n')
db.close()