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"

Solution to MySQL Connector Issue with Python

After spending hours troubleshooting, I finally found the solution to my problem. I'm sharing it here so that others facing the same issue can benefit.

Step 1: Modify the Connection String

Try adding use_pure=True to your MySQL connection string:

import mysql.connector

conn = mysql.connector.connect(
    host="your_host",
    user="your_user",
    password="your_password",
    database="your_database",
    use_pure=True
)

If this resolves the issue, read on for an explanation.

Why Does This Work?

By default, MySQL Connector/Python attempts to use the C extension libmysqlclient for better performance. However, in some cases—especially on certain platforms or specific Python environments—the C extension may not function correctly due to:

• A missing or incompatible libmysqlclient library.
• Issues related to Python version compatibility or platform-specific constraints.

Setting use_pure=True forces MySQL Connector/Python to use its pure Python implementation instead of the C extension, bypassing any related issues.

For further details, follow the official MySQL Connector/Python documentation: MySQL Connector/Python Connection Arguments.

Alternative Solution: Install libmysqlclient

If you prefer to use the C extension, ensure libmysqlclient is installed and accessible:

1. Download libmysqlclient from this offical link.
2. Add the library path to your system's PATH environment variable.
3. Check if libmysqlclient already exists in your MySQL installation folder. If it does, simply add its location to PATH instead of downloading it.

This should help resolve the issue while maintaining the performance benefits of the C extension.