SQLAlchemy-ORM框架

一、ORM 框架简介
　　对象-关系映射（Object/Relation Mapping，简称ORM），是随着面向对象的软件开发方法发展而产生的。面向对象的开发方法是当今企业级应用开发环境中的主流开发方法，
关系数据库是企业级应用环境中永久存放数据的主流数据存储系统。对象和关系数据是业务实体的两种表现形式，业务实体在内存中表现为对象，在数据库中表现为关系数据。
内存中的对象之间存在关联和继承关系，而在数据库中，关系数据无法直接表达多对多关联和继承关系。因此，对象-关系映射(ORM)系统一般以中间件的形式存在，
主要实现程序对象到关系数据库数据的映射。简单的说：ORM相当于中继数据，操作数据、

1.ORM方法论基于三个核心原则：

简单性：以最基本的形式建模数据。
传达性：数据库结构被任何人都能理解的语言文档化。
精确性：基于数据模型创建正确标准化了的结构。

　　面向对象是从软件工程基本原则(如耦合、聚合、封装)的基础上发展起来的，而关系数据库则是从数学理论发展而来的，两套理论存在显著的区别。为了解决这个不匹配的现象,
对象关系映射技术应运而生。O/R中字母O起源于"对象"(Object),而R则来自于"关系"(Relational)。几乎所有的程序里面，都存在对象和关系数据库。在业务逻辑层和用户界面层中，
我们是面向对象的。当对象信息发生变化的时候，我们需要把对象的信息保存在关系数据库中。
当开发一个应用程序的时候(不使用O/R Mapping),可能会写不少数据访问层的代码，用来从数据库保存，删除，读取对象信息，等等。在DAL中写了很多的方法来读取对象数据，
改变状态对象等等任务。而这些代码写起来总是重复的。如果开你最近的程序，看看DAL代码，肯定会看到很多近似的通用的模式。我们以保存对象的方法为例，传入一个对象，
为SqlCommand对象添加SqlParameter，把所有属性和对象对应，设置SqlCommand的CommandText属性为存储过程，然后运行SqlCommand。对于每个对象都要重复的写这些代码。　　
除此之外，还有更好的办法吗？有，引入一个O/R Mapping。实质上，一个O/R Mapping会为你生成DAL。与其自己写DAL代码，不如用O/R Mapping。用O/R Mapping保存，删除，读取对象，
O/R Mapping负责生成SQL，你只需要关心对象就好。对象关系映射成功运用在不同的面向对象持久层产品中，

2. 一般的ORM包括以下四部分：

一个对持久类对象进行CRUD操作的API；
一个语言或API用来规定与类和类属性相关的查询；
一个规定mapping metadata的工具；
一种技术可以让ORM的实现同事务对象一起进行dirty checking, lazy association fetching以及其他的优化操作。

•ORM：及Object-Relational Mapping，把关系数据库的表结构映射到对象上


二、SQLAlchemy介绍
　　SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

1.安装SQLAlchemy（）

pip install SQLAlchemy

　

2.各种数据库使用SQLALchemy方法
　　SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作。

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

　

3.初始化连接

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR ,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/hjx?charset=utf8", max_overflow=5,echo=True)

参数说明：
max_overflow 设置最大连接数
echo参数为True时，会显示每条执行的SQL语句，可以关闭，
"mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8"  设定数据库地址、用户名、密码、端口、数据库名称和字符编码
create_engine()返回一个Engine的实例，并且它表示通过数据库语法处理细节的核心接口，在这种情况下，数据库语法将会被解释成python的类方法。

　

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/hjx?charset=utf8", max_overflow=5,echo=True)

#执行SQL---增
cur = engine.execute(
    "INSERT INTO equipment (equ_id, equ_sn,supplier,cname,location,ipadd) VALUES ('CQ66','CQMT206','重庆银河','12m³环境箱2','一楼','10.127.1.123')"
    )

#新插入行自增ID
cur.lastrowid

#插入多条记录
cur = engine.execute(
    "INSERT INTO host (ip, name) VALUES(%s, %s)",[('1.1.1.22', 'sever1'),('1.1.1.221', 'server2'),]
    )


执行SQL
cur = engine.execute(
    "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
    host='1.1.1.99', color_id=3
    )

