数据库5-索引分类和管理，外键约束，python操作mysql的数据库驱动模块pymysql和数据库ORM模块SQLAlchemy

一索引分类和管理

索引（index，key），是帮助MySQL高效获取数据的数据结构。类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。索引往往存储在磁盘上的文件中，实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

因此总结来说，索引的使用就是一种以空间换时间策略，以硬盘空间保存索引与数据位置的映射关系，在查询数据时间，如果有字段被设置了索引，则MySQL查询优化器会根据索引查找到最优的数据查询计划，可以缩短数据查询所需要消耗的时间。但是数据表在DML操作时，因为数据库需要时刻维护索引与数据位置的关系，因此在DML操作时会耗费额外的资源来完成表的索引检索操作，建议在读多写少的时候创建合适数量的索引。

索引一般用于在作为排序（order by 关键字后面）、查询条件（where关键字后面）、字段投影（select关键字后面）。

1.1 索引分类

主键索引	一般就是一张表只有1个主键索引。索引值唯一，如果是数字类型的主键则一般设置自增（auto_increment）且不能为NULL。当然，主键字段的数据类型，也可以不设置为数值类型。 -- 第一种：在表创建时声明主键 CREATE TABLE user_info( id int PRIMARY KEY auto_increment, name varchar(10) ); -- 第二种：使用alter增加主键 drop table if exists user_info; CREATE TABLE user_info( id int auto_increment, name varchar(10), primary key(id) ); ALTER TABLE table_name ADD PRIMARY KEY (columnName); alter table user_info modify id int auto_increment primary key; -- modify修改数据类型
普通索引	索引字段的值可重复出现多次，一张表中可以有多个普通索引。 -- 第一种： drop table if exists article; create table article( id int auto_increment primary key , title varchar(50) comment '文章标题', pud_date datetime default now() index title_index(title), key pud_date_index(pud_date) ); -- 第二种：也可以后续新增或修改字段 create index title_index on article (title); ALTER TABLE article ADD INDEX pud_date_index (pud_date);
全文索引	主要是针对大文本字段的内容检索，如：文章内容。全文索引在mysql5.7版本以前，只针对MyISAM存储引擎有效，在MySQL5.7以后，InnoDB存储引擎才支持全文索引。虽然MySQl8.0版本中InnoDB支持全文索引，但是只针对英文内容生效，对中文不生效，所以如果我们将来要完成中文全文索引的话，我们一般会采用elastiscsearch、xunsearch、sphinx等。 -- 建表 CREATE TABLE article_info( id int auto_increment primary key, title VARCHAR(200), body TEXT, FULLTEXT(title, body) ); insert into article_info values (null, 'welcome to python', 'hello world'); insert into article_info values (null, 'welcome to beijing', 'hello, beijing'); -- 可以通过查询计划关键字explain 来查看是否命中了索引 -- 使用方法是match(字段名...) against(‘关键字') 进行全文查询 explain select * from article_info where match(title,body) against('welcome'); -- type=FULLTEXT 命中全文索引 explain select * from article_info where title like 'welcome'; -- type=ALL表示全表扫描，最慢 explain select * from article_info where id =2; -- type=const, key=PRIMARY 表示命中主键索引，最快 -- MySQL中的全文索引只针对英语内容生效，要针对中文进行全文搜索，我们后面会学习到es可以实现 ALTER TABLE article_info ADD FULLTEXT title_fulltext (title, body);
单值索引	也叫单列索引，即一个索引只包含单个字段列，一个表可以有多个单列索引，单列索引有单列主键索引，单列唯一索引，单列普通索引，单列全文索引。 -- 第一种： create table goods_info( id int auto_increment primary key, -- 单列主键索引 goods_sn varchar(64) unique, -- 单列唯一索引 title varchar(255), description text, index title_index(title), -- 单列普通索引 fulltext description_fulltext(description) -- 单列全文索引 ) -- 第二种： CREATE unique\| INDEX \| fulltext index_name ON table_name(columnName); ALTER TABLE table_name ADD INDEX index_name ON (columnName);
复合索引	也叫联合索引或多列索引，即一个索引包含多个字段列，一个表中可以有多个复合索引。但是使用时要使用复合索引的字段列就不要其他索引重复了。 -- 第一种： create table order_items( order_id int, goods_id int, primary key(order_id, goods_id) -- 联合主键 ); create table users2( id int auto_increment primary key, username varchar(20), email varchar(255), unique username_mail_unique(username, email) ); CREATE TABLE article_info( id int auto_increment primary key, title VARCHAR(200), body TEXT, FULLTEXT(title, body) ); -- 第二种： CREATE INDEX index_name ON table_name(columnName1，columnName2...); ALTER TABLE table_name ADD INDEX index_name ON (columnName1，columnName2...);

1.2 索引管理

查询索引

-- 第一种：
SHOW INDEX FROM 数据表名;
-- 第二种：
SHOW KEYS FROM 数据表名;

删除索引

-- 第一种： 
DROP INDEX 索引名称 ON 数据表名;

-- 第二种：
ALTER TABLE 数据表名 DROP INDEX 索引名称;

-- 删除主键索引，不需要主键索引名称，因为一个表中只允许1个主键
ALTER TBALE 数据表名 DROP PRIMARY KEY;

总结：

建立索引适用情况：

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单列/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

但以下情况不适合建立索引：

1. Where条件里用不到的字段不创建索引
2. 表记录太少（300w以上建）
3. 写多读少的表（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）
4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。（比如：国籍、性别）

二外键约束

外键(FOREIGN KEY)也是索引的一种，是通过A表中的一个字段列指向B表中的主键，来对两张表进行关联的一种索引。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。当然使用外键会影响数据库性能，而且在对数据表进行DML写操作时，还会引起从表数据的完整性约束检查，所以一般开发中都是不采用数据库提供的物理外键约束，而是基于代码层面来实现逻辑外键，也叫虚拟外键。

1 创建外键

1:1关系的主外键约束

create table article(
    id int auto_increment primary key,
    title varchar(20)
);

create table article_info(
    id int auto_increment primary key,
    content text,
    CONSTRAINT article_info_foreign_key FOREIGN KEY(id) REFERENCES article(id)
);

1:多的主外键约束

create table goods_category(
    id int auto_increment primary key,
    name varchar(20)
);

create table goods(
    id int auto_increment primary key,
    cid int,
    title varchar(20),
    CONSTRAINT goods_foreign_key FOREIGN KEY(cid) REFERENCES goods_category(id)
);  # goods_category(id)为主键

多对多的主外键约束

create table user(
    id int auto_increment primary key,
    name varchar(20)
);

create table coupon(
    id int auto_increment primary key,
    title varchar(50),
    money decimal(8,2)
);

create table user_coupon(
    id int auto_increment primary key ,
    uid int,
    cid int,
    CONSTRAINT coupon_foreign_key FOREIGN KEY(cid) REFERENCES coupon(id),
    CONSTRAINT user_foreign_key FOREIGN KEY(uid) REFERENCES user(id)
);

还可以在后续通过 alter table 来创建外键

ALTER TABLE 当前表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键名)
REFERENCES 外键表名(外键表的主键名)

