Part01、sqlalchemy 使用

一、ORM

        连表

              一对多

              1、创建表，主动指定外键约束。

              2、操作。

                          类：repr

                   一对多：

                          单表   

                          连表

                                session.query（表1).join（表2）.all( )

                     多对多：

                                1、创建表，额外的关系表。

                                2、filter( )  _in( )  把结果可传进去。             

                                3、relationship

                                     A

                                     A_B ==> 【关系表】 relationship 字段 和 primary_key 字段常放在这里。

                                     B          PS：通过A 找到 A_B 关系的时候，是反向找；

                                                        而通过关系找到某一个，是正向找。

                    提示：1、这个关系表不一定要放在A_B 关系表中，其他地方也能放。比如、可以放在A中。

                              2、关系表不影响具体的代码。

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

engine = create_engine("mysql+pymysql://root:123qwe@192.168.1.100:3306/s08day11", max_overflow=5)  #定义引擎。
Base = declarative_base()



# 一对多 例如:一个人只能在一个组里。
class Group(Base):
    __tablename__  = 'group'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    caption = Column(String(32))

class User(Base):
    __tablename__ = 'user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    username =Column(String(32))
    group_id = Column(Integer,ForeignKey('group.nid')) #设置外键约束

    #与生成表结构无关，仅用于查询方便
    _group_ = relationship("Favor", backref='pers')
    # （2）通过对象方式
    #这样看起来不方便，可以自己进行自定制,只是让 print 时候好看些。
    def __repr__(self):   #这里定义什么就可以返回并看到什么。因为在sqlalchemy 中使用了 __repr__()内部规定的方法。
        temp = '%s -- %s: %s' % (self.nid,self.username,self.group_id)
        return temp

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

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

# init_db()  #创建数据库

Session=sessionmaker(bind=engine)   #绑定元信息
session=Session()

####### 添加 插入 数据
session.add(Group(caption='DBA'))
session.add(Group(caption='SA'))
session.add(Group(caption='SA'))

#session.commit()

session.add_all([
        User(username='alex',group_id=1),
        User(username='eric',group_id=2),
        User(username='jack',group_id=3)
])
#session.commit()



############# 单表查询 #################

#1、只是获取 user 表的 用户信息。（1）通过对象方式
ret= session.query(User).filter(User.username == 'alex').all()
print(ret)   #ret 有多个对象
obj=ret[0]   #获取到了对象
print(obj.nid)
print(obj.username)
print(obj.group.id)

print('================================')

#2、获取所有 user 表用户信息。（2） 通过映射方式
ret=session.query(User.username).all()
print(ret)

############## 连表查询 ###############
sql=session.query(User).join(Group,isouter=True) #isouter 为True 打印出sql语句.
print(sql)

print('=================================')

ret=session.query(User).join(Group,isouter=True).all() #join 联表操作。
print(ret)

print('=================================')
#如果想把两个【表】的内容都拿到的话。
ret=session.query(User,Group).join(Group,isouter=True).all()
print(ret)

ret=session.query(User.username,Group.caption).join(Group,isouter=True).all()
print(ret)   #在列表中，有一个元祖，元祖中存放着 User表对象和 Group 表



执行结果：
通过对象的方式展示出来了，如果不想通过对象的话，
[1 -- alex: 1]
1
============================================
[('alex',), ('eric',), ('jack',)]
SELECT "user".nid AS user_nid, "user".username AS user_username, "user".group_id AS user_group_id
FROM "user" LEFT OUTER JOIN "group" ON "group".nid = "user".group_id
============================================
[1 -- alex: 1, 2 -- eric: 2, 3 -- jack: 3]
============================================
[(1 -- alex: 1, <__main__.Group object at 0x000001A19CD9A6D8>), (2 -- eric: 2, <__main__.Group object at 0x000001A19CD9A780>), (3 -- jack: 3, <__main__.Group object at 0x000001A19CD9A828>)]

[('alex', 'DBA'), ('eric', 'SA'), ('jack', 'SA')]

上述中, 通过对象的方式展示出来了，如果不想通过对象的话，
这样的方式很麻烦, 所以  与生成表结构无关，仅用于查询方便
                     group = relationship("Favor", backref='pers')

  于是使用 group=relationship('Group',backref='uuu') 就可以了。

2、使用 relationship 方式不用联表，sqlalchemy 内部帮你联表操作了。

    

【一对多】数据库数据如下：

        

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

engine = create_engine("mysql+pymysql://root:123qwe@10.10.31.99:3306/s08day11", max_overflow=5)  #定义引擎。
Base = declarative_base()

