I. SQLalchemy联表操作
1. 一对多
class Group(Base): # 一对多的表,组中可能包含多个用户
__tablename__ = 'group'
nid = Column(Integer, primary_key=True, autoincrement=True)
caption = Column(String(32))
class User(Base):
__tablename__ = 'user'
uid = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32))
gid = Column(Integer, ForeignKey('group.nid'))
user表和group表中插入数据
# 创建完表之后，建立连接
Session = sessionmaker(bind=engine)
session = Session()
新增Group表数据
session.add_all([
Group(caption='DBA'),
Group(caption='SA'),
]
)
session.commit()
session.add(Group(caption='QA'))
新建User表数据
session.add_all([
User(name='Bob', gid=1),
User(name='Boss', gid=2),
]
)
session.commit()
这俩代码中定义了2个表，一个是“组”，一个是“用户表”。一对多表示：一个组中可能存在多个用户。
1.1 查找用户表中每个用户对应的组。
常规的联表查询如下：
ret = session.query(User.name, Group.caption).join(Group).all()
print(ret) # join默认是进行left join
out: [('Bob', 'DBA'), ('Boss', 'SA')]
SQLacademy查询方法：
步骤如下：
1. 建立关系
# 使用relationship，先必须在创建表的时候建立关系
class Group(Base):
__tablename__ = 'group'
nid = Column(Integer, primary_key=True, autoincrement=True)
caption = Column(String(32))
class User(Base):
__tablename__ = 'user'
uid = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32))
gid = Column(Integer, ForeignKey('group.nid'))
<strong># 仅仅方便查询.</strong>
group = relationship("Group")
2. 正向查询
ret = session.query(User).all()
for obj in ret:
# obj 代指user表中的每一行数据,是User对象
# obj.group代指group对象，可以用obj.group.XXX来获取group表中的字段的值
print(obj.uid, obj.name, obj.gid, obj.group, obj.group.nid, obj.group.caption)
3. 反向查询
group = relationship("Group", backref='uuu')
obj = session.query(Group).filter(Group.caption=='DBA').first() 带有筛选条件的语句，筛选DBA组所有成员
print(obj) # obj表示符合条件的Group对象
out: <__main__.Group object at 0x00000000032EB710><br>
print(obj.uuu) # obj.uuu 就是符合筛选条件的User对象
out: [<__main__.User object at 0x0000000003B15400>, <__main__.User object at 0x0000000003B15470>]
for i in obj.uuu:# obj.uuu需要用for循环来查询结果
print(i.uid, i.name, i.gid)
relationship()函数：这个函数告诉ORM，通过使用user.Group，Group类应该和User类连接起来
relationship()使用外键明确这两张表的关系。决定User.group属性是多对一的，即多个用户可以在同一个组里。
relationship()的子函数backref()提供表达反向关系的细节：relationship()对象的集合被Group.uuu引用。多对一的反向关系总是一对多，即一个组可以包含多个用户
2. 多对多
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)
# 两个关联的id，以外键的形式存在
host_id = Column(Integer, ForeignKey('host.nid'))
host_user_id = Column(Integer, ForeignKey('host_user.nid'))
# 创建完表之后，建立连接
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()
上边增加的数据，增加了五台服务器，分别是c1,c2,c3,c4,c5。对应ID：1,2,3,4,5。增加了四个人，分别是root, db, nb, sb，对应ID：1,2,3,4
2.1 常规查询
# 1. 获取主机为c1的对象
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
# 2. 获取查询关系表中拥有主机c1的用户ID
host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()
print(host_2_host_user) # [(1,), (2,), (3,)]
r = zip(*host_2_host_user)
print(list(r)) #[(1, 2, 3)] 这就是用户ID列表
# 3. 根据用户ID列表，查询用户名
users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
print(users) # [('root',), ('db',), ('nb',)]
2.2 relationship查询
2.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))
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')
2.2.2 查询获取HostToHostUser表中的字段信息
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()<br>
print(host_obj) # <__main__.Host object at 0x0000000003C11128>
print(host_obj.hostname) # 主机名：c1
print(host_obj.h) # host_obj.h表示HostToHostUser表中，符合筛选条件的HostToHostUser数据对象列表 [<__main__.HostToHostUser object at 0x0000000003B3F198>, <__main__.HostToHostUser object at 0x0000000003B3F898>, <__main__.HostToHostUser object at 0x0000000003B3F908>]
# 循环获取用户信息
for item in host_obj.h:
# print(item.host_user) # 一行用户的数据， HostUser 表的一行数据对象
print(item.host_user.username)
II. paramiko
paramiko的两种基本用法：基于用户名密码连接、基于公钥私钥连接。主要有两个大类：SSHClient(用于连接远程服务器并执行基本命令)、SFTPClient(用于连接远程服务器并执行上传下载)
1. SSHClient
1.1 基于用户名密码连接：
import paramiko
# 创建SSH对象
ssh = paramiko.SSHClient()
# 允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# 连接服务器
ssh.connect(hostname='172.25.50.13', port=22, username='work', password='123456')
# 执行命令
stdin, stdout, stderr = ssh.exec_command('ls -l')
# 获取命令结果
result = stdout.read()
print(result.decode())
# 关闭连接
ssh.close()
out:
total 8
drwxr-xr-x 2 work work 4096 Mar 18 19:22 cn_market_lua
drwxrwxr-x 3 work work 4096 Mar 18 19:09 www
基于用户名密码实现执行命令
1.2 基于公钥密钥连接：
import paramiko
# 创建key文件
private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa')
# 创建SSH对象
ssh = paramiko.SSHClient()
# 允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# 连接服务器
ssh.connect(hostname='172.25.50.13', port=22, username='work', key=private_key)
# 执行命令
stdin, stdout, stderr = ssh.exec_command('df -h')
# 获取命令结果
result = stdout.read()
# 关闭连接
ssh.close()
out：
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 20G 4.2G 15G 23% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vdb1 99G 499M 93G 1% /data0
基于公钥私钥实现远程执行命令
2. SFTPClient
2.1 基于用户名密码连接：
import paramiko
# 创建transport
transport = paramiko.Transport(('172.25.50.13',22))
transport.connect(username='work',password='123456')
# 创建sftpclient，并基于transport连接，把他俩进行绑定
sftp = paramiko.SFTPClient.from_transport(transport)
# 将location.py 上传至服务器 /tmp/test.py
sftp.put('/tmp/location.py', '/tmp/test.py')
# 将remove_path 下载到本地 local_path
sftp.get('remove_path', 'local_path')
# 关闭session
transport.close()
基于用户名密码实现上传下载
2.2 基于公钥密钥连接：
import paramiko
# 创建key文件
private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa')
transport = paramiko.Transport(('172.25.50.13', 22))
transport.connect(username='work', pkey=private_key )
sftp = paramiko.SFTPClient.from_transport(transport)
# 将location.py 上传至服务器 /tmp/test.py
sftp.put('/tmp/location.py', '/tmp/test.py')
# 将remove_path 下载到本地 local_path
sftp.get('remove_path', 'local_path')
transport.close()
基于公钥密钥上传下载
3. 堡垒机
堡垒机大概构成：
- 管理员为用户在服务器上创建账号（将公钥放置服务器，或者使用用户名密码）
- 用户登陆堡垒机，输入堡垒机用户名密码，显示当前用户管理的服务器列表
- 用户选择服务器，并自动登陆
- 执行操作并同时记录用户操作
使用paramiko就可以实现上述功能，这里先省略数据库方面：
版本一： 1）用户在终端输入内容，并将内容发送至远程服务器，
2）远程服务器执行命令，并将结果返回
3）用户终端显示内容
import paramiko
import sys
import os
import socket
import select
import getpass
from paramiko.py3compat import u # py27中注释掉这行
tran = paramiko.Transport(('172.25.50.13', 22,))
tran.start_client()
tran.auth_password('work', '123456')
# 打开一个通道
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)) # py3中 代码
# x = chan.recv(1024) # py2中代码
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()
终极版本： 带有用户日志的堡垒机
# 记录用户日志
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()