# 当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键,如果有则不允许删除..
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

# 当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新..
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

2 级联操作

是通过MySQL的内部维护主外键的过程中，在操作主表时，是否对于外键表进行联动操作的一种机制。

MySQL中，提供的级联类型：

级联类型	描述	示例
`RESTRICT`	限制保护，当删除主键对应的数据时，必须先把当前主键对应的外键数据全部删除。也就是必须先删外键才能删主键。	create table article( id int auto_increment primary key, title varchar(20) ); create table article_info( id int auto_increment primary key, content text, CONSTRAINT article_info_foreign_key FOREIGN KEY(id) REFERENCES article(id) ON DELETE RESTRICT );
`CASCADE`	联动删除，当删除主键对应的数据时，外键所在的数据也会被删除掉	drop table if exists goods_category, goods; create table goods_category( id int auto_increment primary key, name varchar(20) ); create table goods( id int auto_increment primary key, cid int, title varchar(20), CONSTRAINT goods_foreign_key FOREIGN KEY(cid) REFERENCES goods_category(id) on delete cascade );
`SET NULL`	空值保留，当删除主键对应的数据时，外键被设置值为NULL
NO ACTION	无操作，当删除主键对应的数据时，外键所在的数据不进行任何操作[mysql 8.0以后，无效]
SET DEFAULT	默认取值，当删除主键对应的数据时，外键会被DEFAULT默认值取代[mysql 8.0以后废弃]

三 python操作mysql

编程语言作为客户端，连接并操作mysql，通常是以下2种方式：

数据库驱动模块：pymysql（用于 Python3）、mysqldb（MySQLdb 用于 Python2 的环境模块, Python3中不支持）。
数据库ORM模块：SQLAlchemy。

不管哪一种方式，mysql都只能识别SQL语句，所以上面的几个模块的作用是充当客户端发送sql语句，通过socket通信间接并操作mysql。当然，其中第二种操作方式，是基于第一种方式而进行高度封装实现的。

1 方式一：数据库驱动模块（以pymysql 为例）

数据操作：

