sacn_comments/server/db.py

85 lines
2.3 KiB
Python

from .cfg import config
import uuid
import aiomysql
import sys
HOST = config["db"]["host"]
PORT = config["db"]["port"]
USER = config["db"]["user"]
PASSWD = config["db"]["passwd"]
DB = config["db"]["db"]
MAXSIZE = config["db"]["maxsize"]
MINSIZE = config["db"]["minsize"]
async def connect_db():
global db
sys.stdout.write("connect db\n")
db = await aiomysql.create_pool(
host=HOST,
port=PORT,
user=USER,
password=PASSWD,
db=DB,
maxsize=MAXSIZE,
minsize=MINSIZE
)
async def recreate():
global db
db.close()
db = await aiomysql.create_pool(
host=HOST,
port=PORT,
user=USER,
password=PASSWD,
db=DB,
maxsize=MAXSIZE,
minsize=MINSIZE
)
async def create_db():
async with db.acquire() as conn:
await (await conn.cursor()).execute(
"CREATE TABLE IF NOT EXISTS comments (comment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255), route VARCHAR(255), comment_text TEXT)"
)
async def create_comment(username: str, texts: str, route: str):
async with db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("INSERT INTO comments (username, comment_text, route) VALUES (%s, %s, %s)", (username, texts, route))
await conn.commit()
await recreate()
return 0
async def get_count(route):
async with db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT COUNT(*) FROM comments WHERE `route` = %s", (route,))
result = await cur.fetchone()
if result:
return result[0]
return None
async def show_comments(route, page: int = 0):
offset = (page - 1) * 5
async with db.acquire() as conn:
async with conn.cursor(aiomysql.DictCursor) as cur:
sql = """
SELECT username, comment_text
FROM comments
WHERE `route` = %s
ORDER BY comment_id DESC
LIMIT %s OFFSET %s
"""
await cur.execute(sql, (route, 5, offset))
result = await cur.fetchall()
return [{"avatar": i["username"][0], "username": i["username"], "comment": i["comment_text"]} for i in result]