MySQL Lab prep (ii)--python simulation data (MySQL database)

Python simulation data (MySQL database) data simulation Purpose: Simulates the insertion and querying data of multiple tables, then tests server performance and collects data through benchmark scripts, simulating simulations.

Note:
If you need a basic Python environment, you can view the MySQL lab preparation (i)-Environment preparation document

Experiment Script

Through a simple table of script insertion and query simulation, can be extrapolate, the completion of multiple tables multi-table insertion, multi-threaded multiple table insertion, multi-threaded query, and multi-threaded join query.

• Table Structure of the database

Mysql> ShowCreate Tablezdemo.student;+---------+------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------+|Table|Create Table| +---------+------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------+| Student |CREATE TABLE' Student ' (' ID ')int(Ten) unsigned not NULLAuto_increment, ' stu_id ' Mediumint (8) unsigned not NULL COMMENT ' Learning ID ', ' Stu_name 'varchar( -) not NULL COMMENT ' name ',PRIMARY KEY(' ID ')) Engine=innodbDEFAULTCharset=utf8 | +---------+------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------+  1 Row inch Set(0.00Sec

• batch_insert_stu.py
  Bulk import of millions of data.

  #!/usr/bin/python#-*-Coding:utf-8-*-ImportMySQLdbImportRandomImportStringconn=MySQLdb.Connect("10.1.11.170","ZSD","[email protected]","Zdemo", CharSet=' UTF8 ') SQL=' INSERT into student (Stu_id,stu_name) VALUES (%s,%s) ' forTimeinch Range(1, the): Data=[];     forRinch 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
  Simple query statement for a simple test of the performance of the MySQL service. The Timeit function is used to calculate the time.

  #!/usr/bin/python  #-*-Coding:utf-8-*-  import  mysqldbimport  randomimport  Stringimport  timeitconn =  mysqldb. connect  ( "10.1.11.170" , , ,  "Zdemo" , Charset=   ' UTF8 ' ) def  SelectOne (conn): SQL =   ' SELECT COUNT (*) from student ' /span> conn.cursor (). Execute (SQL)  #看selectOne函数, performs 10 execution times:  print  (Timeit.timeit ( ' SelectOne (conn) ' ,  ' from __main__ import selectone,conn ' , Number=  10 )) 

• threads_select_stu.py
  Multi-threaded query script, simulation simulation data query, test the performance of MySQL database.

  #!/usr/bin/python#-*-Coding:utf-8-*-ImportMySQLdbImportRandomImportStringImportTimeitImportTimeImportThreadingImportThreaddefSelectOne (threadname,n,delay): Conn=MySQLdb.Connect("10.1.11.170","ZSD","[email protected]","Zdemo", CharSet=' UTF8 ') cursor=Conn.cursor () I= 1   whileI<N:i+= 1Time.sleep (Delay)Print '%s: %sis running ... ' %(ThreadName, Time.ctime (Time.time ())) SQL= ' SELECT COUNT (*) from student 'Cursor.execute (SQL) results=Cursor.fetchall () forRowinchResults:count=row[0]Print "Count=%s" %CountPrint '%s: %sended. ' %(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 ()

• Results of the query
  As the following log can see, threads alternately execute, simulating multi-threaded queries

  [[email protected]data]# python threads_select_stu.pyThread-1: Sat June 20:57:00 2018 is running ...count=1119000Thread-1: Sat June 20:57:00 2018 ended.(' worker-01 ',139755490019072)Thread-2: Sat June 20:57:02 2018 is running ...Thread-1: Sat June 20:57:02 2018 is running ...count=1119000Thread-1: Sat June 20:57:03 2018 ended.(' worker-01 ',139755490019072)count=1119000Thread-2: Sat June 20:57:03 2018 ended.(' worker-02 ',139755479529216)Thread-1: Sat June 20:57:05 2018 is running ...count=1119000Thread-1: Sat June 20:57:05 2018 ended.(' worker-01 ',139755490019072)Thread-2: Sat June 20:57:07 2018 is running ...count=1119000Thread-2: Sat June 20:57:12 2018 ended.(' worker-02 ',139755479529216)Thread-2: Sat June 20:57:16 2018 is running ...count=1119000Thread-2: Sat June 20:57:17 2018 ended.(' worker-02 ',139755479529216)

MySQL Lab prep (ii)--python simulation data (MySQL database)