python学习笔记之paramiko和pymysql (第九天）

封云亭

　　参考银角大王 ：http://www.cnblogs.com/wupeiqi/articles/5095821.html
　　http://www.cnblogs.com/wupeiqi/articles/5713330.html（pymysql模块）
　　金角大王：http://www.cnblogs.com/alex3714/articles/5950372.html（python mysql）
　　http://www.cnblogs.com/alex3714/articles/5978329.html（sqlalchemy ORM）
　　一、Python的paramiko模块，该模块机遇SSH用于连接远程服务器并执行相关操作
　　1、SSHClient
　　用于连接远程服务器并执行基本命令
　　基于用户名密码连接：





import paramiko
ssh = paramiko.SSHClient()
# 允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='192.168.4.*',port=22,username='ubuntu',password='******')
stdin,stdout,stderr = ssh.exec_command('df -Th')
for result in stdout.readlines():
#result = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))[0]
print(result,end='')
ssh.close()
transport = paramiko.Transport('192.168.4.*',22)
transport.connect(username='ubuntu',password='*******')

ssh1 = paramiko.SSHClient()
ssh1._transport = transport
stdin,stdout,stderr = ssh1.exec_command('ls -l')
#result1 = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))
for result1 in stdout.readlines():
#result = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))[0]
print(result1,end='')
transport.close
基于密码　　基于密钥连接：





import paramiko
private_key = paramiko.RSAKey.from_private_key_file('/home/ubuntu/.ssh/id_rsa')
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='192.168.4.*',port=22,username='ubuntu',pkey=private_key)
stdin,stdout,stderr = ssh.exec_command('df -Th')

result = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))[0]
print(result)
ssh.close()
transport = paramiko.Transport('192.168.4.*',22)
transport.connect(username='ubuntu',pkey=private_key)

ssh1 = paramiko.SSHClient()
ssh1._transport = transport
stdin,stdout,stderr = ssh1.exec_command('ls -l')
result1 = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))
print(result1)
transport.close
基于密钥　　基于密钥字符串：





