SQLAlchemy

SQLAlchemy介绍和基本使用

数据库是一个网站的基础，在Flask中可以*的使用MySQL、PostgreSQL、SQLite、Redis、MongoDB来写原生的语句实现功能，也可以使用更高级别的数据库抽象方式，如SQLAlchemy或MongoEngine这样的OR(D)M.

通过SQLAlchemy 连接数据库

from sqlalchemy import create_engine

#数据库的配置变量

HOSTNAME = ‘127.0.0.1‘

PORT = "3306"

DATABASE= ‘xt_flask‘

USERNAME =‘root‘

PASSWORD = ‘root‘

DB_URI = ‘mysql pymysql://{}:{}@{}:{}/{}‘.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)

创建数据库引擎

engine = create_engine(DB_URI)

#创建连接

with engine.connect() as con

rs = con.execute(‘SELECT 1‘)

print(rs.fetchone())

首先从sqlalchemy中导入create_engine，用这个函数来创建引擎，然后用engine.connect()来连接数据库。其中一个比较重要的一点是，通过create_engine函数的时候，需要传递一个满足某种格式的字符串，对这个字符串的格式来进行解释

dialect driver://username:[email protected]:port/database

dialect是数据库的实现，比如MySQL、PostgreSQL、SQLite，并且转换成小写

driver是python对应的驱动，如果不指定，会选择默认的驱动，比如MySQL的默认驱动是MySQLdb。

username是连接数据库的用户名

password是连接数据库的密码

host是连接数据库的域名

port是数据库监听的端口号

database是连接哪个数据库的名字

如果以上输出了1，说明SQLAlchemy能成功连接到数据库

用SQLAlchemy执行原生SQL:

我们将上一个列子中的数据库配置选项单独放在一个constants.py的文件中，

from sqlalchemy import create_engine

from constants import DB_URI

#连接数据库

engine = create_engine(DB_URI,echo=True)

#使用with语句连接数据库，如果发生异常会被捕获

with engine.connect() as con:

#先删除users表

con.execute(‘drop table if exists users‘)

#创建一个users表，有自增长的id和name

con.execute(‘create table users(id int primary key auto_increment,name varchar(25))‘)

#插入两条数据到表中

con.execute(‘insert into users(name) values("xiaoming")‘)

con.execute(‘insert into users(name) values(‘xiaotuo)‘)

#执行查询操作

rs = con.execute(‘select * from users‘)

for row in rs:

print(row)

ORM介绍
- ORM 叫做对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不用再写原生的SQL语句。通过把表映射成类，把行作为实例，把字段作为属性。ORM在执行对象操作的时候最终还是会把对应的操作转换为数据库原生语句。使用ORM有许多优点
  - 1.易用性: 使用ORM做数据库的开发可以有效的减少重复SQL语句的概率，写出来的模型也更加直观、清晰
  - 2.性能损耗小: ORM转换成底层数据库操作指令确定会有一些开销。但从实际的情况来看，这种性能损耗很少(不足5%),只要不是对性能有严苛的要求，综合考虑开发效率，代码的阅读性，带来的好处要远远大于性能损耗，而且项目越大作用越明显。
  - 3.设计灵活：可以轻松的写出复杂的查询
  - 4.可移植性: SQLAlchemy封装了底层的数据库实现，支持多个关系数据库引擎，包括流行的MySQL、PostgreSQL和SQLite。可以非常轻松的切换数据库

定义ORM模型并映射到数据库

1.用‘declarative_base‘根据engine创建一个ORM基类

  from  sqlalchemy.ext.declarative import declarative_base
  engine = create_engine(DB_URI)
  Base = declarative_base(engine)

2.用这个Base类作为基类来写自己的ORM类。要定义‘__ tablename __‘类属性，来指定这个模型映射到数据库中的表名
- ```
class  Person(Base):
 __ tablename__ = ‘person‘
```

3.创建属性来映射到表中的字段,所有需要映射到表中的属性都应该为Column类型

需要导入

from sqlalchemy import Column,Integer,String
?
  Integer 为整型  String为一个类 (字符型)
 

class Person(Base):
?
      __ tablename__ = ‘person‘
      #2.在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射。这些属性必须是sqlalchemy给我们提供好的数据类型
      id = Colum(Integer,primary_key = True,autoincrement=True)
        primary_key = True代表是否设置为主键
        autoincrement=True 代表是否设置为自增长列
      name = Column(String(50))
      age = Column(Integer)