基本使用	-- 直接调用connect与cursor操作数据库 import pymysql.cursors # 创建和数据库服务器的连接 # 返回值：pymysql.connections.Connection 的实例对象 # from pymysql.connections import Connection -- 直接调用connect与cursor操作数据库 conn = pymysql.connect(user="root", password="123", host="127.0.0.1", port=3306, database="students") # 操作数据库，需要创建游标对象 cursor = conn.cursor() sql = """SELECT * FROM `student` where name!='吴杰' and sex=1""" # 数据库完成以后，手动关闭连接 result = cursor.execute(sql) print(result) for row in cursor.fetchall(): print(row) # print(f'row["id"]={row["id"]}, row["name"]={row["id"]}') # 关闭回收游标[相当于关闭文件操作的管道资源] cursor.close() # 关闭数据库连接[相当于关闭socket通信的连接资源] conn.close() 直接调用connect与cursor操作数据库 Connection类与Cursor类都在内部实现了执行上下文管理器协议，所以可以使用with来操作，自动完成关闭操作 -- 基于上下文管理器操作数据库 import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: print(conn) # 要操作数据，就需要创建游标对象 # from pymysql.cursors import Cursor with conn.cursor() as cursor: print(cursor) # 中间可以使用游标完成对数据库的操作 sql = """SELECT * FROM `student` where name!='吴杰' and sex=1""" # 在python操作SQL语句，单个SQL语句不加分号也可以 # 执行SQL语句 # 如果查询语句，则返回值是查询结果的总数 # 如果DML语句，则返回值是当前当前数据表受影响的行数 result = cursor.execute(sql) print(result) for row in cursor.fetchall(): print(row) # print(f'row["id"]={row["id"]}, row["name"]={row["id"]}') 基于上下文管理器操作数据库
添加一条/多条数据	import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """添加一条数据""" sql = "insert into student (name, sex, class, age, description) VALUES ('王小明', 1, 301, 17, '这家伙很懒，一句话都没有留下.')" result = cursor.execute(sql) # cursor是默认把单个DML语句作为事务进行包装执行的，所以需要在执行SQL语句，手动提交事务 conn.commit() # 查看受影响的行数 print(result) # 查看新增数据的主键 print(cursor.lastrowid) 添加一条数据 import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """添加多条数据""" sql = "insert into student (name, sex, class, age, description) VALUES (%s, %s, %s, %s, %s)" args = [ ("王晓红1号", 1, 301, 17, "这家伙很懒，一句话都没有留下."), ("王晓红2号", 1, 301, 17, "这家伙很懒，一句话都没有留下."), ("王晓红3号", 1, 301, 17, "这家伙很懒，一句话都没有留下."), ("王晓红4号", 1, 301, 17, "这家伙很懒，一句话都没有留下."), ] # 批量执行SQL语句 result = cursor.executemany(sql, args) # cursor是默认把单个DML语句作为事务进行包装执行的，所以需要在执行SQL语句，手动提交事务 conn.commit() # 查看受影响的行数 print(result) # 查看新增数据的主键 print(cursor.lastrowid) 基于预处理添加一条多条数据
查询一条/多条数据	import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """查询多条数据""" sql = "select id,name,age,sex from student where class=%(class)s" args = {"class": 301} cursor.execute(sql, args) # cursor的游标会记录每次读取的状态，所以如果前面有执行了fetch方法读取数据，则后续再次调用fetch会在上一次读取数据的基础往后读取 # 查询一条数据 row = cursor.fetchone() print(row) # 可以使用fetchmany 从结果集_rows中读取指定数量的结果 for row in cursor.fetchmany(3): print(row) print(">>>>>>> ") # 可以使用fetchall一次性从结果集_rows中读取全部结果 for row in cursor.fetchall(): print(row) fetchone，fetchmany，fetchall查询一条/多条数据
更新数据	import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """更新数据，可以更新一条，也可以更新多条，主要看设置的where条件""" # 先读取要更新的数据【当然，现在是学习阶段，所以也可以不读取，直接更新】 sql = "select id,name,age,sex from student where name=%(name)s" args = {"name": "小明2号"} cursor.execute(sql, args) row = cursor.fetchone() # 举例修改1条 sql = f"update student set name=%(name)s where id=%(id)s" args = {"name": "小明同学", "id": row["id"]} result = cursor.execute(sql, args) conn.commit() print(result) 更新数据
删除数据	import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """删除数据""" sql = f"delete from student where id=%(id)s" args = {"id": 113} result = cursor.execute(sql, args) conn.commit() print(result) 删除数据
事务处理	mysql本身是支持事务的，所以在Pymysql使用过程中，当游标cursor建立时就自动开始了一个隐形的事务管理了。 drop table if exists users; create table users( id int auto_increment primary key, name varchar(50), money decimal(8,2) ); insert into users values(1, '小明',1000); insert into users values(2, '小红',1000); insert into users values(3, '小白',1000); -- update users set money=money-200 where name = '小明'; -- update users set money=money+200 where name = '小红'; 数据准备 import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """ 事务处理只要小明有钱，并且小红的钱低于1600，就继续转钱 """ conn.begin() sql1 = "select money from users where name = '小明'" cursor.execute(sql1) res1 = cursor.fetchone() if res1["money"] > 0: sql2 = "update users set money=money-200 where name = '小明'" cursor.execute(sql2) sql3 = "select money from users where name = '小红'" cursor.execute(sql3) res2 = cursor.fetchone() if res1["money"] > 0 and res2["money"] < 1600: sql4 = "update users set money=money+200 where name = '小红'" cursor.execute(sql4) conn.commit() print("转账成功") else: conn.rollback() print("转账失败，事务回滚") 事务处理 import pymysql.cursors with pymysql.connect( user="root", password="123", host="127.0.0.1", port=3306, database="students", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式 ) as conn: with conn.cursor() as cursor: """ 事务处理只要小明有钱，并且小红的钱低于1600，就继续转钱 """ conn.begin() # 把小明的money减去200 sql = "update users set money=money-200 where name='小明'" cursor.execute(sql) sql = "select * from users" cursor.execute(sql) print(cursor.fetchall()) sql = "SAVEPOINT s1" cursor.execute(sql) # -- 给小红分100 sql = "update users set money=money+100 where name='小红'" cursor.execute(sql) sql = "select * from users" cursor.execute(sql) print(cursor.fetchall()) sql = "SAVEPOINT s2" cursor.execute(sql) # -- 给小白分100 sql = "update users set money=money+100 where name='小白'" cursor.execute(sql) sql = "select * from users" cursor.execute(sql) print(cursor.fetchall()) sql = "SAVEPOINT s3" cursor.execute(sql) sql = "ROLLBACK TO s1" # 表示从s1事务保存点，到当前一行的中间所有DML操作全部失效 cursor.execute(sql) sql = "select * from users" cursor.execute(sql) print(cursor.fetchall()) conn.commit() 事务多点回滚
基于面向对象封装工具类	在工作中，手写SQL语句基于execute执行，这种方式，性能是最好的！但是往往开发中，如果我们编写大量固定的SQL语句，也会导致程序的维护成本提升，同时将来如果应用程序要切换底层数据库的话，将会带来巨大的兼容问题。 import pymysql.cursors class DB(object): """数据库工具类""" connected = False # 数据库连接状态，False表示没有连接或连接失败 __conn = None # 数据库连接对象 class DBError(Exception): """数据库异常基类""" pass class DBConfigError(DBError): """数据库配置异常类""" pass class ConnectionError(DBError): """数据库连接异常类""" pass class ExecuteError(DBError): """数据库执行异常类""" pass def __init__(self, conf): if type(conf) is not dict: raise self.DBConfigError("错误: 数据库连接参数必须是字典类型！") for key in ["host", "user", "password", "database"]: if key not in conf.keys(): raise self.DBConfigError(f"错误: 数据库连接缺少'{key}'参数") if not conf.get("port", None): conf["port"] = 3306 if 'charset' not in conf.keys(): conf["charset"] = "utf8mb4" try: self.__conn = pymysql.connect( host=conf['host'], port=conf['port'], user=conf['user'], password=conf['password'], database=conf['database'], charset=conf['charset'], cursorclass=pymysql.cursors.DictCursor ) self.connected = True except pymysql.Error as e: raise self.ConnectionError(f"数据库连接失败: {e}") self.sql = "" # 记录最后一次执行的SQL语句 def __enter__(self): return self def __exit__(self, exc_info): del exc_info self.close() def __del__(self): self.close() def close(self): """ 关闭数据库连接 :return: """ try: self.__conn.close() except pymysql.Error as e: pass def insert(self, table, kwargs): """ 添加一条数据 :param table: 表名 :param kwargs: 字典格式数据[字段名与字段值] :return: 新增数据的ID """ fields = kwargs.keys() fields_params = [f'%({item})s' for item in fields] self.sql = f"INSERT INTO {table} ({', '.join(fields)}) VALUES ({', '.join(fields_params)})" with self.__conn.cursor() as cursor: try: cursor.execute(self.sql, kwargs) self.__conn.commit() return cursor.lastrowid except pymysql.Error as e: raise self.ExecuteError(e) def insert_many(self, table, data): """ 添加多条数据 :param table: 表名 :param data: 添加数据列表 :return: 成功添加的数据数量 """ if type(data) not in [tuple, list]: raise self.ExecuteError("错误：添加多条数据，参数data格式必须是元组或字典!") if len(data) < 1: raise self.ExecuteError("错误，待添加必须至少1条！") fields = data[0].keys() fields_params = [f'%({item})s' for item in fields] self.sql = f"INSERT INTO {table} ({', '.join(fields)}) VALUES ({', '.join(fields_params)})" with self.__conn.cursor() as cursor: try: result = cursor.executemany(self.sql, data) self.__conn.commit() return result except pymysql.Error as e: raise self.ExecuteError(e) def get_one(self, sql): """ 查询一条数据 :param sql: 原生DQL :return: 查询的第一条数据结果[字典格式] """ with self.__conn.cursor() as cursor: try: self.sql = sql cursor.execute(self.sql) return cursor.fetchone() except pymysql.Error as e: raise self.ExecuteError(e) def get_all(self, sql): """ 查询多条数据 :param sql: 原生DQL :return: 列表结构的所有查询结果 """ with self.__conn.cursor() as cursor: try: self.sql = sql cursor.execute(self.sql) return cursor.fetchall() except pymysql.Error as e: raise self.ExecuteError(e) def update(self, table, condition=None, kwargs): """ 更新数据 :param table: 表名 :param condition: 更新条件，原生SQL语句 :param kwargs: 字段参数 :return: 受影响行数 """ fields = ', '.join([f"{item}=%({item})s" for item in kwargs.keys()]) self.sql = f"UPDATE {table} SET {fields} WHERE {condition}" print(self.sql) with self.__conn.cursor() as cursor: try: result = cursor.execute(self.sql, kwargs) self.__conn.commit() return result except pymysql.Error as e: raise self.ExecuteError(e) def delete(self, table, condition=None): """ 删除数据 :param table: 表名 :param condition: 删除条件，原生SQL语句 :return: 受影响行数 """ self.sql = f"DELETE FROM {table} WHERE {condition}" print(self.sql) with self.__conn.cursor() as cursor: try: result = cursor.execute(self.sql) self.__conn.commit() return result except pymysql.Error as e: raise self.ExecuteError(e) def count(self, table, condition=None): """ 统计数据表中符合条件的总数量 :param table: 表名 :param condition: 过滤条件，原生SQL语句 :return: """ where_sql = "" if condition: where_sql = f"WHERE {condition}" self.sql = f"SELECT count(id) as c FROM {table} {where_sql}" with self.__conn.cursor() as cursor: try: cursor.execute(self.sql) return cursor.fetchone()["c"] except pymysql.Error as e: raise self.ExecuteError(e) def turncate(self, table): """ 重置表 :param table: 表名 :return: """ self.sql = f"TRUNCATE TABLE {table}" print(self.sql) with self.__conn.cursor() as cursor: try: result = cursor.execute(self.sql) self.__conn.commit() return result except pymysql.Error as e: raise self.ExecuteError(e) if __name__ == '__main__': config = { "host": "127.0.0.1", "port": 3306, "user": "root", "password": "Talent@123", "database": "school", } with DB(config) as db: # """添加一条数据""" # data = { # "name": "李小白", # "sex": 1, # "class": 306, # "age": 18, # "description": "不想说话~" # } # res = db.insert("student", *data) # print(res) # """添加多条数据""" # data = [ # { # "name": "李大白", # "sex": 1, # "class": 302, # "age": 18, # "description": "1-不想说话~" # }, # { # "name": "李中白", # "sex": 1, # "class": 307, # "age": 18, # "description": "2-不想说话~" # }, # ] # res = db.insert_many("student", data) # print(res) # """查询一条数据""" result = db.get_one("select id,name from student") print(result) # """查询多条数据""" # result = db.get_all("select id,name from student") # print(result) """更新数据""" # res = db.update("student", "id>=115", name="不白", age=16, description="就是说一句话....") # print(res) """删除数据""" # res = db.delete("student", "id in (115,116)") # print(res) """查询数据总数""" # res = db.count("student") # print(res) # res = db.count("student", "class=301") # print(res) """重置表状态""" # db.truncate("goods") 封装数据库工具类注意：我们一般不会在代码层面，对数据表、数据库进行创建或删除，否则存在相当大的安全隐患。

