Flask-论坛开发-3-数据库

对Flask感兴趣的,可以看下这个视频教程:http://study.163.com/course/courseLearn.htm?courseId=1004091002

1. SQLAlchemy 连接数据库

要在 python 中连接数据库,则要从 sqlalchemy 中导入 create_engine,并且要配置好数据库的信息,如下代码所示:

# 导入模块
from sqlalchemy import create_engine

# 配置数据库
DIALECT = 'mysql'
DRIVER = 'mysqldb'	# python2 写 mysqldb;python3 写 pymysl
USERNAME = 'root'
PASSWORD = 'root'
HOST = '127.0.0.1'
PORT = '3306'
DATABASE = 'db_demo1'

# DB_URL 的格式是:dialect+driver://username:password@host:port/database
# 所以要将配置变量组合成固定格式
DB_URL = "mysql+mysqldb://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOST,port=PORT,db=DATABASE)

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

# 判断是否连接成功
conn = engine.connect()

注意,以上方法未涉及 Flask 的内容,包括 17. Flask 下使用 SQLalchemy 节之前的内容,只是在纯 python 代码中通过 sqlalchemy 进行数据库的操作。

2. ORM 介绍

ORM``(Object Relationship Mapping):对象关系映射。实际上就是模型与数据库表的映射。

3. 数据库命令:

drop database db_name;				删除数据库
create database db_name charset utf8;		创建数据库
show tables;					查询数据库中的所有 table
drop table person;					删除名称为 person 的 table
desc person;					查看 person 的具体属性

2. Flask 中使用 sqlalchemy 连接数据库

2.1 连接数据库

  1. 要在 Flask 中使用 sqlalchemy 连接数据库,应该先导入 create_engine

     from sqlalchemy import create_engine
    
  2. 再做好连接数据库的相关配置:

     HOSTNAME = '127.0.0.1'
     PORT = '3306'
     DATABASE = 'mydb01'
     USERNAME = 'root'
     PASSWORD = 'root'
     
     # dialect+driver://username:password@host:port/database
     DB_URL = 'mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset:utf8'.format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
    
  3. 连接数据库

     engine = create_engine(DB_URL)
    

2.2 创建 ORM 模型并映射到数据库中

  1. 创建 ORM 模型

    要创建 ORM 模型,这个模型必须继承自 sqlalchemy 给我们定义好的基类。这个基类是通过 sqlalchemy.ext.declarative 下的一个函数(declarative_base)来初始化的,所以我们要先导入它:

     from sqlalchemy.ext.declarative import declarative_base
    

    导入完成后,还需要使用它进行基类的初始化,即使用它创建一个基类:

     Base =  declarative_base(engine)
     # 指定数据库引擎创建一个基类,并赋给 Base 变量
    

    其中,基类和类与对象之间的关系是:基类创建类 -> 类实例化出对象。创建这个基类,是因为这个基类已经帮我们封装好了映射到数据库中的一些方法,自定义的模型若继承自这个基类,会更方便我们通过 python 去操作数据库。

    完成以上步骤后,就可以通过创建出来的基类再创建一个类,而这个类就是 ORM 中的模型,如下:

     class Person(Base):		# 必须继承自基类
     	__tablename__ = 'person'	# 使用 __tablename__ 来指定映射到数据库中的表名
    
  2. 定义模型属性,即定义数据库表的字段

    上一步的代码,只是创建了一个可以映射到数据库中的一个表,但是该表并没有任何字段,我们需要完善其中的属性。而这些属性在数据库中是一个个的数据类型(如 int | char | varchar 等),这些数据类型也在 sqlalchemy 中定义好了,我们可以直接用,但要先导入:

     from sqlalchemy import Column,Integer,String
    
     class Person(Base):
     	__tablename__ = 'person'
     	id = Column(Integer,primary_key=True,autoincrement=True)	# 一个 Column 可以定义表中的一个列,可以在括号内指定数据类型(Integer),主键,自增长等数据库属性
     	name = Column(String(10))
     	age = Column(Integer)
    
  3. 将创建好的模型映射到数据库中

     Base.metadata.create_all()
    

    需要注意的是:一旦使用 Base.metadata.create_all() 将模型映射到数据库中,之后若要改变表中的字段(添加字段或删除字段)再重新映射,那么是不会生效的。

  4. 去数据库中验证是否成功

     show tables;
    
  5. 完整代码如下:

     from sqlalchemy import create_engine,Column,Integer,String
     from sqlalchemy.ext.declarative import declarative_base
     
     HOSTNAME = '127.0.0.1'
     PORT = '3306'
     DATABASE = 'mydb01'
     USERNAME = 'root'
     PASSWORD = 'root'
     
     # dialect+driver://username:password@host:port/database
     DB_URL = 'mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset:utf8'.format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
     
     engine = create_engine(DB_URL)
     
     Base =  declarative_base(engine)
     
     # 1. 创建一个 ORM 模型,这个模型必须继承自 sqlalchemy 给我们定义好的基类
     class Person(Base):
         __tablename__ = 'person'
         # 2. 在这个 ORM 模型中创建一些属性,对应于表中的一些字段,而这些属性必须是 sqlalchemy 给我们定义好的数据类型
         id = Column(Integer,primary_key=True,autoincrement=True)
         name = Column(String(10))
         age = Column(Integer)
     # 3. 将创建好的 ORM 模型映射到数据库中
     Base.metadata.create_all()
    
  6. 魔术方法

在定义一个类的时候,可以定义一个 __str__() 方法,这个方法的作用是:当这个类被 print 方法调用时,那么 print 会首先调用这个类里面的 __str__() 方法。而在 __str__() 方法中,一般都是 return 一个字符串。例如,对刚刚的类定义的 __str__() 方法:

class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(10))
    age = Column(Integer)

    def __str__(self):
        return 'Person(name:%s,age:%s)' % (self.name,self.age)

3. 增删改查

所有的数据库 ORM 操作都必须通过一个叫做 session 的会话对象来实现,那么这个对象的创建是通过以下代码来实现的:

from sqlalchemy.orm import sessionmaker
engine = create_engine(DB_URL)
Session = sessionmaker(engine)
session = Session()

其中:sessionmakerdeclarative_base 的原理类似,也是一个方法。后者接收一个 engine 创建一个基类,再创建一个模型;前者也要接收一个 engine ,从而对 engine 中的数据进行操作(增删改查等)。

后两行代码可以简化写成:session = sessionmaker(engine)()

3.1 增

创建对象,并使用 session 对象添加且提交:

p = Person(name='myyd',age=18)	# 创建对象(对于自增长的属性,主键不用写)
session.add(p)		# 将对象添加到会话对象中
session.commit()	# 使用 commit 将会话中的对象提交到数据库中

如果要添加多条数据,则必须以列表的形式传给 session 对象的 add_all() 方法:

p1 = Person(name='MYYD',age=19)
p2 = Person(name='Myyd',age=20)
session.add_all([p1,p2])	# 注意是 add_all 
session.commit()	

3.2 查

可以查询所某个数据库中某个表的所有数据,也可以使用条件查询该表中的符合条件的数据。

  1. 查找表中的所有数据:

     person_all = session.query(Person).all()
     for person in person_all:
         print(person)
    
  2. 查找表中符合条件的所有数据(方法一):

     person_all = session.query(Person).filter_by(age=18).all()
     for person in person_all:
         print(person)
    
  3. 查找表中符合条件的所有数据(方法二):

     person_all = session.query(Person).filter(Person.age==18).all()
     for person in person_all:
         print(person)
    
  4. 查找表中符合条件的所有数据(区别):

    区别在于,filter() 要指定类名,并且判断时要使用双等于号 ==,要相对麻烦一点。但是这两种方法,在大项目中是会同时用到的,所以两个都要学会!

  5. 使用 get() 方法根据主键查找数据:

    get() 方法会根据表中的主键进行查找数据,若有则返回数据,若无则返回 None。

     person1 = session.query(Person).get(1)
     person2 = session.query(Person).get(100)
     print(person1,person2)
     # 会返回 get(1) 的数据,get(100) 的数据是 None
    
  6. 使用 first() 方法获取表中的第一条数据:

     person3 = session.query(Person).first()
     print(person3)
    

