[Python知识库]第五章：Flask数据库操作

第五章：Flask数据库操作

1、SQLALchemy的介绍

SQLAlchemy 是 Python 中一个通过 ORM 操作数据库的框架。

SQLAlchemy对象关系映射器提供了一种方法，用于将用户定义的Python类与数据库表相关联，并将这些类（对象）的实例与其对应表中的行相关联。它包括一个透明地同步对象及其相关行之间状态的所有变化的系统，称为工作单元，以及根据用户定义的类及其定义的彼此之间的关系表达数据库查询的系统。

可以让我们使用类和对象的方式操作数据库，从而从繁琐的 sql 语句中解脱出来。

ORM 就是 Object Relational Mapper 的简写，就是关系对象映射器的意思。

架构图

安装

先确保你已经安装了以下软件：

mysql：如果是在windows上，到官网下载。如果是ubuntu，通过命令sudo apt-get install mysql-server libmysqlclient-dev -yq进行下载安装。
MySQLdb：MySQLdb是用Python来操作mysql的包，因此通过pip来安装，命令如下：pip install mysql-python。如果您用的是Python 2.x，请安装MySQLdb。

pymysql：pymysql是用Python来操作mysql的包，因此通过pip来安装，命令如下：pip3 install pymysql。

SQLAlchemy：SQLAlchemy是一个数据库的ORM框架，我们在后面会用到。安装命令为：pip3 install SQLAlchemy。

连接数据库

from sqlalchemy import create_engine 
# 数据库的配置变量 
HOSTNAME = '127.0.0.1' 
PORT = '3306' 
DATABASE = 'test' 
USERNAME = 'root' 
PASSWORD = '123123' 
DB_URI = 'mysql+mysqldb://{}:{}@{}?charset=utf8mb4:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE) 

# 创建数据库引擎 
engine = create_engine(DB_URI) 

#创建连接 
with engine.connect() as con: 
	rs = con.execute('SELECT 1') 
	print rs.fetchone()

2、创建ORM映射

ORM：Object Relationship Mapping

创建一个类，一个类对应了一个数据库中的一张表，类的数据属性对应了表中的字段名，这个类称为映射类。 根据映射类创建出一个一个的对象，每个对象对应了表中的一条实际的数据。

1、主动创建映射

使用Declarative系统映射的类是根据基类定义的，换句话说每个映射类需要继承这个基类。我们使用 declarative_base() 函数可以创建这个基类，如下所示：

#1、创建基类 
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类型： 
class Person(Base): 
	__tablename__ ='person' 
	#2.在这个ORM模型中创建一些属性，来跟表中的字段进行 一一 映射。这些属性必须是sqlalchemy给我们提供好的数据类型 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	name = Column(String(50)) 
	age = Column(Integer) 
	country = Column(String(50)) 

#4. 使用`Base.metadata.create_all()`来将模型映射到数据库中。 
Base.metadata.create_all() 

#5. 一旦使用`Base.metadata.create_all()`将模型映射到数据库中后，即使改变了模型的字段，也不会重新映射了。 

SQLAlchemy常用数据类型

1. Integer：整形，映射到数据库中是int类型。
2. Float：浮点类型，映射到数据库中是flfloat类型。他占据的32位。
3. Double：双精度浮点类型，映射到数据库中是double类型，占据64位 (SQLALCHEMY中没有)。
4. String：可变字符类型，映射到数据库中是varchar类型.
5. Boolean：布尔类型，映射到数据库中的是tinyint类型。
6. DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。
7. Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举。
8. Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用datetime.date 来指定。
9. DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用 datetime.datetime 来指定。
10. Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用datetime.time 来创建值。
11. Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。
12. LONGTEXT：长文本类型，映射到数据库中是longtext类型。

注意：这个类型属于Mysql方言里面的

#定义一个枚举类 
class TagEnum(enum.Enum): 
	python="PYHTON" 
	flask="FLASK" 
	django ="DJANGO" 
