如何用Python从SQL文件提取email和password并按指定格式输出

现有一段SQL INSERT语句：

INSERT INTO `users` VALUES (176447, 'Miguel', 'Lucas', 'MLUCAS3', 'lukszmb2011@hotmail.com', 'lukszmb2011@hotmail.com', '{"_token":"3P8y4r34uPPRMFPJZl8Axq2WgKNHJfPZdMw79PRV","payable_vouchers":[null],"user_leg":"default","payment":"free","sponsor":"611d7018d607b","firstname":"Miguel","lastname":"Lucas","country":"EC","state":"P","zip":"170102","address":"Caupicho Bellavista calle EI8 lote 1458","city":"Quito","gender":"m","phone":"0989267630","email":"lukszmb2011@hotmail.com","wechat":null,"passport":"1723806822","transaction_pass":"5BxLkKhYPLF","username":"MLUCAS3","password":"MLucas3#","confirm_password":"MLucas3#","check":null,"reg_type":null,"cpf":null,"package":15,"location":null,"reg_by":"Free","leg":"default","google2fa_secret":"5EWTTF3LVQ7CI37V"}', '$2y$10$K1eAyJRgYmH5otQtdE6dU.W7NDtiAag6jwBKliWS.CORd..7UgTjK', 'default', '613166ea33f0a', 'L', 'eyJpdiI6IllBRjB0QXQrQUlTekVyKzdaSC9zOUE9PSIsInZhbHVlIjoiNmFNTGN3T2ljZmFSM3phU0ZNRXBLUT09IiwibWFjIjoiMzY0NTg5NDI0MzQxMTk3YTZhNjMxZDI4YmM0N2IzYjE3NTJhMGY0NTAyMjdiNzQ0MjBjMTkwMzYxNGM3NzIzNiJ9', '5BxLkKhYPLF', 1, 1, 'Free', 'yes', 'yes', '0', NULL, NULL, 0, 0, 0, 0, 0, 12, 0, NULL, 'eyJpdiI6IlYyVjVvVm16TjlaRGVRZHdhWllsVWc9PSIsInZhbHVlIjoidVlQY1luQnBDSzNxN25LVC82bFZpRXgySEVDS1dyalFKWEQ2ZjFiVlVSaz0iLCJtYWMiOiJkZjU2M2EzN2NjZGZlNDljNjg4MzY0ODEwZTY2MTY1ZTE2NzBmMDNiZTIzOTFhNzRlMThlNDNlMWNjNTAxMGI3In0=', '', 0, 1, 0, 0, NULL, '2021-09-03 00:06:02', '2021-09-03 02:35:19', NULL, NULL, 0, '0');

需要提取语句中JSON字段里的"email":"lukszmb2011@hotmail.com"和"password":"MLucas3#"内容，并按email:password的格式输出。

方法1：正则表达式直接匹配 #

适合快速提取，无需完整解析JSON的场景：

import re

sql_statement = """INSERT INTO `users` VALUES (176447, 'Miguel', 'Lucas', 'MLUCAS3', 'lukszmb2011@hotmail.com', 'lukszmb2011@hotmail.com', '{"_token":"3P8y4r34uPPRMFPJZl8Axq2WgKNHJfPZdMw79PRV","payable_vouchers":[null],"user_leg":"default","payment":"free","sponsor":"611d7018d607b","firstname":"Miguel","lastname":"Lucas","country":"EC","state":"P","zip":"170102","address":"Caupicho Bellavista calle EI8 lote 1458","city":"Quito","gender":"m","phone":"0989267630","email":"lukszmb2011@hotmail.com","wechat":null,"passport":"1723806822","transaction_pass":"5BxLkKhYPLF","username":"MLUCAS3","password":"MLucas3#","confirm_password":"MLucas3#","check":null,"reg_type":null,"cpf":null,"package":15,"location":null,"reg_by":"Free","leg":"default","google2fa_secret":"5EWTTF3LVQ7CI37V"}', '$2y$10$K1eAyJRgYmH5otQtdE6dU.W7NDtiAag6jwBKliWS.CORd..7UgTjK', 'default', '613166ea33f0a', 'L', 'eyJpdiI6IllBRjB0QXQrQUlTekVyKzdaSC9zOUE9PSIsInZhbHVlIjoiNmFNTGN3T2ljZmFSM3phU0ZNRXBLUT09IiwibWFjIjoiMzY0NTg5NDI0MzQxMTk3YTZhNjMxZDI4YmM0N2IzYjE3NTJhMGY0NTAyMjdiNzQ0MjBjMTkwMzYxNGM3NzIzNiJ9', '5BxLkKhYPLF', 1, 1, 'Free', 'yes', 'yes', '0', NULL, NULL, 0, 0, 0, 0, 0, 12, 0, NULL, 'eyJpdiI6IlYyVjVvVm16TjlaRGVRZHdhWllsVWc9PSIsInZhbHVlIjoidVlQY1luQnBDSzNxN25LVC82bFZpRXgySEVDS1dyalFKWEQ2ZjFiVlVSaz0iLCJtYWMiOiJkZjU2M2EzN2NjZGZlNDljNjg4MzY0ODEwZTY2MTY1ZTE2NzBmMDNiZTIzOTFhNzRlMThlNDNlMWNjNTAxMGI3In0=', '', 0, 1, 0, 0, NULL, '2021-09-03 00:06:02', '2021-09-03 02:35:19', NULL, NULL, 0, '0');"""