import paramiko
from io import StringIO
private_str = """-----BEGIN RSA PRIVATE KEY-----
MIIEpQIBAAKCAQEAzbQ+3wA47CEdTCSOiHL38PC4vckze2u/JXiw66uiQMsp9sDX
CP6I4QppLaGqG1s1SjZf7H0sb7jNz7LqRZe24Scnum1Cm0YhBtVLSfy454Ugh7ec
uHD/hM6EbKHm10jdZrd4rJezvSqJkpsc/T2MSbiYWz0I2dKb4sYbJlDB6CPtBoiZ
8mmSadekr67SsmygYpigBWrGyjcPJa1xpaY/dqXZkByGFBY/j4Ipm792T3ilTXd1
U1q0xa/+Axkv1doqEBAzqIukc56alSwvpPyWkTj7uH17BGIaAxjHVQM6BdWGiKbr
AffDQuvZcOeHD/IKVY3qwU7i7kC0fjXkKj3+OwIDAQABAoIBAQCfwYa6hl+u86Df
S9zul+t70liH/MTg67NOFMfCoE+o5qA2pVncAGKp8/3vlIiaKmHeuxAQiL6EHhCp
aBiN59/+SPyPyt9Z3EM2HV0VnxKzrC6xeKZckFXB/OnXvH2dYVehuIgd8suC9JBX
reP7wVs8vgKFiYdgNDMhEh5vyXRvJmMpwEF9T8h3BDA4Dzq/algGheX+fAPbsIV8
lTymzf3y4BZN+F1vBBUidV5IaDvHO8ojwvJmVw0PM/DIfssbruzqw96czeIhv2SN
1TK56aWHWBClq7CRVrSF8wHVcAuKFl4Bldm6OneT4fZFTmsdFgbT7N+Xd3d9T7O7
OhDgkctBAoGBAOjP/BAUv5dI/HBMyPEPrKa27nitlGn+SVXURdLNEIRAL5ayb0FD
xcHG3DoJ68DLTX/E4okyySRERqT8MF3cfpuFC69rzN5ZPvlyLll8iH/y7r7jMsPi
g0T8xN17cRgk3tzkpEU+RbXXZeGWvz7H/taZJ0IjRlrMVgpkX3Fhiv4RAoGBAOIx
FGI0P5MPfZYU0fhL0ltDCbRnOmQKcgnY815dkOi6E/7lDAVKygwuVeFBnw68NhWS
VXUMZHD6cL3PLD1nxMtnUCfjoxsgWed3I53Zr9+werQbp7uagXEkbXxC4fLAB5y2
8+n/Gt8lM2kS270fakuENgjF1TG1dgLcHViWFluLAoGBAMLmw51u0UpEltko3xw4
RBC5moC6yEHH2Psan22vsQEWfwCI1uzrYNYttdZ80bnwOSwa1o+HTa6n3gBnA9LA
Mdnekv7Dn5oRWtAXj8foopmC9e4mZCxrJ/wMJH9KxU4yJ8UDQKabUF7AOZGW0vor
EiPzyVLsFw0SfYFrsB9KSsMRAoGAAKb9lQ7rhAQOa6lhtaKaV7MIyyFlFLbG/2pF
wWbprRIkTp7gev9tN73Gd6DV0ZgPW96RKoY/n+fI/XMkgITVF3UT0Rmh9ckRGU7J
poHjNPTwVaaixDK83tOpESusNSQCoZwRdgJLVItp64qnYZM+njsiYMIZTExmq7lw
yDmelOMCgYEA0zMbr8mivE+9eQ6gWL/u2Z6Dgpuzk2LZ25axvjU9rdt4lZEm1WIR
tbfUPsVMsTEJAAUJDHQ73a0aODLMv03HVNcikiCkPg+1cfJYWqyRFVkfM7Txvqxj
1XN5Uv6Y33j3g3xjC73qQG3uqFXPE1NKh9f0Vr4P12H8hp91JjMPITE=
-----END RSA PRIVATE KEY-----"""
private_key = paramiko.RSAKey(file_obj=StringIO(private_str))
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='192.168.4.*',port=22,username='ubuntu',pkey=private_key)
stdin,stdout,stderr = ssh.exec_command('df -Th')

result = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))[0]
print(result)
ssh.close()
transport = paramiko.Transport('192.168.4.*',22)
transport.connect(username='ubuntu',pkey=private_key)

ssh1 = paramiko.SSHClient()
ssh1._transport = transport
stdin,stdout,stderr = ssh1.exec_command('ls -l')
result1 = list(filter(lambda x:x is not None,[stdout.read(),stderr.read()]))
print(result1)
transport.close
基于密钥字符串　　2、SFTPClient
　　用于连接远程服务器并执行上传下载
　　可单个文件上传、下载，亦可批量上传，下载（但无法保持目录结构）





