Can't transfer data from telegram bot to database table

Hello, can’t transfer data to database on myPHP admin.

I have a PyCharm file with code for telegram and HTML code located on github repository. Database located on local server using myPHP admin.

Here is code:

from aiogram import Bot, Dispatcher, executor, types
from aiogram.types.web_app_info import WebAppInfo
import mysql.connector
from mysql.connector import Error

bot = Bot(‘BotToken’)
dp = Dispatcher(bot)

def create_connection(host_name, user_name, user_password, database_name):
connection = None
try:
connection = mysql.connector.connect(
host=host_name,
user=user_name,
passwd=user_password,
database=database_name,
collation=‘utf8mb4_general_ci’ # Set the collation to utf8mb4_general_ci
)
print(“Connection to MySQL DB successful”)
except Error as e:
print(f"The error ‘{e}’ occurred")

return connection

@dp.message_handler(commands=[‘start’])
async def start(message: types.Message):
markup = types.ReplyKeyboardMarkup(resize_keyboard=True)
markup.add(types.KeyboardButton(‘OpenWebPage’, web_app=WebAppInfo(url=‘linkToHTML’)))
await message.answer(‘Want To buy?’, reply_markup=markup)

@dp.message_handler(lambda message: message.text == ‘Buy’)
async def buy(message: types.Message):
markup = types.ReplyKeyboardMarkup(resize_keyboard=True)
markup.add(types.KeyboardButton(‘name’))
markup.add(types.KeyboardButton(‘phone’))
markup.add(types.KeyboardButton(‘email’))

# Save the email to the database
# Get the name and phone values from the previous messages
name = message.reply_to_message.reply_to_message.text.split(": ")[1]
phone = message.reply_to_message.reply_to_message.reply_to_message.text.split(": ")[1]
email = message.reply_to_message.reply_to_message.reply_to_message.text.split(": ")[1]

insert_customer_data(name=name, phone=phone, email=email)

def insert_customer_data(name, phone, email):
connection = None
cursor = None
try:
connection = create_connection(“localhost”, “root”, “”, “telegram_bot_name_phone_email”)
cursor = connection.cursor()
query = “INSERT INTO customers (name, phone, email) VALUES (%s, %s, %s)”
data = (name, phone, email)
print(f"Data to be inserted: {data}")
cursor.execute(query, data)
connection.commit()
print(“Data inserted successfully”)

except Error as e:
    print(f"The error '{e}' occurred")

except Exception as ex:
    print(f"An unexpected error occurred: {ex}")

finally:
    if cursor is not None:
        cursor.close()
    if connection is not None and connection.is_connected():
        connection.close()

if name == ‘main’:
connection = create_connection(“localhost”, “root”, “”, “NameDatabase”)

if connection is not None:
    create_table_query = """
    CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        phone VARCHAR(8) NOT NULL,
        email VARCHAR(50) NOT NULL
    )
    """

    cursor = connection.cursor()
    cursor.execute(create_table_query)
    connection.commit()
    connection.close()

    executor.start_polling(dp)
else:
    print("Unable to establish a connection to the MySQL server.")

So bot is working, I am receiving message back from him:
name: John
phone: 29559555
emai: John@gmail.com

Please help, I am already 3 days can’t find the answer.

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.