sqlalchemy in查询优化_SQLAlchemy(2)
目录
`SQLAlchemy`
1.简介
2.什么是`ORM`
3.开启`MySQL`的查询日志
4.`SQLAlchemy`与数据库的逻辑对应
5.连接数据库
6.创建会话`Session`
7.创建映射(`Model`)
1.`Column`的数据类型
2.`Column`列级别约束
3.表级别约束
8.单表操作
1.表的操作-增(`insert into`)
2.表的操作-删(`delete`)
3.表的操作-改(`update`)
4.表的操作-查(`select`)
1.基本查询
2.查询时的`where`子句
3.查询返回的列表(多个实例)以及标量(一个实例)
4.聚合查询
9.事务回滚
10.外键约束
11.基本关系模式
1.一对多
2.一对多双向关系
3.一对一关系
4.多对多关系-中间表无意义
5.多对多关系-中间表有意义
12.双向关系中数据的查询和添加
13.`cascade`级联
1.一对多模式中`relationship`的影响
2.`relationship`中`cascade`参数设置
14.`relationship`中使用`order_by`排序
5.连接数据库
在连接数据库之前,先安装一下
python操作mysql数据库的扩展包pymysql,以及flask操作数据库的扩展包sqlalchemy$ conda activate learnpy$ conda install pymysql$ conda install sqlalchemy为了操作方便,这里就不在
python的bash窗口中操作,而是使用Jupyter notebook.
打开命令行工具,创建一个保存
jupyter文件的文件夹,并运行jupyter notebook.# 注意运行环境是learnpy$ conda activate learnpy$ mkdir jupyter$ cd jupyter$ conda install jupyter$ jupyter-notebook
2.点击新建
python3.然后输入代码
查看
sqlalchemy版本,验证jupyter可用(jupiter的基本操作,参考之前的公众号文章):
连接数据库:
dialect用于和数据API进行交流,根据配置文件的不同调用不同的API,从而实现对数据库的操作.from sqlalchemy import create_engineengine = create_engine(SQLAPI)# MySQL-PythonSQLAPI='mysql+mysqldb://:@[:]/''# pymysqlSQLAPI='mysql+pymysql://:@/[?]'# MySQL-ConnectorSQLAPI='mysql+mysqlconnector://:@[:]/'# cx_OracleSQLAPI='oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]'# 更多详见:https://docs.sqlalchemy.org/en/latest/dialects/index.html我们使用的是
pymysql# 使用pymysql 连接数据库from sqlalchemy import create_engineengine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/test', echo=True)# echo=True 表示能看到所有的SQL输出,在开启了mysql数据库的 数据日志 以后,可以通过mysql查看连接和语句的执行# cat /mysql_data/localhost.log初次调用
create_engine时不会真正的去连接数据库,而是在真正执行一条命名时才回去尝试建立连接.$ ssh root@192.168.0.101# 注意查看日志,要根据自己安装的mysql目录决定$ tail -10 /mysql_data/localhost.log# 无内容,sqlalchemy 并没有真的去连接数据库.
6.创建会话Session
在
sqlalchemy中,连接控制语柄的是Session.Session相当于一个会话池,所有与数据库的交互都是通过它来运行的.from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerengine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/test')Session = sessionmaker(bind=engine)如果没有创建
engine,先创建了Session,可以通过Session.configure(bind=engine)来绑定数据库.from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerSession = sessionmaker()engine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/test')Session.configure(bind=engine)以上,不管是哪一种,只要是开始和数据库进行交互,就需要从
Session池中实例化一个session对象来操作.from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerengine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/test')Session = sessionmaker(bind=engine)# 初始化一个sessionsession = Session()当一个实例
session不再使用的时候,需要手动去关闭它.session.close()
Session具有以下特性:
Session会在需要的时候(比如用户读取数据,更新数据时)和数据库进行通信,获取数据对象,并有一个池来维护这些对象,保证访问数据时不出现意外的问题.
Session和connection不等同,Session会通过connection和数据库通信
Session是Query查询的入口.当实例化
Session会话之后,实例对象session有以下特别的属性和方法
flush():将当前session存在的变更发送给数据库,也就是会执行SQL语句.
commit():提交事务,一个事务包括多条SQL语句.
autoflush:默认是True,session在查询之前会自动把当前积累的修改发送给数据库.注意:这里是查询(Query)执行时,并不是session.add()时执行.
autocommit():默认是False,默认不自动提交事务.这意味着,执行完任何关于数据的语句,都需要执行commit()操作来提交.
7.创建映射Model
在
python中一切皆对象.mysql本生是关系型数据库,这里要做的就是,把表映射为python的一个类,让我们以对象的方式来操作表.
描述将要处理的表
定义包含指令的类
将类映射到表.映射的类必须根据基类来定义.这个基类维护相对于基类的类和表的目录,这称为声明性基类.使用
declarative_base来创建基类.from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer, Stringengine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/test')Session = sessionmaker(bind=engine)# 创建基类Base = declarative_base()# 创建映射表class User(Base): __tablename__ = 'Users' # 必须,对应数据库表名 id = Column(Integer, primary_key=True) # primary_key 这列是必须有的 name = Column(String(50)) fullname = Column(String(50)) password = Column(String(50)) def __repr__(self): """这里的魔法方法 repr 并不是必须的,只是为了演示数据好看""" return ''.format(self.name, self.fullname, self.password)注意:
__tablename__是必须存在的,他的值是 数据库中的表名,并且表中的主键列也必须存在
类构建成功后,就定义了表的元数据信息.
sqlalchemy用来呈现这些信息的对象被称为Table对象.可以使用User.__table__来查看这个对象.User.__table__Table('Users', MetaData(bind=None), Column('id', Integer(), table=<Users>, primary_key=True, nullable=False), Column('name', String(length=50), table=<Users>), Column('fullname', String(length=50), table=<Users>), Column('password', String(length=50), table=<Users>), schema=None)# 这是一个Table对象,也称为元数据信息.根据这个元数据信息,
sqlalchemy可以使用metadata.create_all(engine)来创建绑定到Base'基类上的表,需要注意的是这个方法不能更新表的内容(也就是不能使用alter语句).metadata.drop_all(engine),用来删除绑定到Base基类上的表.# Base 是我们创建的基类.这个命令可以生成 绑定到 Base上的表>>> Base.metadata.create_all(engine)# 删除绑定到Base上的表>>> Base.metadata.drop_all(engine)对于我们来说,现在数据库中查看有没有
Users这张表.use test;show tables like 'User%';如果有,直接执行
Base.metadata.drop_all(engine)删除表.如果没有,执行
Base.metadata.create_all(engine)来创建一张表.
1.Column的数据类型
sqlalchemy与mysql中常用的数据类型相对应# 可以通过sqlalchemy导入数据类型from sqlalchemy import String,Date,DateTime,Integer,Float,Boolean,DECIMAL,Time,Enum,Text
MysqlSQLAlchemysmallintBooleanintIntegerfloatFloatdecimal定点数DECIMALdateDatedatetimeDateTimetimeTimeenumEnumtextTEXT
# 通过 sqlalchemy.dialects.mysql 导入更多的Mysql支持的数据类型from sqlalchemy.dialects.mysql import DOUBLE,LONGTEXT,YEAR,LONGBLOB
假设创建如图的
Model
在我们的
mysql中也不存在这张表.使用Pycharm来创建这样的映射.$ git add .$ git commit -m "初始化sqlalchemy v2.001"先查看一下我们的数据库:
mysql root@192.168.0.101:sql_learn> use sql_learn;mysql root@192.168.0.101:sql_learn> show tables like 'a%'; +--------------------------+| Tables_in_sql_learn (a%) |+--------------------------+新建一个
python文件#!/usr/bin/env python# coding=utf-8from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,String,Integer,Date,DateTime,Time,Text,Enum,DECIMALfrom sqlalchemy.dialects.mysql import LONGTEXT# 1.创建engineengine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/sql_learn')# 2.创建Session池Session = sessionmaker(bind=engine)# 3.实例化一个Sessionsession = Session()# 4.创建Model# 继承于基类Base = declarative_base()# 创建映射表class Article(Base): __tablename__ = 'articles' id = Column(Integer, primary_key=True, nullable=False) author = Column(String(45), nullable=False) detail = Column(LONGTEXT, nullable=False) create_time = Column(Date, nullable=False) create_time_1 = Column(DateTime, nullable=False) create_time_2 = Column(Time, nullable=False) author_introduction = Column(Text(100), nullable=False) category = Column(Enum('Python','Java','C'), nullable=True) price = Column(DECIMAL(8,4), nullable=True)# 创建绑定到 Base 上的表Base.metadata.create_all(engine)运行:
$ python sql_column_type.py查看
mysql数据库:mysql root@192.168.0.101:sql_learn> show tables like 'a%'; +--------------------------+| Tables_in_sql_learn (a%) |+--------------------------+| articles |+--------------------------+可以看到
articles表已经被创建.通过查看日志查看更详细的信息.
# 需要ssh到192.168.0.101[root@localhost ~]# tail -20 /mysql_data/localhost.log2020-08-01T10:31:47.825572Z 43 Query SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_12020-08-01T10:31:47.828068Z 43 Query ROLLBACK2020-08-01T10:31:47.839192Z 43 Query SET NAMES utf8mb42020-08-01T10:31:47.849482Z 43 Query DESCRIBE `articles`2020-08-01T10:31:47.858049Z 43 Query ROLLBACK2020-08-01T10:31:47.866063Z 43 Query CREATE TABLE articles (id INTEGER NOT NULL AUTO_INCREMENT,author VARCHAR(45) NOT NULL,detail LONGTEXT NOT NULL,create_time DATE NOT NULL,create_time_1 DATETIME NOT NULL,create_time_2 TIME NOT NULL,author_introduction TEXT(100) NOT NULL, category ENUM('Python','Java','C'), price DECIMAL(8, 4),PRIMARY KEY (id))2020-08-01T10:31:48.010315Z 43 Query COMMIT2020-08-01T10:31:48.013469Z 43 Query ROLLBACK2020-08-01T10:31:54.722323Z 40 Query show tables like 'a%'
在新建的表的基础上,插入一些数据,改造一下:
#!/usr/bin/env python# coding=utf-8from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,String,Integer,Date,DateTime,Time,Text,Enum,DECIMALfrom sqlalchemy.dialects.mysql import LONGTEXTimport datetime# 1.创建engineengine = create_engine('mysql+pymysql://root:2008.Cn123@192.168.0.101:3306/sql_learn')# 2.创建Session池Session = sessionmaker(bind=engine)# 3.实例化一个Sessionsession = Session()# 4.创建Model# 继承于基类Base = declarative_base()# 创建映射表class Article(Base): __tablename__ = 'articles' id = Column(Integer, primary_key=True, nullable=False) author = Column(String(45), nullable=False) detail = Column(LONGTEXT, nullable=False) create_time = Column(Date, nullable=False) create_time_1 = Column(DateTime, nullable=False) create_time_2 = Column(Time, nullable=False) author_introduction = Column(Text(100), nullable=False) category = Column(Enum('Python','Java','C'), nullable=True) price = Column(DECIMAL(8,4), nullable=True)# 先删除绑定的表Base.metadata.drop_all(engine)# 创建绑定到 Base 上的表Base.metadata.create_all(engine)# 添加数据# 实例化类art1 = Article(id=1,author='Jack', detail='Test for SQLAlchemy', create_time=datetime.date(year=2020, month=8,day=1), create_time_1=datetime.datetime(year=2017, month=11, day=11, hour=11, minute=11, second=11, microsecond=11), create_time_2=datetime.time(hour=11, minute=11, second=11, microsecond=11), author_introduction='Popular People', category='Python', price=8888.8888 )# 添加到sessionsession.add(art1)# 提交session.commit()同样的,在
mysql中查询:mysql root@192.168.0.101:sql_learn> show tables like 'a%'; +--------------------------+| Tables_in_sql_learn (a%) |+--------------------------+| articles |+--------------------------+mysql root@192.168.0.101:sql_learn> select * from articles;也可以通过日志查看更详细的输出:
# 需要ssh到192.168.0.101[root@localhost ~]# tail -20 /mysql_data/localhost.log查看表的创建,应该和我们的
python类相对应.mysql root@192.168.0.101:sql_learn> desc articles;+---------------------+---------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------------------+---------------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | <null> | auto_increment || author | varchar(45) | NO | | <null> | || detail | longtext | NO | | <null> | || create_time | date | NO | | <null> | || create_time_1 | datetime | NO | | <null> | || create_time_2 | time | NO | | <null> | || author_introduction | text | NO | | <null> | || category | enum('Python','Java','C') | YES | | <null> | || price | decimal(8,4) | YES | | <null> | |+---------------------+---------------------------+------+-----+---------+----------------+归档一下:
$ git add .$ git commit -m "sqlalchemy column数据类型,v2.002"
2.Column列约束级别
SQL中约束分为表级别约束和列级别约束.列级别约束:
PRIMARY KEY:主键约束
FOREIGN KEY:外键约束,不允许重复
UNIQUE:唯一性约束
NOT NULL:非空约束
DEFAULT:默认约束
CHECK: 检查约束表级别约束:
PRIMARY KEY:主键约束
FOREIGN KEY:外键约束,不允许重复
UNIQUE:唯一性约束他们的区别是:
列级别约束只能应用与一列上,而表级别约束可以用在多个列上.
列级别约束直接跟在列后,不用指定列名.而表级别约束必须指定约束列名称.
这里的
Column函数主要是用来定义列级别的约束的.
SQLAlchemy与Mysql中常用约束的对应.from sqlalchemy import Column# Column级别的约束
SQLAlchemyMySQLprimary_key=Trueprimary keyfrom sqlalchemy import ForeignKeyforeign keyunique=Trueuniquenullable=Truenot nulldefault=xxdefaultname指定别名 asautoincrement=Trueauto_incrementcomment列的描述字符commentindex=primary_keyindex
# 需要导入 外键 类来实现Column中外键的约束from sqlalchemy import ForeignKeyclass Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, Foreignkey=('child.id')) # 在这里指定的是表名和字段
3.表级别约束
Column中定义了列级别的约束.如果要定义表级别的约束,需要导入相关的模块from sqlalchemy import ForeignKeyConstraint,UniqueConstraint,PrimaryKeyConstraint它的创建类似与这样
# 表级别外键约束from sqlalcemy import Table# 注意,这里导入的是 Table 对象invoice_item = Table('invoice_item', metadata,Column('item_id', Integer, primary_key=True),Column('item_name', String(60), nullable=False),Column('invoice_id', Integer, nullable=False),Column('ref_num', Integer, nullable=False),ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num']))# 表级别唯一约束mytable = Table('mytable', metadata,# per-column anonymous unique constraintColumn('col1', Integer, unique=True),Column('col2', Integer),Column('col3', Integer),# explicit/composite unique constraint. 'name' is optional.UniqueConstraint('col2', 'col3', name='uix_1'))# 主键约束my_table = Table('mytable', metadata, Column('id', Integer), Column('version_id', Integer), Column('data', String(50)), PrimaryKeyConstraint('id', 'version_id', name='mytable_pk') )