#创建一个ORM模型 说明基于sqlalchemy 映射到mysql数据库的常用字段类型有哪些？ 
Base = declarative_base(engine) 
class News(Base): 
	__tablename__='news' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	price1 = Column(Float) #存储数据时存在精度丢失问题 
	price2 = Column(DECIMAL(10,4)) 
	title = Column(String(50)) 
	is_delete =Column(Boolean) 
	tag1 =Column(Enum('PYTHON','FLASK','DJANGO')) #枚举常规写法 
	tag2 =Column(Enum(TagEnum)) #枚举另一种写法 
	create_time1=Column(Date) 
	create_time2=Column(DateTime) 
	create_time3=Column(Time) 
	content1 =Column(Text) 
	content2 =Column(LONGTEXT) 
# Base.metadata.drop_all() 
# Base.metadata.create_all() 
#新增数据到表news中
a1 = News(price1=1000.0078,price2=1000.0078,title='测试数据',is_delete=True,tag1="PYTHON",tag2=TagEnum.flask, 
create_time1=date(2018,12,12),create_time2=datetime(2019,2,20,12,12,30),create_time3=time(hour=11,minute=12,second=13), 
content1="hello",content2 ="hello hi nihao") 

Column常用参数

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

案例：

class News(Base): 
__tablename__ = 'news' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	create_time = Column(DateTime,default=datetime.now) 
	read_count = Column(Integer,default=11) 
	title = Column(String(50),name='my_title',nullable=False) 
	telephone = Column(String(11),unique=True) 
	update_time = Column(DateTime,onupdate=datetime.now,default=datetime.now) 

2、自动从数据库中映射

from datetime import datetime 
from sqlalchemy.ext.automap import automap_base 
from sqlalchemy.orm import sessionmaker 
from sqlalchemy import create_engine 

# 数据库连接url 
DB_CONNECT_STRING = 'mysql+pymysql://root:123123@localhost:3306/test' 
# 创建引擎 
engine = create_engine(DB_CONNECT_STRING, echo=True) 
# 自动映射 
Base = automap_base() 
Base.prepare(engine,reflect=True)
# 获取所有表的映射类 
tables = Base.classes.keys() 
# print(tables) 
# 获取指定t_movies表(确保表名没有问题) --> movie实体类 
movie = Base.classes.t_movies 
# 查看映射信息 
print(movie.__dict__) 
# 获取所有字段或属性 
keys = movie.__table__.columns.keys() 
print(keys) 

3、数据的CRUD操作

用session做数据的增删改查操作：

1、 构建session对象：

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

from sqlalchemy.orm import sessionmaker 
engine = create_engine(DB_URI) 
Base = declarative_base(engine) 
session = sessionmaker(engine)()# 注意，返回的是一个函数 

**2、添加对象： **

#创建对象，也即创建一条数据： 
p1 = Person(name='momo1',age=19,country='china') 
# 将这个对象添加到`session`会话对象中： 
session.add(p1) 
# 将session中的对象做commit操作（提交）： 
session.commit() 
# 一次性添加多条数据： 
p1 = Person(name='momo1',age=19,country='china') 
p2 = Person(name='momo2',age=20,country='china') 
session.add_all([p1,p2]) 
session.commit()

3、 查找对象：

# 查找某个模型对应的那个表中所有的数据： 
all_person = session.query(Person).all() 
# 使用filter_by来做条件查询 
all_person = session.query(Person).filter_by(name='momo1').all() 
# 使用filter来做条件查询 
all_person = session.query(Person).filter(Person.name=='momo1').all() 
# 使用get方法查找数据，get方法是根据id来查找的，只会返回一条数据或者None 
person = session.query(Person).get(primary_key) 
# 使用first方法获取结果集中的第一条数据 
person = session.query(Person).first() 

filter过滤条件：

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

1. equals ： ==

news= session.query(News).filter(News.title == "title1").first() 

2. not equals : !=

query(User).filter(User.name != 'ed') 

3. like & ilike [不区分大小写]：

query(User).filter(User.name.like('%ed%'))

4. in：

