mysql持续查询

持续读取mysql：

 

#!/usr/bin/env python
#coding=utf8

import os
import sys
import time
import Queue
import MySQLdb
from threading import Thread
from termcolor import cprint

queues      = Queue.Queue(maxsize=100)
dblen       = 0
thread_num  = 100

#从输入点开始读取，
def Readmysql(dbl):
    conn= MySQLdb.connect(
        host='192.168.1.33', 
        port = 3306,
        user='root',
        passwd='root',
        db = 'userdata',
        use_unicode=True, 
        charset="utf8"
        )
    cur = conn.cursor()
    d = 20
    onedblen = 0
    sql = 'select count(id) from user'
    cur.execute(sql)
    conn.commit()
    currentlen = cur.fetchone() #获取所有的数据量
　　#读取大于输入点的数据
    while currentlen > dbl:
        sql = 'select id FROM user LIMIT %s,%s' %(dbl,d)#每次读取20条
        cur.execute(sql)
        conn.commit()
        dbl += d  #递增
        data = cur.fetchall()
        if len(data) == d: #刚好读到20条
            for i in data:
                queues.put(i)
        elif len(data) < d: #未读到20条，表明已读完
            for i in data:
                queues.put(i)
            onedblen = dbl - d + len(data)#数据总量
            cprint('[+] DB has '+str(onedblen)+' data!','red')
            break
        else:
            cprint('[-] xxx','yellow')
        time.sleep(10)
    conn.close()        

#监控mysql数据变化，如果超过输入点point调用readmysql
def Monitormysql(point):
    conn= MySQLdb.connect(
        host='192.168.1.33', 
        port = 3306,
        user='root',
        passwd='root',
        db = 'userdata',
        use_unicode=True, 
        charset="utf8"
        )
    cur = conn.cursor()
    global dblen
    dblen = point

    while True:
        sql = 'select count(id) from user'
        cur.execute(sql)
        conn.commit()
        data = cur.fetchone()
        if data[0] > dblen:
            cprint('[+] DB has new data!','blue')
            readdb  = threading.Thread(target = Readmysql,args=(dblen,))
            readdb.start()
            readdb.join()
            dblen = data[0]
        else:
            print 'No new data...'
            time.sleep(5)
        time.sleep(8)
    conn.close()

#处理数据
class HandleThread(threading.Thread): 
    def __init__(self): 
        threading.Thread.__init__(self) 
    def run(self): 
        tid = queues.get()
        oid = tid[0].encode('unicode-escape')
        print oid

def Handledata():
    while True:
        if queues.empty():
            time.sleep(1)
            cprint('[-] No data from queues','yellow')
        else:
            for i in range(thread_num):
                sth = HandleThread()
                sth.start() 
                sth.join()

if __name__ == '__main__':
# you may input a start point:
    if len(sys.argv) == 1:
        print "Usage:[%s] + db starting point!!" % sys.argv[0]
        sys.exit()    
    try:
    	startpoint = int(sys.argv[1])
    	cprint('[+] Begin '+sys.argv[1]+' line..','red')
        monitodb  = threading.Thread(target = Monitormysql,args=(startpoint,))
        hd = threading.Thread(target = Handledata)
        monitodb.start()
        time.sleep(5)
        hd.start()

    except Exception,e:
        raise Exception('Exception %s' % str(e))