python 连接操作数据库(二)

一、我们接着上期的博客继续对ORM框架进行补充，顺便把paramiko模块也给大家讲解一下：

      1、ORM框架：

           在连接操作数据库的第一个博客中也已经说了，sqlalchemy是一个ORM框架，总结就是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。先来看下使用sqlalchemy来链接数据库的基本代码：

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://mysql_test:123456@192.168.163.131:3306/pymysql", max_overflow=5)

Base = declarative_base()

           首先，前四行是导入的有关模块，我们对这些模块进行简单的介绍，然后列举例子，你就能明白了；

                 第二行中导入的是Column（字段）、（Integer, String）字段类型、ForeignKey外键、create_engine创建数据库；使用declarative_base模块创建一个Base对象，这个在下面创建表的类中需要继承，在上面的代码中已经事先创建好了pymysql数据库，所以下面我们直接创建表以及插入数据：

class Host(Base):
    __tablename__ = 'host'
    h_id = Column(Integer,primary_key=True,autoincrement=True)
    host_ip = Column(String(32))
    host_port = Column(Integer)
class  HostUser(Base):
    __tablename__ = 'hostuser'
    u_id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(32))
    password = Column(String(20))

Base.metadata.create_all(engine)

           解释说明：

                 这两个类是用来创建表使用的，每个类对应一个表，在类里面可以定义多个字段，在字段里面的参数可以设置多个（比如：主键、自增等）；最后一行才是把类创建成表并存到数据库，当你执行完代码之后，表已经创建了，那么下一步就是添加数据，看下面添加数据的代码：

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

session.add(Host(host_ip = '1.1.1.4',host_port = 5555))

session.add_all([
            Host(host_ip = '1.1.1.1',host_port = 22),
            Host(host_ip = '1.1.1.2',host_port = 22),
            Host(host_ip = '1.1.1.3',host_port = 22),
            HostUser(username = 'ma',password = '123'),
            HostUser(username = 'da',password = '123'),
        ])
session.commit()

                 插入数据有两种方式，一种是一次插入一条数据，使用的方法是add ; 第二种是一次插入多条数据，使用的方法是add_all;你需要注意的是最后要执行commit方法，不然，你上面做的一切都是白费的，提交不到数据库。

          下面看一条查询的语句：

ret1 = session.query(Host.host_ip,Host.host_port).filter(Host.host_ip == '1.1.1.2').all()
print(ret1)

ret2 = session.query(Host.host_ip,Host.host_port).all()
print(ret2)

#输出结果
[('1.1.1.2', 22)]
[('1.1.1.1', 22), ('1.1.1.2', 22), ('1.1.1.3', 22), ('1.1.1.4', 5555)]

                带filter的是查询一条条件语句，不带条件语句的就查询全部的数据，输出的格式是列表中嵌套元祖。

      2、上面说了那么多，其实只是sqlalchemy操作数据库的简单的用法，下面我们说下稍微复杂一点的，连表操作：

           一对多：    

class  HostUser(Base):
    __tablename__ = 'hostuser'
    u_id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(32))
    password = Column(String(20))
    host_id = Column(Integer,ForeignKey('host.h_id'))
    #创建虚拟关系relationship,跟foreignkey配合使用
    host = relationship('Host',backref='hh')

session.add_all([
    HostUser(username = 'ma',password = '123',host_id = 1),
    HostUser(username = 'da',password = '123',host_id = 2),
])
session.commit()

                还有个Host类跟上面是一样的，其实创建这个表的字段也是一样的，只不过在里面增加了一个外键和关系，分别是第六行和第八行，外键的作用就是可以根据特定的字段去查询其它表中的字段，关系的作用下面列举一个例子，你就会明白， 我们先来说下通常我们所做的查询时正向的，什么是正向的呢！就像这个HostUser中有关系，从这个表去其它表中查询数据就是正向的，从其它表中查询数据，但是是通过这个关系去查询的，这样就叫做反向查询，下面我们看个例子：

#正向查询，有虚拟关系
ret = session.query(HostUser).all()
for obj in ret:
    print(obj.username,obj.host.host_ip,obj.host.host_port)

输出结果：
ma 1.1.1.1 22
da 1.1.1.2 22

             这个例子说的是有虚拟关系，但是使用的是正向查询，就是利用关系去查询用户所对应的IP地址和端口，下面再说反向查询的原始方式以及有关系的反向查询：

#反向查询的原始方式
ret1 = session.query(HostUser.u_id,HostUser.username).join(Host,isouter=True).filter(Host.host_ip == '1.1.1.2').all()
print(ret1)

#反向查询的虚拟关系查询,需要遍历
obj = session.query(Host).filter(Host.host_ip == '1.1.1.2').first()
print(obj.hh)
for ret in obj.hh:
    print(ret.u_id,ret.username)

           解释说明：

                 上面两个例子，第一个是反向查询的原始方式，查询的内容是属于1.1.1.2这台机器的所有用户和对应的用户id；其实使用虚拟关系进行查询就是去掉了join，使用生成的HostUser对象进行调用backref的值hh,就可以查出用户了，两种不同的方式输出的结果的如下：

[(4, 'da')]
[<__main__.HostUser object at 0x0000000003BBB080>]
4 da

        多对多：

           多对多的一个关键点是需要第三张表去做关系,下面列举一个例子，来阐述一下，在多对多的查询中使用关系与不使用关系的sql执行：

engine = create_engine("mysql+pymysql://mysql_test:123456@192.168.163.131:3306/orm_test")
Base = declarative_base()

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')


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=HostToHostUser.__table__,backref='h')

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

Base.metadata.create_all(engine)
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()
'''

           先使用类去创建三个表，那个HostToHostUser就是第三张表，用户存放其它两个表的外键和关系的；现在如果要查询c1主机对应的管理员账号，我们如果不用关系的话，应该是这样的：

#没有关系的原始方式
#1.先在host表中查询c1的nid
host_obj = session.query(Host).filter(Host.hostname=='c1').first()
#2.查询hosttohostuer表中的所有host_id等于c1的nid的对应的host_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)
r=zip(*host_2_host_user)
# print(list(list(r)[0]))
#通过查到的host_user_id查询hostuser表中的对应的管理员用户名
users = session.query(HostUser.username).filter(HostUser.nid.in_(list(list(r)[0]))).all()
print(users)

#输出结果
[('root',), ('db',), ('nb',)]

          里面说的很详细了，我就不再过多的阐述了，下面再看下有关系的查询：

# 1.反向查找,查询host表中c1的信息,会得到一个对象,对象中存在一个已经设置好的虚拟关系:h
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
#2.正向查找,遍历对象属性
for item in host_obj.h:
    print(item.host_user.username)

#输出结果
root
db
nb

        怎么样？这个方法是不是简单多了，其实我们在创建表的时候，也创建了一种更简单的方式，那就是把关系放在Host类中，，其实都差不多，都是遍历对象属性得到想要的结果。