Answer the question
In order to leave comments, you need to log in
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:
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 questionAsk a Question
731 491 924 answers to any question