september 29, 2023
Configuring a simple TG bot and connecting it to MS SQL Server database
The development of modern social networks and messengers provides many opportunities to create interactive applications for monitoring or processing database data that are convenient for developers and business users. In this post we will tell you how to connect MS SQL database to Telegram bot in a simple way.
Environment:
Windows 10
Python 3.11.4
1. Bot registration
1.1 You need to find the BotFather user, click start, you will see a list of commands, select /newbot
1.2 The application will ask you to enter a name, and then a name with the bot prefix.
Your bot is registered, it is necessary to save the token, by which the bot will access online.
1.3 To connect the bot to the group as an administrator, run the /setprivacy command. Select the desired bot, click disable.
The bot is ready, now it can be found in search
1.4 Create a group, add the bot to it and set it as an administrator
2. Creation of a virtual environment for the project
2.1 Create an empty folder «tg_bot», open it and run the PowerShell in it. Run the command:
python –m venv <environment name>
You will see the environment folder (isolated python with package manager)
2.1 Activate the environment by running the activate file. Run the activate file:
venv\Scripts\activate
2.2 Install the library for bot development by command:
pip install aiogram
2.3 Install the library for working with the database
pip install pypyodbc
After successful installation, you can close and check the libraries in the folder
Lib\site-packegis
3. Start the bot
3.1 Create the file tg_bot.py in the tg_bot directory
3.2 Create a command line script file (bat file) to launch the bot in the root directory and add the token to it
Open the bat-file with any editor and add the code:
To prevent the console from displaying the file’s service information
@echo off
Activate virtual environment (%~dp0 — get current environment)
call %~dp0tg_bot\venv\Scripts\activate
Enter the project folder
cd tg_bot
Define a variable with token
set TOKEN=Your TOKEN
Starting the project script
python tg_bot.py
To see script errors in the console
Let’s get to the project code. Let’s create a simple echo bot. Write the following code in the file tg_bot.py.
Import necessary libraries
from aiogram import Bot, types
from aiogram.dispatcher import Dispatcher
from aiogram.utils import executor
import os
Initialize the bot and dispatcher
bot = Bot(token=os.getenv(‘TOKEN’))
dp = Dispatcher(bot)
Write the message_handler decorator using the dp read message dispatcher
@dp.message_handler()
An asynchronous function (async def) echo_send is placed in the decorator, which will handle incoming messages (message events)
async def echo_send(message : types.Message):
The function will reply (answer) with the same message (message.text) when the message event occurs
await message.answer(message.text)
Start the bot, set skip_updates to True to not receive messages that came while the bot is offline
executor.start_polling(dp, skip_updates=True)
4. Add the database.
4.1 Create a test database DWL_BOT with one table_test for working with the bot:
To operate with MS SQL, we previously installed the pypyodbc library. Add the following code to work with the library:
Import pypyodbc library
import pypyodbc as po
Declare variables with server and database name
myServerName = ‘<Server name>’
myDataBaseName = ‘<DataBase name>’
Create a function that processes incoming, where query is the input parameter
def sqlcon(query=’Select * from table_test’):
Specify the connection to the database, where we specify the driver (Driver), server name and database name, if necessary, you can add a user name and password.
connection = po.connect(‘Driver={SQL Server};’
‘Server=’ + myServerName + ‘;’
‘Database=’ + myDataBaseName + ‘;’
)
Initialize the cursor containing database data
cursor = connection.cursor()
To handle errors we use the try — except construct
try:
Send a query to the database
cursor.execute(query)
Write all answers pointed to by the cursor to the results variable
results = cursor.fetchall()
Closing the connection to the database
connection.close()
Output the result
return results
except:
If there is an error, display a message
return ‘Error, check the request‘
We replace the response in the last part of the code with the result of the function output:
await message.answer(sqlcon(message.text))
Then you can refine the interface and improve the functionality as you like, using all the power of telegram, python and sql server
