博客核心内容:
1、用户需求描述
2、MySQL数据库中的建表语句以及表结构
3、Python代码构造
4、运行结果展示
5、代码展示
1、用户需求描述 |
①首先需要在MySQL数据库中建立相应的表:主机表、部门表、用户表、用户_主机关系表:
要求:数据库中先建立相应的表
第一张表:主机表
主机ID 用户名 密码 IP地址 端口 公钥
01 root hadoop 192.168.80.100 80 afdfdsfds
第二张表:用户表
用户ID 用户名 密码 邮箱 部门ID
第三张表:部门表
部门ID 部门名称(标题)
第四张表:用户_主机表
用户ID 主机ID
1 1
2 2
注意:主机表中用户名和密码是相同的,不然会比较麻烦!
②用户登陆验证(通过用户表进行登录验证),并且用户登录成功之后,将当前用户的相关登录信息列举出来,包括:
host_info = ['host_id','ip','username','password','ip','port'] ===>即第二张表和第三张表进行关联
③用户登录成功之后,将当前用户管理的所有主机信息列举出来,包括:
用户ID 用户名 主机ID IP地址 ==>将二、四、一表进行关联操作
④用户登录成功之后,执行相应的命令,此时用户管理的所有机器都会得到执行(批量执行),并且用户也可以通过主机ID进行选择部分执行(这个部分需要用到paramiko模块)。
方法如下:
方法:根据用户ID可以获取第一张表中的相应的主机信息,构成一个列表host_list
host_list = [
{'ip':'xx',port:'xx',hostname:'xx',passwd:'xx'},
{'ip':'xx',port:'xx',hostname:'xx',passwd:'xx'},
{'ip':'xx',port:'xx',hostname:'xx',passwd:'xx'}
]
要求:并发的过程中使用线程池,不用进程池。
from concurrent.futures import ThreadPoolExecutor
pool = ThreadPoolExecutor(200)
def cmd(host_info,command):
pass
command = "ls"
for i in host_list = [
{'ip':'xx',port:'xx',hostname:'xx',passwd:'xx'},
{'ip':'xx',port:'xx',hostname:'xx',passwd:'xx'},
{'ip':'xx',port:'xx',hostname:'xx',passwd:'xx'}
]
pool.submit(cmd,i,command)
2、MySQL数据库中的建表语句以及表结构 |
整体构造如下:
mysql> use deptsys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_deptsys |
+-------------------+
| depart_table |
| host_table |
| user_host_table |
| user_table |
+-------------------+
4 rows in set (0.00 sec)
具体建表语句:
主机表建表语句:depsys
create table host_table(
host_id int auto_increment primary key,
username char(12),
password char(15),
ip char(30),
port int
)engine=innodb default charset=utf8;
create table depart_table(
depart_id int auto_increment primary key,
depart_name char(30)
)engine=innodb default charset=utf8;
create table user_table(
user_id int auto_increment primary key,
username char(12),
password char(15),
mailbox char(30),
depart_id int,
constraint c1 foreign key (depart_id) references depart_table(depart_id)
)engine=innodb default charset=utf8;
create table user_host_table(
user_id int ,
host_id int ,
constraint c2 primary key (user_id,host_id),
constraint c3 foreign key (user_id) references user_table(user_id),
constraint c4 foreign key (host_id) references host_table(host_id)
)engine=innodb default charset=utf8;
3、Python代码构造 |
整体代码构造:
DeptSystem(项目名称):
libs:(工具包管理)
__init__.py
user_function.py
main.py(程序的主入口)
其中用户工具包模块user_function.py的具体构造:
class UserManage:该类主要用于获取用户、主机的相关信息.
def __init__(self,username,password):
def login_authen(self):该函数用户用于用户进行登录验证
def list_user_info(self):该函数用于在用户登录成功之后,将用户的相关信息列举出来,包括:
用户ID 用户名 密码 邮箱 部门ID 部门名称(标题)
def get_host_info(self):该函数用于在用户登录成功之后,获取用户管理主机的所有信息,包括:
['host_id','ip','username','password','ip','port']
def get_host_all(self,host_id_lists):该函数用于获取用户管理的所有主机的信息
def get_host_part(self,host_id_lists):该函数用于获取用户管理的部分主机的信息,该函数的
设定,主要用于在批量操作中,用户可以随意选择所管理的部分机器.
class Remote_function:该类主要用于用户通过paramiko进行批量操作
def __init__(self,hostname,username,passwd,port,cmd):
def oper(self):该函数主要用于解析用户的命令,进行相应的操作
def cmd_result(self,cmd):用户进行命令操作,如df
def put_file(self,local_path,remote_path):该函数的功能是从本地向Linux服务端进行上传的
的操作.
def get_file(self,remote_path,local_path):该函数的功能是从linux服务器端下载文件到本地
4、运行结果展示 |
部分功能展示:
功能展示:
用户登录验证:
>>请输入您的用户名:张东
>>请输入您的密码:123456
登录成功!
用户相关信息的展示:
您的用户相关相关信息如下:
******************************
user_id: 1
username: 张东
password: 123456
mailbox: 454646456@qq.com
depart_id: 1
depart_name: 软件部门
******************************
用户管理机器信息的展示:
您管理的主机的相关信息如下:
------------------------------
host_id: 1
ip: 192.168.80.100
username: root
password: hadoop
ip: 192.168.80.100
port: 22
------------------------------
host_id: 2
ip: 192.168.80.60
username: root
password: hadoop
ip: 192.168.80.60
port: 22
------------------------------
批量机器管理的操作:
>请输入all或者部分主机id号,以空格分隔:1
请输入您的命令,q表示退出:df
1
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 5921544 4013772 1606972 72% /
tmpfs 515340 80 515260 1% /dev/shm
/dev/sda1 297485 31772 250353 12% /boot
操作完毕!
5、代码展示 |
main函数:
#!/usr/bin/python
# -*- coding:utf-8 -*-
from libs.user_function import *
from multiprocessing import Pool
if __name__ == '__main__':
username = input(">>请输入您的用户名:").strip()
if not username:print('用户名不能为空,请重新输入!'),exit(-1)
password = input(">>请输入您的密码:").strip()
if not password:print('密码不能为空,请重新输入!'), exit(-1)
user_obj = UserManage(username, password)
"""
随后用户将对所管理的机器进行批量操作:
"""
host_choice = input('请输入*或者机器ID号,并以空格进行分隔>:').strip()
if host_choice == '*':
host_list = user_obj.get_host_all()
else:
host_list = user_obj.get_host_part(host_choice)
while True:
cmd = input('请输入相应的操作命令>:')
if not cmd: continue
for item in host_list:
host_id = item['host_id']
ip = item['ip']
port = item['port']
username = item['username']
password = item['password']
try:
Remote_function(ip, username, password, port, cmd).oper()
except Exception as e:
print(e)
last_choice = input('是否继续操作:y or n :>\n')
if last_choice == 'y':
pass
else:
break
lib工具包:
#!/usr/bin/python
# -*- coding:utf-8 -*-
import pymysql
import paramiko
from multiprocessing import Pool,Lock
import time
class UserManage:
def __init__(self,username,password):
self.uesrname = username
self.password = password
conn = pymysql.Connect(host='192.168.80.100',port=3306,user='root',password='admin',
database='deptsys',charset='utf8')
cursor_oper = conn.cursor(cursor=pymysql.cursors.DictCursor)
self.cursor_oper = cursor_oper
#进行登录验证
self.login_authen()
def login_authen(self):
sql = """
select *
from user_table
where username = %s and password = %s
"""
v1 = self.cursor_oper.execute(sql,[self.uesrname,self.password])
result = self.cursor_oper.fetchall()
if len(result):
print("登录成功!")
#获取用户的相关信息
UserManage.list_user_info(self)
#获取用户管理主机的相关信息,并进行格式化输出
print("您管理的主机的相关信息如下:")
print("-" * 30)
result_hosts = UserManage.get_host_all(self)
host_info = ['host_id','ip','username','password','ip','port']
for line in result_hosts:
for title in host_info:
line2 = title+":\t"+str(line[title])
print(line2.expandtabs(15))
print("-"*30)
print("*" * 30)
else:
print("您的用户名或者密码输入有误,登录失败!")
def list_user_info(self):
"""
function:用户登录成功之后,将当前用户的登录信息列出来
:return:
"""
print("您的用户相关相关信息如下:")
print("*"*30)
sql = """
select A.user_id,A.username,A.password,A.mailbox,A.depart_id,B.depart_name
from user_table A
left join depart_table B
on A.depart_id = B.depart_id
where username = %s and password = %s
"""
v1 = self.cursor_oper.execute(sql, [self.uesrname, self.password])
result = self.cursor_oper.fetchall()
user_info = ['user_id','username','password','mailbox','depart_id','depart_name']
for title in user_info:
line = str(title)+":\t"+str(result[0][title])
print(line.expandtabs(15))
print("*" * 30)
def get_host_all(self):
"""
function:获取主机列表
:return:
"""
sql = """
select C.host_id,C.ip,C.`port`,C.username,C.`password`
from user_table A
left join user_host_table B
on A.user_id = B.user_id
left join host_table C
on B.host_id = C.host_id
where A.username = %s
"""
v1 = self.cursor_oper.execute(sql, [self.uesrname])
result = self.cursor_oper.fetchall()
return result
def get_host_part(self,user_id_choice):
"""
如果用户输入的是主机ID列表,则进行选择操作
:return:
"""
user_id = []
for id in user_id_choice.split():
user_id.append(int(id))
host_list = str(user_id).replace('[','(').replace(']',')')
sql = """
select C.host_id,C.ip,C.`port`,C.username,C.`password`
from user_table A
left join user_host_table B
on A.user_id = B.user_id
left join host_table C
on B.host_id = C.host_id
where A.username = '张东' and C.host_id in %s
"""%host_list
v1 = self.cursor_oper.execute(sql, )
result = self.cursor_oper.fetchall()
return result
class Remote_function:
def __init__(self,hostname,username,passwd,port,cmd):
self.hostname = hostname
self.username = username
self.passewd = str(passwd)
self.port = port
self.cmd = cmd
def oper(self):
if "get" in self.cmd:
remote_path = self.cmd.split()[1]
local_path = self.cmd.split()[2]
self.get_file(remote_path,local_path)
elif "put" in self.cmd:
local_path = self.cmd.split()[1]
remote_path = self.cmd.split()[2]
self.put_file(local_path,remote_path)
else:
self.cmd_result(self.cmd)
def cmd_result(self,cmd):
"""
function:该方法用户获取命令的执行结果
:return:
"""
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(self.hostname,self.port,self.username, self.passewd)
stdin, stdout, stderr = ssh.exec_command(self.cmd)
for line in stdout.readlines():
print(line, end="")
ssh.close()
def put_file(self,local_path,remote_path):
"""
function:该函数的功能是从本地向Linux服务端上传文件
:param local_path: windows本地路径
:param remote_path: Linux服务端路径
"""
t = paramiko.Transport((self.hostname,self.port))
t.connect(username=self.username, password=self.passewd)
sftp = paramiko.SFTPClient.from_transport(t)
sftp.put(local_path, remote_path)
t.close
def get_file(self,remote_path,local_path):
"""
function:该函数的功能是从linux服务器端下载文件到本地
:param remote_path: 远程服务器的路径
:param local_path: windows本地的执行路径
"""
t = paramiko.Transport((self.hostname, self.port))
t.connect(username=self.username, password=self.passewd)
sftp = paramiko.SFTPClient.from_transport(t)
sftp.get(remote_path, local_path)
t.close
def __str__(self):
print(type(self.hostname),type(self.port),type(self.username),type(self.passewd))