3.3 改

要修改表中的数据,思路很简单:先通过查询,将指定数据选出来并赋予一个变量;再修改该变量的属性;最后用 session.commit() 提交即可。如下所示:

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

3.4 删

和修改类似,先查询找到指定的数据,再通过 session 进行删除。如下所示:

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

4. Sqlalchemy 的常用数据类型 和 ORM 模型常用参数

4.1 常用数据类型

  1. 整型(Integer),有微整型,整型和长整型
  2. 浮点型(Float,Double),Float32 位;Double64
  3. 布尔型(Boolean),在数据库中可以用微整型(01)来实现
  4. 定点类型(DECIMAL),用来处理精度丢失的问题,相当于将输入的浮点数当成文本处理
  5. 枚举类型(Enum),只能输入预先指定的内容
  6. 日期类型(Date),只能存储年月日,传入datetime.date()
  7. 日期时间类型(DateTime),可以存储年月日时分秒,传入datetime.datetime()
  8. 时间类型(Time),只能存储时分秒,传入datetime.time()
  9. 字符类型(String),相当于数据库中的varchar类型,即可变长字符类型
  10. 文本类型(Text),相当于数据库中的text类型,最多只能存储6W多个字
  11. 长文本类型(LONGTEXT),如果文字较多,可用LONGTEXT类型,只MySQL支持,要从另外的包中导入

下面我们分别来介绍各个数据类型的特性:

  1. 整型、浮点型、文本类型、字符类型比较常用,我们放到一个例子来讲。如下代码所示:

     class Articles(Base):
         __tablename__ = 'articles'
         id = Column(Integer,primary_key=True,autoincrement=True)
         title = Column(String(50),nullable=True)
         content = Column(Text,nullable=True)
         price = Column(Float,nullable=True)
     
     article = Articles(title='MYYD',content='HelloWorld',price=12.34563)
    

    上述代码表示:

    1. id 字段为整型,其值必须为一个整数;
    2. title 字段为字符类型,对应数据库中的 varchar 类型,是一个可变长度的字符,括号中的数字为该字段所能接受的最大字母数;
    3. content 字段为文本类型,可接受最大字符长度为6W多字;
    4. price 字段为浮点类型,对于 Float 来说只能表示4位小数,对于 Double 来说可以接受8位小数,如果数字太大,可以使用定点类型。
  2. 定点类型

     class Articles(Base):
     	__tablename__ = 'articles'
     	id = Column(Integer,primary_key=True,autoincrement=True)
     	price = Column(DECIMAL(10,6))
     
     article = Articles(price=2345.67891)
    

    其中,DECIMAL(10,6) 代表一共只能表示10个数字,分别是:整数最多只能有4位,小数最多只能表示6位。如果小数位数多了则四舍五入表示6位;如果整数位数多了,则直接报错。即小数位数可以大于6而整数位数不可以大于4。

  3. 日期时间类型

     from datetime import date,datetime,time
    
     class Articles(Base):
         __tablename__ = 'articles'
         id = Column(Integer,primary_key=True,autoincrement=True)
         create_date = Column(Date)
         create_datetime = Column(DateTime)
         create_time = Column(Time)
     
     article = Articles(create_date=date(2011,11,11),create_datetime=datetime(2011,11,11,11,11,11),create_time=time(11,11,11))
    

    要使用日期和时间,需要另外从 datetime 模块中导入 datedatetimetime;与模型字段中的三个数据类型 DateDatetiemTime 一一对应。

  4. 枚举类型(方法一)

     class Articles(Base):
         __tablename__ = 'articles'
         id = Column(Integer,primary_key=True,autoincrement=True)
         Language = Column(Enum('Python','Java','PHP','C++'))
    
     article = Articles(Language='python')
    

    其中,在创建实例的时候,Language 字段必须要从枚举指定的 Python Java PHP C++ 中选择,否则报错。

  5. 枚举类型(方法二)

    定义枚举类型还有另外一种更好的方法,就是借助 python3 中自带的 enum 模块进行定义。要注意四个地方:

    1. 导入 enum 模块
    2. 创建所需的 enum 类并继承自 enum.Enum
    3. 创建模型并且使用枚举数据类型时,从自定义的 enum 类中引用
    4. 根据模型创建实例时也可以从自定义的 enum 类中取

    如下代码所示:

     import enum
    
     class MyEnum(enum.Enum):
         Python = 'Python'
         Java = 'Java'
         PHP = 'PHP'
         C = 'C++'
     
     class Articles(Base):
         __tablename__ = 'articles'
         id = Column(Integer,primary_key=True,autoincrement=True)
         Language = Column(Enum(MyEnum))
     
     article = Articles(Language=MyEnum.Python)
    
  6. 长文本类型

    长文本类型只有 MySQL 数据库才支持,所以如果想使用长文本类型,则需要从 sqlalchemy.dialects.mysql 中导入:

     from sqlalchemy.dialects.mysql import LONGTEXT
    
     class Articles(Base):
         __tablename__ = 'articles'
         id = Column(Integer,primary_key=True,autoincrement=True)
         content = Column(LONGTEXT)
     
     article = Articles(content='over 60000 words')
    

4.2 常用参数

  1. primary_key:设置是否为主键

  2. autoincrement:设置是否为自增长

  3. default:设置默认值,即当实例化的时候没有指定该属性的值时,该属性的值。可以在 create_time 属性中使用。

  4. nullable:设置该属性的值是否可以为空,如果没有给该属性设置该参数,则默认为 True,即默认可空。但主键默认为 False

  5. unique:设置是否唯一,比如手机号码、邮箱等属性,都是唯一的,即要指定 uniqueTrue。不设置时,默认是 False

  6. onupdate:若设置了该属性,则当其他属性有改动的时候,该属性也会更改。最典型的应用是:

    update_time 用来设置文章的更新时间,当文章的标题或者内容被更新时,update_time 也会随之被更新,如下代码所示:

     class Aricles(Base)
     	__tablename__ = 'articles'
     	id = Column(Integer,primary_key=True)
     	title = Column(String(50),nullable=False)
     	content = Column(Text,nullable=Flase)
     	update_time=Column(DateTime,onupdate=datetime.now,default=datetime.now)
    
     article = Articles(datetime.now())
    

    当对象 article 被创建后,在某一时刻其 title 或者 content 属性被修改,那么其 update_time 属性由于被指定了 onupdate=datetime.now 参数,也会随之更改。

    在第一次创建这条数据的时候,不会使用 onupdate 的值,而是使用 default 的值。

  7. name:用来指定某个模型中的属性映射到数据库后,该属性对应字段的名称。也就是说,你在定义模型的时候,有一个 title 属性,但是你想让该属性映射到数据库中的时候变成其他名字的字段,就可以使用 name 参数来实现。如:

     class Aricles(Base)
     	id = Column(Integer,primary_key=True)
     	__tablename__ = 'articles'
     	title = Column(String(50),nullable=False,name='My_title')
    
     # 如果把 name 参数放到该属性第一个位置,则不需要 name 关键字,如下即可:
     	title = Column('My_title',String(50),nullable=False)
    
     # 但是不可以把该参数放到第一个位置的同时还指定参数名,因为关键字参数必须要放在未知参数之后!(name='myyd'这种叫关键字参数;int(12)这种叫未知参数)。
     	title = Column(name='My_title',String(50),nullable=False)	# 这样是不行的
    

5. query 查询详解

准备工作:

在查询之前,我们需要在创建好的模型中定义 __repr__() 函数,这个函数的作用是:当用 print 输出由这个类组成的列表时,会按照这个函数定义的格式输出。

要注意与 __str__() 函数的区别,__str__() 函数是只有在 print 输出去这个类的具体实例的时候才会被调用。

两个函数的定义实现如下:

def __str__(self):
    return 'id=%s;title=%s;price=%s' % (self.id,self.title,self.price)
def __repr__(self):
    return 'id=%s;title=%s;price=%s' % (self.id, self.title, self.price)

5.1 获取模型所有属性

可以用 query 获取 ORM 模型中实例的所有属性:

result = session.query(Articles).all()
for article in result:
    print(article)

5.2 获取模型中指定属性

可以用 query 获取 ORM 模型中实例的指定属性:

result = session.query(Articles.title,Articles.price).all()
for article in result:
	print(article)

5.3 聚合函数

可以用 query 内置的一些聚合函数来实现对查找到的数据做进一步的操作,在使用这些聚合函数之前要先导入这些函数所在的类(func)。

from sqlalchemy import func

这些聚合函数是:

  1. func.count:统计行的数量

     session.query(func.count(Articles.id)).first()	# 注意要指定表的字段,即模型的属性
     # 输出该表中的数据条目数量,例如:有六条数据就输出(6,)
    
  2. func.avg:求平均值

    求平均值也是类似的用法:

     result = session.query(func.avg(Articles.price)).first()
     print(result)	# (78.28571428571429,)
    
  3. func.max:求最大值

     result = session.query(func.max(Articles.price)).first()
     print(result)	# (97,)
    
  4. func.min:求最小值

     result = session.query(func.min(Articles.price)).first()
     print(result)	# (51,)
    
  5. func.sum:求和

     result = session.query(func.sum(Articles.price)).first()
     print(result)	# (548.0,)
    

实际上,func 对象中并没有定义任何函数,因为它底层的实现是把传入的函数翻译成 sql 语句后再进行操作。所以只要是 mysql 中有的聚合函数,都可以使用 func. 来调用。

6. filter 方法常用过滤条件

  1. equal

     article = session.query(Articles).filter(Articles.id==1).all()
     print(article)
    
  2. not equal

     articles = session.query(Articles).filter(Articles.id!=1).all()
     print(articles)
    
  3. like & ilike(不区分大小写)

     articles = session.query(Articles).filter(Articles.title.like('MYY%')).all()		# 其中,% 在 sql 语句中是通配符
     print(articles)
    
  4. in

    这里要注意,使用的 in 的时候要传入一个列表,最终查找的结果是:既在数据库中又是列表中指定的数据被找到。

     articles = session.query(Articles).filter(Articles.title.in_(['MYYD','title1','title2'])).all()
     # 为什么是 in_ ?因为 python 中有关键字 in,为了区分所以不能用 in,而 _in 代表了类中的私有方法,但这里很明显应该是公有方法,所以定义为 in_
     print(articles)
    
  5. not in

    即相对于上例而言,取反的结果。

    方法一:波浪号

     articles = session.query(Articles).filter(~Articles.title.in_(['MYYD','title1','title2'])).all()		# 注意这里要加一个波浪号
     print(articles)
    

    方法二:notin_()

     articles = session.query(Articles).filter(Articles.title.notin_(['MYYD','title1','title2'])).all()		# 注意这里是 notin_()
     print(articles)
    
  6. is nullis not null

    用来根据某个字段是否为空来查找数据,如下:

     articles = session.query(Articles).filter(Articles.content==None).all()
     print(articles)
    
     # 查找 Articles.content 字段为空的数据
    

    is not null 的示例如下:

     articles = session.query(Articles).filter(Articles.content!=None).all()
     print(articles)
    
     # 查找 Articles.content 字段为空的数据
    
  7. and

    用来查找更精细的范围,如 content='abc' 并且 title='MYYD' 的数据,如下:

     articles = session.query(Articles).filter(Articles.content=='abc',Articles.title=='MYYD').all()
     print(articles)
    

    会查找到 title 为 MYYD 并且 content 为 abc 的数据条目。

  8. or

    只要满足指定条件之一即可。如下:

     from sqlalchemy import or_
    
     articles = session.query(Articles).filter(or_(Article.title=='MYYD',Articles.content=='MYYD')).all()
     print(articles)
    

    用得比较多的情况是,搜索一个关键字,这个关键字可能出现在标题中,也可能出现在内容中。这里要注意的是:要从 sqlalchemy 中导入 or_ 这个方法。

    实际上,and 也有这种方法,如下:

     from sqlalchemy impor and_
    
     articles = session.query(Aritlces).filter(and_(Articles.title=='MYYD',Articles.content=='abc')).all()
     pint(articles)
    
  9. 小知识,如果想要获取翻译成的 sql 语句,可以在查询的时候不加 .all() 或者 .first(),如下:

     articles = session.query(Articles).filter(~Articles.title.in_(['MYYD','title1','title2']))
     print(articles)
    
     输出如下:
     SELECT articles.id AS articles_id, articles.title AS articles_title, articles.price AS articles_price FROM articles WHERE articles.title NOT IN (%(title_1)s, %(title_2)s, %(title_3)s, %(title_4)s)
    

7. 外键及其四种约束

外键可以使表与表之间的关系更加紧密,Sqlalchemy 也支持对外键的操作。而 Sqlalchemy 操作外键是通过 ForeignKey 来实现的。最常见的例子是:有用户和文章这两张表,每张表都有自己的属性,但是文章是通过用户来发表的,所以这两张表中必然存在某种联系;使用外键就可以将两张表联系起来。

那么怎么使用 sqlalchemy 创建两张具有约束关系的表呢?如下所示:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(20),nullable=False)

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text)

    user_id = Column(Integer,ForeignKey('users.id'))	# 注意这个地方要使用数据库表名,而不是创建模型时的类名

7.1 外键的四种约束

使外键有四个可以约束的功能,在使用外键连接时用 ondelete 关键字指定。指定方法是:

user_id = Column(Integer,ForeignKey('users.id',ondelete='RESTRICT'))
user_id = Column(Integer,ForeignKey('users.id',ondelete='NO ACTION'))
user_id = Column(Integer,ForeignKey('users.id',ondelete='CASCADE'))
user_id = Column(Integer,ForeignKey('users.id',ondelete='SET NULL'))

四个约束功能介绍如下:

  1. RESTRICT:父表数据被删除时会由于子表数据还在引用而阻止删除

    这个约束是默认就会存在的,不用特意指定。

  2. NO ACTION:与 MySQL 中的 RESTRICT 相同

    既然相同,那么也不用特意指定。

  3. CASCADE:级联删除,即当父表中的某个条目被删除了,子表中关联了该条目的数据也会被删除

     user_id = Column(Integer,ForeignKey('users.id',ondelete='CASCADE'))
    
  4. SET NULL:父表中的某个条目被删除,子表中关联了该条目的数据不会被删除,但是外键字段会被置为 NULL

     user_id = Column(Integer,ForeignKey('users.id',ondelete='SET NULL'))
    

    即原来是:

     mysql> select * from articles;
     +----+-------+---------+---------+
     | id | title | content | user_id |
     +----+-------+---------+---------+
     |  1 | NB    | abc     |       1 |
     +----+-------+---------+---------+
    

    users 表中 id=1 的数据被删除后,articles 表中关于该字段的值就会被置为 NULL

     mysql> select * from articles;
     +----+-------+---------+---------+
     | id | title | content | user_id |
     +----+-------+---------+---------+
     |  1 | NB    | abc     |    NULL |
     +----+-------+---------+---------+
    

    要注意的是,在使用外键时,是不可以为其设置 SET NULL 字段的同时还设置 nullable=False 的。

8. ORM 外键和一对多关系

现在有一个需求,要查找某一篇文章标题为 NB 的作者的信息,怎么才能实现这一需求?

