PyMySQL

Warnings

• breaking PyMySQL 1.0.0 introduced several backward-incompatible changes, dropping support for Python 2.7 and 3.5. `connect()` arguments became keyword-only, and the `db` and `passwd` parameters were deprecated in favor of `database` and `password` respectively. Support for the `old_password` authentication method was also removed.
  Fix: Ensure your project uses Python 3.6+ and update `pymysql.connect()` calls to use keyword arguments for all parameters, especially `database` and `password`. Review and update authentication methods if `old_password` was in use.
• breaking As of PyMySQL 1.1.1, `Cursor.execute()` explicitly prohibits dictionary parameters to prevent potential SQL injection vulnerabilities. Passing a dictionary directly will raise an error.
  Fix: Always use a sequence (tuple or list) of parameters with `cursor.execute()` for parameterized queries. For example, `cursor.execute('INSERT INTO users VALUES (%s, %s)', (value1, value2))` is correct, `cursor.execute('INSERT INTO users VALUES (%(key1)s, %(key2)s)', {'key1': value1, 'key2': value2})` is now forbidden.
• deprecated The direct access to error classes via the `Cursor` object (e.g., `cursor.Error`) will be removed after June 2024. Also, `Connection.set_charset(charset)` and the usage of `db` and `passwd` parameters in `connect()` will emit `DeprecationWarning`s.
  Fix: Refer to error classes directly from the `pymysql` module (e.g., `pymysql.Error`). Avoid `Connection.set_charset()` and use `charset` parameter in `pymysql.connect()` instead. Update `connect()` calls to use `database` and `password` parameters.
• gotcha By default, PyMySQL connections are not in autocommit mode. Changes made via `INSERT`, `UPDATE`, or `DELETE` statements will not be saved to the database unless `connection.commit()` is explicitly called. Closing a connection without committing will result in a rollback of uncommitted changes.
  Fix: Always call `connection.commit()` after performing data modification operations, or set `autocommit=True` when establishing the connection if that behavior is desired. Use `with connection:` for reliable connection handling which includes `commit()` on success or `rollback()` on exceptions.
• gotcha PyMySQL does not provide built-in connection pooling. For applications requiring efficient connection management in multi-threaded or high-concurrency environments, external libraries such as `DBUtils.PooledDB` or `pymysql-pool` are necessary to avoid overhead from frequent connection establishment and teardown.
  Fix: Integrate a connection pooling library (e.g., `pip install DBUtils` and use `PooledDB` or `pip install pymysql-pool`) into your application architecture.
• gotcha The "MySQL server has gone away" error (`pymysql.err.OperationalError: (2006, 'MySQL server has gone away')`) is a common issue often caused by long idle connections timing out, large query packets exceeding server limits, or network interruptions. This can happen if the Python application holds a connection longer than the MySQL server's `wait_timeout` setting.
  Fix: Implement connection `ping()` checks before executing queries (`connection.ping(reconnect=True)` can re-establish the connection if it dropped, though its default behavior has changed in related drivers). Consider increasing MySQL server's `wait_timeout` or `max_allowed_packet` if large queries are the cause. Use connection pooling to manage connection lifecycle effectively.

Install

• pip install PyMySQL Standard installation
• pip install PyMySQL[rsa] For 'sha256_password' or 'caching_sha2_password' authentication
• pip install PyMySQL[ed25519] For MariaDB's 'ed25519' authentication

Imports

• pymysql

  import pymysql

• pymysql.cursors

  import pymysql.cursors

Quickstart

This quickstart demonstrates how to establish a connection to a MySQL database, insert a record, commit the transaction, and fetch data using `pymysql`. It uses `DictCursor` for results as dictionaries and includes error handling and proper connection closing. Remember that `PyMySQL` does not autocommit by default, so explicit `connection.commit()` is necessary.

import pymysql.cursors
import os

# Configure connection details (replace with your actual database info or environment variables)
host = os.environ.get('MYSQL_HOST', 'localhost')
user = os.environ.get('MYSQL_USER', 'root')
password = os.environ.get('MYSQL_PASSWORD', 'your_password')
database = os.environ.get('MYSQL_DATABASE', 'testdb')

try:
    # Connect to the database
    connection = pymysql.connect(host=host,
                                 user=user,
                                 password=password,
                                 database=database,
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    print(f"Successfully connected to MySQL database: {database}")

    with connection.cursor() as cursor:
        # Example: Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@example.com', 'super_secret'))

        # connection is not autocommit by default. So you must commit to save your changes.
        connection.commit()
        print(f"Inserted {cursor.rowcount} row(s).")

        # Example: Read a single record
        sql = "SELECT `id`, `email`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@example.com',))
        result = cursor.fetchone()
        print(f"Fetched record: {result}")

except pymysql.Error as e:
    print(f"Error connecting or querying MySQL: {e}")
finally:
    if 'connection' in locals() and connection.open:
        connection.close()
        print("Database connection closed.")