query(User).filter(User.name.in_(['ed','wendy','jack'])) 

5. not in：

query(User).filter(~User.name.in_(['ed','wendy','jack']))

6. is null：

query(User).filter(User.name==None) 
# 或者是 
query(User).filter(User.name.is_(None)) 

7. is not null:

query(User).filter(User.name != None) 
# 或者是 
query(User).filter(User.name.isnot(None)) 

8. and：

query(User).filter(and_(User.name=='ed',User.fullname=='Ed Jones')) 
# 或者是传递多个参数 
query(User).filter(User.name=='ed',User.fullname=='Ed Jones')
# 或者是通过多次filter操作 
query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones') 

9. or：

query(User).filter(or_(User.name=='ed',User.name=='wendy')) 

聚合函数

• func.count：统计行的数量。

• func.avg：求平均值。

• func.max：求最大值。

• func.min：求最小值。

• func.sum：求和。

#3.mysql聚合函数 
r = session.query(func.count(News.id)).first() 
print(r) 
r = session.query(func.max(News.price)).first() 
print(r) 
r = session.query(func.min(News.price)).first() 
print(r) 
r = session.query(func.avg(News.price)).first() 
print(r)
r = session.query(func.sum(News.price)).first() 
print(r)

4、修改对象：

首先从数据库中查找对象，然后将这条数据修改为你想要的数据，最后做commit操作就可以修改数据了。

person = session.query(Person).first() 
person.name = 'laoliu' 
session.commit() 

5、删除对象：

将需要删除的数据从数据库中查找出来，然后使用 session.delete 方法将这条数据从session中删除，最后做commit操作就可以了。

person = session.query(Person).first() 
session.delete(person) 
session.commit() 

4、ORM的关联关系

1、表的外键关联

使用SQLAlchemy创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和主表的主键字段类型保持一致。

这种关联只关注数据表之间的外键关联，不考虑Python对象之间的关联关系.

# 主表 / 从表 
# user/news 
class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	uname = Column(String(50),nullable=False) 

def __repr__(self): 
	return "<User(uname:%s)>" % self.uname 

class News(Base): 
	__tablename__ = 'news' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	title = Column(String(50),nullable=False) 
	content = Column(Text,nullable=False) 
	uid = Column(Integer,ForeignKey("user.id"))

外键的删除选项

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

# 父表/从表 
# user/news 

class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	uname = Column(String(50),nullable=False) 
	def __repr__(self): 
		return "<User(uname:%s)>" % self.uname 
class News(Base): 
	__tablename__ = 'news' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	title = Column(String(50),nullable=False) 
	content = Column(Text,nullable=False)
	# 设置删除选项 
	# uid = Column(Integer,ForeignKey("user.id",ondelete='RESTRICT')) 
	# uid = Column(Integer,ForeignKey("user.id",ondelete='NO ACTION')) 
	# uid = Column(Integer,ForeignKey("user.id",ondelete='CASCADE')) 
	uid = Column(Integer,ForeignKey("user.id",ondelete='SET NULL')) 
	def __repr__(self): 
		return "<News(title:%s,content=%s)>" % (self.title,self.content) 
Base.metadata.drop_all() 
Base.metadata.create_all() 
user = User(uname='momo') 
session.add(user) 
session.commit() 
news1= News(title='AAA',content='123',uid=1) 
news2= News(title='BBB',content='456',uid=1) 
session.add_all([news1,news2]) 
session.commit()

2、ORM中的一对多/多对一

mysql表级别的外键，还不够爽，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。

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

#创建ORM模型 
Base = declarative_base(engine) 
# 主表 / 从表 
# user/news 
class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	uname = Column(String(50),nullable=False) 
	# newss=relationship("News") #这种写法不是最优的，通常会把它通过反向声明的方式写在“多”的那一方 
	def __repr__(self): 
		return "<User(uname:%s)>" % self.uname 

