def remove_rss_by_url_channel(
url_channel: str) -> None: # возможно переименовать
"""
Удаление выбранного элемента/ов из rss_tab по url_channel.
Применяется для групового удаления элементов связанных с url_channel из rss_tab,
при удалении, в свою очередь, данного канала из channel_tab
:param url_channel: url_channel удаляемого элемента/ов в rss_tab.
:return: None.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """DELETE FROM rss_tab WHERE url_channel_r=%s;"""
cursor.execute(_SQL, (url_channel, ))
except ConnectionError as err:
app.logger.info(
'db_rss_mod.remove_rss_by_url_channel failed with this error: ConnectionError '
+ str(err))
except CredentialsError as err:
app.logger.info(
'db_rss_mod.remove_rss_by_url_channel failed with this error: CredentialsError.'
+ str(err))
except SQLError as err:
app.logger.info(
'db_rss_mod.remove_rss_by_url_channel failed with this error: SQLError '
+ str(err))
except Exception as err:
app.logger.info(
'db_rss_mod.remove_rss_by_url_channel failed with this error: ' +
str(err))
def search_log_by_id(id: int) -> bool:
"""
Поиск выбранного элемента в log_tab по id.
:param id: id искомого элемента в log_tab.
:return: возращает True если находит элемент, и False если нет.
"""
try:
with DatabaseConnection(app.config['log_db_config']) as cursor:
_SQL = """SELECT * FROM log_tab WHERE id=%s;"""
cursor.execute(_SQL, (id,))
contents = cursor.fetchall()
if not contents:
return False
else:
return True
except ConnectionError as err:
app.logger.info('search_log_by_id failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('search_log_by_id failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('search_log_by_id failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('search_log_by_id failed with this error: ' + str(err))
def search_rss_by_id(id: int) -> bool:
"""
Для поиска по id в channel_tab.
Применяется для поиска элемента при удалении конкретного элемента из rss_tab.
:param id: id искомого элемента в rss_tab.
:return: возращает True если находит элемент, и False если нет.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT * FROM rss_tab WHERE id=%s;"""
cursor.execute(_SQL, (id, ))
contents = cursor.fetchall()
if not contents:
return False
else:
return True
except ConnectionError as err:
app.logger.info(
'search_rss_by_id failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'search_rss_by_id failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info('search_rss_by_id failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('search_rss_by_id failed with this error: ' + str(err))
def search_user_by_id(id: int) -> tuple:
"""
Для поиска элемента по id в users_tab.
:param id: id искомого элемента в users_tab.
:return: список с результатом поиска.
"""
try:
with DatabaseConnection(app.config['users_db_config']) as cursor:
_SQL = """SELECT * FROM users_tab WHERE id=%s;"""
cursor.execute(_SQL, (id, ))
contents = cursor.fetchall()
return contents
except ConnectionError as err:
app.logger.info(
'search_user_by_id failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'search_user_by_id failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info('search_user_by_id failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('search_user_by_id failed with this error: ' +
str(err))
def add_log(list_for_log: tuple, operation='error') -> None:
"""
Добавляет элементы в log_tab.
:param list_for_log: логируемые данные (url, ip, browser, os).
:param operation: вид совершённой операции.
:return: None
"""
try:
with DatabaseConnection(app.config['log_db_config']) as cursor:
_SQL = """INSERT INTO log_tab
(operation, url, ip, browser, os)
VALUES
(%s, %s, %s, %s, %s);"""
cursor.execute(_SQL, (operation,
list_for_log[0],
list_for_log[1],
list_for_log[2],
list_for_log[3],
))
except ConnectionError as err:
app.logger.info('add_log failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('add_log failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('add_log failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('add_log failed with this error: ' + str(err))
def search_channel(list_from_request: tuple, list_for_log: tuple) -> list:
"""
Для поиска элемента по url в channel_tab.
Применяется для поиска добавляемого через форму канала в БД.
:param list_from_request: данные из запроса (url_channel, status_code, length_content, title_channel, url_site).
:param list_for_log: данные для add_log.
:return: кортеж с результатами поиска.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor: # попробовать вынести в function
_SQL = """SELECT * FROM channel_tab WHERE url_channel_c=%s;"""
cursor.execute(_SQL, (list_from_request[0],))
contents = cursor.fetchall()
return contents
except ConnectionError as err:
app.logger.info('search_channel failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('search_channel failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('search_channel failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('search_channel failed with this error: ' + str(err))
def delete_all_log() -> None:
"""
Удаляет все записи в таблице log_tab и сбрасывает id на 1.
:return: None.
"""
try:
with DatabaseConnection(app.config['log_db_config']) as cursor:
_SQL = """DELETE FROM log_tab;"""
cursor.execute(_SQL)
_SQL = """ALTER TABLE log_tab AUTO_INCREMENT = 1;"""
cursor.execute(_SQL)
except ConnectionError as err:
app.logger.info('delete_all_log failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('delete_all_log failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('delete_all_log failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('delete_all_log failed with this error: ' + str(err))
def show_rss() -> tuple:
"""
Отоброжает содержимое rss_tab.
:return: список кортежей со всеми данными из таблицы.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT * FROM rss_tab ORDER BY id DESC LIMIT 10000;"""
cursor.execute(_SQL)
contents = cursor.fetchall()
return contents
except ConnectionError as err:
app.logger.info('show_rss failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info('show_rss failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info('show_rss failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('show_rss failed with this error: ' + str(err))
def show_channel_restrict() -> list: # попробавать убрать
"""
Отоброжает содержимое channel_tab.
:return: список кортежей с некоторыми данными из таблицы.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT id, url_site, title_channel, ts
FROM channel_tab ORDER BY ts DESC LIMIT 1000;"""
cursor.execute(_SQL)
contents = cursor.fetchall()
return contents
except ConnectionError as err:
app.logger.info('show_channel_restrict failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('show_channel_restrict failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('show_channel_restrict failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('show_channel_restrict failed with this error: ' + str(err))
def get_url_channel_by_id(id: int) -> str:
"""
Для поиска url_channel_c по id в channel_tab.
:param id: id искомого элемента в channel_tab.
:return: возвращает url_channel_c из channel_tab.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT url_channel_c FROM channel_tab WHERE id=%s;"""
cursor.execute(_SQL, (id,))
contents = cursor.fetchone()
return contents[0]
except ConnectionError as err:
app.logger.info('get_url_channel_by_id failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('get_url_channel_by_id failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('get_url_channel_by_id failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('get_url_channel_by_id failed with this error: ' + str(err))
def add_rss(list_from_request: tuple) -> None:
"""
Добавляет элемент в rss_tab.
:param list_from_request: list_from_request: данные из запроса.
:return: None.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """INSERT INTO rss_tab
(url_channel_r, title_item, summary_item, url_item, published_item)
VALUES
(%s, %s, %s, %s, %s);"""
cursor.execute(_SQL, (
list_from_request[0],
list_from_request[1],
list_from_request[2],
list_from_request[3],
list_from_request[4],
))
except ConnectionError as err:
app.logger.info('add_rss failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info('add_rss failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info('add_rss failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('add_rss failed with this error: ' + str(err))
def search_user_by_login(login: str) -> list:
"""
Для поиска элемента по логину в users_tab.
:param login: login искомого элемента в users_tab.
:return: список с результатом поиска.
"""
try:
with DatabaseConnection(app.config['users_db_config']) as cursor:
_SQL = """SELECT * FROM users_tab WHERE login=%s;"""
cursor.execute(_SQL, (login, ))
contents = cursor.fetchall()
return contents
except ConnectionError as err:
app.logger.info(
'search_user_by_login failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'search_user_by_login failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info(
'search_user_by_login failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('search_user_by_login failed with this error: ' +
str(err))
def remove_rss_by_id(id: int) -> None: # возможно переименовать
"""
Удаление выбранного элемента из rss_tab по id.
Применяется для удаления конкретного элемента из rss_tab.
:param id: id удаляемого элемента в rss_tab.
:return: None.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """DELETE FROM rss_tab WHERE id=%s;"""
cursor.execute(_SQL, (id, ))
except ConnectionError as err:
app.logger.info(
'db_rss_mod.remove_rss_by_id failed with this error: ConnectionError '
+ str(err))
except CredentialsError as err:
app.logger.info(
'db_rss_mod.remove_rss_by_id failed with this error: CredentialsError.'
+ str(err))
except SQLError as err:
app.logger.info(
'db_rss_mod.remove_rss_by_id failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info(
'db_rss_mod.remove_rss_by_id failed with this error: ' + str(err))
def remove_user_by_login(login: str) -> None:
"""
Удаляет юзера из users_tab.
:param login: логин юзера.
:return: None.
"""
print('Проверка из remove_user_by_login', login)
try:
with DatabaseConnection(app.config['users_db_config']) as cursor:
_SQL = """DELETE FROM users_tab WHERE login=%s;"""
cursor.execute(_SQL, (login, ))
except ConnectionError as err:
app.logger.info(
'remove_user_by_login failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'remove_user_by_login failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info(
'remove_user_by_login failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('remove_user_by_login failed with this error: ' +
str(err))
def search_rss_complex(text: str, date: str, list_for_log: tuple) -> list:
"""
Для комплексного поиска по тексту в поле summary_item, и дате в поле published_item таблицы rss_tab.
:param text: подстрока, вхождение которой в summary_item необходимо найти.
:param date: подстрока, вхождение которой в published_item необходимо найти.
:param list_for_log: данные для add_log.
:return: список кортежей с результатами поиска из rss_tab.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT url_channel_r, title_item, summary_item, published_item, url_item
FROM rss_tab WHERE published_item LIKE CONCAT(%s,'%') AND summary_item LIKE CONCAT('%',%s,'%')
ORDER BY published_item DESC LIMIT 1000;"""
cursor.execute(_SQL, (date, text))
contents = cursor.fetchall()
try:
t = Thread(target=db_log_mod.add_log,
args=(list_for_log,
('search rss complex <{}> <{}> '.format(
text, date))))
t.start()
except Exception as err:
app.logger.info(
'Thread in search_rss_complex failed with this error:' +
str(err))
return contents
except ConnectionError as err:
app.logger.info(
'db_rss_mod.search_rss_complex failed with this error: ConnectionError '
+ str(err))
except CredentialsError as err:
app.logger.info(
'db_rss_mod.search_rss_complex failed with this error: CredentialsError.'
+ str(err))
except SQLError as err:
app.logger.info(
'db_rss_mod.search_rss_complex failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info(
'db_rss_mod.search_rss_complex failed with this error: ' +
str(err))
def update_timestamp(list_from_request: tuple, list_for_log: tuple) -> None:
"""
Для обновления timestamp элемента в channel_tab. Без измененя остальных данных.
:param list_from_request: данные из запроса (url_channel, status_code, length_content, title_channel, url_site).
:param list_for_log: данные для add_log.
:return: None.
"""
temp_status = 999 # Костыль для обновлеия timestamp.
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """UPDATE channel_tab SET
status_code=%s
WHERE
url_channel_c=%s;"""
cursor.execute(_SQL, (temp_status,
list_from_request[0]))
_SQL = """UPDATE channel_tab SET
status_code=%s
WHERE
url_channel_c=%s;"""
cursor.execute(_SQL, (list_from_request[1],
list_from_request[0]))
except ConnectionError as err:
app.logger.info('update_timestamp failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('update_timestamp failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('update_timestamp failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('update_timestamp failed with this error: ' + str(err))
try:
t = Thread(target=db_log_mod.add_log, args=(list_for_log, 'update timestamp'))
t.start()
except Exception as err:
app.logger.info('Thread in update_timestamp failed with this error:' + str(err))
def show_channel_content(url_channel: str, list_for_log: tuple) -> list:
"""
Отоброжаем содержимое определённого канала из rss_tab.
:param url_channel: url канала, содержимое которого будем отображать.
:param list_for_log: данные для add_log.
:return: список кортежей с данными из таблицы соответствущие каналу.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT title_item, summary_item, published_item, url_item
FROM rss_tab WHERE url_channel_r=%s ORDER BY published_item DESC LIMIT 1000;"""
cursor.execute(_SQL, (url_channel, ))
contents = cursor.fetchall()
try:
t = Thread(target=db_log_mod.add_log,
args=(list_for_log, 'show channel content'))
t.start()
except Exception as err:
app.logger.info(
'Thread in show_channel_content failed with this error:' +
str(err))
return contents
except ConnectionError as err:
app.logger.info(
'show_channel_content failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'show_channel_content failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info(
'show_channel_content failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('show_channel_content failed with this error: ' +
str(err))
def update_channel(list_from_request: tuple, list_for_log: tuple) -> None:
"""
Обновление данных для изменённого элемента в channel_tab.
:param list_from_request: данные из запроса (url_channel, status_code, length_content, title_channel, url_site).
:param list_for_log: данные для add_log.
:return: None.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """UPDATE channel_tab SET
status_code=%s,
length_content=%s,
title_channel=%s,
url_site=%s
WHERE
url_channel_c=%s;"""
cursor.execute(_SQL, (list_from_request[1],
list_from_request[2],
list_from_request[3],
list_from_request[4],
list_from_request[0]))
except ConnectionError as err:
app.logger.info('update_channel failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('update_channel failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('update_channel failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('update_channel failed with this error: ' + str(err))
try:
t = Thread(target=db_log_mod.add_log, args=(list_for_log, 'update channel'))
t.start()
except Exception as err:
app.logger.info('Thread in update_channel failed with this error:' + str(err))
def remove_log(id: int) -> None:
"""
Удаление выбранного элемента из log_tab по id.
:param id: id удаляемого элемента в log_tab.
:return: None
"""
try:
with DatabaseConnection(app.config['log_db_config']) as cursor:
_SQL = """DELETE FROM log_tab WHERE id=%s;"""
cursor.execute(_SQL, (id,))
except ConnectionError as err:
app.logger.info('remove_log failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('remove_log failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('remove_log failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('remove_log failed with this error: ' + str(err))
def add_channel(list_from_request: tuple, list_for_log: tuple) -> None:
"""
Добавляет элемент в channel_tab.
:param list_from_request: данные из запроса (url_channel, status_code, length_content, title_channel, url_site).
:param list_for_log: данные для add_log.
:return: None.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """INSERT INTO channel_tab
(url_channel_c, status_code, length_content, title_channel, url_site)
VALUES
(%s, %s, %s, %s, %s);"""
cursor.execute(_SQL, (list_from_request[0],
list_from_request[1],
list_from_request[2],
list_from_request[3],
list_from_request[4],
))
except ConnectionError as err:
app.logger.info('add_channel failed with this error: ConnectionError ' + str(err))
except CredentialsError as err:
app.logger.info('add_channel failed with this error: CredentialsError.' + str(err))
except SQLError as err:
app.logger.info('add_channel failed with this error: SQLError ' + str(err))
except Exception as err:
app.logger.info('add_channel failed with this error: ' + str(err))
try:
t = Thread(target=db_log_mod.add_log, args=(list_for_log, 'add channel'))
t.start()
except Exception as err:
app.logger.info('Thread in add_channel failed with this error:' + str(err))
def add_user_to_db(login: str, password_hash: str) -> None:
"""
Добавляет юзера в users_tab.
:param login: логин юзера.
:param password_hash: хеш пароля.
:return: None.
"""
print("Проверка из add_user_to_db", login, password_hash)
try:
with DatabaseConnection(app.config['users_db_config']) as cursor:
_SQL = """INSERT INTO users_tab
(login, password_hash)
VALUES
(%s, %s)"""
cursor.execute(_SQL, (
login,
password_hash,
))
except ConnectionError as err:
app.logger.info(
'add_user_to_db failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'add_user_to_db failed with this error: CredentialsError.' +
str(err))
except SQLError as err:
app.logger.info('add_user_to_db failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('add_user_to_db failed with this error: ' + str(err))
def search_rss_by_url_item(url_item: str) -> bool:
"""
Для поиска по url_item в rss_tab.
Применяется для поиска элемента при добавлении новых данных, для избегания дублирования.
:param url_item: url конкретного rss item
:return: возращает True если находит элемент, и False если нет.
"""
try:
with DatabaseConnection(app.config['rss_db_config']) as cursor:
_SQL = """SELECT * FROM rss_tab WHERE url_item=%s;"""
cursor.execute(_SQL, (url_item, ))
contents = cursor.fetchall()
if not contents:
return False
else:
return True
except ConnectionError as err:
app.logger.info(
'search_rss_by_url_item failed with this error: ConnectionError ' +
str(err))
except CredentialsError as err:
app.logger.info(
'search_rss_by_url_item failed with this error: CredentialsError.'
+ str(err))
except SQLError as err:
app.logger.info(
'search_rss_by_url_item failed with this error: SQLError ' +
str(err))
except Exception as err:
app.logger.info('search_rss_by_url_item failed with this error: ' +
str(err))
