BhtxJGJv

1import telebot
2import sqlite3
3from sqlite3 import Error
4from time import ctime
5
6
7def post_sql_query(sql_query):
8    with sqlite3.connect('my.db') as connection:
9        cursor = connection.cursor()
10        try:
11            cursor.execute(sql_query)
12        except Error:
13            pass
14        result = cursor.fetchall()
15        return result
16
17
18def create_tables():
19    users_query = '''CREATE TABLE IF NOT EXISTS USERS 
20                        (user_id INTEGER PRIMARY KEY NOT NULL,
21                        username TEXT,
22                        first_name TEXT,
23                        last_name TEXT,
24                        reg_date TEXT);'''
25    msq_query = '''CREATE TABLE IF NOT EXISTS MESSAGES
26                        (user_id INTEGER NOT NULL,
27                        msg TEXT NOT NULL,
28                        insert_date TEXT NOT NULL,
29                        PRIMARY KEY (user_id, msg, insert_date));'''
30    post_sql_query(users_query)
31    post_sql_query(msq_query)
32
33
34def register_user(user, username, first_name, last_name):
35    user_check_query = f'SELECT * FROM USERS WHERE user_id = {user};'
36    user_check_data = post_sql_query(user_check_query)
37    if not user_check_data:
38        insert_to_db_query = f'INSERT OR IGNORE INTO USERS (user_id, username, first_name,  last_name, reg_date) ' \
39                             f'VALUES ({user}, "{username}", "{first_name}", "{last_name}", "{ctime()}");'
40        post_sql_query(insert_to_db_query)
41        return f'Добро пожаловать в чат, {first_name}!'
42    else:
43        return f'Вы уже зарегистрированы, {first_name}!'
44
45
46def write_msg(user, msg):
47    user_check_query = f'SELECT * FROM MESSAGES WHERE user_id = {user} ' \
48                       f'AND msg = "{msg}";'
49    user_check_data = post_sql_query(user_check_query)
50    if not user_check_data:
51        insert_to_db_query = f'INSERT OR IGNORE INTO MESSAGES (user_id, msg, insert_date) ' \
52                             f'VALUES ({user}, "{msg}", "{ctime()}");'
53        post_sql_query(insert_to_db_query)
54
55
56def ask_user_id(message):
57    w_user, user = message.text, message.from_user.id
58    msg_query = f'SELECT u.username, m.msg FROM MESSAGES m ' \
59                f'LEFT JOIN USERS u ' \
60                f'ON u.user_id = m.user_id ' \
61                f'WHERE m.user_id = {w_user} ORDER BY insert_date DESC LIMIT 10;'
62    msg = post_sql_query(msg_query)
63    for m in msg:
64        username, text = m
65        bot.send_message(user, f'{username} - {text}')
66
67
68
69token_test = ""
70bot = telebot.TeleBot(token_test)
71admins = [111, 222]
72
73create_tables()
74
75
76@bot.message_handler(commands=['start'])
77def start(message):
78    answer = register_user(message.from_user.id, message.from_user.username,
79                           message.from_user.first_name, message.from_user.last_name)
80    bot.send_message(message.from_user.id, answer)
81
82
83@bot.message_handler(commands=['show'])
84def show(message):
85    if message.from_user.id in admins:
86        ask_question = bot.send_message(message.from_user.id,
87                                        'Введите ID пользователя для отображения последних 10 сообщений ')
88        bot.register_next_step_handler(ask_question, ask_user_id)
89    else:
90        bot.send_message(message.from_user.id, 'Доступ запрещён!')
91
92
93@bot.message_handler(func=lambda m: True)
94def catch_all(message):
95    if not message.text.startswith('/'):
96        write_msg(message.chat.id, message.text)
97
98
99if __name__ == "__main__":
100    try:
101        bot.polling(none_stop=True)
102    except Exception as Error:
103        print(Error)
104