基于模块asyncio，aiomysql实现异步操作mysql：

import asyncio, aiomysql

settings = {
    "host":"127.0.0.1",
    "port": 3306,
    "database": "school",
    "password": "Talent@123",
    "user": "root",
}


async def main():
    # 基于连接池连接数据库
    pool = await aiomysql.create_pool(
        host=settings["host"],
        port=settings["port"],
        user=settings["user"],
        password=settings["password"],
        db=settings["database"],
        charset="utf8mb4",
        cursorclass=aiomysql.cursors.DictCursor,
        minsize=5,   # 最小连接数，默认在初始化创建5个数据库连接对象在连接池中
        maxsize=20,  # 最多连接数，当数据库并发下，最多创建20个数据库连接对象在连接池中
        echo=True,   # 设置打印内部执行的SQL语句
    )

    async with pool.acquire() as conn:
        """基于互斥锁，从连接池中获取一个数据库连接对象"""
        async with conn.cursor() as cursor:
            """创建一个游标"""
            # 异步执行SQL语句
            await cursor.execute("SELECT * from student")
            # 异步获取结果
            # fetchmany(3)  # 获取指定数量
            # fetchone      # 获取一条
            # fetchall      # 获取所有
            data = await cursor.fetchmany(3)
            data = await cursor.fetchall()
            print(data)

    pool.close()
    await pool.wait_closed()

