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()