# 匹配目标字段
email_match = re.search(r'"email":"([^&]+)"', sql_statement)
password_match = re.search(r'"password":"([^&]+)"', sql_statement)

if email_match and password_match:
    print(f"{email_match.group(1)}:{password_match.group(1)}")
else:
    print("未找到目标字段")

运行输出：

lukszmb2011@hotmail.com:MLucas3#

方法2：解析JSON字段（更严谨） #

针对目标字段在转义JSON字符串中的场景，先还原JSON再解析：

import re
import json

sql_statement = """INSERT INTO `users` VALUES (176447, 'Miguel', 'Lucas', 'MLUCAS3', 'lukszmb2011@hotmail.com', 'lukszmb2011@hotmail.com', '{"_token":"3P8y4r34uPPRMFPJZl8Axq2WgKNHJfPZdMw79PRV","payable_vouchers":[null],"user_leg":"default","payment":"free","sponsor":"611d7018d607b","firstname":"Miguel","lastname":"Lucas","country":"EC","state":"P","zip":"170102","address":"Caupicho Bellavista calle EI8 lote 1458","city":"Quito","gender":"m","phone":"0989267630","email":"lukszmb2011@hotmail.com","wechat":null,"passport":"1723806822","transaction_pass":"5BxLkKhYPLF","username":"MLUCAS3","password":"MLucas3#","confirm_password":"MLucas3#","check":null,"reg_type":null,"cpf":null,"package":15,"location":null,"reg_by":"Free","leg":"default","google2fa_secret":"5EWTTF3LVQ7CI37V"}', '$2y$10$K1eAyJRgYmH5otQtdE6dU.W7NDtiAag6jwBKliWS.CORd..7UgTjK', 'default', '613166ea33f0a', 'L', 'eyJpdiI6IllBRjB0QXQrQUlTekVyKzdaSC9zOUE9PSIsInZhbHVlIjoiNmFNTGN3T2ljZmFSM3phU0ZNRXBLUT09IiwibWFjIjoiMzY0NTg5NDI0MzQxMTk3YTZhNjMxZDI4YmM0N2IzYjE3NTJhMGY0NTAyMjdiNzQ0MjBjMTkwMzYxNGM3NzIzNiJ9', '5BxLkKhYPLF', 1, 1, 'Free', 'yes', 'yes', '0', NULL, NULL, 0, 0, 0, 0, 0, 12, 0, NULL, 'eyJpdiI6IlYyVjVvVm16TjlaRGVRZHdhWllsVWc9PSIsInZhbHVlIjoidVlQY1luQnBDSzNxN25LVC82bFZpRXgySEVDS1dyalFKWEQ2ZjFiVlVSaz0iLCJtYWMiOiJkZjU2M2EzN2NjZGZlNDljNjg4MzY0ODEwZTY2MTY1ZTE2NzBmMDNiZTIzOTFhNzRlMThlNDNlMWNjNTAxMGI3In0=', '', 0, 1, 0, 0, NULL, '2021-09-03 00:06:02', '2021-09-03 02:35:19', NULL, NULL, 0, '0');"""

# 提取转义后的JSON字符串
json_match = re.search(r'\'({".*?})\'', sql_statement)
if json_match:
    # 还原HTML转义字符
    raw_json = json_match.group(1).replace('"', '"')
    user_data = json.loads(raw_json)
    # 提取目标字段
    email = user_data.get('email')
    password = user_data.get('password')
    if email and password:
        print(f"{email}:{password}")
    else:
        print("JSON中未找到目标字段")
else:
    print("未找到JSON字段")

运行输出同样为：

lukszmb2011@hotmail.com:MLucas3#

内容的提问来源于stack exchange，提问作者Mr Lonely