#执行SQL---查
cur = engine.execute('select * from hosts')

#获取第一行数据
cur.fetchone()
#获取第n行数据
cur.fetchmany(3)
#获取所有数据
cur.fetchall()

 

三、ORM功能使用
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

1.创建表
a.通过sql语句来创建表：

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/hjx?charset=utf8", max_overflow=5,echo=True)

sql = "create table student(id int not null primary key,name varchar(50),age int,address varchar(100));"

cursor = engine.execute(sql)

　　

b.通过ORM创建表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )


# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))


# 多对多
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)


class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))


def init_db():
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

init_db()   #创建表
drop_db()   #删除表

　

2.操作表

#导入相关模块
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR ,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/test?charset=utf8", max_overflow=5)
Base = declarative_base()

#创建数据表 type
class Type(Base):
    __tablename__ = 'type'
    tid = Column(Integer,primary_key=True,autoincrement=True)
    tname = Column(VARCHAR(25),nullable=False,unique=True)

#创建数据表 host
class Host(Base):
    __tablename__ = 'host'
    hid = Column(Integer,primary_key=True)
    hostname = Column(String(25),nullable=None)
    ipadd = Column(String(32),unique=True)
    type_id = Column(Integer,ForeignKey('type.tid'))

    # 增加relationship不会增加到数据表里，只是在ORM里表示一种关系，参数backref表示反向查询
    host_type = relationship('Type',backref = 'fff')

    __table_args__ = (
        Index('Idx_hostname','hostname'),    #创建普通索引，索引名称必须写在前面
    )

#创建数据表 user
class User(Base):
    __tablename__ = 'user'
    uid = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(VARCHAR(25),nullable=False)
    host_id = Column(Integer,ForeignKey('host.hid'))

    __table_args__ = (
        UniqueConstraint('uname','host_id',name='uix_uname_host_id'),   #创建联合唯一索引
    )


Base.metadata.create_all(engine)   #创建数据表
Base.metadata.drop_all(engine)    #删除数据表

#创建session会话
说到数据库，就离不开Session。Session的主要目的是建立与数据库的会话，它维护你加载和关联的所有数据库对象。它是数据库查询（Query）的一个入口。
在Sqlalchemy中，数据库的查询操作是通过Query对象来实现的。而Session提供了创建Query对象的接口。
Query对象返回的结果是一组同一映射（Identity Map）对象组成的集合。事实上，集合中的一个对象，对应于数据库表中的一行（即一条记录）。所谓同一映射，
是指每个对象有一个唯一的ID。如果两个对象（的引用）ID相同，则认为它们对应的是相同的对象。
要完成数据库查询，就需要建立与数据库的连接。这就需要用到Engine对象。一个Engine可能是关联一个Session对象，也可能关联一个数据库表。
当然Session最重要的功能还是实现原子操作。

DBsession = sessionmaker(bind=engine)
session = DBsession()


#插入数据
type1 = Type(tname='HP')
type2 = Type(tname='IBM')
type3 = Type(tname='Dell')
type4 = Type(tname='联想')

type_lst = [type1,type2,type3,type4]
for i in type_lst:
    session.add(i)
session.commit()

host1 = Host(hostname='linux1',ipadd='10.127.55.1',type_id=1)
host2 = Host(hostname='linux2',ipadd='10.127.55.2',type_id=2)
host3 = Host(hostname='webserver',ipadd='10.127.55.3',type_id=3)
host4 = Host(hostname='mysqlserver',ipadd='10.127.55.4',type_id=2)
host5 = Host(hostname='windows server 2008',ipadd='10.127.55.5',type_id=4)

host_lst = [host1,host2,host3,host4,host5]
ret = session.add_all(host_lst)
session.commit()

User1 = User(uname='felix',host_id=1)
User2 = User(uname='allan',host_id=2)
User3 = User(uname='skye',host_id=3)
User4 = User(uname='luke',host_id=4)
User5 = User(uname='afred',host_id=5)

user_lst = [User1,User2,User3,User4,User5]
for i in user_lst:
    session.add(i)
session.commit()

 

#导入相关模块
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR ,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/test?charset=utf8", max_overflow=5)
Base = declarative_base()