class News(Base): 
	__tablename__ = 'news' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	title = Column(String(50),nullable=False) 
	content = Column(Text,nullable=False) 
	#外键 
	uid = Column(Integer,ForeignKey("user.id")) 
	#正向author = relationship("User") 
	#正向 和 反向在一起 表明两个模型之间的关系 
	author = relationship("User",backref="newss") 
	def __repr__(self): 
		return "<News(title:%s,content=%s)>" % (self.title,self.content) 

# Base.metadata.drop_all() 
# Base.metadata.create_all()

3、ORM中的一对一

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

方法一：参照一对多关联，加上uselist

class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	uname = 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)) 
	uid = Column(Integer,ForeignKey("user.id")) 
	user = relationship("User") 

方法二：

class User(Base): 
__tablename__ = 'user' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	uname = Column(String(50),nullable=False) 
class UserExtend(Base): 
	__tablename__ = 'user_extend' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	school = Column(String(50)) 
	uid = Column(Integer,ForeignKey("user.id")) 
	user = relationship("User",backref=backref("extend",uselist=False))

4、ORM中的多对多

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

#表3 中间表 
news_tag = Table( 
	"news_tag", 
	Base.metadata, 
	Column("news_id",Integer,ForeignKey("news.id"),primary_key=True), 
	Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True) 
)

#表1 
class News(Base): 
	__tablename__ = 'news' 
	id = Column(Integer,primary_key=True,autoincrement=True)
	title = Column(String(50),nullable=False) 

	#产生关系 写法1 
	# tags = relationship("Tag",backref="newss",secondary=news_tag) 
	def __repr__(self): 
		return "<News(title:%s)>" % self.title 

#表2 
class Tag(Base): 
	__tablename__ = 'tag' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	name = Column(String(50), nullable=False) 
	# 产生关系 写法2 
	newss = relationship("News",backref="tags",secondary=news_tag) 
	def __repr__(self): 
		return "<Tag(name:%s)>" % self.name 

5、SQLALchemy的高级

1、排序

1. order_by方法排序：可以指定根据模型中某个属性进行排序，"模型名.属性名.desc()"代表的是降序排序。

# 倒序排序 
articles2 = session.query(Article).order_by(Article.create_time.desc()).all() 
print(articles2) 

2. 在定义模型的时候指定排序：有些时候，不想每次在查询的时候都用order_by方法，可以在定义模型的时候就指定排序的方式。

一、模型对象定义中加排序

#排序方式2：定义模型时，指定排序方式 
class Article(Base): 
	__tablename__ = 'article' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	title = Column(String(50), nullable=False) 
	create_time = Column(DateTime, nullable=False, default=datetime.now) 
	__mapper_args__ = { 
	# "order_by": create_time #正序 
	"order_by": create_time.desc() #倒序 
	}

	def __repr__(self): 
		return "<Article(title:%s,create_time:%s)>" % (self.title,self.create_time)

二、在relationship的方法中order_by属性

#排序方式3：涉及两表时，定义模型时，用relationship方法中的order_by属性指定排序方式 
class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	uname = Column(String(50),nullable=False) 
class Article(Base): 
	__tablename__ = 'article' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	title = Column(String(50), nullable=False) 
	create_time = Column(DateTime, nullable=False, default=datetime.now) 
	uid = Column(Integer,ForeignKey("user.id")) 
	# author = relationship("User", backref=backref("articles",order_by=create_time)) #正序 
	author = relationship("User", 
	backref=backref("articles",order_by=create_time.desc())) #倒序 
	def __repr__(self): 
		return "<Article(title:%s,create_time:%s)>" % (self.title,self.create_time) 

2、分页查询

1. limit：可以限制查询的时候只查询前几条数据。 属top-N查询

articles = session.query(Article).limit(10).all() 
print(articles)

2. offffset：可以限制查找数据的时候过滤掉前面多少条。可指定开始查询时的偏移量。

#offset：可以限制查找数据的时候过滤掉前面多少条。可指定开始查询时的偏移量。 
articles = session.query(Article).offset(10).limit(10).all()