4.使用‘Base.metadata.create_all()‘来将模型映射到数据库中
4.1.使用‘Base.metadata.drop_all()‘删除映射到数据库中的模型
5.一旦使用‘Base.metadata.create_all()‘将模型映射到数据库中后，即使改变了模型的字段，也不会重新映射了

SQLALchemy数据增删改查

#创建session会话对象

  导入sessionmaker   from sqlalchemy.orm import sessionmaker
  engine = create_engine(DB_URI)
  session = sessionmaker(engine)()  #以后数据提交到数据库都通过这个对象
 

class Person(Base):
?
- __ tablename__ = ‘person‘
- #2.在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射。这些属性必须是sqlalchemy给我们提供好的数据类型
- id = Colum(Integer,primary_key = True,autoincrement=True)
  - primary_key = True代表是否设置为主键
  - autoincrement=True 代表是否设置为自增长列
- name = Column(String(50))
- age = Column(Integer)

#添加数据

def add_data():
?
- 向表中添加一条数据
- person = Person(name=‘jack‘,age=20)
- session.add(person)
- #添加数据后、数据保存到电脑内存上，并没有添加到数据库中，需要使用session.commit()方法将数据提交到数据库中
- 向表中添加多条数据(如需添加多条数据、只需使用add_all方法将多条数据添加到一个列表即可)
  - person1 = Person(name = ‘blue‘,age=30)
  - person2 = Person(name=‘tom‘,age=23)
  - session.add_all([person1,persom2])
  - session.commit()

查询数据

def select_data():
?
      #查询表中数据
      results = session.query(Person).all()
      for r in results:
        print(r)
      #查询表中第一条数据
      first = session.query(Person).first()
      print(first)
      #查询表中name为tom的第一条数据
      result = session.query(Person).filter_by(name=‘tom‘).first()
      print(result)
      #查询表中年龄大于20的数据(结果为list，故用for循环遍历)
      results = session.query(Person).filter(Person.age>20).all()
      for r in results:
        print(r)

filter:多用于简单查询, filter_by:用于复杂查询，使用filter作为查询过滤条件时，需要在前面添加对象名，如Person.age

修改数据: 将需要修改的数据从数据库中查找出来,然后使用查找出来的数据.变量名 = ‘tlj‘,就可以将原来数据中的一个属性值修改为你指定的值，然后session.commit()

def update_date():
?
      查询表中第一条数据，将其姓名修改为tlj
      result = session.query(Person).first()
      result.name = ‘tlj‘
      session.commit()
      print(result)

删除数据: 将需要删除的数据从数据库中查找出来，然后使用session.delete方法将这条数据从session中删除，最后做commit操作就可以了
- ```
def del_data():
?
      result = session.query(Person).first()
      session.delete(result)
      session.commit()
```

SQLAlchemy属性常用数据类型详解
- sqlalchemy常用数据类型:
  - Integer: 整型映射到数据库中是int类型
  - Float: 浮点类型映射到数据库中是float类型，它占据的32位
  - Boolean: 传递True/False 进去映射到数据库中的是tinyint类型
  - Double：双精度浮点类型，映射到数据库中的是double类型，占据64位
  - String: 可变字符类型，映射到数据库中是varchar类型
  - enum:枚举类型，映射到数据库中的是enum类型指定某个字段只能是枚举中指定的几个值，不能为其他值。ORM模型中，使用Enum来作为枚举。示例代码如下
    - tag = Column(Enum(‘python‘,‘flask‘,‘django‘))
  - DECIMAL: 定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型，并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能存储多少个数字，第二个参数表示小数点后多少位
  - Date: 存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，传递datetime.date()进去。示例如下
    - ```
      class Article(Base):
        create_time = Column(Date)
      from datetime import date
      article = Article(create_time = date(2017,10,10))
```
- DateTime: 存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用‘datetime.datetime‘来指定。示例代码如下
  - ```
    class  Article(Base):
      create_time = Colum(DateTime)
    from datetime import datetime
    article = Article(create_time=datetime(2011,11,11,11,11,11))
```
  - Time: 存储时间，可以存储时分秒，映射到数据库中也是time类型。在Python代码中，可以使用datetime.time来指定。示例代码如下
    - ```
      class Article(Base):
        create_time = Colum(Time)
      from datetime import time
      article = Article(create_time= time(hour=11,minute=11,second=11))
```
- Text: 文本类型。存储字符串，一般可以存储6w多个字符。如果超出了这个范围，可以使用LONGTEXT。映射到数据库中就是text类型。
- LONGTEXT: 长文本类型。
  - 需要这样导入 from sqlalchemy.dialects.mysql import LONGTEXT
