DATABASE &
TRANSACTIONS

CLIENT

Confidential SaaS

ROLE

Data Persistence

YEAR

2025

AWARDS / TYPE

Independent Work

PROJECT
OVERVIEW

Handled database compatibility, managed connection pools, and ensured the security of atomic transactions for core asset distribution under high concurrency.

database.py

# _*_coding : utf-8 _*_
import pymysql
import datetime
import logging

logger = logging.getLogger('database')

def get_db_connection():
    '''获取安全的数据库连接 (脱敏密码)'''
    try:
        conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='YOUR_DATABASE_PASSWORD',
            database='bot_db',
            cursorclass=pymysql.cursors.DictCursor
        )
        return conn, "%s"
    except Exception as e:
        logger.error(f"连接数据库失败: {e}")
        return None, None

def _safe_close(conn, cursor):
    '''确保在任何情况下连接和游标都被安全关闭，防止内存泄漏。'''
    if cursor:
        try: cursor.close()
        except: pass
    if conn:
        try: conn.close()
        except: pass

def grant_membership(user_id, days=30, tier=1):
    '''核心业务：授予会员并执行原子化的额度重置'''
    conn, cursor = None, None
    try:
        expires_date = datetime.date.today() + datetime.timedelta(days=days)
        cur_month = datetime.datetime.now().strftime("%Y-%m")
        conn, ph = get_db_connection()
        cursor = conn.cursor()

        cursor.execute(f'''
            UPDATE users SET status = {ph}, membership_expires = {ph}, 
            membership_tier = {ph}, char_usage_count = 0, last_reset_month = {ph}
            WHERE user_id = {ph}
        ''', ('member', expires_date, tier, cur_month, user_id))

        conn.commit()
        return True
    except Exception as e:
        if conn: conn.rollback()
        return False
    finally:
        _safe_close(conn, cursor)

PROJECTOVERVIEW

PROJECT
OVERVIEW