一、ORM

在服务器后台，数据都是存储在数据库中的。但是，一个项目在开发和部署的时候，是存在使用不同数据库的可能的（不同的数据库sql语句不尽相同），那么，我们是不是需要把所有的sql语句都再重新写一遍呢？

图片.png

答案当然是否定的，根据懒人智慧的理论，必然存在某个神器可以帮我们避免反复重写sql语句。而这个神器就是ORM系统。那么，什么是ORM呢？

ORM（Object Relational Mapping），全称 “ 对象关系映射 ”。通过ORM可以不关心后台是使用的哪种数据库，只需要按照ORM所提供的语法规则书写相应的代码，ORM就会自动的转换成对应数据库的sql语句。

ORM的优势：不用直接编写sql代码，只需像操作对象一样从数据库操作数据。

图片.png

二、sqlalchemy连接mysql

在tornado中，要使用python中最常用的ORM—sqlalchemy，必须进行连接配置，步骤如下：

1.安装mysql、pymysql、sqlalchemy

2.导入模块
from sqlalchemy import create_engine

3.数据库配置项

HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'mydb'
USERNAME = 'admin'
PASSWORD = 'Root110qwe'

4.数据连接url

db_url = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)

5.创建连接，连接数据库

engine = create_engine(db_url)

6.测试连接

if __name__ == '__main__'：
        connection = engine.connect()
        result = connection.execute('select 1')
        print(result.fetchone())

三、ORM创建Module

刚才已经使用sqlalchemy连接上 了数据库mysql，那么，现在在Tornado中该如何使用sqlalchemy在数据库路面建表呢？具体步骤如下：
1.创建Module的Base类

from sqlalchemy.ext.declarative import declarative_base
#这个基类的作用：维系类和数据表的关系。
Base = delarative_base(engine)

2.创建Module
通过sqlalchemy提供的语法来声明表：

__ tablename __： 数据库中的表名
Column： 用来创建表中的字段的一个方法
Integer： 整形，映射到数据库中的int类型
String： 字符类型，映射到数据库中的varchar类型，使用时，需要提供一个字符长度
DateTime： 时间类型

具体案例如下：

from datetime import datetime
from sqlalchemy import Column,String,Datetime,Boolean,
from connect import Base

class User(Base):
    __tablename__ = ‘user’   #__tablename__是固定的
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(20),nullable=False)
    password = Column(String(50))
    creatime = Column(DateTime,default=datetime.now)
    _locked  = Column(Boolean,default=False,nullable=False)

#使用sqlalchemy时，我们用类来表示数据库中的表，而这些类都必须继承于Base基类。

if __name__ == "__main__"：
          #将Module映射到数据库中
          Base.metadata.create_all()

四、sqlalchemy数据操作—增删改查

连接好数据库，创建了module后，就该往表中进行数据更改了，那么，该如何操作呢？

在对表数据进行增删改查之前，必须建立会话，只有建立会话后才能进行数据操作，就类似于文件要打开之后才能对文件内容进行操作。

1.创建会话

from sqlalchemy.orm import sessionmaker

DBSession = sessionmaker(engine)   #获取一个会话类
session = DBSession()  #实例化一个会话

2.add

#add是添加一条数据
person = User(username='one',password='111')
session.add(person)
session.commit()

#add_all是添加多条数据
session.add_all([
        User(username='two',password='222'),
User(username='three',password='333')
])
session.commit()

3.query
query是查询的意思，在sqlalchemy中用来查询数据

#all：查询所有的意思
rows = session.query(User).all()
#first：是查询第一条数据
rows = session.query(User).first()

4.update

#update用来更改数据，参数以字典形式传入
rows = session.query(User).filter(User.username == 'one').update({User.password:1})
session.commit()

5.delete

#使用delete方法来删除数据
rows = session.query(User).filter(User.username == 'two')[0]
session.delete(rows)
session.commit()

五、深入了解查询（query）操作

1.查询结果

从上述第四大点，我们使用query从数据库中查询到了结果，但是，query返回的对象是直接可用的吗？它是什么数据类型？

接下来，我们将从下列的代码中得到答案。

from connect import session
from user_modules import User

#根据返回结果来看，rs是一个query对象，打印出来的是转化的sql语句
#使用sqlalchemy时，若想查看最终在数据库中执行的sql，可以通过上述方式来查看。
rs = session.query(User).filter(User.username=='three')
print(rs,type(rs))

#all返回所有符合条件的数据，以列表形式返回:[User类，User类，......]
rs = session.query(User).filter(User.username =='three').all()

#first返回所有符合条件的第一条数据，以类的形式返回
rs = session.query(User).filter(User.username =='three').first()

#[0]和first类似，但是如果没有符合条件的数据就会报错，以类的形式返回
rs = session.query(User).filter(User.username =='three')[0]

#取值方式：getattr()  and  .
rs = session.query(User).filter(User.username =='three').all()
get1 = rs[0].username
get2 = getattr(rs[0],'username')

********************************************************************
#当query中只查询对象的某个属性值时，all/first/[0]返回的结果形式将发生变化。

