sqlalchemy
ORM解决的问题
当你的应用程序需要跟数据库大量交互时，如果你直接把sql语句写到代码中，
1.会出现大量的重复代码。（可以把重复的sql语句写成一个函数)
2.当你的后端数据库改变后，你的代码需要重写。
测试:
MySQL数据库
pymysql模块
创建一个test库
一、单表操作
1.创建表
#!/usr/bin/env python
#-*- coding:utf-8 -*-
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base() #生成一个SqlORM 基类
engine = create_engine("mysql+pymysql://root:123.com@localhost:3306/test",echo=True) #echo = True 会打印执行过程
class Host(Base): #创建一个类,继承base
__tablename__ = 'hosts'
id = Column(Integer,primary_key=True,autoincrement=True) #主键primary_key默认就是True,自增id
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22) #默认是22
Base.metadata.create_all(engine)
打印出的sql语句
CREATE TABLE hosts (
id INTEGER NOT NULL AUTO_INCREMENT,
hostname VARCHAR(64) NOT NULL,
ip_addr VARCHAR(128) NOT NULL,
port INTEGER,
PRIMARY KEY (id),
UNIQUE (hostname),
UNIQUE (ip_addr)
)
2.增加数据
#!/usr/bin/env python
#-*- coding:utf-8 -*-
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base() #生成一个SqlORM 基类
engine = create_engine("mysql+pymysql://root:123.com@localhost:3306/test",echo=True) #echo = True 会打印执行过程
class Host(Base): #创建一个类,继承base
__tablename__ = 'hosts'
id = Column(Integer,primary_key=True,autoincrement=True) #主键primary_key默认就是True,自增id
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22) #默认是22
# Base.metadata.create_all(engine)
if __name__ == '__main__':
#创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
#sessionmaker自己是一个类，返回的是一个类.(__call__方法返回)
SessionCls = sessionmaker(bind=engine)
#所以你想生成你的session还需要session = SessionCls()
session = SessionCls()
#写数据信息
h1 = Host(hostname='localhost',ip_addr='127.0.0.1')
h2 = Host(hostname='ubuntu',ip_addr='192.168.2.243',port=20000)
h3 = Host(hostname='ubuntu2',ip_addr='192.168.2.244',port=20000)
#添加数据
#第一种方法
# session.add(h1)
# session.add(h2)
# session.add(h3)
#第二种方法
session.add_all([h1,h2,h3])
#只要没提交,此时修改也没问题
# h2.hostname = 'ubuntu_test'
# session.rollback()
session.commit() #提交
INSERT INTO hosts (hostname, ip_addr, port) VALUES (%(hostname)s, %(ip_addr)s, %(port)s)
{'ip_addr': '127.0.0.1', 'hostname': 'localhost', 'port': 22}
3.查询数据
res = session.query(Host).filter(Host.hostname.in_(['ubuntu2','localhost'])).all()
for i in res:
print(i.hostname,i.ip_addr)
二、一对多
******
一对多
******
一个组可以包含多个主机,一个主机只能属于一个组
A ------ G1,admin,permission
B ------ G1,admin,permission
C ------ G2,admin,permission
C ------ G1,admin,permission
缺点：
有大部分重复字段，浪费空间
在修改时，可能会出现错误
主机表一个字段连接组表的一个字段
主机表 组表
A 1 ----- 1 G1 admin permssion
B 1 ----- 2 G1 admin permssion
C 2 ----- 3 G2 admin permssion
C 1 ----- 4 G1 admin permssion
查询
通过表一可以查询表二,通过表二可以查询表一
当数据库表中添加新的字段,sqlalchemy无法操作。所以当你添加字段有两个方法.
1.把表删了，重新创建
2.原生语句添加
3.sqlalchemy有个开源工具可以实现
这么添加host表中的group_id是空值，没有关联上
g1 = Group(name='g1')
g2 = Group(name='g2')
g3 = Group(name='g3')
session.add_all([g1,g2,g3])
h1 = Host(hostname='localhost',ip_addr='127.0.0.1',group_id = g3.id)
session.add(h1)
session.commit()
#update 参数是一个字典
h1 = session.query(Host).filter(Host.hostname == 'localhost').update({'group_id':g4.id})
查询：
通过host表获取组的名字,或者通过group表获取host主机.都需要用到 relationship
group = relationship("Group") #相当于反射，把Group类反射到Host.group字段
2.查看组里多少台主机
g3 = session.query(Group).filter(Group.name=='g3').first()
obj = g3.host_list
#1
print(obj[0].hostname)
#2
for i in obj:
print(i.hostname)
join 查询
1.查询两个表的交集
select * from hosts join test.group on hosts.group_id = test.group.id;
2.right outer join 右边全列出来，左边如果没有就是null
3.left join 左边全列出来，右边没有就是null
4.full outer join 全显示出来
分组聚合:查看每个组里有几台机器
原生sql版本
select Count(*),test.group.name from hosts inner join test.group on hosts.group_id = test.group.id group by test.group.name;
orm版本
需要func模块，包含count方法
#查询host组,以组名为条件,查询每个组名下的主机个数
obj = session.query(Host,func.count(Group.name)).join(Host.group).group_by(Group.name).all()
一、创建组表
二、创建主机表
三、创建关联
#!/usr/bin/env python
#-*- coding:utf-8 -*-
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base() #生成一个SqlORM 基类
engine = create_engine("mysql+pymysql://root:123.com@localhost:3306/test",echo=True) #echo = True 会打印执行过程
class Host(Base): #创建一个类,继承base
__tablename__ = 'hosts'
id = Column(Integer,primary_key=True,autoincrement=True) #主键primary_key默认就是True,自增id
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22) #默认是22
group_id = Column(Integer,ForeignKey('group.id')) # 第一种
group = relationship("Group",backref='host_list') #backref是group查询host的字段
class Group(Base):
__tablename__ = 'group'
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(64),unique=True,nullable=False)
# Base.metadata.create_all(engine)
if __name__ == '__main__':
SessionCls = sessionmaker(bind=engine)
session = SessionCls()
#添加组信息
# g1 = Group(name='g1')
# g2 = Group(name='g2')
# g3 = Group(name='g3')
# session.add_all([g1,g2,g3])
#查询组名
# g2 = session.query(Group).filter(Group.name == 'g2').first()
# g3 = session.query(Group).filter(Group.name == 'g3').first()
# print(g2.id)
#添加主机信息
# h1 = Host(hostname='localhost',ip_addr='127.0.0.1',group_id = g3.id)
# h2 = Host(hostname='ubuntu',ip_addr='192.168.2.243',port=20000,group_id = g3.id)
# h3 = Host(hostname='ubuntu2',ip_addr='192.168.2.244',port=20000,group_id = g2.id)
#查询主机信息
# h1 = session.query(Host).filter(Host.hostname == 'localhost').update({'group_id':g2.id})
# session.add_all([h1,h2,h3])
# ret = session.query(Host).filter(Host.hostname == 'ubuntu2').delete()
# session.commit()
#查询一个组里面有多少个主机
# g3 = session.query(Group).filter(Group.name == 'g2').first()
# obj = g3.host_list
# for i in obj:
# print(i.hostname +'-----' + i.ip_addr)
obj = session.query(Host,func.count(Group.name)).join(Host.group).group_by(Group.name).all()
print(obj)
#删除一条数据
# ret = session.query(Host).filter(Host.hostname == 'ubuntu2').delete()
#更新一个字段
#h1 = session.query(Host).filter(Host.hostname == 'localhost').update({'group_id':g2.id})
三、多对多
多对多
一个主机可以属于多个组，一个组里可以包含多个主机
------------
h1 g1 ip port #当我改这条数据组的信息时，如果不改第二条，那么h1的数据就是有问题的，这样不能做到多对多。
h1 g2 ip port
h2 g1 ip port
建一个第三张表
host hosttogroup group
=====================================================
id 主机 id host_id group_id id 组名
=====================================================
1 h1 1 1 1 1 g1
2 h2 2 1 2 2 g2
3 2 1
两种方式创建表
1.通过类创建,需要实例化
Host2Group = Table('host_2_group',Base.metadata,
Column('host_id',ForeignKey('host.id'),primary_key=True),
Column('group_id',ForeignKey('group.id'),primary_key=True),
)
2.table方式(中间状态),返回的是一个对象，不需要实例化
class Group(Base):
__tablename__ = 'group'
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(64),unique=True,nullable=False)
关联参数
class Host(Base):
__tablename__ = 'host'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22)
# group_id = Column(Integer,ForeignKey('group.id'))
group = relationship("Group",
secondary=Host2Group, #secondary=第三章表名
backref='host_list') #反向关联，实现相互查询
table表要设置两个主键，因为两个列成为一个主键，非空且唯一。
id host_id group_id
1 null 1
2 1 1
3 1 1
关联:
groups = session.query(Group).all()
h1 = session.query(Host).filter(Host.hostname == 'h1').first()
h1.groups = groups
一、建一个中间表,关联其它两张表
二、指定中间表的实例 #secondary=Host2Group
三、先创建三张表
四、创建组数据
五、创建主机数据
#!/usr/bin/env python
#-*- coding:utf-8 -*-
from sqlalchemy import create_engine,func,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base()
#返回的直接是表的对象
#中间表,关联其它两个表id，并设置为主键.
Host2Group = Table('host_2_group',Base.metadata,
Column('host_id',ForeignKey('host.id'),primary_key=True),
Column('group_id',ForeignKey('group.id'),primary_key=True),
)
engine = create_engine("mysql+pymysql://root:123.com@localhost:3306/tests",echo=True)
class Host(Base):
__tablename__ = 'host'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22)
# group_id = Column(Integer,ForeignKey('group.id'))
group = relationship("Group",
secondary=Host2Group,
backref='host_list') #反向关联，实现相互查询
#返回字符串
def __repr__(self):
return "<id=%s,hostname=%s,ip_addr=%s>" % (self.id,
self.hostname,
self.ip_addr,)
class Group(Base):
__tablename__ = 'group'
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(64),unique=True,nullable=False)
def __repr__(self):
return "<id=%s,name=%s>" % (self.id,
self.name,)
# Base.metadata.create_all(engine) #创建所有表结构
if __name__ == '__main__':
SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = SessionCls()
#创建组数据
# g1 = Group(name='g1')
# g2 = Group(name = 'g2')
# g3 = Group(name = 'g3')
# session.add_all([g1,g2,g3])
#创建主机数据
groups = session.query(Group).all()
# h1 = Host(hostname='h1',ip_addr='192.168.0.1')
# h1.group = groups
h3 = Host(hostname='ubuntu3',ip_addr='192.168.2.222',port=20000)
# h2 = session.query(Host).filter(Host.hostname == 'h2').first()
h3.group = groups[0:2]
# h3 = Host(hostname='ubuntu2',ip_addr='192.168.2.244',port=20000)
# session.add_all([h1,h2])
session.commit()
浙公网安备 33010602011771号