Using TorMySQL in a Tornado web app

[AivanF]

My task

I have an old, large project based in Python 2.7 with Tornado framework. To work with MySQL, it initially used Tornado-MySQL with raw SQL queries, and it worked well, but now I have to deal with MySQL 8, and that library is obsolete, unmaintained.

So, now I use TorMySQL library – it connects easily to MySQL Server 8 and mainly works well. However, I don't fully understand how to use it, and this leads some bugs: different clients of the web application see different versions of data, like if they had different connections with own uncommitted data. The data eventually becomes updated, but too late.

Current code

All the work with database is handled by file database.py which is used in lots of other files. Here is it's simplified code:

from tornado import gen
from tornado.gen import Return
import tormysql
import settings

POOL = tormysql.ConnectionPool(
    max_connections = 20,
    idle_seconds = 7200, #timeout time, 0 is not timeout
    wait_connection_timeout = 3,
    host='127.0.0.1',
    port=3306,
    user=settings.MYSQL_USER,
    passwd=settings.MYSQL_PASSWORD,
    db='aivanf',
    use_unicode=True,
    charset='utf8mb4')

@gen.coroutine
def executeSQL(query, params):
    with (yield POOL.Connection()) as conn:
        with conn.cursor() as cursor:
            try:
                yield cursor.execute(query, params)
            except Exception, ex:
                print('Exception!\n{}'.format(ex))
                yield conn.rollback()
                raise Return(None)
            else:
                first = query[:10].lower()
                if 'update' in first or 'insert' in first:
                    yield conn.commit()
                if 'select' in first:
                    raise Return(cursor.fetchall())
                else:
                    raise Return(None)

I use multiple if because this single function is called with different types of queries. I know, it's ugly, but works fine. Similar, but even simpler code for Tornado-MySQL worked completely perfect, but with MySQL 5.7 only.

The question

How to solve the problem?

I suppose it is related to the pool – maybe I have to close / recreate it? The example on main page of TorMySQL also has this line: yield pool.close() but I found no option to use it in my code.

[ploxiln]

You probably have to conn.commit() even after a SELECT query - otherwise a run of SELECT queries are done within the same transaction as the first.

[ploxiln]

(I think most users are accustomed to "autocommit" by default, but that does not seem to be the default mode for TorMySQL)

[AivanF]

You probably have to conn.commit() even after a SELECT query - otherwise a run of SELECT queries are done within the same transaction as the first.

Thank you very much, Pierce! Now the code works excellent. My perfectionism (desire to reduce commits) was wrong at this time 😅 If you want, you can also write an answer to the same question on StackOverflow, I'll plus it.

[ploxiln]

sure, thanks :)

[Scuury]

oo, verygood