3. 切片：可以对Query对象使用切片操作，来获取想要的数据。
   可以使用 slice(start,stop) 方法来做切片操作。
   也可以使用 [start:stop] 的方式来进行切片操作。
   一般在实际开发中，中括号的形式是用得比较多的。

#实现分页 
from sqlalchemy.orm.query import Query 
def oper3(): 
	articles = session.query(Article).order_by(Article.id.desc()).slice(0,10).all() 
	print(articles) 
def oper4(): 
	articles = session.query(Article).order_by(Article.id.desc())[0:10] 
	print(articles) 

3、懒加载

在一对多，或者多对多关系的时候，如果想要获取多的一方这一部分的数据的时候，往往能通过一个属性就可以全部获取了。

如有一个作者，想要这个作者的所有文章，通过user.articles就可以获取所有的。

但有时候我们不想获取所有的数据，如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship方法添加属性lazy=‘dynamic’，以后通过user.articles获取到的就不是一个列表，而是一个AppenderQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作。

通过 lazy='dynamic' ，获取出来的多的那一部分的数据，就是一个 AppenderQuery 对象了。这种对象既可以添加新数据，也可以跟 Query 一样，可以再进行一层过滤。

class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	uname = Column(String(50),nullable=False) 
class Article(Base): 
	__tablename__ = 'article' 
	id = Column(Integer, primary_key=True, autoincrement=True) 
	title = Column(String(50), nullable=False) 
	create_time = Column(DateTime, nullable=False, default=datetime.now) 
	uid = Column(Integer,ForeignKey("user.id")) 
	# author = relationship("User", backref=backref("articles")) 
	#懒加载 
	author = relationship("User", backref=backref("articles",lazy="dynamic")) 
	def __repr__(self): 
		return "<Article(title:%s,create_time:%s)>" % (self.title,self.create_time) 
def add_data(): 
	Base.metadata.drop_all() 
	Base.metadata.create_all() 
	user = User(uname='莫莫') 
	for x in range(100): 
		article = Article(title="title %s" % x) 
		article.author = user 
		session.add(article)
		session.commit() 

from sqlalchemy.orm.collections import InstrumentedList 

def oper1(): 
	user = session.query(User).first() 
	print(type(user.articles)) #<class 'sqlalchemy.orm.collections.InstrumentedList'> 
	print(user.articles) 
#懒加载 
from sqlalchemy.orm.dynamic import AppenderQuery 
def oper2(): 
	user = session.query(User).first() 
	print(type(user.articles)) #<class 'sqlalchemy.orm.dynamic.AppenderQuery'> 
	print(user.articles) 
#辨析 AppenderQuery 和 Query 
from sqlalchemy.orm.query import Query 
def oper3(): 
	user = session.query(User) 
	print(type(user)) #<class 'sqlalchemy.orm.query.Query'> 
	print(user) #sql语句 
	
#有2层意思 
#1.是一个Query对象。可以调用Query对象的方法 
#2.是一个AppenderQuery对象。可以继续追加数据进去 
def oper4(): 
	user = session.query(User).first()#可以调用Query对象的方法 
	print(type(user)) 
	print(user.articles.filter(Article.id>=50).all()) 
	# article = Article(title='title 100') 
	# user.articles.append(article)#2.是一个AppenderQuery对象。可以继续追加数据进去 
	# session.commit() 
if __name__ == '__main__': 
	# add_data() 
	# oper1() 
	# oper2() 
	# oper3() 
	oper4() 

4、分组和过滤

group_by：

根据某个字段进行分组。如想要根据年龄进行分组，来统计每个分组分别有多少人

r = session.query(User.age,func.count(User.id)).group_by(User.age).all()

having：

having是对分组查找结果作进一步过滤。如只想要看未成年人的人数，那么可以首先对年龄进行分组统计人数，然后再对分组进行having过滤。

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

5、子查询

子查询即select语句中还有select。

那么在sqlalchemy中，要实现一个子查询，需以下几个步骤：

1. 将子查询按照传统的方式写好查询代码，然后在 query 对象后面执行 subquery 方法，将这个查询变成一个子查询。

