[Python] Why mysql.connector is not working, although pymysql is working to connect database...

I tried to connect my MySQL database from a Python script using mysql.connector, but could not be able to connect. Then I tried to connect using pymysql and the db was connected successfully. Why am I unable to connect using MySQL Connector, even though I can connect using pymysql?

I wrote down the following code on VS code editor and the filename is dbconnector.py

import mysql.connector
from mysql.connector import Error

try:
print("Trying to connect to the database...")

mydb = mysql.connector.connect(
host="127.0.0.1",
user="root",
password="s12345678"
)

if mydb.is_connected():
print("Database connected successfully")
else:
print("Failed to connect to the database")

except Error as e:
print(f"Error: {e}")


Then I checked and found that the server is running and user and password is correct. Then I install cryptography using pip install cryptography. My Python version is 3.12.6 and MySQL Server version is 8.0.39 which is okay according to mysql connector official documentation.

I grant all permission by running:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 's12345678' WITH GRANT OPTION;
FLUSH PRIVILEGES;


Then I changed the authentication mode for root:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 's12345678';
FLUSH PRIVILEGES;


I make sure that MySQL is running on correct port by running:

netstat -an | findstr "3306"

Continue reading...