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.

1. 打开命令行工具,创建一个保存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 的一个类,让我们以对象的方式来操作表.

1. 描述将要处理的表

2. 定义包含指令的类

3. 将类映射到表.映射的类必须根据基类来定义.这个基类维护相对于基类的类和表的目录,这称为声明性基类.使用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

Mysql	SQLAlchemy
smallint	Boolean
int	Integer
float	Float
decimal定点数	DECIMAL
date	Date
datetime	DateTime
time	Time
enum	Enum
text	TEXT

---

 # 通过 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级别的约束

SQLAlchemy	MySQL
primary_key=True	primary key
from sqlalchemy import ForeignKey	foreign key
unique=True	unique
nullable=True	not null
default=xx	default
name	指定别名as
autoincrement=True	auto_increment
comment列的描述字符	comment
index=primary_key	index

---

 #  需要导入 外键 类来实现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')  )