8.1 原始的方法:

  1. 查找标题为 NB 的文章
  2. 获取该文章关联到 users 的外键字段的值
  3. 通过该值去 users 表中查找到该作者

代码实现如下:

article_NB = session.query(Article).filter_by(title='NB').first()
print(article_NB)
uid = article_NB.user_id
author = session.query(User).get(uid)
print(author)

8.2 ORM 提供的方法(relationships)

要使用 ORM 提供的这个方法,就必须从 sqlalchemy.orm 模块中导入:

from sqlalchemy.orm import sessionmaker,relationship

然后在定义外键的时候,同时定义外键所链接到的表的字段,并指定 relationship 字段:

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text)

    user_id = Column(Integer,ForeignKey('users.id'))	# 注意这个地方要使用数据库表名,而不是创建模型时的类名

	author = relationship('User')	# 注意这个地方要使用创建模型时的类名,而不是数据库表名

做好上述定以后,就可以直接通过 relationships 提供的方法,来获取文章的作者了,如下所示:

article = session.query(Article).filter(Article.title=='NB').first()
print(article.author)	# article.author 即是所要找的作者
print(article.author.username)	# 打印作者的用户名

8.3 relationship 反向查找

上面的例子是:查找一篇文章对应的作者,那么文章和作者之间的关系是多对一的,即一个作者可能发表多篇文章,而一篇文章只能有一个作者。

8.2 中,已经使用 relationship 引用了文章的作者,那么能否使用 relationship 引用作者的文章呢?答案是肯定的,如下所示:

from sqlalchemy.orm import sessionmaker,relationship

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(20),nullable=False)

    articles = relationship('Article')

通过作者找文章的方法和通过文章找作者的方法类似,如下:

user = session.query(User).filter_by(username='MYYD').filter()
print(user.articles)	# 会打印出该 user 的所有 article

8.4 使用 backref 反向引用

在之前的例子中,我们在 UserArticle 中都分别使用了 relationship 来互相指定对方和自己的关系。实际上,不用这么麻烦,我们只需要在一个模型中指定其和另一个模型的关系即可,这时需要借助另一个参数:bakeref

所以我们只需要在 Article 模型中指定即可:

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text)

    user_id = Column(Integer,ForeignKey('users.id'))

	author = relationship('User',backref='articles')

这样,同样可以使得 user.articles 能够正常使用。而不用再在 User 模型中使用 relationship 再指定一次关系了。

8.5 完整代码

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(20),nullable=False)

    def __repr__(self):
        return 'username:%s'% self.username

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text)
    user_id = Column(Integer,ForeignKey('users.id',ondelete='SET NULL'))
    author = relationship('User',backref='articles')

    def __repr__(self):
        return 'title:%s\tcontent:%s'%(self.title,self.content)

9. 一对一关系

在指定了一对多的关系后,如 usersarticles 这两者之间的关系。实际上,在创建 articles 的时候可以不用指定其 user_id 字段的值,当一篇文章被创建完成后,再指定作者也是被允许的。因为在一对多的关系中,usersarticles 属性是一个 LIST,可以使用 append 方法为其添加值。如下代码所示:

article1 = Article(title='abc',content='abc')
article2 = Article(title='MYYD',content='myyd')
session.add_all([article1,article2])

user1 = User(username='MYYD')
session.add(user1)

user1.articles.append(article1)		# 使用 append() 方法
user1.articles.append(article2)
session.commit()

user = session.query(User).filter_by(username='MYYD').first()
print(user.articles)

# 输出 [title:abc	content:abc, title:MYYD	content:myyd]

以上,是对 ORM 中一对多关系的一点补充。借此,我们可以引出 ORM 中一对一的关系:

有一个需求:为用户创建一个 users 表,表中定义了一些属性,这些属性有:姓名,学校等。其中学校这个属性不是常用属性,我们把它放到另外一张 extends 表中。这样一来,两张表就是一对一的关系了。那么,怎么用 ORM 来实现这一需求呢?可以在创建 extends 表的时候在使用 relationship 参数的字段中,传入 uselist=False 参数,即不可以 uselist,如下代码所示:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    def __repr__(self):
        return 'id:%s\tusername:%s'%(self.id,self.username)
class UserExtend(Base):
    __tablename__ = 'extends'
    id = Column(Integer,primary_key=True,autoincrement=True)
    school = Column(String(20))
    user_id = Column(Integer,ForeignKey('users.id'))
    my_user = relationship('User',backref=backref('extends',uselist=False))		# 注意这里使用 relationship 参数的方法,和传入 backref 与之前的区别

    def __repr__(self):
        return 'school:%s'%self.school

user1 = User(username='MYYD')
session.add(user1)

extend = UserExtend(school='JMU')
session.add(extend)

user1.extends.append(extend)	# 此时再使用 append 方法时就会报错:AttributeError: 'NoneType' object has no attribute 'append'
session.commit()

这就是 ORM 中一对一关系的使用。

10. 多对多的关系

多对多关系的需求常用在文章与标签上,譬如可以按照内容划分文章的标签为:音乐、体育、娱乐、搞笑等;而一篇文章可能涉及多个方面的内容,这时候文章和标签就是多对多的关系。但是我们使用 ORM 定义两张表的多对多关系时,需要借助一个中间表来实现。

10.1 多对多的创建

为两张表创建多对多关系的步骤如下:

  1. 导入定义中间表所需的 Table 类:

     from sqlalchemy import Table
    
  2. 创建两张表(artcielstags):

    按照常规的方法创建两张表即可:

     class Article(Base):
         __tablename__ = 'articles'
         id = Column(Integer,primary_key=True,autoincrement=True)
         title = Column(String(50),nullable=False)
         content = Column(Text)
     
         def __repr__(self):
             return 'title:%s\tcontent%s'%(self.title,self.content)
     
     class Tag(Base):
         __tablename__ = 'tags'
         id = Column(Integer,primary_key=Text,autoincrement=True)
         name = Column(String(50),nullable=False)
     
         def __repr__(self):
             return 'TagName:%s'%self.name
    
  3. 重点在这里!创建中间表:

    这个中间表用来连接刚刚定义的两张表,中间表必须继承自这个 Table 类。并且还要使用 metadate 进行初始化,同时要设置聚合主键,如下所示:

     article_tag = Table(
         'article_tag',
         Base.metadata,
         Column('articles_id',Integer,ForeignKey('articles.id'),primary_key=True),
         Column('tags_id',Integer,ForeignKey('tags.id'),primary_key=True)
     	# tag_id 是字段名称,Integer 的类型要和本表外键引用的字段类型相同
     )
    

    其中:

    1. Base.metadata 用来对这个中间表进行初始化;
    2. 然后用 Column 对其进行相关字段的创建。
    3. 为两个字段同时设置主键,这样就能两张表中的唯一一篇文章。
  4. 使用 relationship 关联两张表:

    最后,还要在两张表的其中一张表中使用 relationship 参数来互相关联一下,同时指定 secondary 参数来指定通过实现多对多关系的中间表,如下:

    1. articles 表中使用:

       tags = relationship('Tag',backref='articles',secondary=article_tag)
      
    2. tags 表中使用:

       articles = relationship('Article',backref='tags',secondary=article_tag)
      

    需要注意的是:如果使用中间表来实现多对多的映射关系后,就不必在两张被映射的表中指定外键关系了。因为已经通过 secondary 来指定中间表格了,而中间表格会实现外键约束。

10.2 多对多的使用

