前期回顾
一、redis
发布订阅
二、rabbitMQ
原始队列exchange
ex全部转发ex,关键字ex,模糊匹配
rpc
三、MySQL
四、Python MySQL
pymysql
excute 执行单条语句,返回受影响的行数excutemany 执行多条语句,返回受影响的行数fetchonefetchallfetchmanyscrolllastrowid
五、SQLAlchemy
ORM框架
db firstcode first====> 我们以后通过类和对象操作数据库code first1、自定义生成表2、使用类操作表
本节摘要
一、ORM
连表
一对多多对多
二、Paramiko模块
链接:
堡垒机
三、前端
HTML
http://www.cnblogs.com/wupeiqi/articles/5699254.html
一、ORM—SQLAlchemy
连表
一对多1、创建表,主动知道外键2、操作:
类:repr单表连表
session.query(表1).join(表2).all()
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
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:123@127.0.0.1:3306/s13", 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)
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'))
def __repr__(self):
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='ddd'))
# session.commit()
# session.add_all([
# User(username='alex1',group_id=1),
# User(username='alex2',group_id=2)
# ])
# session.commit()
# 只是获取用户
# ret = session.query(User).filter(User.username == 'alex1').all()
# print(ret)
# ret = session.query(User).all()
# obj = ret[0]
# print(ret)
# print(obj)
# print(obj.nid)
# print(obj.username)
# print(obj.group_id)
# ret = session.query(User.username).all()
# print(ret)
sql = session.query(User,Group).join(Group, isouter=True)
print(sql)
ret = session.query(User,Group).join(Group, isouter=True).all()
print(ret)
# select * from user left join group on user.group_id = group.nid
正反向查找
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
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:123@127.0.0.1:3306/s13", 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)
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')
def __repr__(self):
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='ddd'))
# session.commit()
# session.add_all([
# User(username='alex1',group_id=1),
# User(username='alex2',group_id=2)
# ])
# session.commit()
# 只是获取用户
# ret = session.query(User).filter(User.username == 'alex1').all()
# print(ret)
# ret = session.query(User).all()
# obj = ret[0]
# print(ret)
# print(obj)
# print(obj.nid)
# print(obj.username)
# print(obj.group_id)
# ret = session.query(User.username).all()
# print(ret)
# sql = session.query(User,Group).join(Group, isouter=True)
# print(sql)
# ret = session.query(User,Group).join(Group, isouter=True).all()
# print(ret)
# select * from user left join group on user.group_id = group.nid
# 原始方式
# ret = session.query(User.username,Group.caption).join(Group, isouter=True).all()
# 新方式(正向查询)
# ret = session.query(User).all()
# for obj in ret:
# obj代指user表的每一行数据
# obj.group代指group对象
# print(obj.nid,obj.username,obj.group_id,obj.group,obj.group.nid,obj.group.caption)
# 原始方式
# ret = session.query(User.username,Group.caption).join(Group,isouter=True).filter(Group.caption == 'DBA').all()
# 新方式(反向查询)
# obj = session.query(Group).filter(Group.caption == 'DBA').first()
# print(obj.nid)
# print(obj.caption)
# print(obj.uuu)
多对多:
1、创建表——额外的关系表2、filter()
==in_(都可以是另外一个查询)
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
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:123@127.0.0.1:3306/s13", 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)
def drop_db():
Base.metadata.drop_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()
# 获取主机1中所有用户
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
# host_obj.nid
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(r)[0])
users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
print(users)
3、relationship
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
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:123@127.0.0.1:3306/s13", 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')
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_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.add_all([
# HostUser(username='root'),
# HostUser(username='db'),
# HostUser(username='nb'),
# HostUser(username='sb'),
# ])
#
# 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()
# 获取主机1中所有用户
# host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
# host_obj.nid
# 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(r)[0])
# users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
# print(users)
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
for item in host_obj.h:
print(item.host_user.username)
4、更简单的方式
A 关系(B,AB Table对象)AB == > fkB操作时,简单
SQLAlchemy总结:
1、创建表
2、操作表
单表操作连表操作
.join关系:
一对多
fk,关系
多对多
多一张表,fk1、关系表:关系2、在某一张表:关系;A:关系,(B,AB)
二、Paramiko模块
使用Paramiko模块连接远程服务器并执行命令:
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
# paramiko模块
import paramiko
# 普通连接远程主机并执行一条命令
# 创建SSH对象
ssh = paramiko.SSHClient()
# 允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# 连接服务器
ssh.connect(hostname='192.168.111.2', port=22, username='root', password='111111')
# 执行命令
stdin, stdout, stderr = ssh.exec_command('ls -l')
# 获取命令结果
result = stdout.read()
print(result)
# 关闭连接
ssh.close()
通过Paramiko模块一次连接服务器实现执行命令,上传文件等多次操作:
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
import paramiko
import uuid
# 实现一次链接执行命令、上传文件、执行命令
class SSHConnection(object):
def __init__(self, host='192.168.111.2', port=22, username='root',pwd='111111'):
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('s1.py', "/root/s7.py")
ssh.close()
堡垒机,通过回车确定输入内容,并返回结果(一次输入一条命令返回结果,只可以在Linux上执行):
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
# 堡垒机,通过回车确定输入内容,并返回结果
import paramiko
import sys
import os
import socket
import select
import getpass
from paramiko.py3compat import u
tran = paramiko.Transport(('192.168.111.2', 22,))
tran.start_client()
tran.auth_password('root', '111111')
# 打开一个通道
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(' *** 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()
堡垒机,通过回车确定输入内容,并返回结果(一次输入一个字符,可以通过tab补全,只可以在Linux上执行):
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
import paramiko
import sys
import os
import socket
import select
import getpass
import termios
import tty
from paramiko.py3compat import u
tran = paramiko.Transport(('192.168.111.2', 22,))
tran.start_client()
tran.auth_password('root', '111111')
# 打开一个通道
chan = tran.open_session()
# 获取一个终端
chan.get_pty()
# 激活器
chan.invoke_shell()
# 获取原tty属性
oldtty = termios.tcgetattr(sys.stdin)
try:
# 为tty设置新属性
# 默认当前tty设备属性:
# 输入一行回车,执行
# CTRL+C 进程退出,遇到特殊字符,特殊处理。
# 这是为原始模式,不认识所有特殊符号
# 放置特殊字符应用在当前终端,如此设置,将所有的用户输入均发送到远程服务器
tty.setraw(sys.stdin.fileno())
chan.settimeout(0.0)
while True:
# 监视 用户输入 和 远程服务器返回数据(socket)
# 阻塞,直到句柄可读
r, w, e = select.select([chan, sys.stdin], [], [], 1)
if chan in r:
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 r:
x = sys.stdin.read(1)
if len(x) == 0:
break
chan.send(x)
finally:
# 重新设置终端属性
termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
chan.close()
tran.close()
堡垒机,通过回车确定输入内容,并返回结果
(终极版本,可以在windows和linux上执行):
#!/usr/bin/env python
# -.- coding:utf-8 -.-
# By Sandler
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)
log = open('handle.log', 'a+', encoding='utf-8')
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(' *** EOF ')
break
if flag:
if x.startswith(' '):
pass
else:
temp_list.append(x)
flag = False
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
if x == ' ':
flag = True
else:
temp_list.append(x)
if x == ' ':
log.write(''.join(temp_list))
log.flush()
temp_list.clear()
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. ")
def writeall(sock):
while True:
data = sock.recv(256)
if not data:
sys.stdout.write(' *** EOF *** ')
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():
default_username = getpass.getuser()
username = input('Username [%s]: ' % default_username)
if len(username) == 0:
username = default_username
hostname = input('Hostname: ')
if len(hostname) == 0:
print('*** Hostname required.')
sys.exit(1)
tran = paramiko.Transport((hostname, 22,))
tran.start_client()
default_auth = "p"
auth = input('Auth by (p)assword or (r)sa key[%s] ' % default_auth)
if len(auth) == 0:
auth = default_auth
if auth == 'r':
default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')
path = input('RSA key [%s]: ' % default_path)
if len(path) == 0:
path = default_path
try:
key = paramiko.RSAKey.from_private_key_file(path)
except paramiko.PasswordRequiredException:
password = getpass.getpass('RSA key password: ')
key = paramiko.RSAKey.from_private_key_file(path, password)
tran.auth_publickey(username, key)
else:
pw = getpass.getpass('Password for %s@%s: ' % (username, hostname))
tran.auth_password(username, pw)
# 打开一个通道
chan = tran.open_session()
# 获取一个终端
chan.get_pty()
# 激活器
chan.invoke_shell()
interactive_shell(chan)
chan.close()
tran.close()
if __name__ == '__main__':
run()
链接:
堡垒机