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 = ''),
             HostUser(username = 'da',password = ''),
         ])
 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 = '',host_id = 1),
     HostUser(username = 'da',password = '',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类中，，其实都差不多，都是遍历对象属性得到想要的结果。