python整理-day13

一、ORM 

　　dbfist——数据库优先

　　corefist——代码优先

　　我们可以通过类和对象来操作数据库了

　　连表操作

　　　　一对多

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

　　　　　　2、操作：

　　　　　　　　　　类：repr

　　　　　　　　　　单表

　　　　　　　　　　连表

　　　　　　　　　　　　session.queue(表1).join(表2).all()

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:@192.168.11.90:3306/wzc", max_overflow=5)

Base = declarative_base()
#单表
class Test(Base):
    __tablename__="test"
    nid=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(32))
#一对多
class Group(Base):
    __tablename__ = "group"
    nid = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))

class User(Base):
    __tablename__ = "User"
    nid = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    group_id=Column(Integer,ForeignKey('group.nid'))
    def __repr__(self):
        temp="%s-%s_%s" %(self.nid,self.name,self.group_id)
        return temp

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

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

init_db()

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:@192.168.11.90:3306/wzc", max_overflow=5)
Base = declarative_base()

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

class Group(Base):
    __tablename__ = "group"
    nid = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))

class User(Base):
    __tablename__ = "User"
    nid = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    group_id=Column(Integer,ForeignKey('group.nid'))
    group=relationship("Group",backref='uuu')

ret=session.query(User).all()
for obj in ret:
    print(obj.nid,obj.name,obj.group)

obj=session.query(Group).filter(Group.caption=="dba").first()
print(obj.nid)
print(obj.aption)
print(obj.uuu)

 上面这个程序里面使用了反向查找,而反向查找主要是使用了relationship这个功能

我们之前都是用的正向查找,但是在有些时候我们需要使用反向查找来得到数据

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:@192.168.11.90:3306/wzc", max_overflow=5)
Base = declarative_base()

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

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.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()

host_obj=session.query(Host).filter(Host.hostname=="c1").first()
host_2_host_user=session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_user_id==host_obj.nid).all()
r=zip(*host_2_host_user)


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

　　　　这个就是多对多,我们在创建的时候,首先创建了用户的表,然后创建了机器的的表,最后创建了他们的关系表,就是服务器上面有哪些用户

　　然后插入数据,表内容和表关系全部存在对应的表里面

多对多

　　1,创建表,额外的表关系

　　2,filter()

　　　　in_(都可以是另外一个查询,把另外一个查询结果放到这个查询里面,其实这个就是连表工能)

　　3,relationship,建立关系

　　4relationship更简便方式

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:@192.168.11.90:3306/wzc", max_overflow=5)
Base = declarative_base()

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

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

host_obj=session.query(Host).filter(Host.hostname=='c1').first()
for i in host_obj.h:
    print(i.host_user.username)

 这个是上面那边程序的简化版,就是灵活的运用多对多来进行使用的,多对多其实就是在一个关系表里面,制造多个一对多的节奏\

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,Table
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine("mysql+pymysql://root:@192.168.11.90:3306/wzc", max_overflow=5)
Base = declarative_base()

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

HostToHostUser=Table('host_to_host_user',Base.metadata,
                     Column('host_id',ForeignKey('host.nid'),primary_key=True),
                     Column('host_user_id',ForeignKey('host_user.nid'),primary_key=True)
                     )

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

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

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

 这个方式更简便,里面表的对应关系,和之前的不一样了,之前是,a b ab关系表,这个是a b ab关系,但是在表a里面有关系信息,这样操作就更简便了

 

从关系表找其他表这个就是正向,从其他表找关系表这个就是反向

 

二、paramiko模块

　　SSHClient

　　　　用户名和密码:

　　　　　　SSHClient,TRANpory

　　SFTPclient

　　　　用户名和密码:

　　　　　　Tranport

　　需求:

　　　　命令\上传文件,命令

　　　　import paramiko

import paramiko
import uuid

class SSHConnection(object):

    def __init__(self, host='192.168.11.90', port=22, username='root',pwd='123'):
        self.host = host
        self.port = port
        self.username = username
        self.pwd = pwd
        self.__k = None

    def run(self):
        self.connect()
        pass
        self.close()

    def connect(self):
        transport = paramiko.Transport((self.host,self.port))
        transport.connect(username=self.username,password=self.pwd)
        self.__transport = transport

    def close(self):
        self.__transport.close()

    def cmd(self, command):
        ssh = paramiko.SSHClient()
        ssh._transport = self.__transport
        # 执行命令
        stdin, stdout, stderr = ssh.exec_command(command)
        # 获取命令结果
        result = stdout.read()
        return result

    def upload(self,local_path, target_path):
        # 连接，上传
        sftp = paramiko.SFTPClient.from_transport(self.__transport)
        # 将location.py 上传至服务器 /tmp/test.py
        sftp.put(local_path, target_path)