Column常用参数:
- from sqlalchemy import Column
- defaule: 设置某个字段的默认值。在发表时间这些字段上面经常用
- nullable: 指定某个字段是否为空。默认值是True，就是可以为空
- primary_key: 设置某个字段是否为主键
- unique: 指定某个字段的值是否唯一。默认是False
- autoincrement: 是否自动增长
- onupdate: 在数据更新的时候会调用这个参数指定的值或函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是‘update_time‘(每次更新数据的时候都要更新的值)
- name: 指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为参数。这个参数也可以当作位置参数，在第一个参数来指定
  - ```
  - title = Column(‘my_title‘,String(50),nullable=False)
  - title = Column(String(50),name=‘my_title‘,nullable=False)
```
query函数可查询的参数
- 1.模型对象。指定查找这个模型中所有的对象
  - session.query(Article).all() 查找Article表中的所有的数据并返回一个数组
- 2.模型中的属性。可以指定只查找某个模型的其中几个属性
  - session.query(Article.title).all() 查找Article表title字段的所有的数据并返回一个数组
- 3.聚合函数
  - func.count ; 统计行的数量
  - func.avg;求平均值
  - func.max;求最大值
  - func.min; 求最小值
  - func.sum; 求和
  - func上，其实没有任何聚合函数，但是因为他底层做了一些魔术，只要mysql中有的聚合函数，都可以通过func调用
  - session.query(func.count(Article.id)).first()
    - 计算Article表中id这个字段的行数

filter过滤条件

过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现的

equals 判断什么等于什么

session.query(Article).filter(Article.title == ‘title0‘).first()

not equals 判断什么不等于什么

session.query(Article).filter(Article.title != ‘title0‘).all()

like 模糊查询 ilike(不区分大小写)

session.query(Article).filter(Article.title.like(‘title%‘)).all()

in 查询满足列表里面的所有数据

session.query(Article).filter(Article.title.in_([‘title1‘,‘title2‘])).all()

not in 查询不满足列表里面的所有数据

session.query(Article).filter(~Article.title.in_([‘title1‘,‘title2‘])).all()

等价于 session.query(Article).filter(Article.title.notin_([‘title1‘,‘title2‘])).all()

is null 判断某个字段是否为空

session.query(Article).filter(Article.title ==null).all()

is not null 判断某个字段是否不为空

session.query(Article).filter(Article.title !=null).all()

and 可以添加多个过滤条件

session.query(Article).filter(Article.title==‘abc‘,Article.content==‘abc‘).all()

等价于

from sqlalchemy import and_

session.query(Article).filter(and_(Article.title==‘abc‘,Article.content==‘abc‘)).all()

or 两者满足一个条件即查询到

from sqlalchemy import or_

session.query(Article).filter(or_(Article.title==‘abc‘,Article.content==‘abc‘)).all()

如果想要查看ORM底层转换的sql语句，可以在filter方法后面不要再执行任何方法直接打印就可以看到了比如:
- ```
articles=session.query(Article).filter(and_(Article.title==‘abc‘,Article.content==‘abc‘))

print(articles)
```

外键及其四种约束
- 表关系：
  - 表之间的关系存在三种: 一对一、一对多、多对多。而SQLAlchemy中的ORM也可以模拟这三种关系。因为一对一其实再SQLALchemy中底层是通过一对多的方法模拟的。所以先来看下一对多的关系；