我们先用定义的两张表创建数据并关联一下:

  1. 为每张表创建两个数据

     article1 = Article(title='ABC',content='ABC')
     article2 = Article(title='abc',content='abc')
     
     tag1 = Tag(name='tag1')
     tag2 = Tag(name='tag2')
    
  2. 为文章添加标签

     article1.tags.append(tag1)
     article1.tags.append(tag2)
    
  3. 添加并提交 session

     session.add_all([article1,article2])
     session.commit()
    
  4. 到数据库中查找

     # articles 表
     	mysql> select * from articles;
     	+----+-------+---------+
     	| id | title | content |
     	+----+-------+---------+
     	|  1 | ABC   | ABC     |
     	|  2 | abc   | abc     |
     	+----+-------+---------+
     	2 rows in set (0.00 sec)
    
     # tags 表
     	mysql> select * from tags;
     	+----+------+
     	| id | name |
     	+----+------+
     	|  1 | tag1 |
     	|  2 | tag2 |
     	+----+------+
     	2 rows in set (0.00 sec)
    
     # article_tag 表
     	mysql> select * from article_tag;
     	+-------------+---------+
     	| articles_id | tags_id |
     	+-------------+---------+
     	|           1 |       1 |
     	|           2 |       1 |
     	|           1 |       2 |
     	|           2 |       2 |
     	+-------------+---------+
     	4 rows in set (0.00 sec)
    

在用我们的 query 去查询:

article = session.query(Article).first()
print(article.tags)
# [TagName:tag1, TagName:tag2]

tag = session.query(Tag).first()
print(tag.articles)
# [title:ABC	contentABC, title:abc	contentabc]

11. ORM 层面删除数据注意事项

ORM 层面对表中的数据进行删除,比如 articles 表中的 uid 字段通过外键引用到 users 表种的 id 字段。在使用 sql 语句对 users 表中的某个数据进行删除的时候,会由于 articles 表中的外键引用,而拒绝删除。

但是在 ORM 层面使用 python 代码对表中的数据进行删除,那么实际上是可以删除成功的。删除的结果是:users 表中该条数据被删除,但是引用了该条数据的 articles 表中的数据关联的 id 字段会被置为空。

这是因为,ORM 在底层对这个操作的实现分为两步:先将 articles 表中该数据的 uid 字段置为 NULL,再删除 users 表中的数据。不过前提是,articles 中的 uid 字段允许设置为空。

但是这个机制实际上并不怎么好,因为这样可能会导致误删除的发生。那如何避免这种情况呢?实际上也很简单:在 articles 的外键字段上,设置 nullable=False 即可。

总结:

ORM 层面对数据库删除操作,会无视 mysql 级别的外键约束,要想避免这种情况发生,需要将外键字段设置为 nullable=False

12. relationship 方法中的 cascade 参数

relationship 方法中的 cascade 参数可以在创建关系的时候,指定一些属性。cascade 参数一共有以下几个值:

  1. save-update(默认属性)

    这是一个默认属性,即当不指定 cascade 参数的时候,默认 cascade='sace-update'。这个属性的意思是:

    如果给 cascade 指定了 save-update 属性,那么在添加一条数据的时候,会同时添加该条数据关联的其他模型中的数据。例如:

    Article 模型在与 User 模型建立关系的时候,如果指定了 cascade='save-update' 属性,那么当使用 session.add() 添加 Article 实例(article)的时候,如果该实例关联的 User 实例(user)已经创建,则将 user 也一并添加。即不用再使用 session.add(user) 进行添加。

    具体的使用方法如下:

     author = relationship('User',backref='articles',cascade="save-update")
    

    如果不想让 sqlalchemy 自动执行这个操作,那么可以将 cascade 置为空,即 cascade=""需要注意的是,这个属性仅对 Article 这个模型生效。也就是说,置为空后,session.add(article) 不会自动执行 session.add(user),但是 session.add(user) 还是会自动执行 session.add(article)。明白?

  2. delete

    delete 的作用就是,当删除一条数据的时候,会同时删除该条数据关联的其他模型中的数据。

    其使用方法和 save-update 一致。

    注意,cascade 属性可以同时指定多个值,例如同时指定 save-updatedelete,可以这么写:

     author = relationship('User',backref='articles',cascade="save-update,delete")
    

    如果想在关联模型中反向使用该属性,还可以将 cascade 放到 backref() 方法中:

     author = relationship('User',backref=backref('articles',cascade="save-update,delete",cascade="save-update,delete"))
    
     # 如果 backref 只接收一个参数,可以写成 backref='参数',如果接收多个参数,可以调用 backref() 方法
    
  3. delete-orphan

    如果父表中的相关数据被删除,会使得子表中的数据的某个字段置为空。如果指定了 cascade='delete-orphan',那么由于父表的数据不存在,子表中的数据也会被删除。不过在指定 delete-orphan 的时候要同时指定 delete 属性。那么删除父表数据带动子表数据被删除的操作也有可能是 delete 属性完成的,所以我们可以将父表数据中关于子表数据的字段置空,这样子表的相关字段也会被置空,进而被删除。这样就能体现出 delete-orphan 的作用了。

    注意这个属性只能在父表中指定,并且只能用在一对多或者多对多的关系中。

  4. merge(默认属性)

    这个属性不仅是 relationship 方法中 cascade 的属性,还是 session 中的属性。其作用是:改变数据库中的值。例如:

    users 表的 ORM 模型如下:

     class User(Base):
         __tablename__ = 'users'
         id = Column(Integer,primary_key=True,autoincrement=True)
         name = Column(String(50),nullable=False)
    

    若表中已经存在一个用户(user1):id=1,name='MYYD';如果再创建一个用户(user2):id=1,name='myyd'。使用 session.add(user2) 后再 commit,会报错。因为 id 是主键,session.add() 不允许添加主键重复的数据。

    那么如果使用 session.merge(user2),则会用 user2id 去匹配 user 表中的 id 字段,并将匹配中的数据中的 name 改为 myyd

    实际上这个属性用得比较少。因为这对于已经存在的数据,相当于修改操作,而修改操作完全不必要使用这种方法。那么这个方法的存在还有什么意义呢?

    这个方法还真有自己存在的意义,因为这种方法可以添加数据,而修改操作却不能添加。使用 session.merge() 的时候,首先会判断表中是否已经存在这条数据,若存在则修改;若不存在则添加。

    除此之外,还有一个作用:如果在父表的 cascade 中指定了这个属性,那么父表下所有关联的子表,在使用 session.merge(父表数据) 的时候,也会实现这个属性。

  5. expunge

    expunge 属性也是删除,用 session.expunge() 实现。session.expunge()session.delete() 的区别是:前者仅仅是将数据从 session 中删除,并不会删除数据库中的内容。这是 expungesession 中的体现。

    而 expunge 在 cascade 中的体现和 merge 类似:当在父表的 cascade 中指定了这个属性,那么在使用 session.cascade(父表数据) 时,父表下所有关联的子表,也会实现这个属性。

  6. all

    如果在 cascade 中指定了 all 属性,那么就相当于包含了以上除了 delete-orphan 外的所有属性。

