Python script DB connection as Pool not working, but simple connection is working

Question

I am writing a script in python 3 that is listening to the tunnel and saving and updating data inside MySQL depend on the message received.

I went into weird behavior, i did a simple connection to MySQL using pymysql module and everything worked fine, ut after sometime this simple connection closes.

So i decide to implement Pool connection to MySQL and here arises the problem. Something happens no errors, but the issue is the following:

My cursor = yield self._pool.execute(query, list(filters.values()))

cursor result = tornado_mysql.pools.Pool object at 0x0000019DE5D71F98

and stacks like that not doing anything more

If i remove yield from cursor pass that line and next line throws error

response = yield c.fetchall()

AttributeError: 'Future' object has no attribute 'fetchall'

How i can fix the MySQL pool connection to work properly?

What i tried:

1. I use few modules for pool connection, all goes in same issue

2. Did back simple connection with pymysql and worked again

Below my code:

python script file

import pika
from model import SyncModel

_model = SyncModel(conf, _server_id)

@coroutine
def main():
    credentials = pika.PlainCredentials('user', 'password')

    try:
        cp = pika.ConnectionParameters(
            host='127.0.0.1',
            port=5671,
            credentials=credentials,
            ssl=False,
        )

        connection = pika.BlockingConnection(cp)
        channel = connection.channel()

        @coroutine
        def callback(ch, method, properties, body):
            if 'messageType' in properties.headers:
                message_type = properties.headers['messageType']
                if message_type in allowed_message_types:
                    result = ptoto_file._reflection.ParseMessage(descriptors[message_type], body)
                    if result:
                        result = protobuf_to_dict(result)
                        if message_type == 'MyMessage':
                            yield _model.message_event(data=result)


                else:
                    print('Message type not in allowed list = ' + str(message_type))
                    print('continue listening...')

        channel.basic_consume(callback, queue='queue', no_ack=True)

        print(' [*] Waiting for messages. To exit press CTRL+C')
        channel.start_consuming()
    except Exception as e:
        print('Could not connect to host 127.0.0.1 on port 5671')
        print(str(e))


if __name__ == '__main__':
    main()

SyncModel

from tornado_mysql import pools  
from tornado.gen import coroutine, Return
from tornado_mysql.cursors import DictCursor 

class SyncModel(object):
    def __init__(self, conf, server_id):
        self.conf = conf
        servers = [i for i in conf.mysql.servers]

        for s in servers:
            if s['server_id'] == server_id:
                // s hold all data as, host, user, port, autocommit, charset, db, password
                s['cursorclass'] = DictCursor                
                self._pool = pools.Pool(s, max_idle_connections=1, max_recycle_sec=3)

    @coroutine
    def message_event(self, data):
        table_name = 'table_name'
        query = ''
        data = data['message']

        filters = {
            'id': data['id']
        }

        // here the connection fails as describe above
        response = yield self.query_select(table_name, self._pool, filters=filters)


    @coroutine
    def query_select(self, table_name, _pool, filters=None):
        if filters is None:
            filters = {}

        combined_filters = ['`%s` = %%s' % i for i in filters.keys()]
        where = 'WHERE ' + ' AND '.join(combined_filters) if combined_filters else ''
        query = """SELECT * FROM `%s` %s""" % (table_name, where)
        c = self._pool.execute(query, list(filters.values()))

        response = yield c.fetchall()

        raise Return({response})

All the code was working with just simple connection to the database, after i start to use pool example is not working anymore. Will appreciate any help in this issue.

This is a stand alone script.

Answer 1

The pool connection was not working, so switched back to pymysql with double checking the connection

I would like to post my answer that worked, only this solution worked for me

1. before connecting to mysql to check if the connection is open, if not reconnect

   if not self.mysql.open:
       self.mysql.ping(reconnect=True)