连接数据库的例子

打开MySQL


在vs中的代码如下：

# 引入pymssql模块
import pymysql
import random
import time
import matplotlib.pyplot as plt
import string

# 连接数据库测试
def conn():
    connect = pymysql.connect(host='localhost',port = 3306,user='root', passwd='xxxxxxxxxxxx',db ='test')  # 服务器名,账户,密码,数据库名
    if connect:
        print("数据库连接成功！")
    return connect


# 创建一个游标对象
db = conn()
cursor = db.cursor()


# 创建表T1、T2、T3
def create_table(insert_tablename):
    cursor.execute('DROP TABLE IF EXISTS T1')  # 如果表存在则删除
    cursor.execute('DROP TABLE IF EXISTS T2')
    cursor.execute('DROP TABLE IF EXISTS T3')
    sql_create = """CREATE TABLE {}(
             id int unsigned auto_increment,
             zero varchar(20),
             one varchar(20),
             two varchar(20),
             three varchar(20),
             four varchar(20),
             five varchar(20),
             six varchar(20),
             seven varchar(20),
             eight varchar(20),
             nine varchar(20),
             primary key (id)
             )""".format(insert_tablename)
    cursor.execute(sql_create)
    print("创建表{}成功！".format(insert_tablename))


# 表插入数据
def insert(table, times):
    first_time = float(time.time())  # 初始时间
    print("正在插入数据到{}\n计时开始时间戳：{:f}".format(table, first_time))
    for num in range(0, times):
        s = string.ascii_letters
        a = random.choice(s)  # 取随机字母
        b = random.choice(s)
        c = random.choice(s)
        d = random.choice(s)
        e = random.choice(s)
        f = random.choice(s)
        g = random.choice(s)
        h = random.choice(s)
        i = random.choice(s)
        j = random.choice(s)

        # first_time = time.time()  # 初始时间
        # if num == 0:
        #     print("计时开始时间戳：{:f}".format(first_time))

        sql_insert = """INSERT INTO {}(zero,
                        one, two, three, four, five,
                        six, seven, eight, nine)
                        VALUES(%s, %s, %s, %s,
                        %s, %s, %s, %s, %s, %s)""".format(table)
        try:
            cursor.execute(sql_insert, (a, b, c, d, e, f, g, h, i, j))
            db.commit()  # 提交到数据库执行
        except:
            db.rollback()  # 如果发生错误则回滚

    end_time = float(time.time())  # 结束时间
    print("计时结束时间戳：{:f}".format(end_time))
    t_ins = end_time - first_time  # 插入数据花费的时间
    Ratio1 = t_ins/times
    print("插入{},循环{:d}次，花费时间：{:f}秒".format(table, num + 1, t_ins))
    print("Ratio1为：{:f}".format(Ratio1))
    return Ratio1


# 查询数据
def query(table, T):
    sql_query = """select * from {}
                where A=21
                """.format(table)
    first_time = float(time.time())  # 初始时间
    print("正在查询{}数据\n计时开始时间戳：{:f}".format(table, first_time))
    for i in range(10):
        cursor.execute(sql_query)
    end_time = float(time.time())  # 结束时间
    t_sel = (end_time - first_time)/10
    Ratio2 = t_sel/T
    print("计时结束时间戳：{:f}".format(end_time))
    print("查询平均时间为：{:f}".format(t_sel))
    print("Ratio2为：{:f}".format(Ratio2))
    return Ratio2


create_table('T1')
create_table('T2')
create_table('T3')

Ratio1_T1 = insert('T1', 10000)
Ratio1_T2 = insert('T2', 100000)
Ratio1_T3 = insert('T3', 1000000)

Ratio2_T1 = query('T1', 10000)
Ratio2_T2 = query('T2', 100000)
Ratio2_T3 = query('T3', 1000000)

# 作图
plt.plot([4, 5, 6], [Ratio1_T1, Ratio1_T2, Ratio1_T3], 'ro', label='Ratio1')
plt.plot([4, 5, 6], [Ratio1_T1, Ratio1_T2, Ratio1_T3], 'g')
plt.plot([4, 5, 6], [Ratio2_T1, Ratio2_T2, Ratio2_T3], 'r*', label='Ratio2')
plt.plot([4, 5, 6], [Ratio2_T1, Ratio2_T2, Ratio2_T3], 'b')
plt.xlabel('T/10^x')
plt.ylabel("Ratio1/Ratio2")
plt.legend()
plt.show()

db.close()  # 关闭数据库连接

输出结果如下：