What's the difference between MySQLdb, mysqlclient and MySQL connector/Python?

Question

So I've been trying to do some database update with python and while setting up the whole dev environment, I came across these three things which made me dizzy.

1. There's MySQLdb

2. There's mysqlclient

3. And then there's a mysql connector python

What's each of them, the difference and where to use them? Thanks

Solution 1

MySQLdb is a thin python wrapper around C module which implements API for MySQL database.

There was MySQLDb1 version of wrapper used some time ago and now it is considered to be a legacy. As MySQLDb1 started evolving to MySQLDb2 with bug fixes and Python3 support, a MySQLDb1 was forked and here is how mysqlclient appeared, with bugfixes and Python3 support. Sum up, so now we have MySQLDb2 which is not ready for production use, MySQLDb1 as an outdated driver and a community supported mysqlclient with bug fixes and Python3 support.

Now, to solve that mess, MySQL provides their own version of MySQL adapter - mysql connector, an all-in python module that uses MySQL API with no C modules dependencies and only standard python modules used.

So now the question comes down to: mysqlclient vs mysql connector.

As for me, I would go with officially supported library, however mysqlclient should be a good choice as well. Both of them are being actively updated with fixes and new features which you can see by active commits in last days.

Note: I did not have much experience with them, so there might be cases when one or another does not suite your needs. Both libraries follow PEP-249 standard which means you should be fine with at least base functionality everywhere.

Installation and Dependencies

• mysqlclient

As a fork of C wrapper it requires C modules to work with MySQL which adds python header files to build these extensions (read python-dev). Installation depends on the system you use, just make sure you aware of package names and can install them.

• mysql connector Main documentation is pretty clear, however you should be aware of Protobuf C++ dependency (for mysql connector versions >= 2.2.3).

Solution 2

There are thee MySQL adapters for Python that are currently maintained:

• mysqlclient - By far the fastest MySQL connector for CPython. Requires the mysql-connector-c C library to work.

• PyMySQL - Pure Python MySQL client. According to the maintainer of both mysqlclient and PyMySQL, you should use PyMySQL if:

  • You can't use libmysqlclient for some reason.
  • You want to use monkeypatched socket of gevent or eventlet.
  • You wan't to hack mysql protocol.

• mysql-connector-python - MySQL connector developed by the MySQL group at Oracle, also written entirely in Python. It's performance appears to be the worst out of the three. Also, due to some licensing issues, you can't download it from PyPI (but it's now available through conda).

Benchmarks

According to the following benchmarks, mysqlclient is faster (sometimes > 10x faster) than the pure Python clients.

• Benchmarking MySQL drivers (Python 3.4)
• PyMySQL Evaluation
• Python MySQLdb vs mysql-connector query performance

Solution 3

A lot of options provided by users. Little late to party. But my 2 cents in on with benchmarking for pypy 3.7 version.

Stick to mysqlclient if you want faster access and repetitive access

MySQL Connector/Python: 23.096168518066406 [sec]
mysqlclient: 6.815327882766724 [sec]
PyMySQL: 24.616853952407837 [sec]
MySQL Connector/Python: 22.619106769561768 [sec]
mysqlclient: 6.607790231704712 [sec]
PyMySQL: 24.410773038864136 [sec]

Loop... from previous benchmarking...

def q100k(cur):
    t = time.time()
    for _ in range(100000):
        cur.execute("SELECT 1,2,3,4,5,6")
        res = cur.fetchall()
        assert len(res) == 1
        assert res[0] == (1, 2, 3, 4, 5, 6)
    return time.time() - t

Solution 4

For developers using SQLAlchemy

This question was a good starting point for learning what DBAPIs are available for Python and their tradeoffs. However, the libraries mentioned above are ever-changing, and their performance and issues are not set in stone. Hence, testing the individual dialects and evaluating their performance for yourself would be a good approach.

I have provided below links listing the available DBAPIs. Those links include references to each library's documentation and a commentary on their features and support.

• For MariaDB: https://docs.sqlalchemy.org/en/14/dialects/mysql.html#module-sqlalchemy.dialects.mysql.base
• PostgreSQL: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#:~:text=DBAPI%20Support-,%C2%B6,-The%20following%20dialect
• More Database dialects can be found in the side menu here.

To understand what DBAPIs are in general, use this link, as well as reading the above mentioned PEP-249. In addition, the diagram below can help you visualise where DBAPIs belong architecturally.

View more solutions

Comments

[radtek]

So looks like PyMySQL is still the way to go if using PyPy.

[Искрен Станиславов]

in case you have problems with compiling mysqlclient you may install it via wheel package as described in here: stackoverflow.com/a/31077052/2848256

[vishal]

does sqlalchemy support mysqlclient ?

[ostrokach]

@vishal AFAIK mysqlclient is the default connector used by sqlalchemy when your database URL starts with mysql://.... To use PyMySQL, start your URL with mysql+pymysql://.... To use mysql-connector-python, start your URL with mysql+mysqlconnector://.... See sqlalchemy docs for more info.

[jackluo923]

FYI, the official package mysql-connector-python can also utilize C extension: dev.mysql.com/doc/connector-python/en/…