事务和索引、pymysql

事务和索引、pymysql

事务

如果sql语句没有在事务环境下执行，那么sql的执行情况：如果sql执行成功，那么多应的操作就成功，如果sql执行失败，对应的操作就失败
– 如果sql语句在事务环境下执行，执行成功后，如果不提交操作会失败
– begin; - 开启事务环境
– commit; - 提交事务环境中sql (执行成功的sql提交才有效)
– rollback; - 让事务环境中执行sql的功能失效（让数据库的状态会到事务环境中sql执行之前

begin;
commit;
rollback;
update tb_bank set money=6000 where carnum=1002;
update tb_bank set money=1000 where carnum=1001;
update tb_bank set money=1100 where carnum=1001;


update tb_bank set money=60000 where carnum=1002;
update tb_bank set money=2000 where carnum=1001;

索引

use school;
explain select * from tb_student where stuid=3923;
explain select * from tb_student where stuname like '%不悔';

-- 添加普通索引 
create index name_index on tb_student (stuname);

-- 添加唯一索引 
create unique index name_uindex on tb_student (stuname);


-- 删除索引 
alter table tb_student drop index name_index;
alter table tb_student drop index name_uindex;

pymysql

import pymysql
from pymysql.cursors import DictCursor
# 1.连接数据库
# host - 需要连接的数据库的主机地址
# port - 端口，一般是3306
# user - 用户名
# password - 用户名对应的密码
# database - 默认使用的数据库的名称（相当于提前use一个数据库）
# charset - 设置文本编码方式
# autocommit - 是否自动提交（pymysql中执行所有的执行都是在事务环境下进行）
conn = pymysql.Connect(
    host ='localhost',
    port = 3306,
    user = 'root',
    password = 'ly19950926.',
    database ='school',
    charset = 'utf8mb4',
    autocommit = False
)
print(conn)

# 2.获取游标对象（获取数据库上下文，获取操作数据库的环境）
# 游标类型:None - 元组
with conn.cursor(DictCursor) as cursor:
    # result=cursor.execute("drop database if exists school2")
    # print(result)
    # result=cursor.execute("select * from tb_student;")
    # print(result)
    #
    # #如果执行的是查询的sql,查询结果在游标对象中
    # result=cursor.fetchall()
    # print(result)

    cursor.execute("update tb_student set stuname='zhangsan' where stuid=234;")
    cursor.execute("commit;")

作业

import pymysql
import openpyxl
conn = pymysql.Connect(
    host='localhost',
    port = 3306,
    user = 'root',
    password='ly19950926.',
    charset = 'utf8mb4',
    autocommit=True
)
with conn.cursor() as cursor:
    cursor.execute("create database if not exists alibaba default charset 'utf8mb4';")
    # cursor.execute("")
    cursor.execute("use alibaba;")
    cursor.execute('''create table tb_alibaba(
   Date datetime not null,
   `Open` decimal(10,6) not null,
   High decimal(10,6) not null,
   Low decimal(10,6) not null,
   `Close` decimal(10,6) not null,
   Adj_Close decimal(10,6) not null,
   Volume bigint not null,
   primary key (Date)
   );''')
    wb =openpyxl.load_workbook('files/alibaba.xlsx')
    sheet = wb.active
    max_row =sheet.max_row
    for row in range(2,max_row+1):
        cursor.execute(f'''insert into tb_alibaba(Date,`Open`,High,Low,`Close`,Adj_Close,Volume) values('{str(sheet.cell(row,1).value)}',{sheet.cell(row,2).value},{sheet.cell(row,3).value},{sheet.cell(row,4).value},{sheet.cell(row,5).value},{sheet.cell(row,6).value},{sheet.cell(row,7).value});''')
    wb.close()