mysql连续查询_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))