- 外键：
  - 使用SQLAlchemy创建外键非常简单。在从表中增加一个字段。指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和父表的主键字段类型保持一致
  - 再Mysql中，外键可以让表之间的关系更加紧密。而SQLAlchemy同样也支持外键。通过ForeignKey类来实现，并且可以指定表的外键约束。相关实例代码如下:
    - ```
      class Article(Base):
        __ tablename __ =  ‘article‘
        id = Column(Integer,primary_key = True,autoincrement = True)
        title = Column(String(50),nullable = Fasle)
        content = Column(Text,nullable = False)
        uid = Column(Integer,ForeignKey(‘user.id‘,ondelete=‘RESTRICT‘))
        def __ repr __(self):
          return self.title
      class User(Base):
        __ tablename__ = ‘user‘
        id = Column(Integer,primary_key = True, autoincrement = True)
        username = Column(String(50),nullable = False)
```
- 外键约束有以下几项:
  - 1.RESTRICT：父表数据被删除，会阻止删除. 默认参数这一项
  - 2.NO ACTION: 再MySQL中，同RESTRICT
  - 3.CASCADE：级联删除父表的数据被删除，子表的数据也会被删除
  - 4.SER NULL：父表数据被删除，子表数据会设置为NULL
  - 外键约束放在 ForeignKey这个类里面的 ondelete参数里面
ORM层外键和一对多关系
- 导入 from sqlalchemy.orm import relationship
- mysql级别的外键，还不够ORM，必须拿到一个表中的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了，SQLAlchemy提供了一个‘relatioship‘，这个类可以定义属性，以后再访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。实例代码
  - ```
    class Article(Base):
    - __ tablename __ =  ‘article‘
    - id = Column(Integer,primary_key = True,autoincrement = True)
    - title = Column(String(50),nullable = Fasle)
    - content = Column(Text,nullable = False)
    - uid = Column(Integer,ForeignKey(‘user.id‘,ondelete=‘RESTRICT‘))
    - author = relationship("User",backref = ‘articles‘)
    - def __ repr __(self):
      - return self.title
    class User(Base):
    - __ tablename__ = ‘user‘
    - id = Column(Integer,primary_key = True, autoincrement = True)
    - username = Column(String(50),nullable = False)
    - //articles = relationship(‘Article‘)
```
- 另外，可以通过‘backref‘来指定反向访问的属性名称。articles是有多个，他们之间的关系是一个一对多的关系
  - 在从表中指定了backref这个属性后，父表就会自动获取到从表中的所有内容。前提是需要通过外键将父表和从表连接起来
    - 在Article中写
      
      author = relationship("User",backref = ‘articles‘)
    - 那么User父表中就会根据backref = ’articles‘这句话自动生成
      
      //articles = relationship(‘Article‘) 然后我们就可以根据articles访问到从表中的所有内容

一对一关系的实现

在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个‘uselist=False‘ 这个参数进行，就是告诉父模型。以后引用这个从模型的时候，不再是一个列表了，而是一个对象了实例代码如下

  class User(Base):  父表
  - __ tablename__ = ‘user‘
  - id = Column(Integer,primary_key = True, autoincrement = True)
  - username = Column(String(50),nullable = False)
  - //extend= relationship(‘UserExtend‘,uselist=False)
  class UserExtend(Base):  从表
    __ tablename__ = ‘user_extend‘
    id = Column(Integer,primary_key = True, autoincrement = True)
    school= Column(String(50),nullable = False)
    uid = Column(Integer,ForeignKey(‘user.id‘))
    user= relationship(‘User‘,backref=backref(‘extend‘,uselist=False))