############# 正向查找 和反向查找 ################
# 一对多
class Group(Base):
    __tablename__  = 'group'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    caption = Column(String(32))

class User(Base):
    __tablename__ = 'user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    username =Column(String(32))
    group_id = Column(Integer,ForeignKey('group.nid')) #设置外键约束
    group=relationship('Group',backref='uuu')
    # 释义：这种 backref='uuu' 会在Group表中加上一个 uuu,直接 Group.uuu 表示找到跟这个组对应的所有的用户。

    def __repr__(self):
        temp = '%s -- %s: %s' % (self.nid,self.username,self.group_id)
        return temp


Session=sessionmaker(bind=engine)
session=Session()

Base.metadata.create_all(engine) #创建表

#1、新方式【正向查询】
ret = session.query(User).all()
for obj in ret:
    # obj 代表user 表的每一行数据
    # obj.group 代指 group 对象。
    print(obj.nid, obj.username,'======', obj.group_id ,obj.group_id, obj.group.caption,':=',obj.group )

print('=================','
')
# 2、【原始方式】; 查询所有DBA的信息。
ret = session.query(User.username,Group.caption).join(Group, isouter=True).filter(Group.caption == 'DBA').all()
print(ret)

print('=================','
')

# 3、新方式 【返向查询】
obj= session.query(Group).filter(Group.caption=='DBA').first()
print(obj.nid)
print(obj.caption)
print(obj.uuu)  #通过反向查找Group表中。找到了关于DBA的组的所有user成员，
                   # 并以列表返回 [1 -- alex: 1, 2 -- eric: 2, 3 -- jack: 3]
 
执行结果：
1 alex ====== 1 1 DBA := <__main__.Group object at 0x0000019AB50FA898>
2 eric ====== 2 2 SA := <__main__.Group object at 0x0000019AB50FAA58>
3 jack ====== 3 3 SA := <__main__.Group object at 0x0000019AB50FAC18>
4 alex1 ====== 1 1 DBA := <__main__.Group object at 0x0000019AB50FA898>
================= 

[('alex', 'DBA'), ('alex1', 'DBA')]
================= 

1
DBA
[1 -- alex: 1, 4 -- alex1: 1]

• 正向查找和反向查找

  一般情况下，foreignKey和relationship 是在一起的。
  当我们通过 Group 表找User表的时候，backref=uuu，没有在此表中定义，所以通过Group 查找User表的时候，为反向查找。

  如下图展示：

• 写成 Table对象也可以。
  

   过程：由 class类转换为 Table对象 ，下面例子是没有写类 直接写 Table对象也可以，一模一样的。

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()

user = Table('user', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
)

Host= Table('host', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
)

engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)
metadata.create_all(engine)

3、多对多查询

     想要操作多对多，就要有第三张表的介入。

    

     流程如图：

      

  

  数据库展示：

  

######## 多表查询 ###########
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()

class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

class HostUser(Base):
    __tablename__ = 'host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))

class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)

    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))

##### 生产 库表 #######
def init_db():
    Base.metadata.create_all(engine)

# init_db()

'''
######生成数据 #######

Session=sessionmaker(bind=engine)
session=Session()

session.add_all([
    Host(hostname='c1',port='22',ip='1.1.1.1'),
    Host(hostname='c2',port='22',ip='1.1.1.2'),
    Host(hostname='c3',port='22',ip='1.1.1.3'),
    Host(hostname='c4',port='22',ip='1.1.1.4'),
    Host(hostname='c5',port='22',ip='1.1.1.5'),
])
session.commit()


session.add_all([
    HostUser(username='root'),
    HostUser(username='db'),
    HostUser(username='nb'),
    HostUser(username='sb'),
])
session.commit()

session.add_all([
    HostToHostUser(host_id=1,host_user_id=1),
    HostToHostUser(host_id=1,host_user_id=2),
    HostToHostUser(host_id=1,host_user_id=3),
    HostToHostUser(host_id=2,host_user_id=2),
    HostToHostUser(host_id=2,host_user_id=4),
    HostToHostUser(host_id=2,host_user_id=3),
])
session.commit() #生成数据按钮
'''

Session=sessionmaker(bind=engine)
session=Session()


#>>>需求来了, 获取c1主机中所有的用户??

1、使用【原始方式】获取 。
host_obj=session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj.nid) #第一步要找的唯一的【主机的ID】。
#得到了结果 ———>ID为1的值。

