85 lines
2.3 KiB
Python
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]
|