{"id":564,"library":"pymysql","title":"PyMySQL","description":"PyMySQL is a pure-Python MySQL client library, fully compliant with PEP 249 (Python Database API Specification v2.0). It enables Python applications to connect to MySQL and MariaDB databases without requiring binary extensions. The library is actively maintained, with version 1.1.2 released on August 24, 2025, and supports standard DB-API 2.0 features like cursors, transactions, and parameterized queries.","status":"active","version":"1.1.2","language":"python","source_language":"en","source_url":"https://github.com/PyMySQL/PyMySQL","tags":["database","mysql","driver","db-api","mariadb"],"install":[{"cmd":"pip install PyMySQL","lang":"bash","label":"Standard installation"},{"cmd":"pip install PyMySQL[rsa]","lang":"bash","label":"For 'sha256_password' or 'caching_sha2_password' authentication"},{"cmd":"pip install PyMySQL[ed25519]","lang":"bash","label":"For MariaDB's 'ed25519' authentication"}],"dependencies":[{"reason":"Required for 'sha256_password' or 'caching_sha2_password' authentication.","package":"rsa","optional":true},{"reason":"Required for MariaDB's 'ed25519' authentication.","package":"ed25519","optional":true}],"imports":[{"symbol":"pymysql","correct":"import pymysql"},{"symbol":"pymysql.cursors","correct":"import pymysql.cursors"}],"quickstart":{"code":"import pymysql.cursors\nimport os\n\n# Configure connection details (replace with your actual database info or environment variables)\nhost = os.environ.get('MYSQL_HOST', 'localhost')\nuser = os.environ.get('MYSQL_USER', 'root')\npassword = os.environ.get('MYSQL_PASSWORD', 'your_password')\ndatabase = os.environ.get('MYSQL_DATABASE', 'testdb')\n\ntry:\n    # Connect to the database\n    connection = pymysql.connect(host=host,\n                                 user=user,\n                                 password=password,\n                                 database=database,\n                                 charset='utf8mb4',\n                                 cursorclass=pymysql.cursors.DictCursor)\n\n    print(f\"Successfully connected to MySQL database: {database}\")\n\n    with connection.cursor() as cursor:\n        # Example: Create a new record\n        sql = \"INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)\"\n        cursor.execute(sql, ('webmaster@example.com', 'super_secret'))\n\n        # connection is not autocommit by default. So you must commit to save your changes.\n        connection.commit()\n        print(f\"Inserted {cursor.rowcount} row(s).\")\n\n        # Example: Read a single record\n        sql = \"SELECT `id`, `email`, `password` FROM `users` WHERE `email`=%s\"\n        cursor.execute(sql, ('webmaster@example.com',))\n        result = cursor.fetchone()\n        print(f\"Fetched record: {result}\")\n\nexcept pymysql.Error as e:\n    print(f\"Error connecting or querying MySQL: {e}\")\nfinally:\n    if 'connection' in locals() and connection.open:\n        connection.close()\n        print(\"Database connection closed.\")","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"breaking","affected_versions":">=1.1.1"},{"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.","message":"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.","severity":"deprecated","affected_versions":">=1.0.0, will be breaking after 2024-06"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Integrate a connection pooling library (e.g., `pip install DBUtils` and use `PooledDB` or `pip install pymysql-pool`) into your application architecture.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure the MySQL server is running and accessible from the application's host. Verify the host, port, username, and password provided to `pymysql.connect()` are correct. Check firewall rules on both the client and server machines. If connecting to `localhost`, ensure the server is configured to accept local connections on the correct port (default is 3306).","message":"The 'Can't connect to MySQL server' error (e.g., `pymysql.err.OperationalError: (2003, \"Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)\")`) indicates that PyMySQL was unable to establish a connection to the specified MySQL server. This is often due to the MySQL server not running, being inaccessible from the host where the application is running (e.g., incorrect host/port, firewall blocking connection), or incorrect network configuration.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Verify that the MySQL server is running and accessible from the client's host. Ensure the `host` and `port` parameters in `pymysql.connect()` are correct. Check server-side configuration (e.g., `bind-address` in `my.cnf` or `my.ini`) and firewall rules to allow incoming connections on the MySQL port (default 3306).","message":"The 'Can't connect to MySQL server' error (code 2003, often with 'Connection refused' or 'Unknown MySQL server host') indicates a failure to establish an initial connection to the MySQL server. This is typically due to the server not running, incorrect host/port in connection parameters, network issues, or a firewall blocking the connection.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-05-12T15:10:46.406Z","next_check":"2026-06-26T00:00:00.000Z","problems":[{"fix":"Install the 'pymysql' package using pip: `pip install pymysql`.","cause":"The 'pymysql' library is not installed in the Python environment being used, or there is a typo in the import statement.","error":"ModuleNotFoundError: No module named 'pymysql'"},{"fix":"Verify the MySQL server is running, check the host address and port (default is 3306), ensure no firewalls are blocking the connection, and confirm the correct `host` and `port` in the `pymysql.connect()` call. Using '127.0.0.1' instead of 'localhost' can sometimes resolve the issue.","cause":"The Python application cannot establish a connection with the MySQL server, often due to an incorrect host or port, the MySQL server not running, or firewall restrictions.","error":"pymysql.err.OperationalError: (2003, \"Can't connect to MySQL server on 'hostname' ([Errno 111] Connection refused)\")"},{"fix":"Change the MySQL user's authentication method to `mysql_native_password` using an SQL command: `ALTER USER 'your_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';`.","cause":"This error occurs when a PyMySQL client attempts to connect to a MySQL 8.0+ server (which defaults to 'caching_sha2_password' authentication) but the client's underlying MySQL client libraries do not support this plugin.","error":"Authentication plugin 'caching_sha2_password' cannot be loaded"},{"fix":"Ensure the database connection is successfully established by wrapping the connection attempt in a try-except block to catch `pymysql.err.OperationalError` or similar connection exceptions, and validate connection parameters (host, user, password, db) before attempting to create a cursor.","cause":"This indicates that the database connection object is 'None', meaning the attempt to connect to the database failed (e.g., due to incorrect credentials or server unavailability) and subsequently, `connection.cursor()` was called on a non-existent connection.","error":"AttributeError: 'NoneType' object has no attribute 'cursor'"},{"fix":"Increase MySQL server configuration parameters like `wait_timeout`, `interactive_timeout`, or `net_write_timeout` to allow longer idle connections. If dealing with large queries, increase `max_allowed_packet`. For very long operations, consider re-establishing the connection or sending keep-alive signals.","cause":"The MySQL server closed the connection unexpectedly, often due to client-side or server-side timeouts (e.g., `wait_timeout`, `net_write_timeout`), large queries exceeding `max_allowed_packet`, or the server being restarted.","error":"pymysql.err.OperationalError: (2006, \"MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))\") OR pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')"}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":80,"quickstart_tag":"verified","pypi_latest":null,"install_checks":{"last_tested":"2026-05-12","tag":"verified","tag_description":"installs cleanly on critical runtimes, fast import, recently tested","results":[{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.07,"mem_mb":2.2,"disk_size":"18.1M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.07,"mem_mb":2.2,"disk_size":"23.6M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.17,"mem_mb":4.4,"disk_size":"33.6M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.2,"disk_size":"19M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":2.2,"disk_size":"24M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.1,"mem_mb":4.4,"disk_size":"34M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.14,"mem_mb":2.4,"disk_size":"20.0M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.14,"mem_mb":2.4,"disk_size":"25.9M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.23,"mem_mb":5.3,"disk_size":"35.7M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.12,"mem_mb":2.4,"disk_size":"21M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.07,"mem_mb":2.4,"disk_size":"27M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.15,"mem_mb":5.3,"disk_size":"36M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.3,"disk_size":"11.9M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.3,"disk_size":"17.7M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.22,"mem_mb":4.8,"disk_size":"27.5M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.1,"mem_mb":2.3,"disk_size":"12M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.07,"mem_mb":2.3,"disk_size":"18M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.15,"mem_mb":4.8,"disk_size":"28M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.4,"disk_size":"11.5M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.4,"disk_size":"17.4M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.14,"mem_mb":5.3,"disk_size":"27.2M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.1,"mem_mb":2.2,"disk_size":"12M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.2,"disk_size":"18M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.15,"mem_mb":5.3,"disk_size":"28M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.07,"mem_mb":2.2,"disk_size":"17.6M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":2.2,"disk_size":"23.9M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.17,"mem_mb":4.3,"disk_size":"33.9M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.2,"disk_size":"18M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"ed25519","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.08,"mem_mb":2.2,"disk_size":"24M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"rsa","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.13,"mem_mb":4.3,"disk_size":"34M"}]},"quickstart_checks":{"last_tested":"2026-04-23","tag":"verified","tag_description":"quickstart runs on critical runtimes, recently tested","results":[{"runtime":"python:3.10-alpine","exit_code":0},{"runtime":"python:3.10-slim","exit_code":0},{"runtime":"python:3.11-alpine","exit_code":0},{"runtime":"python:3.11-slim","exit_code":0},{"runtime":"python:3.12-alpine","exit_code":0},{"runtime":"python:3.12-slim","exit_code":0},{"runtime":"python:3.13-alpine","exit_code":0},{"runtime":"python:3.13-slim","exit_code":0},{"runtime":"python:3.9-alpine","exit_code":0},{"runtime":"python:3.9-slim","exit_code":0}]}}