N
N
Nusup2020-08-30 15:57:06
MySQL
Nusup, 2020-08-30 15:57:06

How to display data from the database (MySQl) in telegrambot (PyTelegramBotApi) to the inline keyboard?

The logic of the project is:
1) Sending the location to the bot
2) Displaying in the inline keyboard the names of the points closest in coordinates (10m = 0.01km)


Tell me how to proceed:

spoiler
import telebot
import mysql.connector
from mysql.connector import MySQLConnection, Error
from telebot import types, TeleBot
from telebot.types import InlineKeyboardMarkup, InlineKeyboardButton, Message
from math import radians, cos, sin, asin, sqrt

bot = telebot.TeleBot("")

# Подключение к MySQL
db = mysql.connector.connect(
    host="",
    user="",
    passwd="",
    port="",
    database=""
)
cursor = db.cursor()

@bot.message_handler(commands=['start'])
def send_welcome(message):

    # Кнопка отправки местоположения
    geo=types.ReplyKeyboardMarkup(one_time_keyboard=True)
    geo_btn = types.KeyboardButton(text='Местоположение', request_location=True)
    geo.add(geo_btn)

    msg = bot.send_message(message.chat.id, "Отправте текущее местоположение:", reply_markup=geo)
    bot.register_next_step_handler(msg, process_geo_step)

def process_geo_step(message):
    # Запрос к БД
    def iter_row(cursor, size=10):
        while True:
            rows = cursor.fetchmany(size)
            if not rows:
                break
            for row in rows:
                yield row

    # Запрос по longitude
    def query_with_lon():
        try:
            dbconfig = read_db_config()
            conn = MySQLConnection(**dbconfig)
            cursor = conn.cursor()

            cursor.execute("SELECT longitude FROM geo")

            for row in iter_row(cursor, 10):
                print(row)

        except Error as e:
            print(e)

        finally:
            cursor.close()
            conn.close()

    # Запрос по latitude
    def query_with_lat():
        try:
            dbconfig = read_db_config()
            conn = MySQLConnection(**dbconfig)
            cursor = conn.cursor()

            cursor.execute("SELECT latitude FROM geo")

            for row in iter_row(cursor, 10):
                print(row)

        except Error as e:
            print(e)

        finally:
            cursor.close()
            conn.close()

    # Преобразовать переменные
    lon1 = message.location.longitude
    lat1 = message.location.latitude
    lon2 = query_with_lon
    lat2 = query_with_lat

    # Преобразовать десятичные градусы в радианы
    lon1, lat1, lon2, lat2 = map(radians, (lon1, lat1, lon2, lat2))
    dlon = lon2 - lon1
    dlat = lat2 - lat1
    a = sin(dlat / 2) ** 2 + cos(lat1) * cos(lat2) * sin(dlon / 2) ** 2
    c = 2 * asin(sqrt(a))
    km = 6367 * c
    if km == 0.01:
        return km

if __name__ == '__main__':
    bot.polling(none_stop=True)

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question