if __name__ == '__main__':
    asyncio.run(main())

异步操作mysql

二方式二：数据库ORM模块：SQLAlchemy

SQLAlchemy 是 Python 著名开源的 ORM 工具包（也叫框架）。通过 ORM，开发者可以用面向对象的方式来操作数据库，不再需要编写 SQL 语句,支持绝大部分流行的关系型数据库。

官方文档：https://www.sqlalchemy.org/

1 ORM框架

ORM是对象关系映射器（Object Relational Mapper）的简写，是一种对数据库底层的SQL语句进行封装，然后对外提供面向对象操作的一种数据库操作方式。

ORM框架会帮我们把类对象和数据表进行了一对一的映射，让我们可以通过类对象提供的属性或方法就可以操作对应的数据表。在项目内嵌ORM框架，这样就可以不需要直接编写SQL语句进行数据库操作，而是通过定义模型类对象，操作模型类对象即可完成对数据库中数据的增删改查和数据表的创建删除等操作。参考下图：

O是Object，也就类对象的意思。

R是Relational，译作联系或关系，也就是关系型数据库中数据表的意思。

M是Mapper，是映射的意思，表示类对象和数据表的映射关系。

ORM的优点

数据模型类都在一个地方定义，容易更新和维护，也利于代码复用。

ORM 有现成的工具，很多功能都可以自动完成，比如数据消除、预处理、事务等等。

使用了ORM以后迫使开发者在开发项目时采用MVC架构，ORM 就是天然的 Model（M模型），最终使代码组织更清晰，更加容易维护。

基于ORM 的业务代码比较简单，代码量少，语义性好，容易理解。

新手对于复杂业务容易写出性能不好的原生SQL，但有了ORM不必编写复杂的SQL语句, 只需要通过操作模型对象即可同步修改数据表中的数据.