2. 在子查询中，将以后需要用到的字段通过 label 方法，取个别名。

3. 在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的 c 属性拿到（c=Column）。

class User(Base): 
	__tablename__ = 'user' 
	id = Column(Integer,primary_key=True,autoincrement=True) 
	uname = Column(String(50),nullable=False) 
	city = Column(String(50),nullable=False) 
	age = Column(Integer,default=0) 
	def __repr__(self): 
		return "<User(username: %s)>" % self.uname 
def add_data(): 
	Base.metadata.drop_all() 
	Base.metadata.create_all() 
	user1 = User(uname='老肖',city="贵阳",age=18) 
	user2 = User(uname='王二',city="贵阳",age=18) 
	user3 = User(uname='张三',city="北京",age=18) 
	user4 = User(uname='赵四',city="贵阳",age=20) 
	session.add_all([user1,user2,user3,user4]) 
	session.commit() 
# 相亲类网站：同城交友 之珍爱网 
#需求： 寻找和 “老肖” 这个人在同一个城市，并且是同年龄的人 
#实现思路1：传统方式 
def oper1(): 
	u = session.query(User).filter(User.uname == '老肖').first() 
	users = session.query(User).filter(User.city==u.city,User.age==u.age).all() 
	print(users) 
#实现思路2：子查询方式 
#原生sql：select `user`.id,`user`.uname,`user`.city,`user`.age from user, 
# (select `user`.city,`user`.age from user where uname='老肖') as yige 
# where `user`.city=yige.city AND `user`.age=yige.age 
def oper2():
	# 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) 
	# print(result) #查看sql语句 
	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() 
	print(result) # 查看结果 

6、Flask-SQLAlchemy

Flask-SQLAlchemy的使用_对SQLAlchemy进行了封装和优化：

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

1.安装：

pip install flask-sqlalchemy 

2.Flask-SQLAlchemy的使用要点：

2.1 数据库连接

数据库初始化不再是通过create_engine。

1. 跟sqlalchemy一样，定义好数据库连接字符串DB_URI。

2. 将这个定义好的数据库连接字符串DB_URI，通过 SQLALCHEMY_DATABASE_URI 这个key名配置到 app.config中。

代码：

app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI 

3. 使用 flask_sqlalchemy.SQLAlchemy 这个类定义一个对象，并将 app 传入进去。

代码：

db = SQLAlchemy(app)

2.2 创建ORM模型类

之前都是通过Base = declarative_base()来初始化一个基类，然后再继承，在Flask-SQLAlchemy中更加简单了。

1. 还是跟使用sqlalchemy一样，定义模型。现在不再是需要使用 delarative_base 来创建一个基类。而是使用 db.Model 来作为基类。
2. 在模型类中， Column 、 String 、 Integer 以及 relationship 等，都不需要导入了，直接使用 db 下面相应的属性名就可以了。
3. 在定义模型的时候，可以不写 tablename ，那么 flask_sqlalchemy 会默认使用当前的模型的名字转换成小写来作为表的名字， 并且如果这个模型的名字用到了多个单词并且使用了驼峰命名法，那么会在多个单词之间使用下划线来进行连接，虽然flflask_sqlalchemy给我们提供了这个特性，但是不推荐使用。（增强代码可读性，提高团队合作效率）

2.3 将ORM模型映射到数据库表

写完模型类后，要将模型映射到数据库的表中，使用以下代码即可

1. 删除数据库表：db.drop_all()

2. 创建数据库表：db.create_all()

2.4 session的使用

以后session也不需要使用 sessionmaker 来创建了， 直接使用 db.session 就可以了，操作这个session的时候就跟之前的 sqlalchemy 的 session 是一样的。

2.5添加数据

这时候就可以在数据库中看到已经生成了对应表了 ,添加数据和之前的没有区别，只是session成为了一个db的属性

2.6 查询数据：

1.单表查询

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

2.多表查询

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

2.7 修改数据：

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

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