13. sqlalchemy 中的三种排序方式

  1. order_by

    可以在查询的时候,使用 order_by 参数对查询到的数据进行排序。默认排序是从小到大,从前到后,如果想使用降序排序则在前面加一个 - 号。

     articles = session.query(Article).order_by(-Article.create_time).all()
     for article in articles:
         print(article)
    

    或者使用 desc 进行排序:

     articles = session.query(Article).order_by(Article.create_time.desc()).all()
     for article in articles:
         print(article)
    

    或者还可以直接传入模型字段的名称,搭配 - 号使用:

     articles = session.query(Article).order_by('-create_time').all()
     for article in articles:
         print(article)
    
  2. 模型中的 order_by

    如果对于一个经常需要排序的模型来说,在每次查询的时候都使用 order_by 参数进行排序,会有点麻烦,所以我们再定义模型的时候就可以定义一个 order_by 属性。如下:

    先在模型中定义 __mapper_args__ 属性:

     __mapper_args__ = {
         'order_by':create_time.desc()
     }	
    

    然后查询的时候就不必指定 order_by 关键字了:

     articles = session.query(Article).all()
     for article in articles:
         print(article)
    

    输出如下:

     title:zhenniubi	create_time:2018-03-28 23:33:32
     title:MYYD		create_time:2018-03-28 23:33:07
    

    但是如果想在某一次查询中不使用倒序排序呢?很简单,在查询的时候指定 order_by 关键字即可。

  3. relationship 中使用 order_by 参数

    可以在定义 relationship 关系的时候直接指定父表下子表的排序方式,如下:

     class User(Base):
         __tablename__ = 'users'
         id = Column(Integer,primary_key=True,autoincrement=True)
         username = Column(String(50),nullable=False)
    
     class Article(Base):
         __tablename__ = 'articles'
         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('users.id'),nullable=False)
     
         author = relationship('User',backref=backref('articles',order_by=create_time.desc()))
    

    当搜索到一个 user 时,获取 user 下的所有 articles,这些 articles 的排序方式就是 order_by=create_time.desc() 指定的倒序。如下:

     user = session.query(User).filter(User.id=='1').first()
     for article in user.articles:
         print(article)
    
     # 打印的内容如下:
     title:title2	create_time:2018-03-29 00:19:18
     title:title1	create_time:2018-03-29 00:19:13
    

14. sqlalchemy 对数据库的 limit 、offset 和 切片 操作

14.1 limit 操作

limit 这个属性可以对每次查询的结果数量进行限制,例如可以在查询时只取 10 个数据:session.query(Article).limit(10).all()

需要注意的是,使用以上操作进行数据的查询时,默认是从第 1 条数据进行查找的。如果我们想要从指定位置开始查找,可以使用 offset 操作:

14.2 offset 操作

offset 这个属性可以指定我们开始查找的位置,例如想要获取第十一到第二十条数据,可以这么操作:session.query(Article).offset(10).limit(10).all()

这个 offsetlimit 谁先谁后无所谓。

14.3 切片操作

实际上,通过 articles = session.query(Article).all() 得到的数据,articles 是一个列表变量,所以我们可以通过列表中的切片属性,来获取指定的内容。例如想要从获取到的所有数据中提取指定范围内的数据(如第3个到第7个数据),有两种方法可以实现:

  1. slice 方法

    这种方法是:article = session.query(Article).slice(2,7).all()

  2. list 切片方法

    这种方法是:articles = session.query(Article).all()[2:7]

14.4 总结

以上三个操作,都可以搭配上一节中提到的排序方法灵活使用。

15. sqlalchemy 数据查询懒加载技术

15.1 lazy='dynamic'

在现实的项目中,常常会遇到这种需求:当我点进一个用户的主页中,该用户的主页会显示当天发表的文章,而不是其所有的文章。要实现这个功能实际上很简单:先将该用户的所有文章都查找出来,然后对每一篇的创建时间进行过滤后再渲染回前端。如下代码所示:

articles = session.query(Article).all()
for article in articles:
    if article.create_time.day == 29:
        print(article)

以上方法,确实简单粗暴。但是我只需要展示该用户当天的文章,实际上没必要从数据库中查找出所有的文章,毕竟这么做很浪费资源。我们可以使用 sqlalchemy 给我们提供的懒加载技术,实现从数据库中查找出来的文章就是当天发表的文章的需求。

但是我们使用 articles = session.query(Article).all() 获取到的对象是一个 LIST,而 LIST 是没有办法进行进一步的 filter 的。但是我们的 Query 对象是可以进行 filter 的,所以我们可以将 articles 转换成 Query 对象,然后再对 articles 进行过滤。想要实现这个功能,那么在创建模型关系的时候必须要在 relationship 中的 backref() 方法中,添加一个 lazy=dynamic 值。如下:

class Article(Base):
	略..
    author = relationship('User',backref=backref('articles',lazy='dynamic'))

然后使用 articles = session.query(Article).all() 查找到的 articles 就是一个 AppenderQuery 对象,该对象除了可以实现 Query 对象的所有功能(包括filter),还能实现 LIST 的所有功能(包括append)。

所以这个需求我们就能轻而易举的实现了:

user = session.query(User).filter_by(id=1).first()
print(type(user.articles))
	# 输出:<class 'sqlalchemy.orm.dynamic.AppenderQuery'>

print(user.articles.filter(Article.create_time.day==29).all())
	# 输出所有 29 号发表的文章
	# 注意这里是去数据库中筛选符合条件的数据,而不是将所有的数据都取出来,这样就节省了资源的消耗

注意:这个地方 lazy='dynamic' 只能对 一对多 或者 多对多 中的 使用,即对于上述两个模型,只能用在 UserArticle 上,也就是说对符合条件的文章的过滤只能通过 user.articles.filter() 操作进行实现。

附:相对完整的代码如下:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

class Article(Base):
    __tablename__ = 'articles'
    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('users.id'),nullable=False)

    author = relationship('User',backref=backref('articles',lazy='dynamic'))	# 注意这里

user = session.query(User).first()
print(user.articles.filter(Article.id>9).all())

15.2 lazy='select'

这个是 sqlalchemy 中的默认选项,即如果不设置 lazy='xxx' 这个属性,则默认为 lazy='select' 生效。

lazy='select' 的作用是,在没有调用 user.articles 之前,sqlalchemy 不会去获取 user.articles 这个属性下的数据;而一旦调用了 user.articles,那么 sqlalchemy 就会自动的去获取 user.articles 这个属性下的所有数据。

这也就是为什么我们在实现上述需求的时候,使用 lazy='dynamic' 的另一大原因。

16. sqlalchemy 对数据库的高级查询

对于定义的 ORM 模型,和插入的数据如下:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)
    age = Column(Integer,nullable=False,default=0)
    gender = Column(Enum('male','female','secret'),default='secret')

user1 = User(username='张伟',age=13,gender='male')
user2 = User(username='无名',age=14,gender='secret')
user3 = User(username='翠花',age=19,gender='female')
user4 = User(username='狗剩',age=17,gender='male')
user5 = User(username='李蛋',age=16,gender='female')

session.add_all([user1,user2,user3,user4,user5])
session.commit()

16.1 group_by 查询

group_by 会根据指定的某个字段进行分组。例如,要根据性别来分组,统计每个性别的人数,可以使用如下代码实现:

result = session.query(User.gender,func.count(User.id)).group_by(User.gender).all()
# 指定查询 User.gender,并且以 User.id 为标识来统计,最后按 User.gander 进行分组
print(result)

# 输出:[('male', 2), ('female', 2), ('secret', 1)]

实际上转换成的 SQL 语句如下:

SELECT users.gender AS users_gender, count(users.id) AS count_1 FROM users GROUP BY users.gender

16.2 having 查询

having 是对查找结果的进一步过滤,和 sql 语句中的 where 类似。例如:查看未成年人的人数,可以先对年龄进行分组统计,再对分组进行过滤。代码如下所示:

result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age<=18).all()
print(result)
# 输出:[(13, 1), (14, 1), (16, 1), (17, 1)]

16.3 join 方法

先来段代码压压惊:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    def __repr__(self):
        return 'username:%s'%self.username

class Article(Base):
    __tablename__ = 'articles'
    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('users.id'),nullable=False)

    author = relationship('User',backref=backref('articles'))

    def __repr__(self):
        return 'title:%s\tcreate_time:%s'%(self.title,self.create_time)

user1 = User(username='mayi')
user2 = User(username='youdu')

for i in range(1):
    article = Article(title='title %s'%i)
    article.author = user1
    session.add(article)
session.commit()

for i in range(1,3):
    article = Article(title='title %s'%i)
    article.author = user2
    session.add(article)
session.commit()

# 以上代码创建了两个模型:User 和 Article。
# 其中 Article 的 uid 属性与 User 的 id 属性建立了外键约束,并且二者相互调用的方法是 User.articles 和 Article.author。
# User 建立了两个实例 user1 和 user2
# Article 建立了三个实例 title0、title1 和 title2
# 其中,user1 关联了 titile0,user2 关联了 title1 和 title2