开发中应用ORM将来如果要切换底层数据库，只需要切换ORM底层对接数据库的驱动类和配置信息即可，例如从mysql数据库切换成postgreSQL数据库，则把底层驱动(mysqldb或pymysql) 切换成 psycopg2，然后把连接配置修改即可。开发代码基本不需要调整与修改。

ORM也有缺点

ORM库不是轻量级模块工具，初学者需要花很多精力学习和使用ORM模块，甚至不同的ORM框架，会存在不同操作代码。

对于复杂的数据业务查询，ORM表达起来比原生SQL语句要更加困难和复杂，例如子查询，事务，连表查询。

ORM操作数据库的性能要比使用原生的SQL语句差。

因为直接使用pymysql或mysqldb数据库驱动，只需发送SQL语句即可，而ORM不仅要发送，还要在发送前组装拼接SQL语句，里面使用了大量的正则以及字符串拼接，所以性能低下。

ORM 抽象掉了底层数据库，开发者无法了解底层的数据库操作，也无法定制一些特殊的 SQL。

针对这种情况，可以使用pymysql或mysqlDB另外编写SQl语句操作即可，用了ORM并不表示当前项目不能使用别的数据库操作模块了。

2 数据操作

数据迁移	ORM框架还可以根据我们设计的类自动帮我们生成数据表，省去了我们自己建表的过程。这个操作一般叫数据迁移（migrate） # from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base # from sqlalchemy import Column, Integer, String, Boolean, Numeric, Text # 字段、整型 from sqlalchemy import * # 1. 创建数据库驱动（引擎） engine = create_engine( # 连接数据库的URL # url="驱动名称://账户:密码@IP地址:端口/数据库名?charset=utf8mb4", # 如果底层驱动是pymysql url="mysql+pymysql://root:123@127.0.0.1:3306/students?charset=utf8mb4", # 如果底层驱动是pymysql # url="mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4", # 如果底层驱动是MySQLdb echo=True, # 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用 pool_size=10, # 连接池的数据库连接数量，默认为5个，设置为0时表示连接无限制 max_overflow=30, # 连接池的数据库连接最大数量，默认为10个 pool_recycle=60*30 # 设置时间以限制数据库连接多久没使用则自动断开（指代max_overflow-pool_size），单位：秒 ) # 基于底层数据库驱动建立数据库连接会话，相当于cursor游标 DbSession = sessionmaker(bind=engine) session = DbSession() # 模型类对象的基类，内部提供了数据库的基本操作以及共同方法 Model = declarative_base() db.py初始化配置 import db from datetime import datetime # 1. 创建一个与数据库对应的模型类对象 class Student(db.Model): """学生表模型""" # 1. 把当前模型与数据库中指定的表名进行关联 __tablename__ = "tb_student" # 2. 绑定字段信息 # 模型属性 = db.Column(数据类型, 字段约束) # primary_key=True 设置当前字段为整型，主键，SQLAlchemy会自动设置auto_increment为自增 id = db.Column(db.Integer, primary_key=True) # db.String(20) 设置当前字段为字符串，varchar(20) name = db.Column(db.String(20)) # db.Boolean 设置当前字段为布尔类型，本质上在数据库中是 0/1 # default=True 设置当前字段的默认值 sex = db.Column(db.Boolean, default=True) age = db.Column(db.SmallInteger) # 当前字段名如果是python关键字，则需要给第一个参数则字段的别名使用 # SmallInteger = SMALLINT classes = db.Column("class", db.SMALLINT) # Text 表示设置当前字段为文本格式，因为文本与字符串varchar在python都是字符串，所以此处可以兼容 description = db.Column(db.Text) status = db.Column(db.Boolean, default=1) # DateTime 设置字段为日期时间类型 # 注意：如果设置当前日期时间为默认值，不能在now加上小括号 addtime = db.Column(db.DateTime, default=datetime.now) orders = db.Column(db.SMALLINT, default=1) if __name__ == '__main__': # 如果没有提前声明模型中的数据表，则可以采用以下代码生成新的数据表，这个操作叫数据迁移 # 如果数据库中已经声明了有数据表，则不会继续生成新的数据表 db.Model.metadata.create_all(db.engine) create_all数据迁移
添加一条/多条数据	import db from datetime import datetime # 1. 创建一个与数据库对应的模型类对象 class Student(db.Model): """学生表模型""" __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20)) sex = db.Column(db.Boolean, default=True) age = db.Column(db.SmallInteger) classes = db.Column("class", db.SMALLINT) description = db.Column(db.Text) status = db.Column(db.Boolean, default=1) addtime = db.Column(db.DateTime, default=datetime.now) orders = db.Column(db.SMALLINT, default=1) if __name__ == '__main__': """添加一条数据""" student = Student(name="小明1号", classes="305", sex=True, age=18, description="滚出去..") db.session.add(student) # 相当于 pymysql的execute db.session.commit() # 相当于事务提交 commit """添加多条数据""" student1 = Student(name="小明1号", classes="302", sex=True, age=18, description="滚出去..") student2 = Student(name="小明2号", classes="303", sex=True, age=18, description="滚出去..") student3 = Student(name="小明3号", classes="304", sex=True, age=18, description="滚出去..") student4 = Student(name="小明4号", classes="305", sex=True, age=18, description="滚出去..") db.session.add_all([student1, student2,student3,student4]) # 相当于 pymysql的executemany db.session.commit() # 相当于事务提交 commit add/add_all添加一条/多条数据
查询一条/多条数据	import db from datetime import datetime # 1. 创建一个与数据库对应的模型类对象 class Student(db.Model): """学生表模型""" __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20)) sex = db.Column(db.Boolean, default=True) age = db.Column(db.SmallInteger) classes = db.Column("class", db.SMALLINT) description = db.Column(db.Text) status = db.Column(db.Boolean, default=1) addtime = db.Column(db.DateTime, default=datetime.now) orders = db.Column(db.SMALLINT, default=1) # def __str__(self): # return f"<{self.__class__.__name__} {self.name}>" def __repr__(self): """ 当实例对象被使用print打印时，自动执行此处当前，当前__repr__使用与上面__str__一致，返回值必须时字符串格式，否则报错！！！ """ return f"<{self.__class__.__name__} {self.name}>" if __name__ == '__main__': """查询一条数据""" """ get 用于根据主键值获取一条，如果查不到数据，则返回None，查到结果则会被ORM底层使用当前模型类来进行实例化成模型对象 get 可以接收1个或多个主键参数，只能作为主键值 """ # get(4) 相当于 where id=4; student = db.session.query(Student).get(4) # 如果查询的是联合主键写法： get((5,10)) 或 get({"id": 5, "version_id": 10}) print(student) # <__main__.Student object at 0x7f4161c69520> <class '__main__.Student'> """ 使用first获取查询结果集的第一个结果 first 不能接收任何参数，所以一般配合filter或者filter_by 来进行使用的 """ student = db.session.query(Student).first() # 获取属性值 if student: print(f"id={student.id}, name={student.name}, age={student.age}") """查询多条数据""" student_list = db.session.query(Student).all() print(student_list) # 基于循环输出每一个模型对象中的属性 for student in student_list: print(f"id={student.id}, name={student.name}, classes={student.classes}") query查询一条/多条数据
过滤条件查询	import db from datetime import datetime # 1. 创建一个与数据库对应的模型类对象 class Student(db.Model): """学生表模型""" __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20)) sex = db.Column(db.Boolean, default=True) age = db.Column(db.SmallInteger) classes = db.Column("class", db.SMALLINT) description = db.Column(db.Text) status = db.Column(db.Boolean, default=1) addtime = db.Column(db.DateTime, default=datetime.now) orders = db.Column(db.SMALLINT, default=1) def __repr__(self): return f"<{self.__class__.__name__} {self.name}>" if __name__ == '__main__': """过滤条件查询""" """ filter_by - 精确查询 filter_by支持值相等=号操作，不能使用大于、小于或不等于的操作一律不能使用 """ # # 单个字段条件 # students = db.session.query(Student).filter_by(name="小明1号").all() # print(students) # # 多个and条件 # students = db.session.query(Student).filter_by(sex=1, age=18).all() # print(students) """ filter - 匹配查询支持所有的运算符表达式，比filter精确查询要更强大注意：条件表达式中的字段名必须写上模型类名 filter中的判断相等必须使用==2个等号 """ # # 获取查询结果集的所有数据，列表 # students = db.session.query(Student).filter(Student.age > 17).all() # print(students) # [<Student 小明1号>, <Student 小明1号>, <Student 小明3号>, <Student 小明4号>] # # # 获取查询结果集的第一条数据，模型对象 # students = db.session.query(Student).filter(Student.age < 18).first() # print(students) # <Student 小明1号> """in运算符""" students = db.session.query(Student).filter(Student.id.in_([1, 3, 4])).all() print(students) # [<Student 小明1号>, <Student 小明1号>, <Student 小明2号>] """多条件表达式""" """多个or条件""" # from sqlalchemy import or_ # # 查询302或303班的学生 # students = db.session.query(Student).filter(or_(Student.classes==303, Student.classes==302)).all() # print(students) # [<Student 小明1号>, <Student 小明2号>] """多个and条件""" # students = db.session.query(Student).filter(Student.age==18, Student.sex==1).all() # print(students) # [<Student 小明1号>, <Student 小明3号>] # from sqlalchemy import and_ # students = db.session.query(Student).filter(and_(Student.age == 18, Student.sex == 1)).all() # print(students) # [<Student 小明1号>, <Student 小明3号>] """and_主要用于与or_一起使用的""" # 查询305的18岁男生或者 305班的17岁女生 from sqlalchemy import and_, or_ # students = db.session.query(Student).filter( # or_( # and_(Student.classes==305, Student.age==18, Student.sex==1), # and_(Student.classes==305, Student.age==17, Student.sex==2), # ) # ).all() students = db.session.query(Student).filter( and_( Student.classes == 305, or_( and_(Student.age == 18, Student.sex == 1), and_(Student.age == 17, Student.sex == 2) ) ) ).all() print(students) # [<Student 小明1号>, <Student 小明4号>] filter过滤条件查询
更新一条/多条数据	import db from datetime import datetime # 1. 创建一个与数据库对应的模型类对象 class Student(db.Model): """学生表模型""" __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20)) sex = db.Column(db.Boolean, default=True) age = db.Column(db.SmallInteger) classes = db.Column("class", db.SMALLINT) description = db.Column(db.Text) status = db.Column(db.Boolean, default=1) addtime = db.Column(db.DateTime, default=datetime.now) orders = db.Column(db.SMALLINT, default=1) # 更新一条数据 # student = db.session.query(Student).get(35) # student.name = "小会" # db.session.commit() # 更新多条数据 # db.session.query(Student).filter(Student.class_name=="303").update({Student.age:Student.age+1}) # db.session.commit() update更新一条/多条数据
删除一条/多条数据	import db class Student(db.Model): __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) sex = db.Column(db.Boolean) age = db.Column(db.Integer) class_name = db.Column("class", db.String(255),) description = db.Column(db.Text) is_delete = db.Column(db.Boolean, nullable=True, default=False) if __name__ == '__main__': # 删除一条数据 # student = db.session.query(Student).get(35) # db.session.delete(student) # db.session.commit() # 删除多条数据 db.session.query(Student).filter(Student.class_name == "401").delete() delete删除一条/多条数据

