leon_mac
85 lines
2.9 KiB
Python
85 lines
2.9 KiB
Python
import pymysql
|
|
from datetime import datetime, timedelta
|
|
|
|
def add_days_to_table_fields(
|
|
conn_params: dict,
|
|
table_name: str,
|
|
days: int
|
|
):
|
|
"""
|
|
为指定表的 end_time 和 end_time_unix 字段分别增加指定天数。
|
|
两个字段独立处理，end_time 为空时不改，end_time_unix 为空时也不改。
|
|
"""
|
|
# 连接数据库
|
|
conn = pymysql.connect(**conn_params)
|
|
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
|
|
|
# 查询整表需要的字段
|
|
cursor.execute(f"SELECT id, end_time, end_time_unix FROM {table_name}")
|
|
rows = cursor.fetchall()
|
|
|
|
count_end_time = 0
|
|
count_end_time_unix = 0
|
|
|
|
for row in rows:
|
|
record_id = row["id"]
|
|
end_time_str = row.get("end_time")
|
|
end_time_unix_str = row.get("end_time_unix")
|
|
|
|
new_end_time = None
|
|
new_end_time_unix = None
|
|
|
|
# 1️⃣ 处理 end_time
|
|
if end_time_str:
|
|
try:
|
|
dt = datetime.strptime(end_time_str, "%Y-%m-%d %H:%M:%S")
|
|
new_dt = dt + timedelta(days=days)
|
|
new_end_time = new_dt.strftime("%Y-%m-%d %H:%M:%S")
|
|
count_end_time += 1
|
|
except Exception as e:
|
|
print(f"[WARN] id={record_id} 的 end_time 无法解析: {end_time_str} ({e})")
|
|
|
|
# 2️⃣ 处理 end_time_unix
|
|
if end_time_unix_str and end_time_unix_str.isdigit():
|
|
try:
|
|
ts = int(end_time_unix_str)
|
|
new_ts = ts + days * 86400 # 1天=86400秒
|
|
new_end_time_unix = str(new_ts)
|
|
count_end_time_unix += 1
|
|
except Exception as e:
|
|
print(f"[WARN] id={record_id} 的 end_time_unix 无法解析: {end_time_unix_str} ({e})")
|
|
|
|
# 3️⃣ 如果有任意一个字段需要更新，则执行更新
|
|
if new_end_time or new_end_time_unix:
|
|
set_clauses = []
|
|
params = []
|
|
if new_end_time:
|
|
set_clauses.append("end_time=%s")
|
|
params.append(new_end_time)
|
|
if new_end_time_unix:
|
|
set_clauses.append("end_time_unix=%s")
|
|
params.append(new_end_time_unix)
|
|
params.append(record_id)
|
|
|
|
sql_update = f"UPDATE {table_name} SET {', '.join(set_clauses)} WHERE id=%s"
|
|
print(f'执行sql ===> {sql_update} 参数 ==> {params}')
|
|
cursor.execute(sql_update, params)
|
|
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
|
|
print(f"✅ 更新完成：end_time {count_end_time} 条，end_time_unix {count_end_time_unix} 条。")
|
|
|
|
# 直接操作数据库，给全体用户增加有效天数
|
|
if __name__ == "__main__":
|
|
db_info = {
|
|
'host': '101.227.74.8',
|
|
'port': 3306,
|
|
'user': 'root',
|
|
'password': '9lYgSVPPDjh6sq2C',
|
|
"database": "ss2_migrated",
|
|
}
|
|
|
|
add_days_to_table_fields(db_info, "group_purchase_users", days=7)
|