【Flask 数据库 操作】 SQLAlchemy 与 Flask-SQLAlchemy

SQLAlchemy

示例：

# pip install sqlalchemy
# pip install pymysql
from sqlalchemy import create_engine
DB_URI = f'数据库的名+驱动名://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'
engine = create_engine(DB_URI)
sql = 'create table t_user(id intprimary key auto_increment, name varchar(32));'
# 链接数据库
conn = engine.connect()
# 执行SQL即可
conn.execute(sql）

对象模型

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

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

DB_URI = f'数据库的名+驱动名://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'

engine = create_engine(DB_URI)
Base = declarative_base(engine)

Base 类作为基类来写自己的ORM类。要定义 tablename 类 属性，来指定这个模型映射到数据库中的表名。

class Person(Base):
    __tablename__ ='t_person'

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

class Person(Base):
    __tablename__ ='t_person'
    # 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射。
    # 这些属性必须是sqlalchemy给我们提供好的数据类型
    id =Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    country = Column(String(50))
    
# 使用 Base.metadata.create_all() 来将模型映射到数据库中
## 一旦使用 Base.metadata.create_all() 将模型映射到数据库中后，即使改变了模型的字段，也不会重新映射
Base.metadata.create_all()

数据增删改查

构建session对象

所有和数据库的ORM操作都必须通过一个叫做 session 的会话对象来实现，通过以下代码来获取会话对象

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DB_URI = f'数据库的名+驱动名://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)

添加对象

 with Session() as session:
	 p1 = Person(name = 'AA',age = 19 ,country='成都')
	 p2 = Person(name = 'BB',age = 18 ,country='北京')
	 p2 = Person(name = 'CC',age = 18 ,country='上海')
	 # 添加单条数据
	 session.add(p1)
	 # 添加多条数据
	 session.add_all([p2,p3])
	 # 提交
	 session.commit()

简单查询对象

with Session() as session:
# 查询所有
	all_person = session.query(Person).all()
	for p in all_person:
    	print(p.name)
    	
# 查询单条
    p1 = session.query(Person).first()
    print(p1.name)
    
# 查询带条件参数
	# 方式1
	p2 = session.query(Person).filter_by(name='BB').first()
	# 方式2
	p3 = session.query(Person).filter_by(Person.name =='BB').first()

修改对象

with Session() as session:
	p3 = session.query(Person).filter_by(Person.name =='BB').first()
	p3.age = 20
	# 提交事务
    session.commit()

删除对象

with Session() as session:
	p3 = session.query(Person).filter_by(Person.name =='BB').first()
	session.delete(p3)
    session.commit()

常用数据模型

Integer：整形，映射到数据库中是int类型。
Float：浮点类型，映射到数据库中是float类型。他占据的32位。
Double：双精度浮点类型，映射到数据库中是double类型，占据64位 (SQLALCHEMY中没有)。
String：可变字符类型，映射到数据库中是varchar类型。
Boolean：布尔类型，映射到数据库中的是tinyint类型。
DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。

这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位

Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举

class News(Base):
    __tablename__ = 't_news'
    tag = Column(Enum("python",'flask','django')

在Python3中，已经内置了enum这个枚举的模块，我们也可以使用这个模块去定义相关的字段

class TagEnum(enum.Enum):
	python = "python"
	flask = "flask"
	django = "django"
	
class News(Base):
	__tablename__ = 't_news'        
	id = Column(Integer,primary_key=True,autoincrement=True)
	tag = Column(Enum(TagEnum))
	
news = News(tag=TagEnum.flask)

Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用 datetime.date 来指定。
DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用datetime.datetime 来指定。
Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用 datetime.time 来至此那个

class News(Base):
    __tablename__ = 't_news'
    create_time = Column(Time)
    
news = News(create_time=time(hour=11,minute=11,second=11))

Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。
LONGTEXT：长文本类型，映射到数据库中是longtext类型。

from  sqlalchemy  import create_engine,Column,Integer,String,Float,Enum,Boolean,DECIMAL,Text,Date,DateTime,Time
from  sqlalchemy.dialects.mysql  import LONGTEXT

Column常用参数

• primary_key：True设置某个字段为主键。
• autoincrement：True设置这个字段为自动增长的。
• default：设置某个字段的默认值。在发表时间这些字段上面经常用。
• nullable：指定某个字段是否为空。默认值是True，就是可以为空。
• unique：指定某个字段的值是否唯一，默认是False。
• onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用于是 update_time 字段（每次更新数据的时候都要更新该字段值）。
• name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为表字段名。这个参数也可以当作位置参数，在第1个参数来指定。
• doc:
• comment: 字段备注

其他说明:
datetime.datetime.now：动态的当前时间，也就是数据库添加、修改的时间。
datetime.datetime.now()：固定的时间，程序部署的时间。

query函数的使用

聚合函数

• func.count：统计行的数量。
• func.avg：求平均值。
• func.max：求最大值。
• func.min：求最小值。
• func.sum：求和

from sqlalchemy import func

with Session() as session:
# 查询所有字段
	all_person = session.query(Person).all()
# 查询指定字段
	all_person = session.query(Person.age).all()
# 统计指定的列数据(聚合)
	all_person = session.query(func.count(Person.name)).all()

filter过滤数据

session.query(Person).filter(模型.属性.like('过滤字符%')).all()   -- 模糊匹配, 不会区分大小写
session.query(Person).filter(~ 模型.属性.in_(["1","2"]).all()    -- 范围内,~ 取反,注意有下划线
session.query(Person).filter(模型.属性 == None).all()        -- 为空  推荐
session.query(Person).filter(模型.属性 is None).all()        -- 为空
session.query(Person).filter(模型.属性 != None).all()        -- 不为空  推荐
session.query(Person).filter(模型.属性 is not None).all()    -- 不为空  不推荐
session.query(Person).filter(模型.属性 > 23).all()    -- 大于 >,小于 <
session.query(Person).filter(模型.属性 is not None and 模型.属性 is not None).all()    -- 条件与  关键字  and   不推荐(有错误数据)
session.query(Person).filter(模型.属性 is not None, 模型.属性 is not None).all()    -- 条件与  使用,号分割           
session.query(Person).filter(and_(模型.属性 is not None, 模型.属性 is not None)).all()    -- 条件与 推荐

session.query(Person).filter(模型.属性 is not None or 模型.属性 is not None).all()    -- 条件或  关键字  or  (只会过滤后面一个条件)
session.query(Person).filter(or_(模型.属性 is not None, 模型.属性 is not None)).all()    -- 条件或 推荐

session.query(Person).filter(User.username.contains('5'))    包含
session.query(Person).filter(DataTable.data_time.between(start_day, end_day))  --范围

表关系

表之间的关系存在三种：一对一、一对多、多对多

外键

ForeignKey的关键字要建立在 多一边

class User(Base):
    __tablename__ = 't_user'
	id = Column(Integer,primary_key=True,autoincrement=True)

class News(Base):
	__tablename__ = 't_news'
	uid = Column(Integer,ForeignKey('t_user.id')

外键约束

• RESTRICT：若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除。此项为默认项
• NO ACTION：在MySQL中，同 RESTRICT。
• CASCADE：级联删除。
• SET NULL：父表对应数据被删除，子表对应数据项会设置为NULL。

Column(Integer,ForeignKey('t_user.id',ondelete = 'RESTRICT')) # 默认的策略
Column(Integer,ForeignKey('t_user.id',ondelete = 'NO ACTION')) # 默认的策略
Column(Integer,ForeignKey('t_user.id',ondelete = 'CASCADE')) # 级联删除，发主表的数据被删除，子表的里数据也会删除
Column(Integer,ForeignKey('t_user.id',ondelete = 'SET NULL')) # 发现主表数据被删除时，子表的数据列会清空

ORM关系中的一对多

SQLAlchemy提供了一个 relationship ，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到。
另外，可以通过 backref 来指定反向访问的属性名称。newss是指有多篇新闻。他们之间的关系是一个“一对多”的关系

from sqlalchemy.orm  import relationship

class User(Base):# 主表
    __tablename__ = 't_user'
    id = Column(Integer,primary_key=True, autoincrement=True)

class News(Base):# 子表
	__tablename__ = 't_news'
	uid = Column(Integer,ForeignKey('t_user.id'))
	user =relationship('User',backref='news')  # 将主表的数据注入到这个字段
	
with Session() as session:
	# 通过子表查询主表的数据
	new = session.query(News).first()
	print(new .user)
	# 通过主表查找子表的数据
	user = session.query(User).first()
	print(user.news)

ORM关系中的一对一

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

from sqlalchemy.orm import relationship, backref

class LoginUser(Base):
    __tablename__ = 't_user_login'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(32),nullable=False)
    
class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    login_id = Column(Integer,ForeignKey('t_user_login.id'))
    login_user = relationship('LoginUser',backref=backref('user',uselist=False))

ORM关系中的多对多

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

from sqlalchemy import Table
# 中间表
news_tag = Table(
    't_news_tag',
    Base.metadata,
    Column('news_id',Integer,ForeignKey('t_news.id'),primary_key = True),
    Column('tag_id',Integer,ForeignKey('t_tag.id'),primary_key = True)
)

class News(Base):
  __tablename__ = 't_news'
  id = Column(Integer,primary_key=True,autoincrement=True)
  title = Column(String(32),nullable=False)
  tags = relationship('Tag',backref='news',secondary= news_tag)

class Tag(Base):
  __tablename__ = 't_tag'
  id = Column(Integer,primary_key=True,autoincrement=True)
  name = Column(String(32),nullable=False)
   # news =relationship('News',backref='tags',secondary = news_tag)

# 添加新闻
new1 = News(title ='AAA')
new2 = News(title ='BBB')

# 添加标签
tag1 = Tag(name='便宜')
tag2 = Tag(name='贵')

# 建立关系
new1.tags.append(tag1)
new1.tags.append(tag2)

new2.tags.append(tag1)
new2.tags.append(tag2)

with  Session() as ses:
	ses.add(news1)
	ses.add(news2)
	ses.commit()

ORM层面删除数据注意事项

ORM层面删除数据，会无视mysql级别的外键约束。直接会将对应的数据删除，然后将从表中的那个外键设置为NULL，也就是数据库的 SET NULL 。
如果想要避免这种行为，应该将从表中的外键的 nullable=False 。

ORM层面的relationship方法中cascade

在SQLAlchemy，只要将一个数据添加到session中，和他相关联的
数据都可以一起存入到数据库中.
cascade属性值为：
save-update：默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。
delete：表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据。
delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。
merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。
expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。
all：是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

articles = relationship('Article',backref='user',cascade='')
articles = relationship('Article',backref='user',cascade='save-update') # 默认cascade的值是save update
articles = relationship('Article',backref='user',cascade='save-update,delete') # delete可以帮助删除关联表的数据
articles = relationship('Article',backref='user',cascade='save-update,delete,delete orphan',single_parent=True) # 当关联关系被解除时，子表数据会被清空

ORM优先级排序

order_by方法排序：可以指定根据模型中某个属性进行排序，"模型名.属性名.desc()"代表的是降序排序。 relationship的方法中order_by属性：在指定relationship方法的时
候，添加order_by属性来指定排序的字段。

# 升序
users = ses.query(User).order_by(User.age).all()
# 降序
users = ses.query(User).order_by(User.age.desc()).all()

涉及两表时，定义模型时，用relationship方法中的order_by属性指定排序方式

user = relationship('User',backref=backref('news',order_by=read_count))

ORM limit、offset、slice使用

limit：可以限制查询的时候只查询前几条数据。 属top-N查询
offset：可以限制查找数据的时候过滤掉前面多少条。可指定开始查询时的偏移量。
切片：可以对Query对象使用切片操作，来获取想要的数据。

1.可以使用 slice(start,stop) 方法来做切片操作。
2.也可以使用 [start:stop] 的方式来进行切片操作。
3.一般在实际开发中，中括号的形式是用得比较多的。

news = session.query(News).limit(3).offset(3).all()
news = session.query(News).slice(3,6).all()
news = session.query(News).all()[3:6]

懒加载

在一对多，或者多对多关系的时候，如果想要获取多的一方这一部分的数据的时候，往往能通过一个属性就可以全部获取.有时候我们不想获取所有的数据,这时候我们可以给relationship方法添加属性lazy='dynamic' ，以后通过 user.articles 获取到的就不是一个列表，而是一个AppenderQuery对象，这样就可以对这个对象再进行一层过滤和
排序等操作。
lazy可用的选项：

• select ： (默认) 后台会用select语句一次性加载所有数据，即访问
  到属性的时候，就会全部加载该属性的数据
• joined - 数据会被JOIN语句加载，即对关联的两个表进行join操作，从而获取到所有相关的对象
• subquery - 数据被用subquery子查询SQL语句加载
• dynamic ：这个也是懒加载。在访问属性的时候，并不在内存中加载数据，而是返回一个 AppenderQuery 对象, 需要执行相应方法才可以获取对象，适用于数据量大的时候。

user = relationship('User',backref=backref('news', lazy='dynamic'))

ORM 分组group_by和过滤分组having

user = session.query(User.age,func.count(User.id)).group_by(User.age).all()
user = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age>18).all()

ORM join的使用_高级查询之多表查询

join分为left join（左外连接）和right join（右外连接）以及内连接（等值连接）。
在sqlalchemy中，使用join来完成内连接。在写join的时候，如果不写join的条件，那么默认将使用外键来作为条件连接。
查询出来的字段，跟join后面的东西无关，而是取决于query方法中传了什么参数。（模型名=全表；模型名.属性=表名.字段）。
在sqlalchemy中，使用outerjoin来完成外连接（默认是左外连接）。

# join里面放对象类名时，默认会以外键做为关联条件
rs = ses.query(User.name,func.count(News.id)).join(News).group_by(User.id).order_by(func.count(News.id)).all()
 # join里面放对象类名时，默认会以外键做为关联条件
rs = ses.query(User.name,func.count(News.id)).join(News,News.uid == User.id).group_by(User.id).order_by(func.count(News.id)).all()

ORM subquery的使用 高级查询之子查询

要实现一个子查询，需以下几个步骤：

• 将子查询按照传统的方式写好查询代码，然后在 query 对象后面执行 subquery 方法，将这个查询变成一个子查询。
• 在子查询中，将以后需要用到的字段通过 label 方法，取个别名。
• 在父查询中，如果想要使用子查询的字段，那么可以通过子查询>- 的返回值上的 c 属性拿到（c=Column）。

stmt = session.query(User.city.label('city'),User.age.label('age')).filter(User.uname == '校长').subquery()
result = session.query(User).filter(User.city == stmt.c.city, User.age == stmt.c.age).all()

ORM aliased的函数_高级查询之别名使用

from sqlalchemy.orm import aliased

c = aliased(City)
citys = ses.query(City,c.name,c.id).join(c,City.id == c.pid).all()

Flask-SQLAlchemy的使用

Flask-SQLAlchemy是一个插件， Flask-SQLAlchemy是对SQLAlchemy进行了一个简单的封装的一个插件，使得我们在flask中使用sqlalchemy更加的简单。
pip install flask-sqlalchemy

数据库连接

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app =  Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] =  DB_URI
# 链接数据库
db = SQLAlchemy(app)

创建ORM模型类

还是跟使用sqlalchemy一样，定义模型。现在不再是需要使用 delarative_base 来创建一个基类,而是使用 db.Model 来作为基类.
在模型类中， Column 、 String 、 Integer 以及 relationship 等，都不需要导入了，直接使用 db,下面相应的属性名就可以.
在定义模型的时候，可以不写 __tablename__ ，那么 flask_sqlalchemy 会默认使用当前的模型的名字转换成小写来作为表的名字

id = db.Column(db.Integer,primary_key=True,autoincrement = True)
name = db.Column(db.String(32))

将ORM模型映射到数据库表

删除数据库表： db.drop_all()
创建数据库表： db.create_all()

session的使用

直接使用 db.session
操作这个session的时候就跟之前的 sqlalchemy 的 session 是一样一样的

添加数据

添加数据和之前的没有区别，只是session成为了一个db的属性

user = User(name = '哈哈')
news = News(content = '这个好')
# 创建关联关系
user.newss.append(news)
# 添加数据
db.session.add(user)
# 提交数据
db.session.commit()

查询数据

单表查询

查询数据不再是之前的session.query方法了，而是将query属性放在了db.Model上， 所以查询就是通过“模型名.query”的方式进行查询了， query 就跟之前的sqlalchemy中的query方法是一样用的。

users = User.query.all()

多表查询

如果查找数据涉及多个模型，只能使用db.session.query(模型名).all() 这种方式

rs = db.session.query(User,News.content).join(News,News.uid == User.id).all()

修改数据

修改数据和之前的没有区别，只是session成为了一个db的属

user = User.query.first()
user.name = '小明'
db.session.commit()

删除数据

删除数据跟添加数据和修改数据类似，只不过session是db的一个属性而已

news = News.query.first()
db.session.delete(news)
db.session.commit()