5、Python操作数据库以及事务

一、表字段操作补充

# 1、添加表字段
alter table 表名 add 字段名 字段类型 约束条件;  # 默认在尾部追加
alter table 表名 add 字段名 字段类型 约束条件 after 已经存在的字段名;  # 在某个字段后面添加
alter table 表名 add 字段名 字段类型 约束条件 first;  # 在字段开头添加(一般字段的开头都是id主键，所以这个仅做一个了解)

# 2.修改字段
"""modify只能改字段数据类型和完整约束条件，不能改字段名，但是change可以！"""
alter table 表名 modify 字段名 新的字段类型 [完整性约束条件…];
alter table 表名 change 旧字段名 新字段名 数据类型 [完整性约束条件…];

# 3.删除字段
alter table 表名 rop 字段名;

二、Python操作MySQL

1、安装PyMySQL

# 下载模块
pip3 install pymysql

"""
python默认下载模块的地址是国外的网站
速度有时候会非常的慢 如果想要提升速度我们可以切换下载源
（1）阿里云 http://mirrors.aliyun.com/pypi/simple/
（2）豆瓣http://pypi.douban.com/simple/
（3）清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
（4）中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/
（5）华中科技大学http://pypi.hustunique.com/
"""
# 1.命令行切换源
pip3 install pymysql -i 源地址
# 2.pycharm永久切换
file → etting → interpreter双击 → manage 仓库

2、PyMySQL的使用

import pymysql

# 链接数据库
conn = pymysql.connect(
    host='127.0.0.1',  # ip地址
    port=3306,
    user='root',  # 用户名
    password='123',  # 密码
    database='db1',  # 库名
    charset='utf8'
    autocommit=True  # 自动进行增删改的二次确认
)
cursor = conn.cursor()  # 产生一个游标对象（就是帮你执行接下来的命令，类似于命令行里闪烁的光标）
sql = 'select * from emp'  # 写SQL语句

res = cursor.execute(sql)  # 执行sql语句
# execute返回的是你当前sql语句所影响的行数，该返回值一般用不着
print(res)  

res = cursor.fetchall()  # 这条命令才是返回sql语句的结果
print(res)

'''
如果游标对象不填写参数，执行后返回的结果是元组，结果不够清晰
写入下列的参数，返回的结果是一个字典，会显示字段名，结果清晰
cursor=pymysql.cursors.DictCursor
'''

3、PyMySQL其他操作

# 拿到数据
print(cursor.fetchone())  # 只拿一条数据
print(cursor.fetchall())  # 拿到所有数据
print(cursor.fetchmany(5))  # 指定拿几条数据

# 改变游标位置（游标类似于鼠标的指针，可以通过改变游标位置来控制数据查询结果的获取）
cursor.scroll(1,'relative')  # 相对于光标所在的位置继续往后移动1位
cursor.scroll(1,'absolute')  # 相对于数据的开头往后继续移动1位
print(cursor.fetchall())

# 执行语句
cursor.execute(sql,())  # 执行单条语句
cursor.executemany(sql,[(),(),()])  # 执行多条语句

# 增加数据
sql = 'insert into info(name,password) values("heiying","hy123")'
cursor.execute(sql)  # 执行sql命令
conn.commit()  # 手动二次确认
'''我们可以在链接数据库的代码里增加一行autocommit=True，代表自动二次确认，这样就不需要增加手动写二次确认了'''

针对PyMySQL增删改查
查重要程度很低 ，无需二次确认。
增改删重要程度很高，都需要二次确认

4、解决SQL注入问题

SQL注入是一种漏洞，具体请百度。如何解决SQL注入呢，pymysql模块已经为我们考虑到了，我们接受敏感数据之后，不要手动处理，交给pymysql拼接即可

import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'db3',
    charset = 'utf8'
    autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  产生一个游标对象

username = input('用户名>>>:')
password = input('密码>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可

rows = cursor.execute(sql,(username,password))  # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('用户名密码错误')

三、事务

1、什么是事务

开启一个事务可以包含一组SQL语句，这些SQL语句要么全部执行成功，要么全部执行失败，这也叫做事务的原子性

2、事务的ACID特性

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3、事务的使用

# 首先我们创建一个表，用来模拟银行账户
create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('Poco',1000),
('王小美',1000),
('路飞',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='Poco';  # 买支付100元
update user set balance=1010 where name='王小美';  # 中介拿走10元
update user set balance=1090 where name='路飞';  # 卖家拿到90元

# 表的结果应该是这样的
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | Poco      |     900 |
|  2 | 王小美     |    1010 |
|  3 | 路飞       |    1090 |
+----+-----------+---------+

# 回滚到上一个状态，但如果二次确认之后就无法回滚了
rollback;

# 开启事务之后，执行commit操作，二次确认，数据刷新到硬盘。
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""