python小白-day9 数据库操作与Paramiko模块

paramiko模块

SSHClient

用于连接远程服务器并执行基本命令

基于用户名密码连接：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

import paramiko   # 创建SSH对象 ssh = paramiko.SSHClient() # 允许连接不在know_hosts文件中的主机 ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) # 连接服务器 ssh.connect(hostname = '192.168.11.200' , port = 22 , username = 'hetan' , password = '123456' )   # 执行命令 stdin, stdout, stderr = ssh.exec_command( 'df' ) # 获取命令结果 result = stdout.read() print (result.decode()) # 关闭连接 ssh.close()

SSHClient 封装 Transport：

1 2 3 4 5 6 7 8 9 10 11 12

import paramiko   transport = paramiko.Transport(( '192.168.11.200' , 22 )) transport.connect(username = 'hetan' , password = '123456' )   ssh = paramiko.SSHClient() ssh._transport = transport   stdin, stdout, stderr = ssh.exec_command( 'df' ) print (stdout.read().decode())   transport.close()

基于公钥密钥连接：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

import paramiko    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 = '192.168.11.200' , port = 22 , username = 'hetan' , key = private_key)    # 执行命令 stdin, stdout, stderr = ssh.exec_command( 'df' ) # 获取命令结果 result = stdout.read() print (result.decode()) # 关闭连接 ssh.close()

SSHClient 封装 Transport：

1 2 3 4 5 6 7 8 9 10 11 12 13

import paramiko   private_key = paramiko.RSAKey.from_private_key_file( '/home/auto/.ssh/id_rsa' )   transport = paramiko.Transport(( '192.168.11.200' , 22 )) transport.connect(username = 'hetan' , pkey = private_key)   ssh = paramiko.SSHClient() ssh._transport = transport   stdin, stdout, stderr = ssh.exec_command( 'df' )   transport.close()

SFTPClient

用于连接远程服务器并执行上传下载

基于用户名密码上传下载

1 2 3 4 5 6 7 8 9 10 11 12

import paramiko    transport = paramiko.Transport(( '192.168.11.200' , 22 )) transport.connect(username = 'hetan' ,password = '123456' )    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()

基于公钥密钥上传下载

1 2 3 4 5 6 7 8 9 10 11 12 13 14

import paramiko   private_key = paramiko.RSAKey.from_private_key_file( '/home/auto/.ssh/id_rsa' )   transport = paramiko.Transport(( '192.168.11.200' , 22 )) transport.connect(username = 'hetan' , 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()

上传文件并改名：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

#!/usr/bin/env python import paramiko import uuid   class Haproxy( object ):        def __init__( self ):          self .host = '192.168.11.200'          self .port = 22          self .username = 'hetan'          self .pwd = '123456'          self .__k = None        def create_file( self ):          file_name = str (uuid.uuid4())          with open (file_name, 'w' ) as f:              f.write( 'sb' )          return file_name        def run( self ):          self .connect()          self .upload()          self .rename()          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 upload( self ):          # 连接，上传          file_name = self .create_file()            sftp = paramiko.SFTPClient.from_transport( self .__transport)          # 将location.py 上传至服务器 /tmp/test.py          sftp.put(file_name, '/home/hetan/tttttttttttt.py' )        def rename( self ):            ssh = paramiko.SSHClient()          ssh._transport = self .__transport          # 执行命令          stdin, stdout, stderr = ssh.exec_command( 'mv /home/hetan/tttttttttttt.py /home/hetan/ooooooooo.py' )          # 获取命令结果          result = stdout.read()     ha = Haproxy() ha.run()

数据库操作

Python MySQL API

一、插入数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14

import pymysql     conn = pymysql.connect(host = '192.168.11.200' ,user = 'hetan' ,passwd = '123456' ,db = 'mydb' )     cur = conn.cursor()     reCount = cur.execute( 'insert into students(name,sex,age,tel) values(%s,%s,%s,%s)' ,( 'liuyao' , 'man' , '20' , '1235' ))     conn.commit()     cur.close() conn.close()     print (reCount)

二、批量插入数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

import pymysql     conn = pymysql.connect(host= '192.168.11.200' ,user= 'hetan' ,passwd= '123456' ,db= 'mydb' )     cur = conn.cursor() li = [      ( 'alex' , 'man' , 18 , '1515151' ),      ( 'wupeiqi' , 'man' , 18 , '1551515' ) ]     reCount = cur.executemany( 'insert into students(name,sex,age,tel) values(%s,%s,%s,%s)' ,li)     conn.commit()     cur.close() conn.close()     print(reCount)

三、删除数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14

import pymysql     conn = pymysql.connect(host = '192.168.11.200' ,user = 'hetan' ,passwd = '123456' ,db = 'mydb' )     cur = conn.cursor()   reCount = cur.execute( 'delete from students where id=%s' ,( '1' ,))     conn.commit()     cur.close() conn.close()     print (reCount)

四、修改数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14

import pymysql     conn = pymysql.connect(host = '192.168.11.200' ,user = 'hetan' ,passwd = '123456' ,db = 'mydb' )     cur = conn.cursor()   reCount = cur.execute( 'update students SET name=%s WHERE id=%s' ,( 'hetan' , '2' ,))     conn.commit()     cur.close() conn.close()     print (reCount)

五、查数据​

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

import pymysql     conn = pymysql.connect(host = '192.168.11.200' ,user = 'hetan' ,passwd = '123456' ,db = 'mydb' )     cur = conn.cursor()   reCount = cur.execute( 'select * from students' )     print (cur.fetchone()) print (cur.fetchone()) cur.scroll( - 1 ,mode = 'relative' ) print (cur.fetchone()) print (cur.fetchone()) cur.scroll( 0 ,mode = 'absolute' ) print (cur.fetchone()) print (cur.fetchone()) cur.close() conn.close()     print (reCount)

查询全部：

1 2 3 4 5 6 7 8 9 10 11 12 13

import pymysql     conn = pymysql.connect(host = '192.168.11.200' ,user = 'hetan' ,passwd = '123456' ,db = 'mydb' )     cur = conn.cursor()   reCount = cur.execute( 'select * from students' )     print (cur.fetchall()) cur.close() conn.close()     print (reCount)

​

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/hetan/p/5274227.html