spelld_prepare_query_data.py

#! /usr/bin/env python

import pymysql as db
import configparser

class ConnectionManager(object):
    connections = {}

    def connection(self, source):
        if source not in self.connections:
            if 'mysql' == source:
                c = configparser.ConfigParser()
                c.read("/root/.my.cnf")
                self.connections[source] = db.connect(db='stat', unix_socket='/var/run/mysqld/mysqld.sock', user=c['client']['user'], passwd=c['client']['password'])
            elif 'sphinx' == source:
                self.connections[source] = db.connect(port=10306)

        return self.connections[source]

    def cursor(self, source):
        return self.connection(source).cursor()

def main():
    conn_mgr = ConnectionManager()

    mysql = conn_mgr.cursor('mysql')
    sphinx = conn_mgr.cursor('sphinx')

    sphinx.execute('SELECT query_id, SUM(cnt) as query_count FROM query_time GROUP BY query_id ORDER BY query_count DESC LIMIT 100000 OPTION max_matches=1000000;')
    query_counts = dict(sphinx.fetchall())
    mysql.execute('SELECT id, text FROM query WHERE id IN %s', set(query_counts.keys()))
    for (query_id, query_text) in mysql.fetchall():
        print(query_id, query_counts[query_id], query_text)

if "__main__" == __name__:
    main()