#创建数据表 type
class Type(Base):
    __tablename__ = 'type'
    tid = Column(Integer,primary_key=True,autoincrement=True)
    tname = Column(VARCHAR(25),nullable=False,unique=True)

#创建数据表 host
class Host(Base):
    __tablename__ = 'host'
    hid = Column(Integer,primary_key=True)
    hostname = Column(String(25),nullable=None)
    ipadd = Column(String(32),unique=True)
    type_id = Column(Integer,ForeignKey('type.tid'))

    # 增加relationship不会增加到数据表里，只是在ORM里表示一种关系，参数backref表示反向查询
    host_type = relationship('Type',backref = 'fff')

    __table_args__ = (
        Index('Idx_hostname','hostname'),    #创建普通索引，索引名称必须写在前面
    )

#创建数据表 user
class User(Base):
    __tablename__ = 'user'
    uid = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(VARCHAR(25),nullable=False)
    host_id = Column(Integer,ForeignKey('host.hid'))

    __table_args__ = (
        UniqueConstraint('uname','host_id',name='uix_uname_host_id'),   #创建联合唯一索引
    )


Base.metadata.create_all(engine)   #创建数据表
Base.metadata.drop_all(engine)    #删除数据表

#创建session会话
说到数据库，就离不开Session。Session的主要目的是建立与数据库的会话，它维护你加载和关联的所有数据库对象。它是数据库查询（Query）的一个入口。
在Sqlalchemy中，数据库的查询操作是通过Query对象来实现的。而Session提供了创建Query对象的接口。
Query对象返回的结果是一组同一映射（Identity Map）对象组成的集合。事实上，集合中的一个对象，对应于数据库表中的一行（即一条记录）。所谓同一映射，
是指每个对象有一个唯一的ID。如果两个对象（的引用）ID相同，则认为它们对应的是相同的对象。
要完成数据库查询，就需要建立与数据库的连接。这就需要用到Engine对象。一个Engine可能是关联一个Session对象，也可能关联一个数据库表。
当然Session最重要的功能还是实现原子操作。

DBsession = sessionmaker(bind=engine)
session = DBsession()

 

(1)增

session.add()    增加单条记录
session.add_all()    增加多个记录

　　

type1 = Type(tname='HP')
type2 = Type(tname='IBM')
type3 = Type(tname='Dell')
type4 = Type(tname='联想')

type_lst = [type1,type2,type3,type4]
for i in type_lst:
    session.add(i)
session.commit()

host1 = Host(hostname='linux1',ipadd='10.127.55.1',type_id=1)
host2 = Host(hostname='linux2',ipadd='10.127.55.2',type_id=2)
host3 = Host(hostname='webserver',ipadd='10.127.55.3',type_id=3)
host4 = Host(hostname='mysqlserver',ipadd='10.127.55.4',type_id=2)
host5 = Host(hostname='windows server 2008',ipadd='10.127.55.5',type_id=4)

host_lst = [host1,host2,host3,host4,host5]
ret = session.add_all(host_lst)
session.commit()

User1 = User(uname='felix',host_id=1)
User2 = User(uname='allan',host_id=2)
User3 = User(uname='skye',host_id=3)
User4 = User(uname='luke',host_id=4)
User5 = User(uname='afred',host_id=5)

user_lst = [User1,User2,User3,User4,User5]
for i in user_lst:
    session.add(i)
session.commit()

 

(2)删

ret = session.query(Host).filter(Host.hid == 2 ).delete()
print(ret)
session.commit()
执行结果
1

　　

(3)改

session.query(Host).filter(Host.hid > 4 ).update({'hostname':'TestServer'})
session.commit()

session.query(Host).filter(Host.hid > 4).update({Host.hostname:Host.hostname + '001'}, synchronize_session=False)
session.commit()

session.query(Host).filter(Host.hid == 4).update( {'hostname': Host.hostname + '002'}, synchronize_session="fetch")
# #指定synchronize_session=False也可以
session.commit()

ret = session.query(Host).filter(Host.hid > 4).all()
print(ret[0].hid,ret[0].hostname,ret[0].type_id)
ret[0].hostname = 'TestServer'
session.commit()

 

(4)查

• one()　返回且仅返回一个查询结果。当结果的数量不足一个或者多于一个时会报错。
• first()　返回至多一个结果，而且以单项形式，而不是只有一个元素的tuple形式返回这个结果.
• all()　 返回多个结果，组成一个列表

