批量造数据程序,适用于压力测试,测试数据准备,SQL查询语句优化(需要大量数据)
可一定之进程数,每个进程中开启线程数,已经进程处理的数据量等
版权 © 2011, 2012 http://netkiller.github.com
摘要
下面是我多年积累下来的经验总结,整理成文档供大家参考:
主程序
- #!/usr/bin/env python3
- #-*- coding: utf-8 -*-
- ##############################################
- # Home : http://netkiller.sourceforge.net/
- # Author: Neo <openunix@163.com>
- ##############################################
- # vim:ts=4:sw=4
- logfile = '/tmp/loopdata.log'
- ##############################################
- import MySQLdb
- import Queue
- import threading
- import time
- import logging
- import os,sys
- import random, string
- import table
- class ThreadDB(threading.Thread):
- def __init__(self, queue):
- threading.Thread.__init__(self)
- self.queue = queue
- logging.basicConfig(level=logging.NOTSET,
- format='%(asctime)s %(levelname)-8s %(message)s',
- datefmt='%Y-%m-%d %H:%M:%S',
- filename=logfile,
- filemode='a')
- self.logging = logging.getLogger()
- self.logging.debug(self.name + ' Start')
- def run(self):
- db=MySQLdb.connect(host='localhost', user='neo', passwd='chen',db="neo", charset="utf8")
- cursor=db.cursor()
- running = True
- sql = None
- while running:
- #grabs host from queue
- try:
- sql = self.queue.get()
- except Exception as e:
- print (e)
- running=False
- break
- try:
- n = 0
- n = cursor.execute(sql)
- log = self.name +"\t"+ '' +''+' '+sql
- self.logging.debug(log.replace('\n','').replace('\t',''))
- except NameError as e:
- print (e)
- break
- except Exception as e:
- print (e)
- break
- except:
- break
- self.queue.task_done()
- db.commit()
- cursor.close()
- db.close()
- self.logging.debug(self.name + ' End')
- class Main():
- def __init__(self):
- self.queue = Queue.Queue()
- def threading(self, maxconn = 100):
- for i in range(maxconn):
- t = ThreadDB(self.queue)
- t.setDaemon(True)
- t.start()
- pass
- def run(self, count = 0):
- n = 0
- while n < count:
- self.queue.put(table.test1())
- n = n + 1
- #wait on the queue until everything has been processed
- self.queue.join()
- class Loop():
- def __init__(self, process = 1, thread = 1, count = 1):
- n = 0
- while n < process :
- try:
- pid = os.fork()
- if pid > 0:
- # exit first parent
- sys.exit(0)
- else:
- main = Main()
- start = time.time()
- main.threading(thread)
- main.run(count)
- print ("Elapsed Time: %s" % (time.time() - start))
- except OSError, e:
- print >>sys.stderr, "fork #1 failed: %d (%s)" % (e.errno, e.strerror)
- sys.exit(1)
- n = n + 1
- if __name__ == '__main__':
- """
- main = Main()
- start = time.time()
- main.threading(5)
- main.run(5)
- print ("Elapsed Time: %s" % (time.time() - start))
- """
- try:
- loop = Loop(5, 100, 10000)
- except KeyboardInterrupt:
- print ("Crtl+C Pressed. Shutting down.")
- os.exit()
- $ cat table.py
- #-*- coding: utf-8 -*-
- import random,string
- def <span style="background-color: #ffffff;">test0</span>():
- sql = """
- INSERT INTO test0
- VALUES(
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s',
- '%s'
- );
- """
- chinese_name = 'test'
- sex = random.choice(("male", "female"))
- id_name = random.choice(("身份证","护照","军人证"))
- id_number = random.randint(000000000000000000, 999999999999999999)
- city = random.choice((
- "黑龙江省哈尔滨市",
- "黑龙江省齐齐哈尔市",
- "黑龙江省鸡西市",
- "黑龙江省鹤岗市",
- "黑龙江省双鸭山市",
- "黑龙江省大庆市",
- "黑龙江省伊春市",
- "黑龙江省佳木斯市",
- "黑龙江省七台河市",
- "黑龙江省牡丹江市",
- "黑龙江省黑河市",
- "广东省广州市",
- "广东省深圳市",
- "广东省珠海市",
- "广东省汕头市",
- "广东省佛山市",
- "广东省江门市",
- "广东省湛江市",
- "广东省茂名市",
- "广东省肇庆市",
- "广东省惠州市",
- "广东省梅州市",
- "广东省汕尾市",
- "广东省河源市",
- "广东省阳江市",
- "广东省清远市",
- "广东省东莞市",
- "广东省中山市",
- "广东省潮州市",
- "广东省揭阳市",
- "广东省云浮市"
- ))
- address = city + random.choice(("XXXX", "XXXX", "XXXX"))
- mobile_prefix = random.choice(("131", "133", "134", '135', '136', '137','138', '139', '186'))
- mobile_suffer = random.randint(00000000, 99999999)
- mobile = '086-'+mobile_prefix + str(mobile_suffer)
- bankname = random.choice((
- "中国工商银行",
- "招商银行",
- "中国农业银行",
- "中国建设银行",
- "中国银行",
- "中国民生银行",
- "中国光大银行",
- "中信银行",
- "交通银行",
- "兴业银行",
- "上海浦东发展银行",
- "中国人民银行",
- "华夏银行",
- "深圳发展银行",
- "广东发展银行",
- "国家开发银行",
- "中国邮政储蓄银行",
- "中国进出口银行",
- "中国农业发展银行"
- ))
- bankaddr = city + bankname
- bankno = random.randint(000000000000, 9999999999999)
- createtime = random.randint(0000000000, 9999999999)
- amount = str(random.randint(50, 999999)) + '.00'
- add_amount = '0.00'
- question = 'question'
- answer = 'answer'
- browser = random.choice(("Mozilla/4.0", "Firefox 3.5", "MSIE 7", "MSIE 8", "Chrome 3.0", "Opera 7.1", "Safer 5.0"))
- country = '中国(大陆)'
- username = string.join(random.sample(['a','b','c','d','e','f','g','h','i','j'], 3)).replace(" ","")
- return(sql % (chinese_name + username , sex, id_name, id_number, address, mobile, bkname, bkaddr, bno, createtime,amount, question, answer, browser, ))
- def test1():
- insert = """INSERT INTO test1(name, sex, mobile,qq,email,username,password, createtime,type,area,,browser) VALUES %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ;"""
- sql = insert % (_demousers_value(),_demousers_value(),_demousers_value(),_demousers_value(),_demousers_value(), _demousers_value(),_demousers_value(),_demousers_value(),_demousers_value(),_demousers_value())
- #print(sql);
- return(sql)
- def _demousers_value():
- name = string.join(random.sample(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
- 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
- ], 6)).replace(" ","")
- sex = random.choice(("male", "female"))
- mobile_prefix = random.choice(("131", "133", "134", '135', '136', '137','138', '139', '186'))
- mobile_suffer = str(random.randint(00000000, 99999999))
- mobile = mobile_prefix + str(mobile_suffer)
- qq = str(random.randint(00000000, 99999999))
- email = ' '
- other_contact = ' '
- username = str(random.randint(00000000, 99999999))
- password = str(random.randint(00000000, 99999999))
- createtime = str(random.randint(000000000, 999999999))
- ad_from = ' '
- ad_keyword = ' '
- area = 'cn'
- browser = random.choice(("Mozilla/4.0", "Firefox 3.5", "MSIE 7", "MSIE 8", "Chrome 3.0", "Opera 7.1", "Safer 5.0"))
- sms_status = random.choice(('succeed','failed','mobilesucceed','emailsucceed','unsend'))
- values = """('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (name, sex, nationality, mobile,qq,email,username,password, createtime,browser)
- return(values);
仅供参考,学习使用,不能直接运行,table.py需要根据你的实际情况定制