字段类型：

ORM模型提供字段类型	对应的python中数据类型	描述
Integer	int	普通整数，一般是32位
SmallInteger	int	取值范围小的整数，一般是16位
Float	float	浮点数
Numeric	decimal.Decimal	普通数值，一般是32位
String	str	变长字符串
Text	str	变长字符串，对较长或不限长度的字符串做了优化
Unicode	unicode	变长Unicode字符串
BigInteger	int	不限制精度的整数，替代integer
UnicodeText	unicode	变长Unicode字符串，对较长或不限长度的字符串做了优化
Boolean	bool	布尔值
DateTime	datetime.datetime	日期和时间
Date	datetime.date	日期
Time	datetime.time	时间
LargeBinary	bytes	二进制文件内容
Enum	enum.Enum	枚举类型，相当于django的choices，但是功能没有choices那么强大

字段约束

选项名	说明
primary_key	如果为True，代表当前数据表的主键
unique	如果为True，为这列创建唯一索引，代表这列不允许出现重复的值
index	如果为True，为这列创建普通索引，提高查询效率
nullable	如果为True，允许有空值，如果为False，不允许有空值
default	为这列定义默认值

3 异步编程

import asyncio
import sqlalchemy as sa
import aiomysql
from aiomysql.sa import create_engine
settings = {
    "host":"127.0.0.1",
    "port": 3306,
    "db": "student",
    "password":"123",
    "user": "root",
}

