Pymysql使用，事务，索引

1. pymysql使用

# 1.导包
import pymysql

# 2.创建连接对象
'''
    host=服务器的主机地址,
    port=msyql数据库的端口号,
    user=用户名',
    passwd=密码,
    database=操作的数据库,
    charset=操作数据库使用的编码格式
'''
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='admin',
    database='py112',
    charset='utf8'
)

# 3.获取游标，目的就是要执行SQL语句
cursor = conn.cursor()

# 准备SQL，之前在MySQL客户端如何编写SQL，在Python程序里面还怎么编写
sql = "select * from students"

# 4.执行SQL语句  execute执行
cursor.execute(sql)

# 获取查询的结果
row = cursor.fetchall()
# print(row)
for i in row:
    print(i)

# 5.关闭游标
cursor.close()

# 6.关闭连接
conn.close()

注意：

对数据表完成添加，删除，修改等操作，需要把修改的数据提交到数据库

# 1.导包
import pymysql

# 2.创建连接对象
'''
    host=服务器的主机地址,
    port=msyql数据库的端口号,
    user=用户名',
    passwd=密码,
    database=操作的数据库,
    charset=操作数据库使用的编码格式
'''
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='admin',
    database='py112',
    charset='utf8'
)

# 3.获取游标，目的就是要执行SQL语句
cursor = conn.cursor()

# 准备SQL，之前在MySQL客户端如何编写SQL，在Python程序里面还怎么编写
# sql = "select * from students"

sql = "insert into classes(name) value('爬虫开发')"

# 4.执行SQL语句  execute()执行
try:
    cursor.execute(sql)

# 获取查询的结果
# row = cursor.fetchall()
# # print(row)
# for i in row:
#     print(i)
# 提交修改的数据到数据库
    conn.commit()
except Exception as e:
    # 对插入的数据发生异常进行撤销，表示数据回滚，回滚就是回到没有修改数据之前的一个状态
    conn.rollback()

# 5.关闭游标
cursor.close()

# 6.关闭连接
conn.close()

说明：

• conn.commit()表示将修改操作提交到数据库

• conn.rollback()表示回滚数据

小结

1. 导包

import pymysql

2. 创建连接对象

connt = pymysql.connect(参数)

3. 获取游标对象

cursor = conn.cursor()

4. 执行sql语句

row = cursor.execute(sql)

5. 获取查询结果集

# fetchall()全部的数据  fetchone()一条数据
cursor.fetchall()

6. 将修改操作提交到数据库

conn.commit()

conn.rollback() # 回滚数据

事务

事务的使用

在使用事务之前，先确保表的存储引擎是InnoDB类型，只有这个类型才可以使用事务。MySQL数据库中表的存储引擎默认是InnoDB

表的存储引擎：表的存储引擎就是提供存储数据一种体制，不同表的存储引起提供不同的存储机制

-- 查看MySQL数据库支持的表的存储引擎
show engines;

说明：

• 常用的表的存储引擎是InnoDB 和 MyISＡＭ

• InnoDB是支持事务的

• MyISＡＭ不支持事务，优势是访问速度快，对事务没有要求或者以select，insert为主的都可以使用该存储引擎来创建表

-- 创建 "京东" 数据库
create database jing_dong charset=utf8;
-- 使用 "京东" 数据库
use jing_dong;

-- 创建一个商品goods数据表
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null, 
    brand_name varchar(40) not null, 
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);

-- 查看表结构
desc goods;
-- 主要用三个字段 cate_name 分类名称 bradd_name品牌名称  price价格

-- 向goods表中插入数据

insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

-- select * from goods;

说明：

• 通过创表语句可以得知，goods表的存储引擎是InnoDB

• 修改表的存储引擎使用: alter table 表名 engines=引擎类型;

• alter table students engines='MyISAM';

开启事务

begin;
或者
start transaction;

• 开启事务执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不是维护到物理表中

• MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务，那么每条SQL语句都会被当做一个事务执行提交操作

• 当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该事务结束。

• set autocommit=0 表示取消自动提交事务模式，需要手动执行commit完成提交事务的操作

提交事务：commit

将本地缓存文件中的数据提交到物理表中，完成数据的更新

回滚事务：rollback

放弃本地缓存中的缓存数据，表示回到开始事务前的状态

索引

索引作用：能够加快数据库的查询速度

索引的使用

查看表中已有的索引：

show index from 表名;

-- 主键列会自动创建索引

索引的创建：

-- 创建索引的语法格式
-- alter table 表名 add index 索引列名(列名);
alter table classes add index my_name(name);

索引名不指定，默认使用字段名

索引的删除

alter table classes drop index my_name;

案例 -验证索引查询性能

创建测试表 mytest

create table mytest(title varchar(50) not null);

向表中插入十万条数据：

from pymysql import connect

def main():
    # 创建连接
    conn = connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='admin',
        database='py112',
        charset='utf8'
    )

    # 获取游标对象
    cursor = conn.cursor()

    sql = "insert into mytest(title) values(%s)"

    # 插入10万条数据
    try:
        for i in range(100000):
            cursor.execute(sql,["test"+str(i)])
        conn.commit()
    except Exception as e:
        conn.rollback()

    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    main()

验证索引性能操作

-- 开启运行时间监测：
set profiling=1;

-- 查找第一万条数据 test9999
select * from mytest where title = 'test9999';

-- 查看执行的时间:
show profiles;

-- 给title 字段创建索引
alter table mytest add index (title);

-- 再次执行查询语句
select * from mytest where title = 'test9999';

-- 再次查看执行的时间
show profiles;