2、在第三张关系表中--> 利用【主机host的ID】找到了所有的对应的【用户user的 ID】。
host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()
print(host_2_host_user)  #在关系表中拿到了C1【主机的ID】对应的 【全部用户ID】。
#执行结果：————> [(1,),(2,),(3),]的列表。

3、根据【用户user的ID】找到所有用户。
r=zip(*host_2_host_user)
# print(list(r)[0])  #目的：转换 [(1,),(2,),(3),] --->为[1,2,3]
# [1,2,3]

users=session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
print(users)

#执行结果：
#[('root',), ('db',), ('nb',)]

########################################

以上的查询方法太low了，其实可以一句话进行才查找。

• 多表查询（三）、
  

############## 多表查询【高逼格】方法 ###################
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()


class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

class HostUser(Base):
    __tablename__ = 'host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))

class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)

    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))

    host = relationship('Host',backref='h')
    host_user = relationship('HostUser',backref='u')

Session=sessionmaker(bind=engine)
session=Session()

########查询结果#######：
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj)
print(host_obj.nid)
print(host_obj.hostname)
print('=========================','
')

print(host_obj.h) #第三张表对应的对象。

print('=========================','
')
for item in host_obj.h:
    print(item.host_user,item.host_user.nid,item.host_user.username)

执行结果：
<__main__.Host object at 0x000001447FC99B70>
1
c1
=========================

[<__main__.HostToHostUser object at 0x000001447FC99828>, <__main__.HostToHostUser object at 0x000001447FC99EB8>, <__main__.HostToHostUser object at 0x000001447FC99F28>]
=========================

<__main__.HostUser object at 0x000001447FCAE4E0> 1 root
<__main__.HostUser object at 0x000001447FC64C50> 2 db
<__main__.HostUser object at 0x000001447FC99080> 3 nb

---

4、更简单的方式：多对多查询

      A  

      ＡＢ　＝＝》关系表  foreign 和 relationship

      B    

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

engine = create_engine("mysql+pymysql://root:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()


class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

class HostUser(Base):
    __tablename__ = 'host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))

class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)

    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))

    host = relationship('Host',backref='h')
    host_user = relationship('HostUser',backref='u')

Session=sessionmaker(bind=engine)
session=Session()

host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj)
print(host_obj.nid)

print('==============================')
print(host_obj.h) #第三张表对应的对象。

print('===============================','
')
for item in host_obj.h:
    print(item.host_user,item.host_user.nid,item.host_user.username)

执行结果：

<__main__.Host object at 0x000001FDBBA4E630>
1
==============================
[<__main__.HostToHostUser object at 0x000001FDBBA4E2B0>, <__main__.HostToHostUser object at 0x000001FDBBA4E940>, <__main__.HostToHostUser object at 0x000001FDBBA4E9B0>]
=============================== 

<__main__.HostUser object at 0x000001FDBBA6C048> 1 root
<__main__.HostUser object at 0x000001FDBBA6C208> 2 db
<__main__.HostUser object at 0x000001FDBBA6C3C8> 3 nb

---

5、更更 简单的方式：多对多查询

      A       ＝＝》foreign 和 relationship

      ＡＢ关系表　只需要进行简单的定义。

      B    

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

engine = create_engine("mysql+pymysql://root:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()



class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

    host_user = relationship('HostUser',secondary=lambda :HostToHostUser.__table__,backref='h')
    # 如果 有lambda的话，则可以关系对应表可以不用 放在上面，可以自由放置。放下面就会报错的;无lambda 的话 ，关系对应表应该放在上面。
             无lambda 的话 ，关系对应表应该放在上面。

class HostUser(Base):
    __tablename__ = 'host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))


class HostToHostUser(Base):
    __tablename__ = 'host_to_host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)

    host_id = Column(Integer,ForeignKey('host.nid'))
    host_user_id = Column(Integer,ForeignKey('host_user.nid'))


Session=sessionmaker(bind=engine)
session=Session()

host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj)
print(host_obj.nid)

print('==============================')

print(host_obj.host_user)

# print(host_obj.h) #第三张表对应的对象。

print('===============================')
for item in host_obj.host_user:
    print(item.username)

执行结果：
<__main__.Host object at 0x000002E592B8F860>
1
==============================
[<__main__.HostUser object at 0x000002E592B8F550>, <__main__.HostUser object at 0x000002E592B8F518>, <__main__.HostUser object at 0x000002E592B8F7B8>]
===============================
root
db
nb

---

---

来自为知笔记(Wiz)