How to use PyMySQL with Django

2020-02-04 Jumping through hoops

Django provides MySQL and MariaDB suport out of the box. It supports the mysqlclient library as its DB API driver to connect.

mysqlclient is a Python 3 compatible fork of the original Python MySQL driver, MySQLdb. It still provides a Python module called MySQLdb. On install, it compiles against either the MariaDB client library or MySQL client library - whichever one you have installed.

Compiling against the C extension can be frustrating:

It requires not only the MariaDB/MySQL client library, but also OpenSSL.
Obtaining the C extension libraries might not be trivial on some platforms, such as AWS Lambda.
After install, Pip’s wheel cache will store the one compiled version, so if you swap to another C library (e.g. MySQL -> MariaDB), the wheel might not work any more.

If these are blockers for you, an alternative is to use the PyMySQL library. It’s a pure Python DB API driver - no C compilation necessary! It’s also made by the same developers as mysqlclient and is better maintained at this point.

Django doesn’t officially support it, but PyMySQL ships with an (undocumented) compatibility install that can help you use it. It’s an “at your own risk” feature. I’ve done this on one project and didn’t have any problems, but that’s no guarantee there aren’t some hidden bugs.

That said, Django has also fixed a couple issues to remain compatible with PyMySQL despite the lack of official support: #20542, #30380, etc..

(I’d love to add PyMySQL support, I just haven’t had the time. If you’re interested in sponsoring such work, contact me.)

To use PyMySQL with Django, you need to run the compatibility install before Django tries to import mysqlclient. For Django 2.2+ you also need to patch PyMySQL to lie about its version. This is to pass Django’s compatibility check, as per this comment from the PyMySQL lead developer.

You need to add this compatibility install before Django, or any other code, attempts to import MySQLdb. You could do this in your manage.py - this will work if it’s your only entrypoint, but in my experience projects often grow to add others, such as celery worker.

The most universal place is in your settings file, around the definition of DATABASES. Django hasn’t tried to import MySQLdb at this point, since it doesn’t know which database you want to use. You can do this like so:

import pymysql

...

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "testapp",
    }
}

# Fake PyMySQL's version and install as MySQLdb
# https://adamj.eu/tech/2020/02/04/how-to-use-pymysql-with-django/
pymysql.version_info = (1, 4, 2, "final", 0)
pymysql.install_as_MySQLdb()

You can then verify that import MySQLdb now produces the pymysql module instead:

$ python manage.py shell

In [1]: import MySQLdb

In [2]: MySQLdb
Out[2]: <module 'pymysql' from '/.../site-packages/pymysql/__init__.py'>

Tada!

Fin

Enjoy using MariaDB / MySQL,

—Adam

Newly updated: my book Boost Your Django DX now covers Django 5.0 and Python 3.12.

One summary email a week, no spam, I pinky promise.

Related posts:

Tags: django