How to make a database for telegram chats?

Question

Python

I took SQLITE for the database - after all, as far as I know, it is the simplest.

It is necessary that when the bot is added to a new chat, it creates either a new table or a new database - I can’t even imagine how it will work.
When adding a bot, it must create a new table that will contain the participants of this particular conversation, any settings, and so on.

How can this be done? I tried to draw this on a piece of paper - I still have no ideas. I can't even imagine what it will look like.

Answer 1

No need to create new tables and even more so the database. All this is done in one table.

| ID записи | ID чата | ID юзера | Прочие данные      | ...
|         1 |     546 |    23456 | Бла, бла, тут со.. | ...
|         2 |     546 |    23456 | Привет, как дела?  | ...
|         3 |    9786 |   786444 | О, есть че?        | ...

And if, indeed, there is a desire to really create a whole table for the chat, then there are no problems here either.
You write a method that creates a table in the database with the name including the chat ID. For example, "chat23456".

CREATE TABLE "chat" + chatId ( ID INTEGER PRIMARY KEY, UserId INTEGER, ... );

By this name you will address later. tableName = "chat" + 23456;

Answer 2

Why a separate table for each chat?
Create one table

CREATE TABLE [dbo].[tab_query](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [UpdateId] [int] NOT NULL,
  [UpdateType] [varchar](100) NULL,
  [Message_ID] [int] NOT NULL,
  [first_name] [varchar](100) NULL,
  [last_name] [varchar](100) NULL,
  [chat_id] [int] NOT NULL,
  [text] [varchar](400) NOT NULL,
  [dateins] [datetime] NOT NULL,

Index by chat_id field,
query by condition

SELECT * FROM  [dbo].[tab_query] WHERE chat_id = <вставить чат ид>

Also it is not necessary to fence a heap of tables.

Answer 3

You can try the lightweight Tinybd library