ssh = SSHConnection()
ssh.connect()

r1 = ssh.cmd('df')
print(r1)

ssh.upload('1.mp4', "/1.mp4")

ssh.close()

 

这个是最基本的使用,使用ssh和sftp

 

import paramiko
import sys
import os
import socket
import select
import getpass
from paramiko.py3compat import u

tran = paramiko.Transport(('192.168.11.90', 22,))
tran.start_client()
tran.auth_password('root', '123')

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

while True:
    # 监视用户输入和服务器返回数据
    # sys.stdin 处理用户输入
    # chan 是之前创建的通道，用于接收服务器返回信息
    readable, writeable, error = select.select([chan, sys.stdin, ], [], [], 1)
    if chan in readable:
        try:
            x = u(chan.recv(1024))
            if len(x) == 0:
                print('\r\n*** EOF\r\n')
                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()

 这个就可以在终端输入内容了,但是输入的时候不是实时的,需要执行一条回车一次

import paramiko
import sys
import os
import socket
import select
import getpass
from paramiko.py3compat import u

tran = paramiko.Transport(('192.168.11.90', 22,))
tran.start_client()
tran.auth_password('root', '123')

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

while True:
    # 监视用户输入和服务器返回数据
    # sys.stdin 处理用户输入
    # chan 是之前创建的通道，用于接收服务器返回信息
    readable, writeable, error = select.select([chan, sys.stdin, ], [], [], 1)
    if chan in readable:
        try:
            x = u(chan.recv(1024))
            if len(x) == 0:
                print('\r\n*** EOF\r\n')
                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()

 这个是更简便的一个版本,每次点一个就会有返回

 

　　连接：

　　　　堡垒机

　　　　

import paramiko
import sys
import os
import socket
import getpass

from paramiko.py3compat import u

# windows does not have termios...
try:
    import termios
    import tty

    has_termios = True
except ImportError:
    has_termios = False


def interactive_shell(chan):
    if has_termios:
        posix_shell(chan)
    else:
        windows_shell(chan)


def posix_shell(chan):
    import select

    oldtty = termios.tcgetattr(sys.stdin)
    try:
        tty.setraw(sys.stdin.fileno())
        tty.setcbreak(sys.stdin.fileno())
        chan.settimeout(0.0)

        flag = False
        temp_list = []
        while True:
            r, w, e = select.select([chan, sys.stdin], [], [])
            if chan in r:
                try:
                    x = u(chan.recv(1024))
                    if len(x) == 0:
                        sys.stdout.write('\r\n*** EOF\r\n')
                        break
                    sys.stdout.write(x)
                    sys.stdout.flush()
                except socket.timeout:
                    pass
            if sys.stdin in r:
                x = sys.stdin.read(1)
                import json

                if len(x) == 0:
                    break
                chan.send(x)

    finally:
        termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)


def windows_shell(chan):
    import threading

    sys.stdout.write("Line-buffered terminal emulation. Press F6 or ^Z to send EOF.\r\n\r\n")

    def writeall(sock):
        while True:
            data = sock.recv(256)
            if not data:
                sys.stdout.write('\r\n*** EOF ***\r\n\r\n')
                sys.stdout.flush()
                break
            sys.stdout.write(data)
            sys.stdout.flush()

    writer = threading.Thread(target=writeall, args=(chan,))
    writer.start()

    try:
        while True:
            d = sys.stdin.read(1)
            if not d:
                break
            chan.send(d)
    except EOFError:
        # user hit ^Z or F6
        pass


def run():
    # 获取当前登录用户
    username = raw_input('Username ')
    hostname = raw_input('Hostname: ')
    pwd = raw_input('password: ')


tran = paramiko.Transport(('192.168.11.90', 22,))
tran.start_client()
tran.auth_password('root', '123')

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

interactive_shell(chan)

chan.close()
tran.close()

if __name__ == '__main__':
    run()