a.查询表里的所有数据，不带任何条件查询

#查询所有结果
ret = session.query(Host).all()
for i in ret:
    print(i.hid,i.hostname,i.type_id)

执行结果
1 linux1 1
2 linux2 2
3 webserver 3
4 mysqlserver 2
5 windows server 2008 4

注意：如果想知道SQLAlchemy生成的SQL语句则

ret = session.query(Host)
print(ret)
执行结果：

SELECT host.hid AS host_hid, host.hostname AS host_hostname, host.ipadd AS host_ipadd, host.type_id AS host_type_id FROM host

　　

b.带条件查询 

• filter()　可以像写 sql 的 where 条件那样写 > < 等条件，但引用列名时，需要通过 类名.属性名 的方式。
• filter_by()　可以使用 python 的正常参数传递方法传递条件，指定列名时，不需要额外指定类名。参数名对应名类中的属性名，但不能使用 > < 等条件。

　　当使用filter的时候条件之间是使用“=="，fitler_by使用的是"="。
　　filter不支持组合查询，只能连续调用filter来变相实现。而filter_by的参数是**kwargs，直接支持组合查询。

 

# 带条件查询
ret = session.query(Host).filter(Host.hid > 3).all()
for i in ret:
    print(i.hid,i.hostname,i.type_id)

执行结果
4 mysqlserver 2
5 windows server 2008 4

# 带条件查询某一列 filter
ret = session.query(Host.hostname).filter(Host.hid == 5).all()
for i in ret:
    print(i.hostname)
执行结果
windows server 2008

#带条件查询某一列 filter_by   all()查询所有满足条件的
ret = session.query(Host).filter_by(hid = 5).all()
for i in ret:
    print(i.hid,i.hostname,i.type_id)
执行结果
5 windows server 2008 4


#带条件查询某一列 filter_by   first()满足条件的第一个
ret = session.query(Host).filter_by(hid = 1).first()
print(ret.hid,ret.hostname,ret.type_id)
执行结果
1 linux1 1


#等于条件查询
ret = session.query(Host).filter_by(hostname='mysqlserver').all()
print(ret[0].hid,ret[0].hostname)
执行结果
4 mysqlserver

#使用原生SQL语句带条件查询
ret = session.query(Host).from_statement("SELECT * FROM host where hostname=:hostname").params(hostname='mysqlserver').all()
print(ret[0].hid,ret[0].hostname,ret[0].ipadd,ret[0].type_id)
执行结果
4 mysqlserver 10.127.55.4 2

 

c.and、or查询，SQLAlchemy默认使用and条件，如果需要使用or查询时，需要导入or_ ，也可以导入and_进行and查询。from sqlalchemy import and_, or_

 

#and查询
from sqlalchemy import and_, or_
ret = session.query(Host).filter(and_(Host.hid > 1, Host.hostname == 'webserver')).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果
[<__main__.Host object at 0x000000000AFA57B8>]
3 webserver

#or条件查询
ret = session.query(Host).filter(or_(Host.hid < 2, Host.hostname == 'webserver')).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果
[<__main__.Host object at 0x000000000AFA5128>, <__main__.Host object at 0x000000000AFA5978>]
1 linux1
3 webserver

#and or 混合查询
ret = session.query(Host).filter(
    or_(
        Host.hid < 2,
        and_(Host.hostname == 'webserver', Host.hid > 2),
        Host.hostname == "windows server 2008"
    )).all()

print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果
[<__main__.Host object at 0x000000000AFA3208>, <__main__.Host object at 0x000000000AFA3A58>, <__main__.Host object at 0x000000000AFA39E8>]
1 linux1
3 webserver
5 windows server 2008

　　

d.between...and...查询

#between...and...
ret = session.query(Host).filter(Host.hid.between(1, 3)).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
# 执行结果
1 linux1
2 linux2
3 webserver

　　

e.in和not in 查询，not in方法为 在 类名.字段.in_  前加 ~  取反（波浪线）

#in
ret = session.query(Host).filter(Host.hid.in_([1,3,4])).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果
[<__main__.Host object at 0x000000000AFA9748>, <__main__.Host object at 0x000000000AFA96D8>, <__main__.Host object at 0x000000000AFA97B8>]
1 linux1
3 webserver
4 mysqlserver

