python - Bagaimana untuk menulis skrip untuk mengosongkan baris tertentu data ujian dalam pangkalan data

Question

Adalah perlu untuk menulis skrip Pangkalan data mempunyai nombor telefon mudah alih untuk ujian Selepas pendaftaran, akan terdapat banyak data yang berkaitan dalam pangkalan data Kemudian skrip perlu mengosongkan data ini atau menetapkannya kepada null . Ini adalah skrip yang serupa.

dari

masa hadapan import print_functionimport itertools
import pymysql.cursors
import csv

daripada utils import is_telephone, is_email

jika

nama == '__main__':sg_connection = pymysql.connect(
host='localhost',
user='root',
password='root',
db='sellergrowth',='charset utf8',
cursorclass=pymysql.cursors.DictCursor
)

uc_connection = pymysql.connect(

host='localhost',

user='root',
password='root',
db='sguc',
charset='utf8',
cursorclass=pymysql.Cursors.
)

def _filter_valid(entiti):

jika bukan entiti['nama pengguna']:

kembali Palsu
jika entiti['telefon'] dan bukan is_telephone(entiti['telefon']):
kembali Palsu
jika entiti['email' ] dan bukan is_email(entity['email']):
return False
if not (
(entiti['telefon'] dan entiti['telephone_verified']) atau (entiti['email'] dan entiti['email_verified' ])):
kembali Salah
kembali Benar

def _map_to_correct(entiti):

def _set_blank_to_none(nama):

jika bukan entiti[nama]:
entiti[nama] = Tiada

_set_blank_to_none('telefon')

_set_blank_to_none('email')

jika entiti['telefon'] dan bukan entiti['telephone_verified']:
entiti['telefon'] = Tiada
jika entiti['e-mel'] dan bukan entiti['email_verified']:
entiti['email'] = Tiada
_set_blank_to_none('password')
_set_blank_to_none('wechat_unionid')
_set_blank_to_none('wechat_app_openid_penid_set)
')
_set_blank_to_none('wechat_site_openid' )
_set_kosong_kepada_tiada('qq_openid')
_set_blank_to_noe('last_login')
_set_blank_to_noe('sign_url')
_set_blank_to_noe('user_uuid')
_set_blank_to_noe('sign_url')
_set_blank_to_none('user_uuid')
_service'd_signup id' ] = 11300
jika entiti['signup_method'] == 1:
entiti['signup_method'] = 'MOBILE'
elif entiti['signup_method'] == 2:
entiti['signup_method'] = 'TABLET'
elif entiti['signup_method'] '] == 3:
entity['signup_method'] = 'WE_CHAT_PUBLIC'
elif entiti['signup_method'] == 4:
entity['signup_method'] = 'WE_CHAT_SITE'
elif entiti['signup_method'] == 12:
entity['signup_method'] = 'WE_CHAT_APP'
elif entiti['signup_method'] == 6:
entiti['signup_method'] = 'QQ_WEB'
elif entiti['signup_method'] == 7:
['signup_method'] = 'QQ_WEBAPP'
lain:
entiti['signup_method'] = Tiada
entiti['is_active'] = 1 jika entiti['is_active'] lain 0
kembali entiti

def _dict_to_tuple(entiti):
return tuple((
entiti['telefon'], entiti['emel'], entiti['username'], entiti['kata laluan'], entiti['wechat_unionid'],
entiti ['wechat_app_openid'],
entiti['wechat_public_openid'], entiti['wechat_site_openid'], entiti['qq_openid'],
int(entiti['signup_service_id']), entiti['date_joined'], entity['last '], entiti['adalah_aktif'],
entiti['kaedah_pendaftaran'], entiti['sign_url'], entiti['user_uuid']
))

cuba:
dengan sg_connection.cursor() sebagai kursor, uc_connection.cursor() sebagai uc_cursor:
sql = 'SELECT is_active, p.telephone, email, username, password, p.wechat_unionid, p.wechat_app_openid, p.wechat_public_openid, p.wechat_site_openid, p.qq_openid, p.signup_service_id, p.telephone_verified, p.email_verified, date_joined, last_login, p.signup_method, e.sign_url, p2.user_uuid FROM account_profile p LEFT JOIN event_baseevent e ON e.id=p.signup_event_id LEFT JOIN account_profile p2 ON p2.user_id=p.inviter_id, auth_user u WHERE p.user_id=u.id'
cursor.execute(sql)
for_record, =cursor_for_cursor .tee(cursor)
cursor = itertools.ifilter(_filter_valid, cursor)
cursor = itertools.imap(_map_to_correct, cursor)
cursor = itertools.imap(_dict_to_tuple, cursor)

sql = 'INSERT into cas_service (service_id, service_group) NILAI (%s, %s)'
service_list = ((11300, 'SG'), (12300, 'SGASK'), (13300, 'YQSXY'), ( 14300, 'CHUANGLAN'))
uc_cursor.executemany(sql, service_list)
uc_connection.commit()

insert_sql = 'INSERT ke dalam cas_user (telefon, e-mel, nama pengguna1, kata laluan1, wechat_unionid, wechat_app_openid, wechat_public_openid, wechat_site_openid, qq_openid, sign_up_service_id, date_joined, last_login_up_device, is ) NILAI (%s, %s, %s , %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
uc_cursor.executemany(insert_sql, list( kursor))
uc_connection.commit()

dengan open('not_migration_report.csv', 'wb') sebagai csvfile:
spamwriter = csv.writer(csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)
_init = False_for = itertools.ifilterfalse(_filter_valid, cursor_for_record)
untuk i dalam cursor_for_record:
jika bukan init:
init = True
print(i.keys())
spamwriter.writerow(
[a.encode("utf8") jika taip("utf8") jika (a) == unicode else a untuk a dalam i.values()])
akhirnya:
sg_connection.close()
uc_connection.close()

Answer 1

Jika anda mempunyai kebenaran untuk mengendalikan jadual, anda hanya perlu menambah bendera untuk mengenal pasti status, seperti bendera = ujian

DELETE * FROM table_name WHERE flag = test