python3 nameko RPC server with mysql connection

Question

I try to use a nameko rpc server to read data from mysql. here is the server code.

class SendService:
    name = 'url_feature_rpc_service'
    def __init__(self):
        print('new connection')
        self.db = MySQLdb.connect(host="localhost", user="user", passwd="123456", db="today_news", charset='utf8')
        self.cursor = self.db.cursor()

    @rpc
    def get_feature(self, url):
        sql = 'select title_seg, entity_seg, title_entity_vec from article_feature where url_md5 = md5(\'{}\')'.format(url)
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        if result == None:
            return ''
        return '\t'.join(result)

and here is the client code:

with ClusterRpcProxy(config) as cluster_rpc:
    for line in sys.stdin:
        line = line.strip()
        try:
            result = cluster_rpc.url_feature_rpc_service.get_feature(line)
        except Exception as e:
            print(e)

my question is every time I call the rpc service, it will set up a new connection. and I sometimes get the mysql error(99)"cannot connect mysql server". can I use only one connection?

@DashWinterson,sorry, i cannot not upload a pic. the exception message is "OperationalError(2003, "Can't connect to MySQL server on 'mysql.server' (99)" — Yaozong Li, Jan 11 '18 at 05:10

score 1 · Accepted Answer · answered Jan 11 '18 at 06:57

1

You should be using a DependencyProvider such as nameko-sqlalchemy to be connecting to a database.

Instantiating the MySQL connection inside __init__ means you'll create a new connection every time the RPC method fires, probably means you're running out of connections.

answered Jan 11 '18 at 06:57

Matt

2,153
1
18
29

thanks, you just point out the problem. I donnot known how to use DependencyProvider, but I change the db and cursor to statistic variable in class and use SendService.cursor to execute sql in func get_feature just solved this problem. Is this ok? – Yaozong Li Jan 11 '18 at 09:08
It's definitely not the correct way to do it and there will probably be unexpected side-effects. Read http://nameko.readthedocs.io/en/stable/dependency_injection_benefits.html to learn why we use the dependency injection pattern. – Matt Jan 11 '18 at 17:35

score 0 · Answer 2 · answered Jan 11 '18 at 05:20

You have hanging DB connections, you need to close() the db at the end of your request.

@rpc
def get_feature(self, url):
    sql = 'select title_seg, entity_seg, title_entity_vec from article_feature where url_md5 = md5(\'{}\')'.format(url)
    self.cursor.execute(sql)
    result = self.cursor.fetchone()
    self.db.close()
    if result == None:
        return ''
    return '\t'.join(result)

python3 nameko RPC server with mysql connection

2 Answers2