join 分为 left join(左外连接)right join(右外链接)内链接(等值链接),其中左外链接将A表链接到B表的左边,右外链接将A表链接到B表的右边;且总是以左表为主表,右表为副表。join 方法实际上是两张表联合在一起进行查询,例如:要对所有用户按发表文章的数量进行进行由大到小的排序,可以使用如下代码实现。

# 原生 SQL 语句查询如下
select users.username,count(articles.id) from users join articles on users.id=articles.uid group by users.id order by count(articles.id) desc;	

# sqlalchemy 方式查询如下
result = session.query(User,func.count(Article.id)).join(Article,User.id==Article.uid).group_by(User.id).order_by(func.count(Article.id).desc()).all()
print(result)
# 输出:[(username:youdu, 2), (username:mayi, 1)]

sqlalchemy 查找时候要注意几点:

  1. 查找的出来的内容是由 query() 括号中的参数决定,而不是由 join 连接的两张表决定。
  2. 如果 join 的两张表之间有且只有一个外键建立起了关系,那么在 join 的时候就可以不写 User.id==Article.uid
  3. 查询时内链接用 join 完成;左外连接用 outterjoin 来完成;

16.4 别名

16.5 子查询

即在一个 select 中使用一个 select。子查询可以在一次访问数据库的时候对查询结果再进行一次查询,这样可以减少对数据库的操作。当你的网站访问量很高的时候,建议使用子查询;当你的网站访问量比较少,那么可以不考虑这个问题。

例如,有如下代码:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)
    age = Column(Integer,nullable=False,default=0)
    city = Column(String(20),nullable=False)

user1 = User(username='张伟',age=20,city='广州')
user2 = User(username='无名',age=21,city='广州')
user3 = User(username='翠花',age=22,city='厦门')
user4 = User(username='狗剩',age=21,city='长沙')
user5 = User(username='李蛋',age=22,city='厦门')

session.add_all([user1,user2,user3,user4,user5])
session.commit()

# 以上代码创建了 User 模型并且为其实例化了五个对象,添加到数据库中

有一个需求:要查找和某人(如翠花)在同一个城市且年龄一样的所有人。这个需求有两种实现:

  1. 先查找出翠花,再根据翠花的信息去查找其他同城同年龄的人。

     cuihua = session.query(User).filter(User.username == '翠花').first()
     other = session.query(User.username,User.city,User.age).filter_by(city=cuihua.city, age=cuihua.age).all()
     print(other)
    
  2. 使用子查询

    SQL 语句查询:

     mysql> select users.username,users.age,users.city from users,(select * from users where users.username='李蛋') as LD where users.city=LD.city and users.age=LD.age;
     # 其中 () 括起来的是子 select 语句,括号外面的 select 语句是根据括号内的 select 的结果进行 select 操作的。结果如下:
     +----------+-----+------+
     | username | age | city |
     +----------+-----+------+
     | 翠花     |  22 | 厦门 |
     | 李蛋     |  22 | 厦门 |
     +----------+-----+------+
     2 rows in set (0.00 sec)
    

    sqlalchemy 语句查询:

     temp = session.query(User.city.label('city'),User.age.label('age')).filter(User.username=='李蛋').subquery()
     result = session.query(User.username,User.city,User.age).filter(User.city==temp.c.city,User.age==temp.c.age).all()
     print(result)
    
     # 其中,temp 就是子查询得到的变量,subquery 将该查询转换为子查询,同时使用 label 为属性指定别名;
     # result 是根据子查询进行查询的变量,在引用时要用 'xxx.c.xx' 的形式,打印出来的结果如下:
     # [('翠花', '厦门', 22), ('李蛋', '厦门', 22)]
    

17. Flask 下使用 SQLalchemy

在此之前的所有学习,都是 python 下使用 sqlalchemy 操作数据库的内容,和 Flask 没有任何关系。但实际上,Flask 也帮我们对 sqlalchemy 做了一层封装,比如将所有之前要从 sqlalchemy 中导入的东西都集成到了一个 SQLAlchemy 类中,而连接数据库时,使用 SQLAlchemyFlask 对象进行初始化即可,如下所示:

db = SQLAlchemy(app)

以后所有在之前 sqlalchemy 中导入的内容,都可以从 db 这个对象中获取。例如创建 ORM 模型的时候,就不需要之前的 declarative_base 来创建基类了,直接用 db.Model 作为基类。所以可以用如下代码实现:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(50),nullable=False)

    def __repr__(self):
        return 'username:%s'%self.username

class Article(db.Model):
    __tablename__ = 'articles'
    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('users.id'),nullable=False)
    author = db.relationship('User',backref='articles')

    def __repr__(self):
        return 'title:%s\tauthor:%s'%self.title,self.author

在创建模型的时候,可以省略 __tablename__ 的表名指定动作,但不建议,因为明言胜于暗喻。

映射到数据库中也可以使用如下代码实现:

db.drop_all()
db.create_all()

17.1 Flask-SQLAlchemy 下的增删改查

session 的使用方法和之前的一样。其中:

  1.  user = User(username='MYYD')
     article = Article(title='abc')
     user.articles.append(article)
     db.session.add(user)
     db.session.commit()
    
  2.  user = User.query.filter(User.id==2).first()
     db.session.delete(user)
     db.session.commit()
    
  3.  user.query.filter(User.id==1).first()
     user.username = 'MYYD'
     db.session.commit()
    
  4. 在查询的时候,如果是针对单张表进行查询,那么直接使用 TableName.query.xxx.x 的格式即可。而且在查询的时候也可以使用之前学习的 order_byfiltergroup_by 等方法。如下代码所示:

     users = User.query.order_by(User.id.desc()).all()
    

18. Flask-script 命令行操作

Flask-script 的作用是可以通过命令行的方式去操作 Flask。安装方式:在虚拟环境下 pip install flask-script

18.1 编写 flask_script 脚本代码

  1. 新建一个 manage.py 文件,将代码写在该文件中,而不是写在主 app 文件中。内容如下:

     from flask_script import Manager        # 从 flask_script 导入 Manager 
     from flask_script_demo1 import app      # 从 flask_script_demo1 导入 app 
     
     manage = Manager(app)       # 初始化 app 
     
     @manage.command     # 装饰器
     def runserver():        # 执行命令的程序写在这个函数下
         print('服务器跑起来了。')
     
     @manage.command     # 装饰器
     def stopserver():       # 执行命令的程序写在这个函数下
         print('服务器关闭了。')
    
     @manager.option('-u','--username',dest='username')	# 还可以在执行命令的时候向命令传递参数
     @manager.option('-e','--email',dest='email')	#还可以在执行命令的时候向命令传递参数
     def addBackendUser(username,email):
         user = BackendUser(username=username,email=email)
         db.session.add(user)
         db.session.commit()
     
     if __name__ == '__main__':
         manage.run()
    
  2. 命令行调用 manage.py 文件:

    在虚拟环境的命令行下,用 python manage.py command 执行 manage.py 文件下的某段程序,如:python manage.py runserverpython manage.py stopserver 分别会执行 manage.py 文件中的 runserver()stopserver() 方法。

    在定义可以传递参数的命令时要注意,使用的装饰器是 @manager.option(),以 @manager.option('-u','--username',dest='username') 为例:-u 是在传递参数时要指定的选项缩写,选项完整的写法是 --usernamedest='username' 是指该选项后面跟的参数要传递给 addBackendUser(username,email) 方法中的 username 参数。

    所以在执行可以传递参数的命令时,应该这么写(例):python manage.py -u MYYD -e 90q7w0s7x@qq.com。需要注意的是,有几个参数就要写几个 option 装饰器。

18.2 从其他文件中调用自命令