#all：以列表形式返回：[(User.username,),(User.username,),......]
rs = session.query(User.username).filter(User.username == 'three').all()

#first:以元组形式返回
rs = session.query(User.username).fiter(User.username == 'three').first()

#[0]:和first类似，但是如果没有符合条件的数据则会报错
rs = session.query(User.username).filter(User.username == 'three')[0]

2.条件查询

在实际的工作中，查询会有很多的要求，通过不同的条件筛选出精准的数据。那再sqlalchemy中该如何进行条件查询呢？

1）过滤函数：filter 和 filter_by

#filter是一个过滤函数，过滤条件都可以书写在此函数中，不同的条件之间用逗号分隔
rs = session.query(User).filter(User.username == 'three').all()

#filter_by也是一个过滤函数，但是功能比filter要弱一些
rs = session.query(User).filter_by(username = 'three').all()

filter和filter_by的区别：
a）filter中需要添加类对象，filter_by不需要；
b）filter_by中只能添加等于的条件（类似函数的关键字参数），不能添加不等于、大于小于等条件，而filter则没有这个限制；

2）模糊查询：like 和 notlike

rs = session.query(User.id).filter(User.username.like('th%')).all()
rs = session.query(User.id).filter(User.username.notlike('th%')).all()

3）范围查询：in_ 和 notin_

rs = session.query(User.id).filter(User.username.in_(['one','three'])).all()

rs = session.query(User.id).filter(User.username.notin_(['one','three'])).all()

4）精准查询：is_ 和 isnot

#is_ 和 isnot 多用于判断是否为空
rs = session.query(User.id).filter(User.username.is_(None)).all()

rs = session.query(User.id).filter(User.username.isnot(None)).all()

#判断为空还可以使用：
rs = session.query(User.id).filter(User.username == None).all()

5）查询结果数

#all():查看所有数据
rs = session.query(User.username).filter(User.username != 'one').all()

#limit(num):限制查看数量
rs = session.query(User).filter(User.username != 'one').limit(2).all()

#offset(num):偏移
rs = session.query(User.username).filter(User.username != 'one').offset(1).all()

#slice(num,num+n):切片（左闭右开）
rs = session.query(User.username).filter(User.username != 'one').slice(2,4).all()

#one():查询一条数据，若存在多条则报错
rs = session.query(User.username).filter(User.username == 'three').one()

6）排序

from sqlalchemy import desc

#order_by():对查询出来的结果进行排序，默认顺序
rs = session.query(User.username).filter(User.username != 'one').order_by(User.id).all()

#desc():降序
rs = session.query(User.username).filter(User.username != 'one').order_by(desc(User.id)).all()

#order_by()和limit()
rs = session.query(User.username).filter(User.username != 'three').order_by(User.username).limit(3).all()

7）函数

from sqlalchemy import func,extract

#func.count()
rs = session.query(User.password,func.count(User.id)).group_by(User.password).all()

#func.count(),加上筛选条件
rs = session.query(User.password,func.count(User.id)).group_by(User.password).having(func.count(User.id)>1).all()

#func.sum()
rs = session.query(User.password,func.sum(User.id)).group_by(User.password).all()

#func.max()
rs = session.query(User.password,func.max(User.id)).group_by(User.password).all()

#func.min()
rs = session.query(User.password,func.min(User.id)).group_by(User.password).all()

#extract():提取对象中的数据
#label():命名
rs = session.query(extract('minutes',User.creatime).label('minutes'),func.count(User.id)).group_by('minutes').all()

rs = session.query(extract('day',User.creatime).label('day'),func.count(User.id)).group_by('day).all()

8）选择条件 or_

from sqlalchemy import or_

#or_():或者
rs = session.query(User.username).filter(or_(User.username.isnot(None),User.password == '111')).all()

3.多表查询

在上述以演示了查询中常用的方法，但是都是一张表去查询，而实际工作中经常会要多个表来查询，那么这种情况该怎么做呢？

首先，我们再建一个Module(UserDetail):

from sqlalchemy import ForeginKey

class UserDetails(Base):
          __tablename__='user_details'
          id = Column(Integer, primary_key=True, autoincrement=True)
          id_card = Column(Integer,nullable=True,unique=True)
          lost_login = Column(DateTime)
          login_num = Column(Integer,default=0)
          user_id = Column(Integer,ForeignKey('user.id'))
          def __repr__(self):
                    return '<UserDetails(id=%s,id_card=%s,last_login=%s,login_num=%s,user_id=%s)>'%(self.id,self.id_card,self.lost_login,self.login_num,self.user_id)

1）cross join

from user_modules import UserDetails

#cross join
#session.query(UserDetails,User),通过打印出来的sql来看，这种是cross join
rs = session.query(UserDetails,User).filter(UserDetail.id == User.id).all()

2）inner join

#inner join
#session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id == User.id),通过打印出来的sql来看，这种是inner join
rs = session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id == User.id).all()

3）left join

#left join
#session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id == User.id),从打印出来的sql来看，这种是left join
rs = session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id == User.id).all()

