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的类方法。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.操作表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#导入相关模块 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()
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#导入相关模块 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() 增加多个记录
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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()