当然也可以借助 sqlalchemy.orm.backref来简化代码

  //extend= relationship(‘UserExtend‘,uselist=False) 等价于
  user= relationship(‘User‘,backre=backref(‘extend‘,uselist=False)

在一对多关系中，并且父表和从表通过外键给连接起来了，并且都建立了relationship属性，那么我们添加数据的时候。我们就可以创建好父表和从表的数据。通过列表的形式添加从表的数据

  user1 = User(username=‘xiaoxin‘)
  userextend = UserExtend(school = ‘shaoyi‘,uid=1)
  user1.extend.append(userextend)  #因为user.extend是一个列表   而且通过user.extend可以访问从表中的所有数据，所以我们可以这样给从表添加数据
  session.add(user1)
  session.commit()

在一对一添加数据的时候
- 我们在从表中指定
  - user= relationship(‘User‘,backre=backref(‘extend‘,uselist=False))
- 或者在父表中指定
  - //extend= relationship(‘UserExtend‘,uselist=False)
- uselist = Fasle 将父表和从表映射成一对一的关系，也就是说从表数据将不是一个列表形式
- 添加数据的时候就要按照属性的方式进行添加
  - ```
    user1 = User(username=‘xiaoxin‘)
    userextend = UserExtend(school = ‘shaoyi‘,uid=1)
    user1.extend=userextend  #一对一添加数据的方式
    session.add(user1)
    session.commit()
```

多对多关系的实现

多对多的关系需要通过一张中间表来绑定他们之间的关系
1.先把两个需要做多对多的模型定义出来
2.使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”
3.在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary = 中间表

实例代码如下：

  #创建一个中间表
  article_tag = Table(
  ‘article_tag‘,   //中间表的名字
  Base.metadata, //将中间表放在Base这个类里面
  Column(‘article_id‘,Integer,ForeignKey(‘article.id‘),primary_key=True)
  Column(‘tag_id‘,Integer,ForeignKey(‘tag.id‘),primary_key=True)
  )
  #创建两个多对多的表
  class  Article(Base):
    __ tableaname __ = ‘article‘
    id = Column(Integer,primary_key = True,autoincrement=True)
    title = Colum(String(50),nullable=False)
  class Tag(Base):
    __ tablename__ = ‘tag
    id = Column(Integer,primary_key = True,autoincrement=True)
    name= Colum(String(50),nullable=False)
   articles=relationship("Article",backref=‘tags‘,secondary=article_tag)

我们可以查询到tag表然后通过articles这个属性可以访问到Article表里面的所有内容实例代码
- ```
  tag= session.query(Tag).first()
  print(tag.articles)
```
我们可以查询到Article表然后可以通过tags这个属性可以访问到Tag表里面的所有内容实例代码
- ```
  article = session.query(Article).first()
  print(article.tags)
```

ORM层面删除数据的注意事项
- 就是直接用session.delete()删除数据的时候，会直接删除数据。然后将对应的从表的外键设置为NULL
- ORM层面删除数据，会无视mysql级别的外键约束，直接会将对应的数据删除，然后将从表中的那个外键设置为NULL，如果想要避免这种行为，应该将从表中的外键的‘nullable=False‘
relationship方法中的cascade参数详解
- ORM层面的CASCADE
  - 如果将数据库的外键设置为RESTRICT，那么在ORM层面，删除了父表中的数据，那么从表中的数据将会NULL，如果不想要这种情况发生，那么应该将这个值的nullable=False
  - 在SQLAlchemy，只要将一个数据添加到session中，和她相关联的数据都可以一起存入到数据库中了。其实是通过relationship的时候，有一个关键字参数cascade可以设置这些属性：
    - 1.save-update: 默认选项，在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的
    - 2.delete：表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据
    - 3.delete-orphan:表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent = True的参数
    - 4.merge：默认选项。当在使用session.merge,合并一个对象的时候，将会使用了relationship相关联的对象也进行merge操作
    - 5.expunge:移除操作的时候，会将相关联的对象也进行移除，这个操作只是从session中移除，并不会真正的从数据库中删除
    - 6.all: 是对save-update,merge,refresh-expire,expunge,delete 几种的缩写
三种排序方式详解
- 1.order_by: 可以指定根据这个表中的某个字段进行排序，如果在前面加了一个. ,代表是降序排序
  - ```
    articles = session.query(Article).order_by(Article.create_time).all() //升序 等价于
      articles = session.query(Article).order_by("create_time").all() 
    articles =session.query(Article).order_by(Article.create_time.desc()).all() //降序 等价于
      articles = session.query(Article).order_by("-create_time").all() 
```
- 2.在模型定义的时候指定默认排序：有些时候，不想每次在查询的时候都指定排序的方式，可以在定义模型的时候就指定排序的方式。有以下两种方式：
  - relationship的order_by参数：在指定relationship的时候，传递order_by参数来指定排序的字段。
  - 在模型定义中，添加以下代码
    - ```
    __ mapper _args __ = {
    
    "order_by": title
    
    }
```
- 即可让文章使用标题来进行排序
- 3.正向排序和反向排序：默认情况是从小到大，从前到后排序的，如果想要反向排序，可以调用排序的字段的desc方法或者是在排序的时候使用这个字段的字符串名字，然后再前面加一个负号

limit、offset以及切片操作

1.limit：可以限制每次查询的时候只查询几条数据

  articles = session.query(Article).limit(10).all()  #查找前10篇文章
  articles = session.query(Article).order_by(Article.id.desc()).limit(10).all()  #查找最后10篇文章

2.offset: 可以限制查找数据的时候过滤掉前面多少条
- articles = session.query(Article).offset(10).limit(10).all() #过滤掉前面10篇文章，然后从第10篇文章开始取10篇文章出来

3.切片：可以对Query对象使用切片操作，来获取想要的数据

导入Query类

  from sqlalchemy.orm.query import Query
  #使用slice切片查找最后10篇文章   start(start,stop)
    articles = session.query(Article).order_by(Article.id.desc()).slice(0,10).all() 
  使用简写切片操作 查找最后10篇文章 [start,stop]
    articles = session.query(Article).order_by(Article.id.desc())[0:10]  #查找最后10篇文章

一般来实际开发中，中括号的形式是用得比较多的

数据查询懒加载技术
- 在一对多，或者多对多的时候，如果想要获取多的这一部分的数据的时候，往往能通过一个属性就可以全部获取了。比如有一个作者，想要获取这个作者的所有文章，那么可以通过user.articles就可以获取所有的。但有时候我们不想获取所有的数据。比如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship传递一个lazy=‘dynamic‘,以后通过user.articles获取到的就不是一个列表，而是一个AppenderQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作
  - ```
    在relationship里面设置lazy= ‘dynamic‘
      author = relationship(‘User‘,backref=backref(‘articles‘,lazy=‘dynamic‘))
    定义好之后再去查询User表
      user = session.query(User).first()  #是一个AppenderQuery对象
    现在我们需要查询articles表里面id大于50的数据
      user.articles.filter(Article.id>50).all()
    还可以继续追加数据进去
      article = Article(title=‘title 100‘)
      user.articles.append(article)
      session.commit()
```
- 通过lazy = ‘dynamic‘，获取出来的多的那一部分，就是一个AppenderQuery对象了。这种对象既可以添加新数据，也可以跟Query一样，可以再进行一层过滤
  - 总而言之一句话：如果你再获取数据的时候，想要对多的那一步的数据再进行一层过滤，那么这时候就可以考虑使用lazy = ‘dynamic‘
  - lazy可用的选项
    - select:这个是默认选项。还是拿‘user.articles‘来讲。如果你没有访问user.articles这个属性，那么sqlalchemy就不会从数据库中查找文章。一旦你访问了这个属性，那么sqlalchemy就会立马从数据库中查找所有的文章，并把查询出来的数据组装成一个列表返回。这也是懒加载
    - dynamic：这个就是再访问user.articles的时候返回回来的不是一个列表，而是AppenderQuery对象

查询高级

group_by子句
- 根据某个字段进行分组。比如想要根据性别进行分组，来统计每个分组分别有多少人，那么可以使用以下代码来完成：
  - ```
  session.query(User.gender,func.count(User.id)).group_by(User.gender).all()
```
having子句:
- having是对查找结果进一步过滤。比如只想要看未成年人的数量，那么可以首先对年龄进行分组统计人数，然后再对分组进行having过滤示例代码如下:
  - ```
  result=session.query(User.gender,func.count(User.id)).group_by(User.gender).having(User.age>=18).all()
```

join方法：

join查询分为两种，一种是inner join，另一种outer join 默认的是 inner join，如果指定left join 或者是 right join 则为outer join. 如果想要查询User 及其对应的Address,则可以通过以下方式来实现:

result= session.query(User,func.count(Article.id)).filter(User.id==Article.uid).all()

这是通过普通方式的实现，也可以通过join的方式实现，更加简单

result1 = session.query(User,func.count(Article.id)).join(Article,User,id==Article.uid).all()

等价于

result1 = session.query(User,func.count(Article.id)).join(Article).all()

再sqlalchemy中，使用join来完成内连接，再写join的时候，如果不写join的条件，那么默认讲使用外键来作为条件连接

query查找 出来什么值，不会取决于join后面的东西，而是取决于query方法中传了什么参数，就跟元素sql的select后面那一个一样

比如现在要实现一个功能，要查找所有用户，按照发表文章的数量来进行排序。

result1 = session.query(User,func.count(Article.id)).join(Article).group_by(User.id).order_by(func.count(Article.id).desc()).all()

别名：

当多表查询的时候，有时候同一个表要用到多次，这时候用别名就可以方便的解决命名冲突的问题了

  from sqlalchemy.orm import aliased
  adalias1 = aliased(Address)
  adalias2 = aliased(Address)
  for username, email1,email2 in session.query(User.name,adalias1.email_address,adalias2.email_address).join(adalias1).all()
    print(username,email1,email2)

subquery实现复杂子查询
- 子查询可以让多个查询变成一个查询，只要查找一次数据库，性能相对来讲更加高效一点。不用写多个sql语句就可以实现一些复杂的查询。那么再sqlalchemy中，要实现一个子查询，应该使用以下几个步骤：
  - 1.将子查询按照传统的方式写好查询代码，然后再‘query‘对象后面执行‘subquery‘方法，将这个查询变成一个子查询
  - 2.在子查询中，将以后需要用到的字段通过‘label‘方法，取个别名
  - 3.在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的‘c‘属性拿到
  - 整体的代码示例如下
    - ```
    实现一个子查询
    
    stmt = session.query(User.city.label("city"),User.age.label("age")).filter(User.username=="李A“).subquery()
    在父查询中使用子查询
    
    result = session.query(User).filter(User.city==stmt.c.city,Usser.age==stmt.c.age).all()
```

Flask-sqlalchemy使用详解
- 数据库连接：
  - 1.跟sqlalchemy一样，定义好数据库连接字符串DB_URI。
  - 2.将这个定义好的数据库连接字符串DB_URI，通过‘SQLALCHEMY_DATABASE_UR‘这个键放到‘app.config‘中。示例代码如下
    - app.config[‘SQLALCHEMY_DATABASE_URI‘] =DB_URL
  - 3.使用‘flask_sqlalchemy.SQLAlchemy‘这个类定义一个对象,并将app传入进去。示例代码
    - db = SQLAlchemy(app)
- 创建ORM模型
  - 1.还是跟使用sqlalchemy一样，定义模型。现在不再是需要使用‘delarative_base‘来创建一个基类，而是使用db.Model来作为基类
  - 2.在模型类中,Column,String,Integer,以及relationship等都不需要导入了，直接使用db下面相应的属性名就可以了
  - 3.在定义模型的时候，可以不写__ tablename__,那么flask_sqlalchemy会默认使用当前的模型的名字转化成小写来作为表的名字，并且如果这个模型的名字使用了多个单词，并且使用了驼峰命名法，那么会在多个单词之间使用下划线来进行连接
    - 虽然flask_sqlalchemy给我们提供了这个特性，但是不推荐使用。因为名言胜于暗喻
    - ```
    class User(db.Model):   
    ?
       __ tablename__ = ‘user‘   
       id = db.Column(db.Integer,primary_key = True,autoincrement = True)    
       username = db.Column(db.String(50),nullable = False)    
       def __ repr__(self):        
         return  self.username
```
- 将ORM模型映射到数据库
  - 1.db.drop_all() 删除所有的表
  - 2.db.create_all() 创建表
- 使用session:
  - 以后session也不需要使用sessionmaker来创建了，直接使用db.session就可以了。操作这个session的时候就跟之前的sqlalchemy的session是一样的
    - ```
      #添加数据user = User(username = ‘xiaoxin‘)
      article = Article(title = ‘title one‘)
      article1 = Article(title= ‘title two‘)
      user.articles = [article,article1]
      db.session.add(article)
      db.session.commit()
```
- 查询数据：
  - 如果查询数据只是查找一个模型上的数据，那么可以通过模型.query的方式进行查找。query就跟之前的sqlalchemy中的query方法是一样用的示例代码如下
    - ```
      users = User.query.order_by(User.id.desc()).all()
      print(users)
    User.query.get(2) get查询接收的参数为主键，如果不存在，返回空
```

alembic教程

alembic用来做ORM模型与数据库的迁移与映射。alembic使用方式跟git有点了类似，表现在两个方面，第一个，alembic的所有命令都是以alembic开头；第二，alembic的迁移文件也是通过版本进行控制的。以下将解释alembic的用法

1.初始化alembic仓库：在终端中，cd到你项目目录中，然后执行命令 alembic init alembic,创建一个名叫 alembic的仓库

2.创建模型类：创建一个models.py然后在里面定义你的模型类，示例代码如下

  from sqlalchemy  import Column,Integer,String,create_engine,Text
  from sqlalchemy.orm  import  sessionmaker
  from sqlalchemy.ext.declarative import declarative_base
  engine = create_engine(DB_URL)
  Base = declarative_base(engine )
  class  User(Base):
    __ tablename__ = ‘user‘
    id = Column(Integer,primary_key = True)
    username = Column(String(20),nullable = False)
    password = Colum(String(100),nullable = False)
  class Article(Base):
    __ tablename__ = ‘article‘
    id = Column(Integer,primary_key = True)
    title = Column(String(100),nullable = False)
?
    content = Column(Text,nullable = False)

3.修改配置文件：
- 在alembic.ini中设置数据库连接 sqlalchemy.url = driver://user:[email protected]
  
  比如以mysql数据库为列，则配置后的代码为
  - sqlalchemy.url = mysql mysqldb://root:[email protected]/alembic_demo?charset=urt-8
- 为了使用模型类更新数据库，需要在env.py文件中设置target_metadata,默认为target_metadata = None.使用sys模块把当前项目的路径导入到path中
  - ```
    import  os
    import sys
    sys.path.append(os.path.dirname(os.path.abspath(__ file__)) "/../")
    from models import Base
    target_metadata = Base.metadata   # 设置创建模型的元类
```
- 4.自动生成迁移文件: 使用alembic revision --autogenerate -m "message" 将当前模型中的状态生成迁移文件
- 5.更新数据库：使用 alembic upgrade head 将刚刚生成的迁移，真正映射到数据库中。同理，如果要降级，那么使用alembic downgrade head
- 6.修改代码后重复4~5的步骤
- 7.命令和参数解释、
  - init: 创建一个 alembic仓库
  - revision: 创建一个新的版本文件
  - --autogenerate: 自动将当前模型的修改，生成迁移脚本
  - -m: 本次迁移做了哪些修改，用户可以指定这个参数，方便回顾
  - upgrade: 将指定版本的迁移文件映射到数据库中，会执行版本文件中的upgrade函数
  - head: 展示当前可用的heads脚本文件
  - history：列出所有的迁移版本及其信息
  - current:展示当前数据库中的版本号
- 另外，在你第一个执行upgrade的时候，就会在数据库中创建一个名叫 alembic_version表，这个表只会有一条数据，记录当前数据库映射的是哪个版本的迁移文件
使用alembic的步骤：
- 1.定义好自己的模型
- 2.使用alembic创建一个仓库:‘alembic init [仓库的名字，推荐使用alembic]‘。
  - sqlalchemy.url = mysql pymysql://root:[email protected]/alembic_demo
- 3.修改配置文件：
  - 在‘alembic.ini‘中，给sqlalchemy.url
    - 设置数据库的连接方式，这个连接方式跟sqlalchemy的方式一样的
  - 在‘alembic/env.py‘中的‘target_metadata‘设置模型的‘Base.metadata‘，但是要导入‘models‘ 需要将models所在的路径添加到这个文件中。示例代码如下
    - import sys,os
    - sys.path.append(os.path.dirname(os.path.dirname(__ file__)))
- 4.将ORM模型生成迁移脚本：alembic revision --autogenerate -m ‘message‘
- 5.将生成的脚本映射到数据库中: alembic upgrade head
- 6.以后如果修改了模型，重复4、5步骤
- 7.注意事项：在终端中，如果想要使用alembic，则需要首先进入到安装了alembic的虚拟环境中，不然就找不到这个命令

经典错误

错误描述	原因	解决办法
FAILED: Target database is not up to dates	主要是heads和current不相同，current落后于heads的版本	将current移动到head上。alembic upgade haed
FAILED: Can‘t locate revision ident ified by ‘77525ee61b5b‘	数据库中存的版本号不在迁移脚本文件中	删除数据库的 alembic_version表中的数据.重新执行alembic upgrade head

执行‘upgrade head 时报某个表已经存在的’错误
- 原因：执行这个命令的时候，会执行所有的迁移脚本，因为数据库中已经存在了这个表，然后迁移脚本中又包含了创建表的代码
- 解决办法：
  - 1.删除versions中所有的迁移文件
  - 修改迁移脚本中创建表的代码

相关文章