async def fetch_one_note(
connection: aiomysql.Connection,
note_id: int) -> dict[str, Union[int, str, datetime.datetime]]:
"""
Fetch the one particular note.
:param connection: db connection
:type connection: aiomysql.Connection
:param note_id: note id
:type note_id: int
:return: data of the note
:rtype: dict[str, Union[int, str, datetime.datetime]]
"""
query = """
SELECT
`notes`.*,
`note_rubrics`.title AS `rubric`
FROM
`notes`
LEFT JOIN
`note_rubrics` ON `notes`.`rubric_id` = `note_rubrics`.`id`
WHERE
`notes`.`id` = %(note_id)s
;
"""
params = {'note_id': note_id}
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, params)
note = await cursor.fetchone()
return note
async def insert_citizen_list(citizens, conn: aiomysql.Connection):
import_id = await Import.create_import(conn)
citizen_sql = 'insert into citizens (import_id, citizen_id, town, street, building, apartment, name, day, month, year, gender) values {0}'
relation_sql = 'insert into relations (import_id, citizen_id, related_citizen_id) values {0}'
citizen_values = []
relation_values = []
for c in citizens:
citizen = Citizen(**c, import_id=import_id)
citizen_values.append(
"({import_id},{citizen_id},'{town}','{street}','{building}',{apartment},'{name}',{day},{month},{year},'{gender}')"
.format(
import_id=citizen.import_id,
citizen_id=citizen.citizen_id,
town=citizen.town,
street=citizen.street,
building=citizen.building,
apartment=citizen.apartment,
name=citizen.name,
day=citizen.day,
month=citizen.month,
year=citizen.year,
gender=citizen.gender,
))
relation_values.extend([
f"({import_id},{citizen.citizen_id},{related_citizen_id})"
for related_citizen_id in citizen.relatives
if related_citizen_id > citizen.citizen_id
])
async with conn.cursor() as cursor:
await cursor.execute(citizen_sql.format(','.join(citizen_values)))
await cursor.execute(relation_sql.format(','.join(relation_values))
)
return import_id
async def fetch_one_random_post(
connection: aiomysql.Connection
) -> dict[str, Union[int, str, datetime.datetime]]:
"""
Fetch an one random post.
:param connection: db connection
:type connection: aiomysql.Connection
:return: data of the random post
:rtype: dict[str, Union[int, str, datetime.datetime]]
"""
query = """
SELECT
`posts`.*,
`post_rubrics`.`title` AS `rubric`
FROM
`posts`
LEFT JOIN
`post_rubrics` ON `posts`.`rubric_id` = `post_rubrics`.`id`
ORDER BY RAND()
LIMIT 1
;
"""
# params = {
# 'order_key': random.random()
# }
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query)
post = await cursor.fetchone()
return post
async def checkIfExists(key: str,
keyType: str,
table: str,
db: aiomysql.Connection = None):
"""
Checks if a key exists on the database.
Arguments
---
key: The value of a key on the row.
keyType: The key header of the value (ID, Name, etc.)
table: The table in the database that contains the row in question.
db: An existing MySQL connection to avoid making a new uncesssary connection.
Returns
---
`True` if the key exists, `False` if otherwise.
"""
if db is None:
raise NoDatabaseConnection()
async with db.cursor() as cursor:
sql = f"SELECT {keyType} FROM {table} WHERE {keyType} = %s"
arg = (key, )
await cursor.execute(sql, arg)
result = await cursor.fetchone()
await db.commit()
if result is None:
return False
return True
async def fetch_one_user(
connection: aiomysql.Connection,
*args,
user_id: Optional[int] = None,
login: Optional[str] = None,
password: Optional[str] = None,
) -> dict[str, Union[int, str]]:
"""
Fetch the user by keyword argument(s).
Will raise an error if no one keyword argument was passed.
If were passed few arguments - all will be considered.
:param connection: db connection
:type connection: aiomysql.Connection
:keyword user_id: user id
:type user_id: int
:keyword login: login
:type login: str
:keyword password: password
:type password: str
:return: data of the user
:rtype: dict[str, Union[int, str]]
:raises TypeError: raised if no one keyword argument was passed
"""
if not (user_id or login or password):
message = "fetch_one_user() missing (at least) 1 required keyword argument: ('user_id', 'login', 'password')"
raise TypeError(message)
query_template = """
SELECT
*
FROM
`users`
WHERE
1 = 1
{% if user_id %}
AND `id` = {{ user_id }}
{% endif %}
{% if login %}
AND `login` = {{ login }}
{% endif %}
{% if password %}
AND `password` = {{ password }}
{% endif %}
;
"""
params = {'user_id': user_id, 'login': login, 'password': password}
query, bound_params = jinja_sql.prepare_query(query_template, params)
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, bound_params)
user = await cursor.fetchone()
return user
async def init_db(conn: aiomysql.Connection) -> None:
from app_soc.settings import BASE_DIR
with open(BASE_DIR / 'app_soc' / 'sql' / 'create_tables.sql') as sql_file:
sql_create_db = sql_file.read()
cur: aiomysql.Cursor
async with conn.cursor() as cur:
await cur.execute(sql_create_db)
async def fetch_all_notes(
connection: aiomysql.Connection, user_id: int,
params: validators.NoteUrlParams
) -> list[dict[str, Union[int, str, datetime.datetime]]]:
"""
Fetch all notes (considering extra arguments).
:param connection: db connection
:type connection: aiomysql.Connection
:param user_id: user id
:type user_id: int
:param params: additional params
:type params: validators.NoteUrlParams
:return: data of the notes
:rtype: list[dict[str, Union[int, str, datetime.datetime]]]
"""
query_template = """
SELECT
`notes`.`id` AS `id`,
LEFT(`notes`.`content`, 200) AS `content`,
`notes`.`created_date` AS `created_date`,
`notes`.`edited_date` AS `edited_date`,
`notes`.`rubric_id` AS `rubric_id`,
`note_rubrics`.title AS `rubric`
FROM
`notes`
LEFT JOIN
`note_rubrics` ON `notes`.`rubric_id` = `note_rubrics`.`id`
WHERE
`notes`.`user_id` = {{ user_id }}
{% if rubric_id %}
AND `rubric_id` = {{ rubric_id }}
{% endif %}
{% if search_word %}
AND MATCH (`notes`.`content`) AGAINST ({{ search_word }})
{% endif %}
ORDER BY `notes`.`created_date` DESC
LIMIT {{ offset }}, {{ rows_quantity }}
;
"""
params = params.dict(by_alias=True)
params['user_id'] = user_id
params['offset'] = (params['page_number'] - 1) * params['rows_quantity']
query, bound_params = jinja_sql.prepare_query(query_template, params)
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, bound_params)
notes = await cursor.fetchall()
return notes
async def fetch_posts_possible_pages_quantity(
connection: aiomysql.Connection,
params: validators.PostUrlParams,
*args: Any,
user_id: Optional[int] = None) -> int:
"""
Fetch quantity of the possible posts pages.
:param connection: db connection
:type connection: aiomysql.Connection
:param params: additional params
:type params: validators.PostUrlParams
:keyword user_id: user id
:type user_id: int
:return: possible quantity of pages
:rtype: int
"""
query_template = """
SELECT
COUNT(*)
FROM
`posts`
WHERE
1 = 1
{% if rubric_id %}
AND `rubric_id` = {{ rubric_id }}
{% endif %}
{% if search_word %}
AND MATCH (`posts`.`title`, `posts`.`content`) AGAINST ({{ search_word }})
{% endif %}
{% if user_id %}
AND `posts`.`user_id` = {{ user_id }}
{% endif %}
;
"""
params = params.dict(by_alias=True)
if 'user_id' not in params and user_id is not None:
params['user_id'] = user_id
query, bound_params = jinja_sql.prepare_query(query_template, params)
async with connection.cursor() as cursor:
await cursor.execute(query, bound_params)
query_result = await cursor.fetchone()
posts_quantity = int(query_result[0])
possible_pages_quantity = math.ceil(posts_quantity /
params['rows_quantity'])
return possible_pages_quantity
async def get_citizen(cls, import_id, citizen_id,
conn: aiomysql.Connection):
citizen_sql = 'select import_id, citizen_id, town, street, building, apartment, name, day, month, year, gender from citizens.citizens where import_id = %s and citizen_id = %s', (
import_id, citizen_id)
relates_sql = 'select related_citizen_id as relative from citizens.relations where import_id = %s and citizen_id = %s union all select citizen_id as relative from citizens.relations where import_id = %s and related_citizen_id = %s', (
import_id, citizen_id, import_id, citizen_id)
async with conn.cursor(aiomysql.DictCursor) as cursor:
await cursor.execute(*citizen_sql)
citizen = await cursor.fetchone()
if not citizen:
return None
await cursor.execute(*relates_sql)
relates = await cursor.fetchall()
return cls.from_db(**citizen,
relatives=[r['relative'] for r in relates])
async def get_cursor(self,
conn: Connection = None,
cur: Cursor = None,
write: bool = False) -> Tuple[Connection, Cursor]:
if conn and cur:
yield conn, cur
else:
async with self._get_lock(write):
async with self._db_conn_pool.acquire() as conn:
async with conn.cursor(*self.cursor_type) as cur:
yield conn, cur
if write:
await conn.commit()
# https://github.com/aio-libs/aiomysql/issues/449
if write:
await self._db_conn_pool.clear()
async def execute_query(connection: aiomysql.Connection, query: str,
params: dict) -> None:
"""
Execute query (might be used for insert, update, delete actions that do not fetch result - only execute).
:param connection: db connection
:type connection: aiomysql.Connection
:param query: sql query
:type query: str
:param params: query params
:type params: dict
:return: None
:rtype: None
"""
async with connection.cursor() as cursor:
await cursor.execute(query, params)
async def fetch_all_post_rubrics(
connection: aiomysql.Connection) -> list[dict[str, Union[int, str]]]:
"""
Fetch all post rubrics.
:param connection: db connection
:type connection: aiomysql.Connection
:return: data of the post rubric
:rtype: list[dict[str, Union[int, str]]]
"""
query = 'SELECT * FROM `post_rubrics`;'
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query)
post_rubrics = await cursor.fetchall()
return post_rubrics
async def fetch_all_moderators(
connection: aiomysql.Connection) -> list[dict[str, Union[int, str]]]:
"""
Fetch all users with moderator grant.
:param connection: db connection
:type connection: aiomysql.Connection
:return: list of the moderators
:rtype: list[dict[str, Union[int, str]]]
"""
query = 'SELECT * FROM `moderators`;'
async with connection.cursor(aiomysql.DictCursor) as cursor:
await cursor.execute(query)
moderators = await cursor.fetchall()
return moderators
async def fetch_one_post(
connection: aiomysql.Connection,
post_id: int) -> dict[str, Union[int, str, datetime.datetime]]:
"""
Fetch the one particular post.
:param connection: db connection
:type connection: aiomysql.Connection
:param post_id: post id
:type post_id: int
:return: data of the post
:rtype: dict[str, Union[int, str, datetime.datetime]]
"""
query = """
SELECT
`posts`.`id` AS `id`,
`posts`.`title` AS `title`,
`posts`.`content` AS `content`,
`posts`.`created_date` AS `created_date`,
`posts`.`edited_date` AS `edited_date`,
`posts`.`user_id` AS `user_id`,
`posts`.`rubric_id` AS `rubric_id`,
`post_rubrics`.`title` AS `rubric`,
`users`.`login` AS `author`
FROM
`posts`
LEFT JOIN
`post_rubrics` ON `posts`.`rubric_id` = `post_rubrics`.`id`
LEFT JOIN
`users` ON `posts`.`user_id` = `users`.`id`
WHERE
`posts`.`id` = %(post_id)s
;
"""
params = {'post_id': post_id}
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, params)
post = await cursor.fetchone()
return post
async def fetch_one_post_rubric(
connection: aiomysql.Connection,
post_rubric_id: int) -> dict[str, Union[int, str]]:
"""
Fetch one post rubric.
:param connection: db connection
:type connection: aiomysql.Connection
:param post_rubric_id: post rubric id
:type post_rubric_id: int
:return: data of the post rubric
:rtype: dict[str, Union[int, str]]
"""
query = 'SELECT * FROM `post_rubrics` WHERE `id` = %(post_rubric_id)s;'
params = {'post_rubric_id': post_rubric_id}
async with connection.cursor(aiomysql.DictCursor) as cursor:
await cursor.execute(query, params)
post_rubric = await cursor.fetchone()
return post_rubric
async def fetch_all_note_rubrics(
connection: aiomysql.Connection,
user_id: int) -> list[dict[str, Union[int, str]]]:
"""
Fetch all notes by rubric.
:param connection: db connection
:type connection: aiomysql.Connection
:param user_id: user id
:type user_id: int
:return: data of the note rubrics
:rtype: list[dict[str, Union[int, str]]]
"""
query = 'SELECT * FROM `note_rubrics` WHERE `user_id` = %(user_id)s;'
params = {'user_id': user_id}
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, params)
note_rubrics = await cursor.fetchall()
return note_rubrics
async def fetch_one_note_rubric(
connection: aiomysql.Connection,
note_rubric_id: int) -> dict[str, Union[int, str]]:
"""
Fetch all notes by rubric.
:param connection: db conncetion
:type connection: aiomysql.Connection
:param note_rubric_id: note rubric id
:type note_rubric_id: int
:return: data of the note rubric
:rtype: dict[str, Union[int, str]]
"""
query = 'SELECT * FROM `note_rubrics` WHERE `id` = %(note_rubric_id)s;'
params = {'note_rubric_id': note_rubric_id}
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, params)
note_rubric = await cursor.fetchone()
return note_rubric
async def fetch_all_posts(
connection: aiomysql.Connection,
params: validators.PostUrlParams,
*args: Any,
user_id: Optional[int] = None
) -> list[dict[str, Union[int, str, datetime.datetime]]]:
"""
Fetch all posts (considering extra arguments).
:param connection: db connection
:type connection: aiomysql.Connection
:param params: additional params
:type params: validators.PostUrlParams
:keyword user_id: user id
:type user_id: int
:return: data of the posts
:rtype: list[dict[str, Union[int, str, datetime.datetime]]]
"""
query_template = """
SELECT
`posts`.`id` AS `id`,
`posts`.`title` AS `title`,
LEFT(`posts`.`content`, 100) AS `content`,
`posts`.`created_date` AS `created_date`,
`posts`.`edited_date` AS `edited_date`,
`posts`.`user_id` AS `user_id`,
`posts`.`rubric_id` AS `rubric_id`,
`post_rubrics`.`title` AS `rubric`,
`users`.`login` AS `author`
FROM
`posts`
LEFT JOIN
`post_rubrics` ON `posts`.`rubric_id` = `post_rubrics`.`id`
LEFT JOIN
`users` ON `posts`.`user_id` = `users`.`id`
WHERE
1 = 1
{% if rubric_id %}
AND `rubric_id` = {{ rubric_id }}
{% endif %}
{% if search_word %}
AND MATCH (`posts`.`title`, `posts`.`content`) AGAINST ({{ search_word }})
{% endif %}
{% if user_id %}
AND `posts`.`user_id` = {{ user_id }}
{% endif %}
ORDER BY `posts`.`created_date` DESC
LIMIT {{ offset }}, {{ rows_quantity }}
;
"""
params = params.dict(by_alias=True)
if user_id:
params['user_id'] = user_id
params['offset'] = (params['page_number'] - 1) * params['rows_quantity']
query, bound_params = jinja_sql.prepare_query(query_template, params)
async with connection.cursor(aiomysql.cursors.DictCursor) as cursor:
await cursor.execute(query, bound_params)
posts = await cursor.fetchall()
return posts