#not in
ret = session.query(Host).filter(~Host.hid.in_([1,3,4])).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)

[<__main__.Host object at 0x000000000AFB77B8>, <__main__.Host object at 0x000000000AFB7748>]
2 linux2
5 windows server 2008

#带子查询
ret = session.query(Host).filter(Host.hid.in_(session.query(Host.hid).filter(Host.hid > 2))).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果
[<__main__.Host object at 0x000000000B05A6D8>, <__main__.Host object at 0x000000000B05A2B0>, <__main__.Host object at 0x000000000B05A780>]
3 webserver
4 mysqlserver
5 windows server 2008

　　

f.通配符 like

# 通配符 like
ret = session.query(Host).filter(Host.hostname.like('w%')).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
ret = session.query(Host).filter(~Host.hostname.like('%server')).all()
print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果

[<__main__.Host object at 0x000000000AFA3128>, <__main__.Host object at 0x000000000AFA3588>]
3 webserver
5 windows server 2008
[<__main__.Host object at 0x000000000AFA37F0>, <__main__.Host object at 0x000000000AFA3860>, <__main__.Host object at 0x000000000AFA3588>]
1 linux1
2 linux2
5 windows server 2008

　　

g.限制查询的结果数目 limit

# 限制 类似limit
ret = session.query(Host)[0:3]  #左闭右开
print(ret)
for i in ret:
    print(i.hid,i.hostname)
执行结果
[<__main__.Host object at 0x000000000AFA56A0>, <__main__.Host object at 0x000000000AFA5630>, <__main__.Host object at 0x000000000AFA5710>]
1 linux1
2 linux2
3 webserver

　　

h.排序

# 排序
#单字段排序
ret = session.query(Host).order_by(Host.hostname.desc()).all()
for i in ret:
    print(i.hid,i.hostname)
执行结果
5 windows server 2008
3 webserver
4 mysqlserver
2 linux2
1 linux1

#多字段排序
ret = session.query(Host).order_by(Host.hostname.desc(), Host.hid.asc()).all()
for i in ret:
    print(i.hid,i.hostname)
执行结果
5 windows server 2008
3 webserver
4 mysqlserver
2 linux2
1 linux1

　　

i.统计 count

#统计count
ret = session.query(Host).filter(Host.type_id == 2).count()
print(ret)
执行结果
2

　　

j.分组加聚合函数，分组后having。使用聚合需要导入func模块

　　from sqlalchemy.sql import func

# 分组后count
from sqlalchemy.sql import func
ret = session.query(Host.type_id,func.count(Host.hid)).group_by(Host.type_id).all()
print(ret)
for i in ret:
    print(i)
执行结果：
[(1, 1), (2, 2), (3, 1), (4, 1)]
(1, 1)
(2, 2)
(3, 1)
(4, 1)

# 分组-sum\max\min
ret = session.query(Host.type_id,
    func.max(Host.hid),
    func.sum(Host.hid),
    func.min(Host.hid)).group_by(Host.type_id).all()
print(ret)
for i in ret:
    print(i)
执行结果
[(1, 1, Decimal('1'), 1), (2, 4, Decimal('6'), 2), (3, 5, Decimal('5'), 5), (4, 3, Decimal('3'), 3)]
(1, 1, Decimal('1'), 1)
(2, 4, Decimal('6'), 2)
(3, 5, Decimal('5'), 5)
(4, 3, Decimal('3'), 3)

#分组后 having
ret = session.query(
    Host.type_id,
    func.max(Host.hid),
    func.sum(Host.hid),
    func.min(Host.hid)).group_by(Host.type_id).having(Host.type_id > 3).all()
print(ret)
for i in ret:
    print(i)
执行结果
[(4, 3, Decimal('3'), 3)]
(4, 3, Decimal('3'), 3)

　　

k.连表查询

　不使用relationship进行连表查询

# 连表 默认使用inner join
#不使用join
ret = session.query(Host, Type).filter(Host.type_id == Type.tid).all()
for i in ret:
    print(i[0].hostname,i[0].ipadd,i[1].tid,i[1].tname)
