A test project to showcase one of potential implementations of SAP SQL Anywhere RDMS in chat-bot development.
Click to expand
Asynchronous bot for Telegram messaging app utilizing a SAP (Sybase) SQL Anywhere RDMS and Computer Vision elements. It's implied that a machine this bot runs on is connected to a webcam and has got a SAP SQL Anywhere 17 database running. The bot is able to send its users specific messages based on a QR-code the webcam captures from its live video feed. This particular code simulates delivery notification system but via an instant messenger (Telegram), not e-mail. For the details, please see the comment block at the beginning of main.py file. We also tried to thoroughly document the source code, so feel free to browse it.
- Asynchronous HTTP requests
- Detection and decoding QR-codes on the fly
- Detailed and customizable logging system
- Scalability and low imprint on hardware
- Easily repurposable for different tasks involving QR-codes.
- sqlanydb as a driver for SAP SQL Anywhere RDMS
- AIOgram as an asynchronous framework for the Telegram Bot API
- open-cv and numpy for the computer vision portion
- pyzbar for QR-code decoding
- loguru for extensive logging
- dotenv for environment variables setup.
This project is featured and meticulously explained in several articles:
- (:us:) On Blogs.SAP.com: Chat-bots with SAP SQL Anywhere,
- (:ru:) On Habr.com: Как автоматизировать оповещения о статусе заказов через Telegram-бота, Computer Vision и SQL Anywhere.
It also got hosted as a sample under the official SAP Samples repository. Main development has been moved there.
The full installation process consists of several steps involving different pieces of software. There's nothing complicated, but we'll try to go through this process step-by-step, explaining it as clearly as possible, so hopefully you'll end up with a minimal working example.
This is merely an example of a possible technical integration of SAP SQL Anywhere RDMS into a Python-based project. The code of this particular sample heavily relies on Telegram messaging app and its Bot API to function properly. By proceeding, running and using the sample's code, the user becomes
- fully responsible for adherence to Telegram's Terms of Service as well as acceptance of Telegram's Privacy Policy
- fully aware of the respective privacy regulations.
This sample is also provided "as-is" without any guarantee or warranty that raised issues will be answered or addressed in future releases.
Before proceeding, please ensure that all of the following conditions are met:
- Your device has got a webcam connected
- You have got a Telegram account
- Python 3.7 or later is installed on your system
- SAP SQL Anywhere 17 and SQL Central are installed on your system
- You have a basic understanding of the command line.
First, clone this repository:
git clone https://github.com/gevartrix/qr-notify-bot.gitChange directory to this project's root folder. Create and activate a virtual environment (in this case, running on Windows) and install the required modules:
python -m venv venv
venv\Scripts\activate
python -m pip install -r requirements.txtYou'll need to know your Telegram ID to store it in the database, so later the bot will be able to send you notifications. Your Telegram ID is just an integer, the quickest way to acquire it is via @MyIDBot: open it in your Telegram app, start it and send it the /getid command. It should reply with your ID (say, 123456789).
You should also create your own Telegram bot. To do that, search for the @BotFather Telegram bot and enter the /newbot command to create a new bot. Follow the instructions and provide a screen-name and a username for your bot. The username must, however, be unique and end with "bot"; the screen-name can be whatever you like. You should then receive a message with a new API token generated for your bot (say, 11111:XXXXXXXXXXX). Now you can find your newly created bot on Telegram based on the username you gave it.
The bot interprets QR-codes on the webcam feed as encoded addresses in our model. Let's turn an address into a QR-code and print it, so we can show it to the webcam later. The QR-code below encodes WDF 01 BU04 Dietmar-Hopp-Allee 16 69190 Walldorf:
You can download it by clicking it and print it on a white sheet of paper or just open it on your smartphone. Alternatively, you may encode your preferred address online.
Now when you've got every piece of data, you can create a database and fill all required columns. First, create an additional folder (in this case, named db) inside this project's root folder:
mkdir dbCreate a database file using dbinit (in this case, named orders.db; admin as UID and YourPassword as password (change it, it's just an example)):
dbinit -dba admin,YourPassword -p 4k -z UTF8 -ze UTF8 -zn UTF8 db/orders.dbNow you've got a database file orders.db located in the db folder of this project (you may store this database file wherever you'd like). Open SQL Central and proceed with the following steps:
- right-click on "SQL Anywhere 17" and hit "Connect...",
- fill the "User ID" and "Password" fields with the same values you provided to
dbinit(in this case,adminandYourPasswordrespectively), - under "Action" choose "Start and connect to a database on this computer",
- provide full path to the database file you've just created (in this case, it's
full/path/to/this/project/db/orders.db) and hit "Connect".
You're connected to the SQL Anywhere database and can interact with it. Right-click anywhere to open the Interactive SQL window, so you may execute SQL queries in the database.
First, create a table of orders (in our case, named Orders):
CREATE TABLE Orders (
id UNSIGNED INT PRIMARY KEY NOT NULL IDENTITY,
product NVARCHAR(24) NOT NULL,
model NVARCHAR(20),
price DECIMAL(10,2) NOT NULL,
amount UNSIGNED INT NOT NULL DEFAULT 1,
weight DECIMAL(8,3) NOT NULL,
first_name NVARCHAR(16) NOT NULL,
last_name NVARCHAR(20),
address NVARCHAR(48) NOT NULL,
telegram_id UNSIGNED INT NOT NULL,
timezone NVARCHAR(16) DEFAULT 'UTC',
locale NVARCHAR(5) DEFAULT 'en_US'
);Then you can add an example order record to test the bot:
INSERT INTO "admin"."Orders"(product, model, price, weight, first_name, last_name, address, telegram_id, timezone) VALUES (
'Lenovo Thinkpad',
'X220',
150.00,
1.725,
'Jon',
'Doe',
'WDF 01 BU04 Dietmar-Hopp-Allee 16 69190 Walldorf',
123456789,
'Europe/Berlin'
);where WDF 01 BU04 Dietmar-Hopp-Allee 16 69190 Walldorf is the address encoded in the QR-code you printed by following the "QR-coding the address" section, and 123456789 is your Telegram ID sent by @MyIDBot from the "Getting Telegram-related Data" section. Obviously, you may customize other values however you like.
Make sure to close the Interactive SQL window afterwards, as it blocks query execution from any other source.
For the sake of convenience we store all required environment variables in a .env file. This repository contains a .env.dist file filled with dummy data in the root folder, so you'll need to copy it to .env file and change its values, as it's currently preset to the example values.
You'll absolutely have to set the PROD_BOT_TOKEN variable to the API token sent to you by @BotFather, so it looks like this: PROD_BOT_TOKEN="11111:XXXXXXXXXXX".
The sqlanydb module also requires the SQLANY_API_DLL variable to be set to the full path of dbcapi.dll. Unfortunately, SQL Anywhere doesn't create this variable automatically upon installation anymore, hence you have to specify it manually. On Windows this path is usually C:\Program Files\SQL Anywhere 17\Bin64\dbcapi.dll. However, if you run the 32-bit version of Python, you should change Bin64 to Bin32 in the path above.
So, if you're using the 64-bit version of Python on Windows and all our example values, the variables inside your .env file should end up looking like this:
SQLANY_API_DLL="C:\Program Files\SQL Anywhere 17\Bin64\dbcapi.dll"
PROD_BOT_TOKEN="11111:XXXXXXXXXXX"
PROD_DB_USER="admin"
PROD_DB_PASSWORD="YourPassword"
PROD_DB_TABLENAME="Orders"You may also set the DEV variables using different values meant for testing, if you're going to run the bot with the --dev flag.
Make sure that you still have the virtual environment activated, the QR-code printed, your webcam connected and the SQL Anywhere database connection established. Start the bot by running
python main.pyin the project's root directory. After the Updates were skipped successfully log message, a window with your webcam's video stream should appear. Search for the bot you've created with @BotFather and start it. If everything is right, the bot should respond by offering you to select the language.
So now, whenever you show the QR-code encoding your address to your webcam, the bot should alert you with a notification. With the example record from our table, the notification should look like this:
Hello, Jon!
As of 25/10/2020 18:47:19 CET, your order 1 has arrived to our base.
We are going to deliver it to your address "WDF 01 BU04 Dietmar-Hopp-Allee 16 69190 Walldorf" no later than in 3 days.
Product Details:
Product: Lenovo Thinkpad
Model: X220
Price: €150.00
Amount: 1
Weight: 1.725 kg
ID: 1
You may configure the camera UI via the CLI arguments. To see all configurable options of the bot, run python main.py --help.
If you'd like to contribute to this little project, please follow these steps:
- Fork this repository.
- Create a branch:
git checkout -b feature/foo. - Make your changes and commit them:
git commit -am 'Add foo feature'. - Push your changes to the branch:
git push origin feature/foo. - Create a new pull request.
Pull requests are warmly welcome!
We try our best sticking to the PEP 8 Style Guide, using type hints (PEP 484) and providing descriptive docstrings adhering to the Google Python Style Guide. So we check that our code is formatted properly using Flake8 and black code formatter; the type checking is handled by mypy.
Install these modules in your virtual environment by running
python -m pip install -r dev-requirements.txtso you can either run the checks manually, or set up this great pre-commit that will be running it for you.
You may also check out the configuration we use in pyproject.toml and setup.cfg.
Copyright (c) 2019-2021 Artemy Gevorkov. This project is licensed under the Apache Software License, version 2.0.