python – So schreiben Sie ein Skript, um eine bestimmte Zeile mit Testdaten in der Datenbank zu löschen

Question

Es ist notwendig, ein Skript zu schreiben, das eine Mobiltelefonnummer zum Testen enthält. Nach der Registrierung müssen diese Daten gelöscht oder auf Null gesetzt werden . Dies ist ein ähnliches Skript.

aus

Zukunft print_function importierenitertools importieren
pymysql.cursors importieren
csv importieren

aus den Dienstprogrammen importieren is_telephone, is_email

if

name == '__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.DictCursor
)

def _filter_valid(entity):

wenn nicht entity['username']:
false zurückgeben
wenn entity['telephone'] und nicht is_telephone(entity['telephone']):
false zurückgeben
if entity['email' ] und nicht is_email(entity['email']):
false zurückgeben
wenn nicht (
(entity['telephone'] und entity['telephone_verified']) oder (entity['email'] und entity['email_verified' ])):
falsch zurückgeben
wahr zurückgeben

def _map_to_correct(entity):

def _set_blank_to_none(name):
if not entity[name]:
entity[name] = None

_set_blank_to_none('telephone')

_set_blank_to_none('email')
wenn entity['telephone'] und nicht entity['telephone_verified']:
entity['telephone'] = None
wenn entity['email'] und nicht entity['email_verified']:
entity['email'] = None
_set_blank_to_none('password')
_set_blank_to_none('wechat_unionid')
_set_blank_to_none('wechat_app_openid')
_set_blank_to_none('wechat_public_openid' )
_set_blank_to_none('wechat_site_openid' )
_set_blank_to_none('qq_openid')
_set_blank_to_none('last_login')
_set_blank_to_none('sign_url')
_set_blank_to_none('user_uuid')
wenn nicht. entity['signup_service_id']:
entity['sign up_service_id'] = 11300
if entity['signup_method'] == 1:
entity['signup_method'] = 'MOBILE'
elif entity['signup_method'] == 2:
entity['signup_method'] = 'TABLET'
elif entity['signup_method '] == 3:
entity['signup_method'] = 'WE_CHAT_PUBLIC'
elif-Entity['signup_method'] == 4:
entity['signup_method'] = 'WE_CHAT_SITE'
elif-Entity['signup_method'] == 12:
entity['signup_method'] = 'WE_CHAT_APP'
elif-Entity['signup_method'] == 6:
entity['signup_method'] = 'QQ_WEB'
elif-Entity['signup_method'] == 7:
entity ['signup_method'] = 'QQ_WEBAPP'
else:
entity['signup_method'] = Keine
entity['is_active'] = 1, wenn entity['is_active'] sonst 0
Entität zurückgeben

def _dict_to_tuple(entity):
return tuple((
entity['telefon'], entity['email'], entity['username'], entity['password'], entity['wechat_unionid'],
entity ['wechat_app_openid'],
entity['wechat_public_openid'], entity['wechat_site_openid'], entity['qq_openid'],
int(entity['signup_service_id']), entity['date_joined'], entity['last_login '], Entity['is_active'],
Entity['signup_method'], Entity['sign_url'], Entity['user_uuid']
))

try:
mit sg_connection.cursor() als Cursor, uc_connection.cursor() als 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)
cursor, Cursor_for_record = itertools .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) VALUES (%s, %s)'
service_list = ((11300, 'SG'), (12300, 'SGASK'), (13300, 'YQSXY'), ( 14300, 'CHUANGLAN'))
uc_cursor.executemany(sql, service_list)
uc_connection.commit()

insert_sql = 'INSERT into cas_user (Telefon, E-Mail, Benutzername1, Passwort1, wechat_unionid, wechat_app_openid, wechat_public_openid, wechat_site_openid, qq_openid, sign_up_service_id, date_joined, last_login, is_active, sign_up_device, sg_event_url, sg_inviter_uid) WERTE (%s, %s, %s , %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
uc_cursor.executemany(insert_sql, list( Cursor))
uc_connection.commit()

mit open('not_migration_report.csv', 'wb') als csvfile:
spamwriter = csv.writer(csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)
init = False
cursor_for_record = itertools.ifilterfalse(_filter_valid, Cursor_for_Record)
for i in Cursor_for_Record:
if not init:
init = True
print(i.keys())
spamwriter.writerow(
[a.encode("utf8") if type (a) == Unicode else a für a in i.values()])
finally:
sg_connection.close()
uc_connection.close()

Answer 1

如果对表有操作权限的话 只需要 增加一个标识状态的flag即可 如 flag = test

DELETE * FROM table_name WHERE flag = test