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.


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:


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 


3. Start the bot
3.1 Create the file 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


Starting the project script


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
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

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


Send a query to the database

Write all answers pointed to by the cursor to the results variable
        results = cursor.fetchall()

Closing the connection to the database

Output the result
        return results


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

For more information, please get in touch here:

Contact Form main