Python模拟数据(MySQL数据库)
数据模拟
目的:模拟多个表的插入和查询数据的模拟，再通过基准测试脚本测试服务器性能和收集数据，仿真模拟。
备注:
如果需要基础的python环境，可以查看《MySQL实验准备(一)--环境准备》文档
实验脚本
通过对一个简单表的脚本插入和查询模拟，后面能 举一反三，完成多张表的多表插入，多线程的多表插入，多线程查询，和多线程的join查询。
- 数据库的表结构
mysql> show create table zdemo.student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stu_id` mediumint(8) unsigned NOT NULL COMMENT '学号id',
`stu_name` varchar(30) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- batch_insert_stu.py
批量导入百万的数据。
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import random
import string
conn = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )
sql ='insert into student (stu_id,stu_name) values (%s,%s)'
for time in range(1,110):
data = [];
for r in range(1,9001):
data.append((
time * 10000 + r ,
['zhang', 'liu', 'hu','lu','han'][random.randint(0, 4)] + str(r)
))
conn.cursor().executemany(sql, data)
conn.commit()
time.sleep(5)
print("9000 inserted.")
conn.close()
- select_stu.py
简单的查询语句，用于简单测试MySQL服务的性能。使用了timeit函数，计算时间。
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import random
import string
import timeit
conn = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )
def selectOne(conn):
sql = 'select count(*) from student'
conn.cursor().execute(sql)
#看selectOne函数，执行10次的执行时间：
print(timeit.timeit('selectOne(conn)', 'from __main__ import selectOne,conn', number=10))
- **threads_select_stu.py **
多线程的查询脚本，仿真模拟数据的查询，测试MySQL数据库的性能。
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import random
import string
import timeit
import time
import threading
import thread
def selectOne(threadName,n,delay):
conn = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )
cursor = conn.cursor()
i = 1
while i < n:
i += 1
time.sleep(delay)
print '%s: %s is running...' % ( threadName, time.ctime(time.time()) )
sql = 'select count(*) from student'
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
count = row[0]
print "count=%s" % count
print '%s: %s ended.' % ( threadName, time.ctime(time.time()) )
print(threading.current_thread().name, threading.current_thread().ident)
conn.close()
t1 = threading.Thread(target=selectOne,args=("Thread-1",4,2, ),name='worker-01')
t2 = threading.Thread(target=selectOne,args=("Thread-2",4,4, ),name='worker-02')
t1.start()
t2.start()
t1.join()
t2.join()
- **查询的结果 **
如下日志能看到，线程交替执行，模拟多线程查询
[root@db01 data]# python threads_select_stu.py
Thread-1: Sat Jun 16 20:57:00 2018 is running...
count=1119000
Thread-1: Sat Jun 16 20:57:00 2018 ended.
('worker-01', 139755490019072)
Thread-2: Sat Jun 16 20:57:02 2018 is running...
Thread-1: Sat Jun 16 20:57:02 2018 is running...
count=1119000
Thread-1: Sat Jun 16 20:57:03 2018 ended.
('worker-01', 139755490019072)
count=1119000
Thread-2: Sat Jun 16 20:57:03 2018 ended.
('worker-02', 139755479529216)
Thread-1: Sat Jun 16 20:57:05 2018 is running...
count=1119000
Thread-1: Sat Jun 16 20:57:05 2018 ended.
('worker-01', 139755490019072)
Thread-2: Sat Jun 16 20:57:07 2018 is running...
count=1119000
Thread-2: Sat Jun 16 20:57:12 2018 ended.
('worker-02', 139755479529216)
Thread-2: Sat Jun 16 20:57:16 2018 is running...
count=1119000
Thread-2: Sat Jun 16 20:57:17 2018 ended.
('worker-02', 139755479529216)