Python代码无法正确提取、对比并发送缺失许可证编号问题求助

• 数据库包含两张表：license_numbers_table、retired_license_table
• 需求：仅筛选两表中player_status='1'的许可证编号，为license_numbers_table的编号加前缀A，为retired_license_table的编号加前缀B；将处理后的编号与名为「MySQL Test Table」的Google Sheet数据对比，找出双方互不存在的缺失编号并发送邮件
• 异常情况：预期缺失编号为B25788909、A87841405、B78613685，但实际邮件中混入了player_status=0的编号，附上代码请求排查

import mysql.connector
import config
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Get Google Sheets credentials and connect to sheet 
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('Keys/Google Sheets API.json', scope) 
client = gspread.authorize(creds) 
sheet = client.open('MySQL Test Table').sheet1 

import smtplib
from email.mime.text import MIMEText
from mysql.connector.errors import Error as MySQLError

# Connect to MySQL database try:
mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="BLABLABLABLA",
        database="test_schema"
    ) except MySQLError as e:
print(f"Error connecting to MySQL database: {e}")
exit(1)

# Create cursor 
mycursor = mydb.cursor()

# Get license numbers and their corresponding table names where player_status = 1 
mycursor.execute("SELECT license_number, 'license_numbers_table' as table_name FROM license_numbers_table WHERE player_status = 1 UNION SELECT license_number, 'retired_license_table' as table_name FROM retired_license_table WHERE player_status = 1")
license_numbers_data = mycursor.fetchall()

# Prefix the license numbers with A or B depending on which table they come from
prefixed_license_numbers = [('A' + str(row[0]) if row[1] == 'license_numbers_table' else 'B' + str(row[0])) for row in license_numbers_data]

# Get Google Sheets credentials and connect to sheet
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] 
creds = ServiceAccountCredentials.from_json_keyfile_name('Keys/Google Sheets API.json', scope) 
client = gspread.authorize(creds)
sheet = client.open('MySQL Test Table').sheet1

# Get all license numbers from the Google Sheet 
sheet_license_numbers = [row[0] for row in sheet.get_all_values()][1:]

# Prefix the license numbers in the Google Sheet with A or B if they are not already prefixed 
prefixed_sheet_license_numbers = [] 
for license_number in sheet_license_numbers:
    if license_number[0] == 'A' or license_number[0] == 'B':
        prefixed_sheet_license_numbers.append(license_number)
    else:
        prefixed_sheet_license_numbers.append('A' + license_number)

# Create sets of the license numbers from each source 
license_numbers_table = set(prefixed_license_numbers) 
sheet_license_numbers = set(prefixed_sheet_license_numbers)

# Find any license numbers that are not in at least one of the sources 
missing_license_numbers = sheet_license_numbers.union(license_numbers_table) - sheet_license_numbers.intersection(license_numbers_table)

# Create the message to be sent 
message = MIMEText("Missing license numbers:\n" + "\n".join(missing_license_numbers))

# Set the sender and recipient email addresses 
sender = "emailcaseydent@gmail.com"
recipient = "emailcaseydent@gmail.com"

# Set the subject of the email 
message['Subject'] = 'Missing License Numbers'

# Set the sender and recipient of the email 
message['From'] = sender 
message['To'] = recipient

# Set up SMTP server and login with
smtplib.SMTP('smtp.gmail.com', 587, timeout=120) as smtpObj:
    smtpObj.starttls()
    try:
        smtpObj.login('emailcaseydent@gmail.com', config.EMAIL_PASSWORD)
    except smtplib.SMTPAuthenticationError as e:
        print(f"SMTP login error: {e}")
        exit(1)

    # Send the email
    smtpObj.sendmail(sender, recipient, message.as_string())

    # Close the connection to the SMTP server
    smtpObj.quit()

1. 验证MySQL查询有效性 #

首先确认SQL查询是否真的只返回player_status=1的记录：

• 直接在MySQL客户端执行以下SQL，检查结果中的player_status是否全部为1：

  SELECT license_number, player_status FROM license_numbers_table WHERE player_status = 1
  UNION
  SELECT license_number, player_status FROM retired_license_table WHERE player_status = 1;
  

• 重点检查player_status字段的数据类型：
  • 如果字段是数值类型（int/tinyint），SQL中WHERE player_status = 1是正确的；
  • 如果字段是字符串类型，必须写成WHERE player_status = '1'，否则会因为隐式转换导致误匹配（比如非数字的player_status值会被转成0，可能意外包含不符合条件的记录）。

2. 修正Google Sheet数据处理逻辑 #

当前代码给所有无前缀的Sheet编号统一加A前缀，但如果Sheet中存在来自retired_license_table的编号，会被错误标记为A前缀，导致对比时出现假阳性缺失（看起来像是来自player_status=0的记录）：

• 如果Sheet中没有标识编号所属表的字段，建议调整对比逻辑：先去掉两边的前缀，对比原始编号，再结合表来源判断；
• 示例调整代码：

  # 拆分MySQL的编号为前缀和原始编号，建立映射
  mysql_license_map = {}
  for num in prefixed_license_numbers:
      prefix = num[0]
      original = num[1:]
      mysql_license_map[original] = prefix
  
  # 处理Sheet编号，匹配MySQL的前缀规则
  corrected_sheet_nums = []
  for num in sheet_license_numbers:
      if num[0] in ('A', 'B'):
          corrected_sheet_nums.append(num)
      else:
          # 根据MySQL中的映射添加正确前缀
          corrected_sheet_nums.append(mysql_license_map.get(num, 'A') + num)
  

3. 清理代码冗余与潜在错误 #

• 删除重复的Google Sheet连接代码，避免变量重复赋值导致的意外问题；
• SMTP部分使用with语句管理连接时，无需手动调用smtpObj.quit()，with会自动关闭连接，手动调用可能引发报错。

4. 打印中间结果定位问题 #

在代码中添加打印语句，明确两个集合的内容，确认是MySQL查询出了问题还是Sheet处理逻辑有误：

print("MySQL筛选并加前缀后的编号集合：", license_numbers_table)
print("Sheet处理后的编号集合：", sheet_license_numbers)
print("缺失编号集合：", missing_license_numbers)

内容的提问来源于stack exchange，提问作者Casey Dent