python周报第十三周

0.本周知识点预览

• SQLAlchemy 进阶
• paramiko

1.SQLAlchemy 进阶

1.一对多查询

1.普通联表查询和正向查询

###导入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
from sqlalchemy import and_, or_

###SQLAlchemy
###创建连接
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
###绑定
Session = sessionmaker(bind=engine)
###初始化
session = Session()
###声明基类
Base = declarative_base()

class UserInfo(Base):
    ###创建的表名
    __tablename__ = 'userinfo'
    ###正常列
    uid = Column(Integer, primary_key=True)
    username = Column(String(64))
    gid = Column(Integer, ForeignKey('groupinfo.gid'))
    ###虚拟列,和groupinfo 表建立关系,正向查找要用groupInfo, 反向查找用fuck.
    groupInfo = relationship("GroupInfo", backref='fuck')

    ###当创建表中有 __repr__ 方法,利用方向查找得到的是具体数据,没有此方法,则获得的是表对象.
    def __repr__(self):
        temp = '%s - %s - %s' % (self.uid, self.username, self.gid)
        return temp


class GroupInfo(Base):
    __tablename__ = 'groupinfo'
    gid = Column(Integer, primary_key=True)
    gname = Column(String(64))

def create_db():
    ###创建表的语句
    Base.metadata.create_all(engine)

###创建表
# create_db()
# 插入数据
# session.add(GroupInfo(gid=1, gname='CEO'))
# session.add(GroupInfo(gid=2, gname='CTO'))
# session.commit()
#
# session.add(UserInfo(uid=1, username='lk', gid=1))
# session.add(UserInfo(uid=2, username='liukai', gid=2))
# session.commit()

###联表普通查询,查找用户名是lk的职位.
ret = session.query(GroupInfo.gname).join(UserInfo, isouter=True).filter(UserInfo.username=="lk").all()
print("职位: ", ret[0][0])
###通过外键关系,正向查询,先在用户名表中查出用户名为lk的所有数据,然后通过关系groupInfo
obj = session.query(UserInfo).filter(UserInfo.username=="lk").first()
print("职位: ", obj.groupInfo.gname)

程序执行如下：

职位:  CEO
职位:  CEO

代码解析：

1.可以看出程序执行结果相同，这是两种相同的联表查询操作，不同的是，一个是通过联表查询，一个是通过在建表类中定义的关系字段。

2.关系字段必须在建表类中定义，并且，必须这两个表之间有外键关联.

3.这种方法可以称之为正向查询，通过定义关系的表，来查与之关联的表的数据。

4.关系字段的参数中有个backref，这个是反向查询，请看下边的例子。

2.反向查询

建表的代码和上个例子一样，不过方向查询的数据不同，上个例子的代码中，关系字段在userinfo 表中，从userinfo 来查groupinfo表的数据叫正向查找；从groupinfo表来查userinfo的数据叫反向查找。

查找例子代码：

###根据职位反查用户名
###普通联表查询
ret1 = session.query(UserInfo.username).join(GroupInfo).filter(GroupInfo.gname=="CTO").first()
print("CTO: ", ret1[0])
###反向查询
ret = session.query(GroupInfo).filter(GroupInfo.gname=="CTO").first()
print("CTO: ", ret.fuck[0].username)

执行结果如下：

CTO:  liukai
CTO:  liukai

代码解析：

1.这两种查找方法结果相同，不同的是，一个是联表查询，一个是通过关系字段反向查找。

2.联表查询就不多说了，通过关系字段反向查询时，得到的ret是GroupInfo表的对象，ret.fuck是userinfo表中通过外键关联的数据列表如：[2- liukai-2]，这个格式取决于 __repr__ 方法的定义。ret.fuck[0].username 就得到了userinfo 表的准确数据。

2.多对多查询

1.普通联表查询

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))
    ######不利用关系表的方法,secondary代表关系表,可以写成表名,可以写类的Table对象
    # host_user = relationship('HostUser', backref='h', secondary="host_to_host_user")
    # host_user = relationship('HostUser', backref='h', secondary=HostToHostUser.__table__)

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')
drop_db()
create_db()
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()

##笨方法查询c1主机上的所有用户
##第一步得出在Host表中,hostname=c1的所有数据
host_obj = session.query(Host).filter(Host.hostname=='c1').first()
print("host_obj: ",host_obj)
##第二步得出当主机id为1时,关联表的所有用户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)
###第三步得出当hostuser中的nid 在上述所有用户ID所对应的用户名
users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
print(users)

执行结果如下：

host_obj:  <__main__.Host object at 0x10b0a9908>
[(1,), (2,), (3,)]
[('root',), ('db',), ('nb',)]

2.正向反向综合查询

##正向反向 综合查找
###第一步得出Host表中,hostname为c2的数据
host_obj = session.query(Host).filter(Host.hostname=='c2').first()
###h第二步 host_obj.h 是反向查找,因为关系字段在hosttohostuser表,能得出与之关联外键的相同nid的,hosttohostuser表中的数据
print("host_obj: ", host_obj, host_obj.h)
###第三步 host_obj.host_user 是正向查找,能得出与之关联外键相同host_user_id的,hostuser表中的值,进而得出用户名为host_obj.host_user.username
for i in host_obj.h:
    print(i, i.host_user, i.host_user.username)

执行结果如下：