metadata = sa.MetaData()
tbl = sa.Table('tb_student', metadata,
               sa.Column('id', sa.Integer, primary_key=True),
               sa.Column('username', sa.String(255)),
               sa.Column('nickname', sa.String(255)),
               sa.Column('password', sa.String(255),nullable=False),
               sa.Column('age', sa.Integer(),default=0,nullable=True),
               sa.Column('sex', sa.Boolean(),default=False,nullable=True),
               sa.Column('email', sa.String(255),default=None,nullable=True),
       )

async def main(loop):
    engine = await create_engine(user=settings["user"],
                                 db=settings["db"],
                                 host=settings["host"],
                                 password=settings["password"],
                                 port=settings["port"],
                                 charset="utf8mb4",
                                 loop=loop)
    async with engine.acquire() as conn:
        # 添加操作
        await conn.execute(tbl.insert().values(username='xiaohong',nickname="小红",password="123456",age=12,sex=False,email="xiaohong@qq.comn"))
        await conn.execute(tbl.insert().values(username='xiaolan',nickname="小兰",password="123456",age=13,sex=False,email="xiaolan@qq.comn"))
        # 查询操作
        async for row in conn.execute(tbl.select()): #这里不支持query操作
            print(row)

    engine.close()
    await engine.wait_closed()

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main(loop))

python操作mysql异步编程

posted on 2022-06-12 22:13 大明花花阅读(59) 评论(0) 编辑收藏举报

搜索

常用链接

随笔档案

阅读排行榜

推荐排行榜

数据库5-索引分类和管理，外键约束，python操作mysql的数据库驱动模块pymysql和数据库ORM模块SQLAlchemy

一索引分类和管理

1.1 索引分类

1.2 索引管理

二外键约束

1 创建外键

2 级联操作

三 python操作mysql

1 方式一：数据库驱动模块（以pymysql 为例）

二方式二：数据库ORM模块：SQLAlchemy

1 ORM框架

ORM的优点

ORM也有缺点

2 数据操作

3 异步编程

搜索

常用链接

随笔档案

阅读排行榜

推荐排行榜

数据库5-索引分类和管理，外键约束，python操作mysql的数据库驱动模块pymysql和数据库ORM模块SQLAlchemy

一 索引分类和管理

1.1 索引分类

1.2 索引管理

二 外键约束

1 创建外键

2 级联操作

三 python操作mysql

1 方式一：数据库驱动模块（以pymysql 为例）

二 方式二：数据库ORM模块：SQLAlchemy

1 ORM框架

ORM的优点

ORM也有缺点

2 数据操作

3 异步编程

一索引分类和管理

二外键约束

二方式二：数据库ORM模块：SQLAlchemy