How do you keep the time counter from the date in the table? PytelegramBotApi, Python, SQLite

[amarantha]

Welcome! There's a question in writing Telegram bot'a on the PyTelegramBotAPI library with the SQLite database.

There's a challenge. User to command /remont (extent) - Category Answers the question, "What exactly? - Category Answers the question, "What malfunction?" It goes on to the SQLite database in line: user_id, what_broke, defect, date

user_id and date received in such a way:

tconv = lambda x: time.strftime("%H:%M:%S %d.%m.%Y", time.localtime(x))
id = message.from_user.id

date = tconv(message.date)

The question is:
What are the ways to implement the counter or timer that saw the date of the malfunction in the user ' s table and every two days informed the group (to which it was added) that the application of such a failure was not corrected?

Or when the application is default, it takes seven days and every day the unit is removed and the alert comes (but in this case, when the beans are rebooted, everything will be erased, which would not be desirable)

As far as I've learned, asynchronicity is also necessary, so that the bot is not just considered, and that's what it means:

bot = telebot.AsyncTeleBot('<Token>')

I would be very grateful for the examples, or at least for anyone to suggest what structure the process should be.
A little more detailed, it'll be clear to professionals, and I can't figure it out.

[guilherme]

I think, as a primary decision, you could do something common to all users:

Write a scrip, which will be cyclical every day of all records in the database and compare the current date and time with the application and then send notifications to the required users.

In the future, you could work with me. https://ru.wikipedia.org/wiki/Cron so the server can be responsible for activating the violin.

If you do not want all users to be notified at one time, the crypt could be started periodically and watched exactly 24 hours after the malfunction has been reported, but for this, you might need to establish an additional table to record the notifications already sent.

The initial violin could look like this:

import time
import sqlite3
from sqlite3 import Error
Функция для отправки сообщения через Telebot
def send_message(chat_id, text):

import telebot

bot = telebot.TeleBot('1234')

bot.send_message(chat_id, text)
Функция для проверки прохождения суток от полученной даты
def check_date(date):

import datetime

# получаем текущую дату

now = datetime.datetime.now()

# преобразуем дату из строки в дату

date_from_sql = datetime.datetime.strptime(date, '%Y-%m-%d %H:%M:%S')

# получаем время в секундах между датами

delta = now - date_from_sql

# преобразуем время в секундах в часы, минуты и секунды

delta_hours = delta.seconds // 3600

delta_minutes = (delta.seconds // 60) % 60

delta_seconds = delta.seconds % 60

# выводим время в часах, минутах и секундах

print(f'С записи прошло {delta.days} дней {delta_hours} часов, {delta_minutes} минут и {delta_seconds} секунд')

# проверяем прошли ли сутки от записи в sqlite

if delta.days > 0:

print ('прошли сутки')
Функция для подключения к БД
def create_connection(db_file):
conn = None
try:
    conn = sqlite3.connect(db_file)
except Error as e:
    print(e)

return conn

Основная функция для сравнения дат и отправки уведомлений
def select_yesterday_tasks(conn):
cur = conn.cursor()

# Первый вариант (тогда доп. проверки через check_date не нужны): 
# Получаем все записи из таблицы record где дата записи больше текущей даты на день
# cur.execute("SELECT * FROM record WHERE date > date('now', '-1 day')")

# Второй вариант: с использованием свой проверки даты
# Получаем все записи из таблицы record
cur.execute("SELECT * FROM record")

rows = cur.fetchall()

for row in rows:
    date = row[1] # находим значение колонки даты для конкретной записи
    if check_date(date): # Если прошли сутки, то отправляем уведомление:
        send_message(row[0], 'Прошли сутки!') # Отправляем сообщение через Telebot

def main():

# Здесь указываете местоположение вашей БД

database = r"C:\sqlite\db\pythonsqlite.db"
# Подключаемся к БД
conn = create_connection(database)
with conn:
    while True:
    # Указываем время ожидания в секундах (1 день)
        time.sleep(86400)
        # Вызываем функцию получения записей и отправки уведомлений
        select_yesterday_tasks(conn, 1)

if name == 'main':

main()