async def checkCardsByQQNum(qqNum: str) -> List[tuple]: ''' 根据qq号码查询卡牌收集情况 :returns: 卡列表 (id, level, name) ''' sql = """ SELECT c.id, c.level, c.name FROM lottery_record l INNER JOIN cards c ON l.card_id = c.id WHERE l.user_id = (SELECT modian_id FROM user WHERE qq = %s) AND version = %s GROUP BY c.id , c.level """ async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.execute(sql, (qqNum, config['version'])) return await cursor.fetchall()
async def insertLotteryData(orderInfo: Dict[str, Any], cards: List[tuple]): ''' 将抽卡结果写入数据库 :param orderInfo: 订单信息 :param cards: 卡牌列表 ''' sql = """ insert ignore into lottery_record (user_id, card_id, pay_date_time, insert_time, card_version) values (%s, %s, %s, now(), %s) """ ''' 订单信息 { "user_id": , "nickname": "", "order_time": "2019-05-11 10:44:13", "pay_success_time": "2019-05-11 10:44:20", "backer_money": } ''' rows: List[tuple] = lotteryDataRowsBuilder(orderInfo, cards) async with pool.acquire() as conn: async with conn.cursor() as cursor: try: # 写入卡牌数据 await cursor.executemany(sql, rows) # 标记订单状态为已经抽取卡牌 await markOrderOne(orderInfo, cursor) await conn.commit() except Exception as e: print(e.with_traceback()) await conn.rollback()
async def initCardTable(cards: List[tuple]): ''' 向数据库写入卡牌信息 用于新卡牌数据读入 ''' sql = """ insert into cards (level, name, pic_dir, version) values (%s, %s, %s, %s) """ async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.executemany(sql, cards) await conn.commit()
async def getOrders() -> List[tuple]: ''' 获取未抽卡的订单 ''' sql = """ SELECT user_id, pay_date_time, money, pro_id FROM daily WHERE lottery = 0 AND money >= '13.14' """ async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.execute(sql) return await cursor.fetchall()
async def getLatestTime() -> List[tuple]: ''' 取出已保存的最新订单时间 根据pro_id分组 ''' sql = """ select pro_id, max(pay_date_time) latest from daily group by pro_id """ async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.execute(sql) return await cursor.fetchall()
async def getCardsByVersion(version: int) -> List[tuple]: ''' 根据卡牌版本 获取相应的卡牌 :param version: 需要读取的卡牌版本号 :returns: 对应版本号的卡牌列表结果集 ''' sql = """ select id, level, name, pic_dir from cards where version = %s """ async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.execute(sql, version) return await cursor.fetchall()
async def bindModianUserAndQQ(userId: str, qqNum: str): ''' 绑定摩点id与QQ号 ''' sql = """ UPDATE user SET qq = %s WHERE modian_id = %s """ async with pool.acquire() as conn: async with conn.cursor() as cursor: try: await cursor.execute(sql, (qqNum, userId)) await conn.commit() except Exception as e: print(e.with_traceback()) conn.rollback()
async def getCardsByUserId(userId: str) -> Set[tuple]: ''' 根据摩点用户id获取用户已拥有的卡牌情况 返回已拥有卡牌的哈希集 :param userId: 用户的摩点id :returns: 用户已经拥有的卡牌集合 (已去重) ''' sql = """ SELECT c.id, c.level, c.name, c.pic_dir FROM lottery_record l INNER JOIN cards c ON l.card_id = c.id WHERE l.user_id = %s GROUP BY c.id """ async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.execute(sql, userId) cards = await cursor.fetchall() return set(cards)
async def insertNewOrders(pro_id: str, orders: list): ''' 向数据库写入新订单信息 ''' sql = """ insert ignore into daily (user_id, pro_id, money, pay_date_time) values (%s,%s,%s,%s) """ # data = [] # for order in orders: # row: tuple = (order['user_id'], pro_id, order['backer_money'], # order['pay_success_time']) # data.append(row) data = [(order['user_id'], pro_id, order['backer_money'], order['pay_success_time']) for order in orders] async with pool.acquire() as conn: async with conn.cursor() as cursor: await cursor.executemany(sql, data) await conn.commit()
async def updateScore(userId: str, score: int): ''' 更新用户积分信息 无用户记录则创建 有则更新积分 :param userId: 摩点用户id :param score: 本次获得的积分 ''' sql = """ insert into user (modian_id, score) values (%s, %s) on duplicate key update score = score + %s """ async with pool.acquire() as conn: async with conn.cursor() as cursor: try: await cursor.execute(sql, (userId, score, score)) await conn.commit() except Exception as e: print(e.with_traceback()) await conn.rollback()