4）联合查询：union()、union_all()

q1 = session.query(User.id)
q2 = session.query(UserDetails.id)
q1.union(q2).all()

4.子表查询

#声明子表：subquery()
sql_0 = session.query(UserDetails.lost_login).subquery()
#使用子表,注意使用的时候必须加.c.
rs = session.query(User,sql_0.c.lost_login).all()

5.原生sql查询

sqlalchemy虽然可以不用担心sql问题，但是再有些情况下难免看上去比较麻烦，这个时候使用原生sql会更加方便，那么原生sql如何实现呢？

实现方法如下：

#sql语句,注意再sql中，字段或者表名要加引号，只能使用反引号！！！
sql_1 = " select * from `user` "
#查询
row = session.execute(sql_1)

#fetchone():取值,取一条数据(元组形式：(字段1，字段2，....))
row.fetchone()
#fetchmany():取值,取一条数据(列表形式:[(字段1，字段2，....)])
row.fetchmany()
#fetchall():取值,取所有数据(列表形式：[(字段1，字段2，...),(字段1，字段2,......),...])
row.fetchall()

#循环取值
for i in row:
      print(i)   #打印一条数据

六、sqlalchemy—表关系

1.什么是表关系，什么是relationship

表关系是逻辑上的关系。
在mysql层面并没有直接说明表关系的东西，只有外键可以用来表示表之间的约束关系。
而在sqlalchemy层面则可以使用relationship来声明表关系。relationship默认是一对多关系，当然，我们是可以通过参数改变关系的。
在这里，我们必须明白，relationship声明的关系只是sqlalchemy层面的，和数据库没有半毛钱关系。虽然relationship是和外键一起使用的。

2.relationship参数分解

relationship('User',backref='details',uselist=False,cascade='all')

User : 关联的Module
backref ：在对饮Module中添加属性
uselist ：默认True（一对多关系），如果是一对一关系，则需要赋值为False
cascade ：自动关系处理，和mysql中的on delete类似

cascade所有的可选字符串项：
all ：所有操作都会自动处理到关联对象上；
save-update ：关联对象自动添加到会话；
delete ：关联对象自动从会话中删除
delete-orphan ：属性中去掉关联对象，则会话中会自动删除关联对象；
merge ：session.merge()时会处理关联对象
refresh-expire ：session.expire()时会处理关联对象；
expunge ：session.expunge()时会处理关联对象

3.一对一表关系

要搞明白表关系，必须先创建好对应的Module，这里就用之前建立好的User和UserDetails

from datetime import datetime
from sqlalchemy import Column,String,Datetime,Boolean,
from connect import Base
from sqlalchemy import relationship
from sqlalchemy import ForeginKey

class User(Base):
        __tablename__ = ‘user’ 
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(20),nullable=False)
    password = Column(String(50))
    creatime = Column(DateTime,default=datetime.now)
    _locked  = Column(Boolean,default=False,nullable=False)

#再UserDetails中添加relationship，表明表关系
class UserDetails(Base):
          __tablename__='user_details'
          id = Column(Integer, primary_key=True, autoincrement=True)
          id_card = Column(Integer,nullable=True,unique=True)
          lost_login = Column(DateTime)
          login_num = Column(Integer,default=0)
          user_id = Column(Integer,ForeignKey('user.id'))
          userdetail = relationship('User',backref='details'userlist=False,cascade='all')
          def __repr__(self):
                    return '<UserDetails(id=%s,id_card=%s,last_login=%s,login_num=%s,user_id=%s)>'%(self.id,self.id_card,self.lost_login,self.login_num,self.user_id)


#使用方法：
rows = session.query(User).get(1)
result = rows.details  #反向查询

在上列代码中，User类里并没有details这个属性，为什么使用时可以User可以采用属性调用的方式调用你details？

原因如下：
User里面本来没有details这个属性，但是在UserDetails里面添加relationship后，User实例就会自动加上details属性。

至此，这里还有一个问题点必须弄清楚，那就是什么是反向查询？正向查询呢？

要分辨正向查询和反向查询，主要看ForeignKey在哪个Module上

图片.png

4.一对多表关系

relationship默认是一对多关系，即uselist = True

5.多对多表关系

用户和文章之间，可以是一对多关系，但是如果用户转载的话，就可以看成是多对多关系，那么，多对多关系在sqlalchemy中该怎么表示呢？

创建中间表user_article和文章表article

from sqlalchemy import Table

#中间表
user_article = Table('user_article',Base.metadata,
          Column('user_id',integer,ForeignKey('user.id'),primary_key=True),
          Column('article_id',integer,ForeignKey('article.id'),primary_key=True)
)

#article表
class Article(Base):
        __ table __ = 'article'
        id = Column(integer,primary_key=True,autoincrement=True)
        content = Column(String(500),nullable=True)
        create_time = Column(Datetime,default=datetime.now)
        article_user = relationship('User',backref='article',secondary=user_article)

        def __repr__(self):
                return 'Article(id=%s, content=%s, creat_time=%s)' % (self.id,self.content,self.create_time)