mysqlclient

Warnings

• breaking Installation via `pip install mysqlclient` often fails without pre-installed system-level MySQL client development headers, a C/C++ compiler, and `pkg-config` (on POSIX systems). This is because `mysqlclient` is a C extension that needs to compile against native libraries.
  Fix: Before installing `mysqlclient`, install the necessary system packages. For Debian/Ubuntu: `sudo apt-get install python3-dev default-libmysqlclient-dev build-essential pkg-config`. For Red Hat/CentOS: `sudo yum install python3-devel mysql-devel pkgconfig`. For macOS: `brew install mysql pkg-config`. For Windows, ensure 'Build Tools for Visual Studio' and 'MariaDB Connector/C' are installed.
• breaking In version 2.2.0, `mysqlclient` switched from using `mysql_config` to `pkg-config` for discovering compiler and linker flags during installation. If `pkg-config` is not installed or configured correctly, compilation will fail.
  Fix: Ensure `pkg-config` is installed on your system. For Debian/Ubuntu: `sudo apt-get install pkg-config`. For Red Hat/CentOS: `sudo yum install pkgconfig`.
• breaking The `Cursor.executemany()` method's argument format changed in version 2.2.0. It now expects a sequence of tuples, even for single-value inserts, instead of a sequence of scalar values.
  Fix: Update your `executemany` calls. For example, change `executemany("INSERT INTO t (data) VALUES (%s)", [1, 2, 3])` to `executemany("INSERT INTO t (data) VALUES (%s)", [(1,), (2,), (3,)])`.
• deprecated The `passwd` and `db` keyword arguments in the `MySQLdb.connect()` function are deprecated. They will be removed in future versions.
  Fix: Use `password` and `database` keyword arguments instead.
• gotcha As of v2.2.8, `mysqlclient` offers experimental support for free-threaded Python (importing `MySQLdb` doesn't enable the GIL). However, the library explicitly states that it *does not* support simultaneous operations on a single `Connection` object from multiple threads concurrently. Doing so will result in undefined behavior.
  Fix: Ensure that each `Connection` object is used by only one thread at a time. If multi-threading is required, establish a separate `Connection` for each thread or use a connection pooling mechanism that manages thread-safe access.
• deprecated The `Connection.shutdown()` and `Connection.kill()` methods are deprecated, as the underlying MySQL C API functions (`mysql_shutdown()` and `mysql_kill()`) were removed in MySQL 8.3. These methods will emit a `DeprecationWarning` in future versions.
  Fix: Avoid using `Connection.shutdown()` and `Connection.kill()`. If server administration is required, use dedicated MySQL administration tools or SQL commands executed via a standard cursor.
• gotcha On macOS, certain versions of `mysql-connector-c` installed via Homebrew or official packages may have incorrect default configuration options, causing `mysqlclient` compilation errors. This often manifests as linker errors related to `ssl` or `crypto` libraries.
  Fix: If encountering compilation errors on macOS, a common workaround is to modify the `mysql_config` script (usually found in `/usr/local/bin` or a similar path). Specifically, change the `libs` definition from `libs="-L$pkglibdir" libs="$libs -l "` to `libs="-L$pkglibdir" libs="$libs -lmysqlclient -lssl -lcrypto"`.

Install

• pip install mysqlclient Basic Installation
• sudo apt-get install python3-dev default-libmysqlclient-dev build-essential pkg-config pip install mysqlclient Debian/Ubuntu (with system dependencies)
• sudo yum install python3-devel mysql-devel pkgconfig pip install mysqlclient Red Hat/CentOS (with system dependencies)
• brew install mysql pkg-config pip install mysqlclient macOS (Homebrew, with system dependencies)

Imports

• MySQLdb

  import MySQLdb

  MySQLdb is the higher-level, DB API-compliant module. _mysql is a lower-level, non-portable module that directly wraps the MySQL C API and should generally be avoided for application development.
• connect

  from MySQLdb import connect

Quickstart

This quickstart demonstrates how to establish a connection to a MySQL database, execute a simple query, create a table, insert data, and retrieve data using `mysqlclient`. Connection parameters are loaded from environment variables for flexibility. Remember to replace placeholder credentials with actual, securely managed values in a production environment.

import MySQLdb
import os

DB_HOST = os.environ.get('MYSQL_HOST', '127.0.0.1')
DB_USER = os.environ.get('MYSQL_USER', 'root')
DB_PASSWORD = os.environ.get('MYSQL_PASSWORD', 'your_password') # Replace with a secure method for production
DB_NAME = os.environ.get('MYSQL_DATABASE', 'testdb')

try:
    # Establish a connection
    conn = MySQLdb.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    cursor = conn.cursor()

    # Execute a query
    cursor.execute("SELECT VERSION();")
    version = cursor.fetchone()
    print(f"Database version: {version[0]}")

    # Example: Create a table (if it doesn't exist)
    cursor.execute("CREATE TABLE IF NOT EXISTS my_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))")
    print("Table 'my_table' ensured.")

    # Example: Insert data
    cursor.execute("INSERT INTO my_table (name) VALUES (%s)", ("Test Name",))
    conn.commit()
    print("Data inserted.")

    # Example: Read data
    cursor.execute("SELECT id, name FROM my_table")
    rows = cursor.fetchall()
    for row in rows:
        print(f"ID: {row[0]}, Name: {row[1]}")

finally:
    # Close the cursor and connection
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'conn' in locals() and conn:
        conn.close()
    print("Database connection closed.")