MySQL实验准备(二)--Python模拟数据(MySQL数据库)

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)

原文：https://www.cnblogs.com/zhangshengdong/p/9190354.html