C
C
codexim2022-04-10 10:50:12
Python
codexim, 2022-04-10 10:50:12

How to add a user id and the message they entered to a SQLite3 database?

I want the user id and the message entered by him to be added to the sqlite database. Moreover, this message should be overwritten every time the bot is launched in the telegram. How can this be implemented?

import sqlite3
import telebot

bot = telebot.TeleBot("token")

conn = sqlite3.connect('database.db',check_same_thread=False)
cursor = conn.cursor()


def db_table_val(us_id,text):
    cursor.execute('INSERT INTO test (us_id, text) VALUES (?, ?)',
                   (us_id,text))
    conn.commit()

@bot.message_handler(content_types=['text'])
def get_text_messages(message):
    if message.text.lower() == '/reg':
        bot.send_message(message.chat.id, 'success')

        us_id = message.from_user.id
        text = message.text

        db_table_val(us_id,text)

bot.infinity_polling()

in short: id should be saved and the message should be overwritten every time
this code gives an error when you run / reg a second time
2022-04-10 12:41:32,445 (__init__.py:615 MainThread) ERROR - TeleBot: "Infinity polling exception: UNIQUE constraint failed: test.us_id"
2022-04-10 12:41:32,446 (__init__.py:617 MainThread) ERROR - TeleBot: "Exception traceback:
Traceback (most recent call last):
  File "C:\Users\Maxim\PycharmProjects\pythonProject\venv\lib\site-packages\telebot\__init__.py", line 611, in infinity_polling
    self.polling(none_stop=True, timeout=timeout, long_polling_timeout=long_polling_timeout,
  File "C:\Users\Maxim\PycharmProjects\pythonProject\venv\lib\site-packages\telebot\__init__.py", line 658, in polling
    self.__threaded_polling(non_stop, interval, timeout, long_polling_timeout, allowed_updates)
  File "C:\Users\Maxim\PycharmProjects\pythonProject\venv\lib\site-packages\telebot\__init__.py", line 720, in __threaded_polling
    raise e
  File "C:\Users\Maxim\PycharmProjects\pythonProject\venv\lib\site-packages\telebot\__init__.py", line 680, in __threaded_polling
    self.worker_pool.raise_exceptions()
  File "C:\Users\Maxim\PycharmProjects\pythonProject\venv\lib\site-packages\telebot\util.py", line 135, in raise_exceptions
    raise self.exception_info
  File "C:\Users\Maxim\PycharmProjects\pythonProject\venv\lib\site-packages\telebot\util.py", line 87, in run
    task(*args, **kwargs)
  File "C:\Users\Maxim\PycharmProjects\pythonProject\амао.py", line 23, in get_text_messages
    db_table_val(us_id,text)
  File "C:\Users\Maxim\PycharmProjects\pythonProject\амао.py", line 11, in db_table_val
    cursor.execute('INSERT INTO test (us_id, text) VALUES (?, ?)',
sqlite3.IntegrityError: UNIQUE constraint failed: test.us_id

I created a table for the database in the sqlite studio program itself, this is how the parameters for the columns look like
62528c0a85a98217606455.png

Answer the question

In order to leave comments, you need to log in

2 answer(s)
S
szjyakgf, 2022-04-10
@codexim

Because your bot writes the second time the message and id. But you made the id column unique. Therefore it throws an error. You need to make a check: if there is such an id in the database, then write only the message, without the id.

A
Alexander Nesterov, 2022-04-10
@AlexNest

I would advise you to read, for starters, something on database design and sql .
It just feels like you are poking at random, in the hope that something will come of it. At the first message of the user, I suppose everything goes fine. And the second time it gives an error:

sqlite3.IntegrityError: UNIQUE constraint failed: test.us_id
.
What does it mean (relatively free translation).
sqlite3.IntegrityError: ' UNIQUE value' condition not met: test.us_id
.
The word UNIQUE is specially written in capital letters. In addition, you can see in the screenshot that there is a "tick" in the uniqueness column. Therefore, with this table schema, one user has one message.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question