Install

MySQL Simple Query Builder can employ three MySQL client adapters which are either equivalent or compatible by design:

  • PyMySQL
  • mysqlclient
  • MySQL-python

The library works on Python 2, Python 3 and PyPy. At server side all MySQL-family should work, including MySQL, MariaDB, Percona Server, Sphinx Search and probably others. Though CI is made only for MySQL [1].

Adapter

For actual combination of supported Python versions and MySQL adapters, see tox file [2].

PyMySQL

This is a pure-Python adapter, so it is straightforward:

pip install MysqlSimpleQueryBuilder[pymysql]

mysqlclient

This one made recent appearance, and in fact it is a fork of MySQL-python. But the fork has remarkable qualities. Unlike long-stale MySQL-python and its possible future version called moist, it also supports Python 3 and PyPy. Moreover it’s being actively developed by the same PyMySQL people [3], who port bugs from the MySQL-python bug tracker, and overall making a great job!

To be able to build C part of the client on Debian you, of course need build-essential, corresponding development version of Python, for instance python-dev for Python 2, and libmysqlclient-dev:

apt-get install build-essential python-dev libmysqlclient-dev

Then:

pip install MysqlSimpleQueryBuilder[mysqlclient]

MySQL-python

This is our old default Python 2-only thing. The same OS packages are requires as for mysqlclient:

pip install MysqlSimpleQueryBuilder[mysqldb]

Performance

Here goes a quick benchmark of the supported environments to give you a clue of expectation. I set myquerybuilder.test.benchmark.Benchmark.repeat = 10240, commented out py27-mysqldb commands in tox, which also serves a role of code coverage environment and run:

for i in {1..8}; do tox -- -s myquerybuilder.test.benchmark.Benchmark.testSelectManyFields; done

As it wasn’t the point to benchmark MySQL per se, all queries are served from query cache. For PyMySQL it was ~95% CPU utilization by Python process, ~8% by mysqld. For libmysqlclient ~90% and ~15%, respectively.

It was run on Linux Mint Olivia with Intel Core i5 1.8GHz x 2 with HT. MySQL 5.5.34, Python 2.7.4 and 3.3.1 were from Ubuntu repository. Python 3.4.2 was from deadspakes PPA, PyPy 2.4.0 (implements equivalent of Python 2.7.8) from PyPy PPA (see tox setupdrone environment [2]).

There’s no surprise, but I will comment on a few observations anyway:

  • PyPy is good for pure-Python libraries
  • PyPy is bad for libraries with native C calls
  • deadsnakes is test-only
  • mysqlclient and MySQL-python are neck and neck
[1]https://drone.io/bitbucket.org/saaj/mysql-simple-query-builder
[2](1, 2) https://bitbucket.org/saaj/mysql-simple-query-builder/src/default/tox.ini
[3]https://github.com/PyMySQL/