测试mysql插入数据的几种方法

1、先建立一个简单的表
mysql>create table test7(id int(4) not null auto_increment primary key, name varchar(256) default 'aaaa');

2、然后写一个python代码测试

# cat test.py

#!/usr/bin/python
import MySQLdb
import sys
import os
import random
import time

time1 = time.time()
char_all = ['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']
# the count of rows inserted into the table
count = 50000
def no_transaction():
    conn = MySQLdb.connect(host="localhost", user="root",passwd="123456", port=3306, db="test1")
    cursor = conn.cursor()
    for m in range(1,count):
        #the length of string
        length = random.randrange(1,100)
        string = ''
        for i in range(1,length):
            char = char_all[random.randrange(0,25)]
            string = string + char
        sql = "insert into test1.test7 (name) values('%s')" % string
        print sql
        cursor.execute(sql)
    sql = "delete from test1.test7"
    cursor.execute(sql)
    cursor.close()
    conn.close()
    time2 = time.time()
    secs = time2 - time1   
    print secs

def transaction():
    conn = MySQLdb.connect(host="localhost", user="root",passwd="123456", port=3306, db="test1")
    #conn.begin()
    cursor = conn.cursor()
    #the length of string
    for m in range(1,count):
        length = random.randrange(1,100)
        string = ''
        for i in range(1,length):
            char = char_all[random.randrange(0,25)]
            string = string + char
        sql = "insert into test1.test7 (name) values('%s')" % string
        #print sql
        cursor.execute(sql)
    sql1 = "delete from test1.test7"
    cursor.execute(sql1)
    conn.commit()  
    cursor.close()
    conn.close()
    time2 = time.time()
    secs = time2 - time1
    print secs

def batch_insert():
    conn = MySQLdb.connect(host="localhost", user="root",passwd="123456", port=3306, db="test1")
    cursor = conn.cursor()
    param = []
    for m in range(1,count):
        #the length of string
        length = random.randrange(1,100)
        string = ''
        for i in range(1,length):
            char = char_all[random.randrange(0,25)]
            string = string + char
        sql = "insert into test1.test7 (name) values('%s')"
        param.append(string)
    cursor.executemany(sql,param)
    sql = "delete from test1.test7"
    cursor.execute(sql)
    cursor.close()
    conn.close()
    time2 = time.time()
    secs = time2 - time1   
    print secs

#no_transaction()
#batch_insert()
transaction()