D
D
ddgryaz2020-11-12 18:29:23
MySQL
ddgryaz, 2020-11-12 18:29:23

How to check user uniqueness in Telegram bot?

Good day! Tell me, you need to add all bot users to the table.
(I use - pytelegrambotapi) Implemented it like this:

conn = mysql.connector.connect(user='user', password='password',
                               host='localhost', database='xxx')
cursor = conn.cursor(buffered=True)

@bot.message_handler(commands=['start'])
def first(message):
    global user_id
    global nickname
    global fn
    global sn
    user_id = message.from_user.id
    nickname = message.from_user.username
    fn = message.from_user.first_name
    sn = message.from_user.last_name
    add_allusers = (
        'INSERT INTO allusers3 (nickname, name, second_name, telegram_id, dt) VALUES (%(nn)s, %(fn)s, %(sn)s, %(t_id)s, %(dtn)s)')
    data_allusers = {
        'nn': nickname,
        'fn': fn,
        'sn': sn,
        't_id': user_id,
        'dtn': datetime.datetime.now(),
    }
    cursor.execute(add_allusers, data_allusers)
    conn.commit()


How to check for uniqueness by the telegram_id column?
I tried something like this, I did not get the result:
if cursor.execute('SELECT EXISTS(SELECT telegram_id FROM allusers3 WHERE telegram_id = ' + str(user_id) + ')'):
        add_allusers = (
            'INSERT INTO allusers3 (nickname, name, second_name, telegram_id, dt) VALUES (%(nn)s, %(fn)s, %(sn)s, %(t_id)s, %(dtn)s)')
        data_allusers = {
            'nn': nickname,
            'fn': fn,
            'sn': sn,
            't_id': user_id,
            'dtn': datetime.datetime.now(),
        }
        cursor.execute(add_allusers, data_allusers)
        conn.commit()
    else:
        pass


That is, it is necessary to check if there is such an ID in the table, then do nothing. If not, then write the user to the table

Answer the question

In order to leave comments, you need to log in

2 answer(s)
I
Ivan, 2020-11-12
@ddgryaz

cursor.execute(f'SELECT * FROM allusers3 WHERE t_id = {t_id}')
if cursor.fetchall() == []:
...
#не найдено, тут вставляешь
else:
#найдено
...

S
shurshur, 2020-11-13
@shurshur

The most compact option:
INSERT IGNORE INTO allusers3 ...
In this case, telegram_id must be PRIMARY KEY or UNIQUE. IGNORE does exactly what it's supposed to do - it ignores the insert if it violates an integrity constraint.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question