import paramiko,os,sys
from stat import ST_MODE, S_ISREG, S_ISDIR, S_ISLNK
# 定义一个类，表示一台远端linux主机
class Linux(object):
# 通过IP, 用户名，密码，超时时间初始化一个远程Linux主机
def __init__(self, ip, username, password, timeout=30):
self.ip = ip
self.username = username
self.password = password
self.timeout = timeout
# transport和chanel
self.t = ''
self.chan = ''
# 链接失败的重试次数
self.try_times = 3
# 调用该方法连接远程主机
def connect(self):
pass
# 断开连接
def close(self):
pass
# 发送要执行的命令
def send(self, cmd):
pass
# get单个文件
def sftp_get(self, remotefile, localfile):
t = paramiko.Transport(sock=(self.ip, 22))
t.connect(username=self.username, password=self.password)
sftp = paramiko.SFTPClient.from_transport(t)
sftp.get(remotefile, localfile)
t.close()
# put单个文件
def sftp_put(self, localfile, remotefile):
t = paramiko.Transport(sock=(self.ip, 22))
t.connect(username=self.username, password=self.password)
sftp = paramiko.SFTPClient.from_transport(t)
sftp.put(localfile, remotefile)
t.close()
# ------获取远端linux主机上指定目录及其子目录下的所有文件------
def __get_all_files_in_remote_dir(self, sftp, remote_dir):
# 保存所有文件的列表
all_files = list()
# 去掉路径字符串最后的字符'/'，如果有的话
if remote_dir[-1] == '/':
remote_dir = remote_dir[0:-1]
# 获取当前指定目录下的所有目录及文件，包含属性值
files = sftp.listdir_attr(remote_dir)
for x in files:
# remote_dir目录中每一个文件或目录的完整路径
print(x)
print(x.st_mode)
filename = remote_dir + '/' + x.filename
# 如果是目录，则递归处理该目录，这里用到了stat库中的S_ISDIR方法，与linux中的宏的名字完全一致
if S_ISDIR(x.st_mode):
all_files.extend(self.__get_all_files_in_remote_dir(sftp, filename))
else:
all_files.append(filename)
return all_files
def sftp_get_dir(self, remote_dir, local_dir):
t = paramiko.Transport(sock=(self.ip, 22))
t.connect(username=self.username, password=self.password)
sftp = paramiko.SFTPClient.from_transport(t)
# 获取远端linux主机上指定目录及其子目录下的所有文件
all_files = self.__get_all_files_in_remote_dir(sftp, remote_dir)
# 依次get每一个文件
for x in all_files:
filename = x.split('/')[-1]
local_filename = os.path.join(local_dir, filename)
print('Get文件%s传输中...' % filename)
sftp.get(x, local_filename)

# ------获取本地指定目录及其子目录下的所有文件------
def __get_all_files_in_local_dir(self, local_dir):
# 保存所有文件的列表
all_files = list()
# 获取当前指定目录下的所有目录及文件，包含属性值
files = os.listdir(local_dir)
for x in files:
# local_dir目录中每一个文件或目录的完整路径
filename = os.path.join(local_dir, x)
# 如果是目录，则递归处理该目录
if os.path.isdir(x):
all_files.extend(self.__get_all_files_in_local_dir(filename))
else:
all_files.append(filename)
return all_files
def sftp_put_dir(self, local_dir, remote_dir):
t = paramiko.Transport(sock=(self.ip, 22))
t.connect(username=self.username, password=self.password)
sftp = paramiko.SFTPClient.from_transport(t)
# 去掉路径字符穿最后的字符'/'，如果有的话
if remote_dir[-1] == '/':
remote_dir = remote_dir[0:-1]
# 获取本地指定目录及其子目录下的所有文件
all_files = self.__get_all_files_in_local_dir(local_dir)
# 依次put每一个文件
for x in all_files:
filename = os.path.split(x)[-1]
remote_filename = remote_dir + '/' + filename
print('Put文件%s传输中...' % filename)
sftp.put(x, remote_filename)

if __name__ == '__main__':
remotefile = '/home/ubuntu/devops/currency_rate.sh'
localfile = '/home/ubuntu/test/currency_rate.sh'
remote_path = '/home/ubuntu/aa'
local_path = '/home/ubuntu/test'
host = Linux('192.168.4.*', 'ubuntu', '******')
# 将远端的xxoo.txt get到本地，并保存为ooxx.txt
#host.sftp_get(remotefile, localfile)
# # 将本地的xxoo.txt put到远端，并保持为xxoo.txt
# host.sftp_put(localfile, remotefile)

# 将远端remote_path目录中的所有文件get到本地local_path目录
    host.sftp_get_dir(remote_path, local_path)
# # 将本地local_path目录中的所有文件put到远端remote_path目录
#host.sftp_put_dir(remote_path, local_path)
批量上传下载　　二、使用pymysql模块进行mysql数据库操作：

import pymysql

conn = pymysql.connect(host='192.168.4.*',port=3306,
                       user='hzfdt', passwd='****', db='test')
cur = conn.cursor()
cur.execute("USE test")

#cur.execute("insert into students(name,sex,age,tel) values('kai','feman',36,'18069859005')")
#conn.commit()

cur.execute("select * from students")

#cur.scroll(-1,mode='relative')

#cur.scroll(2,mode='absolute')

aa = cur.fetchall()


cur.close()
conn.close()