Python modules for connecting to the database server

[hamishmb]

This morning I have been doing some research on Python modules for connecting to our database server.

Modules I have found so far:

• MySQLdb https://github.com/farcepest/moist:
  • Seems to have been a popular choice, but appears to be unmaintained.
  • Doesn't support Python 3 - not useful for us.
• mysql-connector-python https://pypi.org/project/mysql-connector-python/:
  • Currently maintained and supports Python 3.
  • Good documentation at https://dev.mysql.com/doc/connector-python/en/.
  • Have to use an old version to connect to MySQL 5.1 (version on NAS box) :/
• PyMySQL https://pypi.org/project/PyMySQL/:
  • Currently maintained and supports Python 3.
  • Good documentation at https://pymysql.readthedocs.io/en/latest/index.html.
  • Seems to only support MySQL >= 5.5 :/
• CyMySQL https://pypi.org/project/cymysql/:
  • Currently maintained (as of Jan 2019), and supports Python 3.
  • This is a fork of PyMySQL that uses C code to speed up parts of the library.
  • Points to same documentation as PyMySQL https://pymysql.readthedocs.io/en/latest/
  • Requires MySQL 5.5 or higher :/
• mysqlclient https://pypi.org/project/mysqlclient/ <-- Trying this first
  • A forl pf MySQLdb that adds Python 3 support.
  • Currently maintained.
  • Documentation available at https://mysqlclient.readthedocs.io/.
  • Fortunately, seems to support older versions of MySQL, mentions 4.0 and 4.1.
  • This is probably our best bet.

Benchmark tool (might be useful to test our setup):

Instructions and tool are at https://github.com/methane/mysql-driver-benchmarks.

Unfortunately it seems being stuck on MySQL 5.1 might be a problem, but there is at least one module that seems to support it. I will look into building a newer database server for the NAS box when I get the cross compiler working. I will post updates here if I discover anything new.

[TerryJC]

Hamish,

There was a fairly useful article in this month's Linux Format about using Flask to develop Web Apps. It never got further than 'Hello World', but it led me to viewing a brief Tutorial on You Tube about developing a web page for accepting comments. You may remember that when I was first looking into developing the Webserver at WMT for the Audio Guide etc, I was exploring the idea of implementing a Content Management System to allow the WMT staff to upload new audio files and / or Quizzes. In the end it was put on the back burner, but at the time Ralph suggested using Flask.

Flask (http://flask.pocoo.org/docs/1.0/) is a micro-framework for developing Web Apps, so won't necessarily be any use for writing to the Results Database, but it is likely to be extremely useful for the Visitor / Staff GUI. Apparently, it is a fairly lean framework, but it is extensible and extensions exist for database access. AIUI, Django is also a powerful tool which can cope with database applications, the difference being that Django arrives fully fettled (and is therefore limited to what you get), whereas Flask is much more flexible.

Anyway, Flask is what we've always said that we should use for Web applications at WMT. It's not set in concrete; the Audio Guide and Kiddies Quiz were culled from examples that I found on the Internet and are very simple in comparison, but that doesn't mean that we shouldn't keep it in mind.

[hamishmb]

Flask sounds like it might be a good idea. Mostly I'm thinking from the perspective of connecting the river control system pi's to the NAS box with this, but this could definitely be useful.

A quick skim read of the documentation suggests that Flask uses standard python libraries to connect to the database, and then wraps them into the rest of its framework. This is good, because otherwise connecting to our obsolete database server would almost definitely be a problem!

[hamishmb]

So, I tried mysqlclient and did a stress test with our NAS box to see how it handles a flood of requests (though only one at a time). Here are the results from the script I made:

Code: Select all

hamish@hamish-Alienware-X51-R2 ~ $ python3 test_db.py 100000
Creating 100000 rows...
Avg of three tries: 57.98124099100005
Reading 100000 rows...
Avg of three tries: 3.957569676666632
Updating 100000 rows...
Avg of three tries: 72.07151066133322
Deleting 100000 rows...
Avg of three tries: 62.012823819000005
hamish@hamish-Alienware-X51-R2 ~ $ 

This is not blazingly fast, but should be perfectly good enough for us I think. The script is attached for anyone who;s interested.

[TerryJC]

Excellent!