执行结果
linux1 10.127.55.1 1 HP
linux2 10.127.55.2 2 IBM
webserver 10.127.55.3 4 联想
mysqlserver 10.127.55.4 2 IBM
windows server 2008 10.127.55.5 3 Dell

#使用join,默认方法为inner join，在join里增加isouter=True参数，则使用left join
ret = session.query(Host,Type).join(Type，isouter=True).all()
ret = session.query(Host,Type).join(Type).all()
print(ret)
for i in ret:
    print(i[0].hostname,i[0].ipadd,i[0].type_id,i[1].tname)
执行结果
linux1 10.127.55.1 1 HP
linux2 10.127.55.2 2 IBM
webserver 10.127.55.3 4 联想
mysqlserver 10.127.55.4 2 IBM
windows server 2008 10.127.55.5 2 IBM

ret = session.query(Host.hostname,Type.tname).join(Type).all()
print(ret)
for i in ret:
    print(i[0],i[1])
    print(i.hostname,i.tname)
执行结果
[('linux1', 'HP'), ('linux2', 'IBM'), ('webserver', '联想'), ('mysqlserver', 'IBM'), ('windows server 2008', 'IBM')]
linux1 HP
linux1 HP
linux2 IBM
linux2 IBM
webserver 联想
webserver 联想
mysqlserver IBM
mysqlserver IBM
windows server 2008 IBM
windows server 2008 IBM

　　

　　使用relationship参数进行连表查询，在创建表（类）且与在增加外键的下面增加 

　　字段名 = relationship('类名称',backref='关系名称')   #增加backref参数可进行反向查询。

 

#relationship 正向查询

class Host(Base):
    __tablename__ = 'host'
    hid = Column(Integer,primary_key=True)
    hostname = Column(String(25),nullable=None)
    ipadd = Column(String(32),unique=True)
    type_id = Column(Integer,ForeignKey('type.tid'))

    # 增加relationship不会增加到数据表里，只是在ORM里表示一种关系，参数backref表示反向查询
    host_type = relationship('Type',backref = 'fff')

    __table_args__ = (
        Index('Idx_hostname','hostname'),    #创建普通索引，索引名称必须写在前面
    )

host_type不会增加此字段进数据表，只是在ORM里表名两个表的关系，只能加在有外键ForeignKey的地方


#查询每台服务器的名称和类型
ret = session.query(Host).all()
for i in ret:
    print(i.hid,i.hostname,i.ipadd,i.host_type.tname)

执行结果
1 linux1 10.127.55.1 HP
2 linux2 10.127.55.2 IBM
3 webserver 10.127.55.3 Dell
4 mysqlserver 10.127.55.4 IBM
5 windows server 2008 10.127.55.5 联想


#relationship 反向查询，需要加backref='名称'参数
#查询每一种类型所有的host
#不加relationship且不加backref='fff'参数
type_lst = session.query(Type)
for i in type_lst:
    print(i.tid,i.tname,session.query(Host).filter(Host.type_id == i.tid).all())
执行结果
3 Dell [<__main__.Host object at 0x000000000AFD19B0>]
1 HP [<__main__.Host object at 0x000000000AFD1B38>]
2 IBM [<__main__.Host object at 0x000000000AFD1C50>, <__main__.Host object at 0x000000000AFD1CC0>]
4 联想 [<__main__.Host object at 0x000000000AFD1D30>]

#加relationship参数且加入 backref='fff'参数，fff为名称，即反向查询
type_lst = session.query(Type).all()
for i in type_lst:
    print(i.tid,i.tname,i.fff)

执行结果
3 Dell [<__main__.Host object at 0x000000000AFE6160>]
1 HP [<__main__.Host object at 0x000000000AFE6208>]
2 IBM [<__main__.Host object at 0x000000000AFE62B0>, <__main__.Host object at 0x000000000AFE6320>]
4 联想 [<__main__.Host object at 0x000000000AFE6390>]

#说明 每一种类型可能有一个或多个主机，因此print(i.fff)就表示主机的对象，因此可以使用两层for循环取到主机信息

type_lst = session.query(Type).all()
for i in type_lst:
    for j in i.fff:
        print(i.tid, i.tname, j.hostname)
执行结果：
3 Dell webserver
1 HP linux1
2 IBM linux2
2 IBM mysqlserver
4 联想 windows server 2008

　　

l.组合 union

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()