Connecting Sqlite3 to Telegram bot

Introduction

For many newcomers to Python development , the problem arises - how to connect the database? I myself faced such a problem at the beginning of development. The topic turned out to be quite simple, but there are many guides on the Internet that can be confusing. In this tutorial, I will tell you how to easily integrate a Sqlite3 database using the Telegram bot as an example .

Beginning of work

Python, . , . PyCharm, , SQLiteStudio .

. pyTelegramBotAPI ( - ).

pip install pyTelegramBotAPI
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

.py. :

import sqlite3
import telebot

bot = telebot.TeleBot("token")
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

sqlite3 — . "token" , BotFather. , .

@bot.message_handler(commands=['start'])
def start_message(message):
	bot.send_message(message.chat.id, ' ')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. " ", .

, .

SQLiteStudio. Database Add a database. Ctrl+O.

. . db, . , database. . . . Connect to the database. . : . . Add a table.

. test. (Ins). . , , NULL . id, INTEGER . . ID . .

user_id. INT, . user_name, STRING NULL. 2 user_surname username. , , .

.

. 2 .

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . , db, . , .

, .

def db_table_val(user_id: int, user_name: str, user_surname: str, username: str):
	cursor.execute('INSERT INTO test (user_id, user_name, user_surname, username) VALUES (?, ?, ?, ?)', (user_id, user_name, user_surname, username))
	conn.commit()
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , . user_id inst. test user_id. . . , . . .

@bot.message_handler(content_types=['text'])
def get_text_messages(message):
	if message.text.lower() == '':
		bot.send_message(message.from_user.id, '!      !')
    
    us_id = message.from_user.id
		us_name = message.from_user.first_name
		us_sname = message.from_user.last_name
		username = message.from_user.username
		
		db_table_val(user_id=us_id, user_name=us_name, user_surname=us_sname, username=username)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, id, Telegram, . , .

bot.polling(none_stop=True)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . "".

Now we can go to SQLiteStudio , and select the Data tab to see what happened:

As we can see, the data is already in the database. If for some reason they are not there, then update the database by clicking on the blue Update button

Here's the whole code:

import sqlite3
import telebot

bot = telebot.TeleBot("token")

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


def db_table_val(user_id: int, user_name: str, user_surname: str, username: str):
	cursor.execute('INSERT INTO test (user_id, user_name, user_surname, username) VALUES (?, ?, ?, ?)', (user_id, user_name, user_surname, username))
	conn.commit()


@bot.message_handler(commands=['start'])
def start_message(message):
	bot.send_message(message.chat.id, ' ')


@bot.message_handler(content_types=['text'])
def get_text_messages(message):
	if message.text.lower() == '':
		bot.send_message(message.chat.id, '!      !')
		
		us_id = message.from_user.id
		us_name = message.from_user.first_name
		us_sname = message.from_user.last_name
		username = message.from_user.username
		
		db_table_val(user_id=us_id, user_name=us_name, user_surname=us_sname, username=username)


bot.polling(none_stop=True)

      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Conclusion

In this article, we got a little familiar with sqlite3 and made a simple script for adding user information. This, of course, is not enough. Soon I will release a sequel, in which I will touch on the topic of deleting users and editing fields.

Thank you so much for reading. Hope it helped someone. Good luck to everyone and fewer bugs in the code!