host_obj:  <__main__.Host object at 0x1029427b8> [<__main__.HostToHostUser object at 0x102942400>, <__main__.HostToHostUser object at 0x102942518>, <__main__.HostToHostUser object at 0x102942ac8>]
<__main__.HostToHostUser object at 0x102942400> <__main__.HostUser object at 0x10291f9b0> db
<__main__.HostToHostUser object at 0x102942518> <__main__.HostUser object at 0x1029420b8> sb
<__main__.HostToHostUser object at 0x102942ac8> <__main__.HostUser object at 0x10294e128> nb

代码解析：只要两个表中有外键关联，就可以用关系字段连接(relationship)。

总结：如表1和表2，表2定义关系字段(必含外键)，则利用表1查表2，就用关系字段变量，如用表2查表1，则用关系字段backref参数。

2.paramiko

1.简介

##paramiko 远程执行,获取终端
import paramiko
import sys
import os
import socket
import select
import getpass
from paramiko.py3compat import u

tran = paramiko.Transport(('192.168.149.131', 22,))
tran.start_client()
tran.auth_password('root', 'liukai')

# 打开一个通道
chan = tran.open_session()
# 获取一个终端
chan.get_pty()
# 激活器
chan.invoke_shell()

while True:
    # 监视用户输入和服务器返回数据
    # sys.stdin 处理用户输入
    # chan 是之前创建的通道，用于接收服务器返回信息
    # 利用select 监听通道以及用户输入的终端
    readable, writeable, error = select.select([chan, sys.stdin, ],[],[],1)
    ## 假如通道有变更,则接收消息并打印
    if chan in readable:
        try:
            x = u(chan.recv(1024))
            if len(x) == 0:
                print('
*** EOF
')
                break
            sys.stdout.write(x)
            sys.stdout.flush()
        except socket.timeout:
            pass
    ### 假如终端输入有变化,则就是要发送指令了
    if sys.stdin in readable:
        inp = sys.stdin.readline()
        chan.sendall(inp)

chan.close()
tran.close()

执行结果如下：

Last login: Mon Aug  1 14:20:02 2016 from 192.168.149.1
[root@python ~]# ls
ls
anaconda-ks.cfg  Documents  install.log         Music     Public    Templates
Desktop          Downloads  install.log.syslog  Pictures  software  Videos
[root@python ~]# df
df
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/sda3       24461440 4529760  18682452  20% /
tmpfs             502384     228    502156   1% /dev/shm
/dev/sda1         194241   36322    147679  20% /boot
[root@python ~]# 

代码解析：paramiko就是实现ssh 远程连接、sftp的模块，提供了密码登陆，公钥登陆的方式。

2.ssh 远程连接

1.密码方式

import paramiko

###设置ssh连接的远程主机地址和端口
transport = paramiko.Transport(('192.168.149.131', 22))
###设置登录名和密码
transport.connect(username='root', password='liukai')

###创建一个SSH连接
ssh = paramiko.SSHClient()
ssh._transport = transport

stdin, stdout, stderr = ssh.exec_command('df')
print (stdout.read())

transport.close()
ssh.close()

执行结果如下：

b'Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/sda3       24461440 4529804  18682408  20% /
tmpfs             502384     228    502156   1% /dev/shm
/dev/sda1         194241   36322    147679  20% /boot
'

2.公钥方式

import paramiko

###私钥文件
private_key = paramiko.RSAKey.from_private_key_file('/Users/liukai/.ssh/id_rsa')

# 创建SSH对象
ssh = paramiko.SSHClient()
# 允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# 连接服务器,主机端口用户名pkey为私钥文件
ssh.connect(hostname='192.168.149.131', port=22, username='test', pkey=private_key)

# 执行命令
stdin, stdout, stderr = ssh.exec_command('df')
# 获取命令结果
result = stdout.read()
print(result)

# 关闭连接
ssh.close()

执行结果如下：

b'Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/sda3       24461440 4529968  18682244  20% /
tmpfs             502384     228    502156   1% /dev/shm
/dev/sda1         194241   36322    147679  20% /boot
'

3.sftp的实现

1.密码方式

### SFTP  密码方式

import paramiko

transport = paramiko.Transport(('192.168.149.131',22))
transport.connect(username='liukai',password='123')

sftp = paramiko.SFTPClient.from_transport(transport)
# 将location.py 上传至服务器 /tmp/test.py
sftp.put('/tmp/location.txt', '/home/liukai/put.txt')
# 将/home/liukai/a.txt 下载到本地 /tmp/get.txt 。注：第二个参数不能是目录
sftp.get('/home/liukai/a.txt', '/tmp/get.txt')

transport.close()

代码解析：

1.SFTP 只有transport这一种连接方式，不像执行命令时，可以声明一个SSHClient，所以以后建议都用transport这种方式

2.put、get方法的参数必须是目录

2.公钥方式

### SFTP 公钥方式

import paramiko

###定义私钥模式：RSA
private_key = paramiko.RSAKey.from_private_key_file('/Users/liukai/.ssh/id_rsa')

transport = paramiko.Transport(('192.168.149.131', 22))
transport.connect(username='liukai', pkey=private_key )

sftp = paramiko.SFTPClient.from_transport(transport)
# 将location.py 上传至服务器 /tmp/test.py
sftp.put('/tmp/location.txt', '/home/liukai/put2.txt')
# 将remove_path 下载到本地 local_path
sftp.get('/home/liukai/a.txt', '/tmp/get2.txt')

transport.close()

代码解析：公钥和密码方式除了定义时基本都是一样的，遵循固定的语法。