1#!/usr/bin/python -u 2# 3# Processing of the queries results 4# 5import sys 6import index 7import time 8import traceback 9import string 10 11if index.openMySQL(verbose = 0) < 0: 12 print "Failed to connect to the MySQL database" 13 sys.exit(1) 14 15DB = index.DB 16 17def getTopQueriesDB(base = "Queries", number = 50): 18 global DB 19 20 try: 21 import os 22 os.mkdir("searches") 23 except: 24 pass 25 26 date = time.strftime("%Y%m%d") 27 f = open("searches/%s-%s.xml" % (base, date), "w") 28 c = DB.cursor() 29 try: 30 ret = c.execute("""select sum(Count) from %s""" % (base)) 31 row = c.fetchone() 32 total = int(row[0]) 33 ret = c.execute("""select count(*) from %s""" % (base)) 34 row = c.fetchone() 35 uniq = int(row[0]) 36 ret = c.execute( 37 """select * from %s ORDER BY Count DESC LIMIT %d""" % (base, number)) 38 i = 0; 39 f.write("<queries total='%d' uniq='%d' nr='%d' date='%s'>\n" % ( 40 total, uniq, number, date)) 41 while i < ret: 42 row = c.fetchone() 43 f.write(" <query count='%d'>%s</query>\n" % (int(row[2]), row[1])) 44 i = i + 1 45 f.write("</queries>\n") 46 except: 47 print "getTopQueries %s %d failed\n" % (base, number) 48 print sys.exc_type, sys.exc_value 49 return -1 50 f.close() 51 52def getTopQueries(number = 50): 53 return getTopQueriesDB(base = "Queries", number = number) 54 55def getAllTopQueries(number = 50): 56 return getTopQueriesDB(base = "AllQueries", number = number) 57 58def increaseTotalCount(Value, count): 59 global DB 60 61 c = DB.cursor() 62 try: 63 ret = c.execute("""select ID,Count from AllQueries where Value='%s'""" % 64 (Value)) 65 row = c.fetchone() 66 id = row[0] 67 cnt = int(row[1]) + count 68 ret = c.execute("""UPDATE AllQueries SET Count = %d where ID = %d""" % 69 (cnt, id)) 70 except: 71 ret = c.execute( 72 """INSERT INTO AllQueries (Value, Count) VALUES ('%s', %d)""" % 73 (Value, count)) 74 75 76def checkString(str): 77 if string.find(str, "'") != -1 or \ 78 string.find(str, '"') != -1 or \ 79 string.find(str, "\\") != -1 or \ 80 string.find(str, " ") != -1 or \ 81 string.find(str, "\t") != -1 or \ 82 string.find(str, "\n") != -1 or \ 83 string.find(str, "\r") != -1: 84 return 0 85 return 1 86def addCounts(frmtable): 87 global DB 88 89 i = 0 90 c = DB.cursor() 91 entries=[] 92 try: 93 ret = c.execute("""select Value,Count from %s""" % (frmtable)) 94 while i < ret: 95 i = i + 1 96 row = c.fetchone() 97 if checkString(row[0]): 98 entries.append((row[0], int(row[1]))) 99 else: 100 entries.append((None, int(row[1]))) 101 102 for row in entries: 103 if row[0] != None: 104 increaseTotalCount(row[0], row[1]) 105 except: 106 print "addCounts %s failed" % (frmtable) 107 print sys.exc_type, sys.exc_value 108 traceback.print_exc(file=sys.stdout) 109 110 try: 111 c.execute("""DELETE from %s""" % (frmtable)) 112 except: 113 pass 114 115 116 117getTopQueries() 118addCounts('Queries') 119getAllTopQueries() 120