from flask import Flask 
from flask_sqlalchemy import SQLAlchemy 
app = Flask(__name__) 
HOSTNAME = '127.0.0.1' 
PORT = '3306' 
DATABASE = 'test' 
USERNAME = 'root' 
PASSWORD = '123123' 
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}? 
charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) 

app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI 
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False 
#1.连接数据库 
db = SQLAlchemy(app) 

#2.创建ORM模型 
class User(db.Model): 
	__tablename__ = 'user' 
	id = db.Column(db.Integer,primary_key=True,autoincrement=True) 
	uname = db.Column(db.String(50),nullable=False) 
	def __repr__(self): 
		return "<User(uname: %s)>" % self.uname 

class Article(db.Model): 
__tablename__ = 'article' 
	id = db.Column(db.Integer,primary_key=True,autoincrement=True) 
	title = db.Column(db.String(50),nullable=False) 
	uid = db.Column(db.Integer,db.ForeignKey("user.id")) 
	author = db.relationship("User",backref="articles") 

#3.删除表 
db.drop_all() 

#4.创建表 
db.create_all() 

#5.添加数据 
user = User(uname='莫莫') 
article = Article(title='华为5G 算法突破了，俄罗斯小伙突破的') 
article.author = user
db.session.add(article) 
db.session.commit() 

#6.查询数据 
# users = User.query.all() #等价于 db.session.query(User).all() 
# print(users) 
#在query属性之后 可以用 order_by 、 filter、filter_by、group_by、having等方法进行更复杂的单表查询

#若要进行更复杂的多表查询，只能使用db.session.query(User).all() 这种方式 
#如 order_by 
users = User.query.order_by(User.id.desc()).all() 
print(users) 

#7.修改数据 
user = User.query.filter(User.uname=='露露').first() 
user.uname = '探探' 
db.session.commit() 

#8.删除数据 
user = User.query.filter(User.uname=='探探').first() 
db.session.delete(user) 
db.session.commit() 

@app.route('/') 
def hello_world(): 
	return 'Hello World!' 

if __name__ == '__main__': 
	app.run() 

3、Flask-SQLAlchemy和alembic结合

在之前的数据库操作中，我们新增一个字段是不是每次都得删除数据库表，然后再重新将新创建的数据库表映射到数据库中。这样操作是不是很蛋疼？是吧？于是sqlalchemy作者拜尔为了解决这一问题，开发了alembic这一迁移工具。

步骤一：安装****alembic

pip install alembic

步骤二：配置好数据库连接文件 如****confifig.py

HOSTNAME = '127.0.0.1' 
PORT = '3306' 
DATABASE = 'test' 
USERNAME = 'root' 
PASSWORD = '123123' 
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}? 
charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) 

SQLALCHEMY_DATABASE_URI = DB_URI 

步骤三：注册confifig.py文件到Flask项目

import config 

app = Flask(__name__) 
app.config.from_object(config) 

class User(db.Model): 
	__tablename__ = 'user' 
	id = db.Column(db.Integer,primary_key=True,autoincrement=True) 
	uname = db.Column(db.String(50),nullable=False) 
	age = db.Column(db.Integer) 
	gender=db.Column(db.String(2)) 

步骤四：创建一个仓库

alembic init [仓库的名字]

注意：先进入虚拟环境，然后cd到当前项目中

步骤五：修改配置文件alembic.ini和env.py

sqlalchemy.url = mysql+pymysql://root:123123@localhost/test?charset=utf8 

import os 
import sys 
import alembic_demo 

# 把当前项目路径加入到path中 
sys.path.append(os.path.dirname(os.path.dirname(__file__))) 
target_metadata = alembic_demo.Base.metadata

步骤六：自动生成迁移文件
将当前模型中的状态生成迁移文件。

alembic revision --autogenerate -m "提示信息" 

步骤七：映射到数据库中
使用alembic upgrade head将刚刚生成的迁移文件，真正映射到数据库中。

alembic upgrade head 

步骤八：以后如果修改了模型，重复6、7步骤