如果有一些关于数据库的操作,我们可以放在一个文件中执行。如 db_script.py 文件:

from flask_script import Manager

# 因为本文件不是作为主 app 文件,所以不需要写 if __name__ == '__main__'
# 也不需要在初始化的时候传入 app 文件

DBManage = Manager()

@DBManage.command
def init():
    print('服务器初始化完成。')

@DBManage.command
def migrate():
    print('数据库迁移完成。')

这时候要想用上 db_script.py 里定义的命令,需要在主 manage.py 文件中导入该文件并引用该文件的命令:

from db_scripts import DBManage     # 导入 db_script.py 文件
manage.add_command('db',DBManage)   # 引用该文件

# 之后要是想使用 db_script.py 中的命令,命令行中就要通过 python manage.py db init 来调用
# 其中,db 是 manage.add_command() 中引号内的值,调用子命令的方法就是这种格式

18.3 总结

  1. 如果直接在主 manage.py 文件中写命令,且该命令不需要传递参数,调用时只需要执行 python manage.py command_name 格式的命令即可

  2. 如果直接在主 manage.py 文件中写命令,且该命令需要传递参数,调用时需要执行 python manage.py command_name -[选项] 参数 格式的命令

  3. 如果将一些命令集中在另一个文件中,那么就需要输入一个父命令,比如 python manage.py db init

18.4 例子:两个文件的完整代码如下

(python2.7 环境)

1. manage.py

# encoding:utf-8

from flask_script import Manager
from flask_script_demo1 import app
from db_scripts import DBManage
manage = Manager(app)

@manage.command
def runserver():
    print u'服务器跑起来了。'

@manage.command
def stopserver():
    print u'服务器停止了。'

@manager.option('-u','--username',dest='username')
@manager.option('-e','--email',dest='email')
def addBackendUser(username,email):
    user = BackendUser(username=username,email=email)
    db.session.add(user)
    db.session.commit()

manage.add_command('db',DBManage)

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


2. db_script.py

# encoding: utf-8
from flask_script import Manager

DBManage = Manager()

@DBManage.command
def init():
    print u'服务器初始化完成。'

@DBManage.command
def migrate():
    print u'数据库迁移完成。'

19. 分开 Models 和解决循环引用

之前我们都是将数据库的模型(类)放在主 app 文件中,但是随着项目越来越大,如果对于加入的新的模型,我们还放在主 app 文件中,就会使主 app 文件越来越大,同时也越来越不好管理。所以我们可以新建一个专门存放模型的文件。如 models.py 文件用来专门存放模型的文件。

  1. 将原本在主 app 文件中定义的模型(类)移动到 models.py 文件中,但是会提示错误,所以我们在 models.py 文件中导入主 app 文件的 dbfrom models_sep.py import db;同时,因为我们的主 app 文件肯定会操作到 models 文件中的模型,所以要在主 app 文件中导入 models 创建的模型。两个文件的完整代码下所示:

     1. # 主 app 文件
     
     from flask import Flask
     from flask_sqlalchemy import SQLAlchemy
     from models import Article
     
     app = Flask(__name__)
     db = SQLAlchemy(app)
     db.create_all()
     
     @app.route('/')
     def index():
         return 'index!'
     
     if __name__ == '__main__':
         app.run()
     
     2. # models.py 文件
     
     from flask_script_demo1 import db
     
     class Article(db.Model):
         __tablename__ = 'articles'
         id = db.Column(db.Integer,primary_key=True,autoincrement=True)
         title = db.Column(db.String(100),nullable=False)
         content = db.Column(db.Text,nullable=False)
    
  2. 执行以上文件,会报错。

    报错提示:ImportError: cannot import name Article,出现此类报错,先排查路径和导入的内容是否有错,若保证没错,则很可能是出现循环引用。

    报错原因:循环引用,即 models_sep.py 引用了 models.py 中的 Article,而 models.py 又引用了 models_sep.py 中的 db,从而造成循环引用。

  3. 解决循环引用:

    解决方法:将 db 放在另一个文件 exts.py 中,然后 models_sep.pymodels.py 都从 exts.py 中引用 db 变量,这样就可以打破引用的循环。

    三个文件的代码如下:

     1. # exts.py 文件
     
     from flask_sqlalchemy import SQLAlchemy
     db = SQLAlchemy()
     
     2. # 主 app 文件
     
     from flask import Flask
     from models import Article
     from exts import db
     import config
     
     app = Flask(__name__)
     app.config.from_object(config)
     db.init_app(app)
     
     @app.route('/')
     def index():
         return 'index'
     
     if __name__ == '__main__':
         app.run()
     
     3. # models.py 文件
     
     from exts import db
     
     class Article(db.Model):
         __tablename__ = 'articles'
         id = db.Column(db.Integer,primary_key=True,autoincre)
         title = db.Column(db.String(100),nullable=False)
         content = db.Column(db.Text,nullable=False)
    
  4. 总结:

    分开 models 的目的是:让代码更方便管理。

    解决循环引用的方法:把 db 放在一个单独文件中如 exts.py ,让主 app 文件和 models 文件都从 exts.py 中引用。

20. flask-migrate 数据库迁移

这个时候如果我们的模型(类)要根据需求添加一个作者字段,这时候我们需要去修改模型 Article,修改完成我们需要再映射一遍。但是对于 flask-sqlalchemy 而言,当数据库中存在了某个模型(类)后,再次映射不会修改该模型的字段,即再次映射不会奏效。

20.1 传统解决办法:

在数据库中删除该模型对应的表格,再将带有新字段的模型重新进行映射。

很显然,这种方式明显很简单粗暴,非常不安全,因为在企业中一个数据库中的表格是含有大量数据的,如果删除可能会造成重大损失。所以我们需要一个可以动态修改模型字段的方法,使用 flask-migrate。先安装:在虚拟环境下使用命令 pip install flask-migrate 即可。

20.2 使用 migrate 动态修改模型字段

使用 flask-migrate 的最简单方法是:借助 flask-script 使用命令行来对 flask-migrate 进行操作。一共有好几个步骤,分别说明一下:

  1. 新建 manage.py 文件:

    新建 manage.py 文件后:

    1. 导入相应的包并初始化 manager

       from flask_script import Manager
       from migrate_demo import app
       from flask_migrate import Migrate,MigrateCommand
       from exts import db
       manager = Manager(app)
      
    2. 要使用 flask_migrate 必须绑定 appdb

       migrate = Migrate(app,db)
      
    3. MigrateCommand 命令添加到 manager 中,实际上就是添加 migrate 子命令到 manager

       manager.add_command('db',MigrateCommand)
      
    4. manage.py 文件代码如下:

       from flask_script import Manager
       from migrate_demo import app
       from flask_migrate import Migrate,MigrateCommand
       from exts import db
       
       manager = Manager(app)
       
       # 1. 要使用 flask_migrate 必须绑定 app 和 db
       migrate = Migrate(app,db)
       
       # 2. 把 MigrateCommand 命令添加到 manager 中
       manager.add_command('db',MigrateCommand)
       
       if __name__ == '__main__':
           manager.run()
      
  2. manage.py 文件中,导入需要映射的模型(类):

    因为在主 app 文件中已经不再需要对模型进行映射,而对模型的操作是在 manage.py 文件中进行的,包括 flask-migrate 动态映射,所以要导入需要映射的模型。

     from models import Article
    
  3. 完成以上步骤后,即可到命令行中更新数据库中的模型了:

    1. python manage.py db init,初始化 flask-migrate 环境,仅在第一次执行的时候使用。
    2. python manage.py db migrate,生成迁移文件
    3. python manage.py db upgrade,将迁移文件映射到数据库的表格中
    4. 更多命令参考 python manage.py db --help
posted @ 2018-04-01 16:53  蚂蚁有毒  阅读(187)  评论(0编辑  收藏  举报