python操作MySQL

本篇对于Python操作MySQL主要使用两种方式：

  • 原生模块 pymsql
  • ORM框架 SQLAchemy

pymsql

ymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

下载安装

1 pip3 install pymysql
View Code

使用操作

1、执行SQL

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 # 创建连接
 6 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 7 # 创建游标
 8 cursor = conn.cursor()
 9   
10 # 执行SQL，并返回收影响行数
11 effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
12   
13 # 执行SQL，并返回受影响行数
14 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
15   
16 # 执行SQL，并返回受影响行数
17 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
18   
19   
20 # 提交，不然无法保存新建或者修改的数据
21 conn.commit()
22   
23 # 关闭游标
24 cursor.close()
25 # 关闭连接
26 conn.close()
View Code

2、获取新创建数据自增ID

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6 cursor = conn.cursor()
 7 cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
 8 conn.commit()
 9 cursor.close()
10 conn.close()
11   
12 # 获取最新自增ID
13 new_id = cursor.lastrowid
View Code

3、获取查询数据

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6 cursor = conn.cursor()
 7 cursor.execute("select * from hosts")
 8   
 9 # 获取第一行数据
10 row_1 = cursor.fetchone()
11   
12 # 获取前n行数据
13 # row_2 = cursor.fetchmany(3)
14 # 获取所有数据
15 # row_3 = cursor.fetchall()
16   
17 conn.commit()
18 cursor.close()
19 conn.close()
View Code

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

  • cursor.scroll(1,mode='relative')  # 相对当前位置移动
  • cursor.scroll(2,mode='absolute') # 相对绝对位置移动

4、fetch数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6   
 7 # 游标设置为字典类型
 8 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 9 r = cursor.execute("call p1()")
10   
11 result = cursor.fetchone()
12   
13 conn.commit()
14 cursor.close()
15 conn.close()
View Code

SQLAchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

安装：

1 pip3 install SQLAlchemy

 

 

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

 1 MySQL-Python
 2     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
 3    
 4 pymysql
 5     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
 6    
 7 MySQL-Connector
 8     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
 9    
10 cx_Oracle
11     oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
12    
13 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
View Code

一、内部处理

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 from sqlalchemy import create_engine
 4   
 5   
 6 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
 7   
 8 # 执行SQL
 9 # cur = engine.execute(
10 #     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
11 # )
12   
13 # 新插入行自增ID
14 # cur.lastrowid
15   
16 # 执行SQL
17 # cur = engine.execute(
18 #     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
19 # )
20   
21   
22 # 执行SQL
23 # cur = engine.execute(
24 #     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
25 #     host='1.1.1.99', color_id=3
26 # )
27   
28 # 执行SQL
29 # cur = engine.execute('select * from hosts')
30 # 获取第一行数据
31 # cur.fetchone()
32 # 获取第n行数据
33 # cur.fetchmany(3)
34 # 获取所有数据
35 # cur.fetchall()
View Code

二、ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

1、创建表

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 5 from sqlalchemy.orm import sessionmaker, relationship
 6 from sqlalchemy import create_engine
 7  
 8 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
 9  
10 Base = declarative_base()
11  
12 # 创建单表
13 class Users(Base):
14     __tablename__ = 'users'
15     id = Column(Integer, primary_key=True)
16     name = Column(String(32))
17     extra = Column(String(16))
18  
19     __table_args__ = (
20     UniqueConstraint('id', 'name', name='uix_id_name'),
21         Index('ix_id_name', 'name', 'extra'),
22     )
23  
24  
25 # 一对多
26 class Favor(Base):
27     __tablename__ = 'favor'
28     nid = Column(Integer, primary_key=True)
29     caption = Column(String(50), default='red', unique=True)
30  
31  
32 class Person(Base):
33     __tablename__ = 'person'
34     nid = Column(Integer, primary_key=True)
35     name = Column(String(32), index=True, nullable=True)
36     favor_id = Column(Integer, ForeignKey("favor.nid"))
37  
38  
39 # 多对多
40 class Group(Base):
41     __tablename__ = 'group'
42     id = Column(Integer, primary_key=True)
43     name = Column(String(64), unique=True, nullable=False)
44     port = Column(Integer, default=22)
45  
46  
47 class Server(Base):
48     __tablename__ = 'server'
49  
50     id = Column(Integer, primary_key=True, autoincrement=True)
51     hostname = Column(String(64), unique=True, nullable=False)
52  
53  
54 class ServerToGroup(Base):
55     __tablename__ = 'servertogroup'
56     nid = Column(Integer, primary_key=True, autoincrement=True)
57     server_id = Column(Integer, ForeignKey('server.id'))
58     group_id = Column(Integer, ForeignKey('group.id'))
59  
60  
61 def init_db():
62     Base.metadata.create_all(engine)
63  
64  
65 def drop_db():
66     Base.metadata.drop_all(engine)
View Code

注：设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

2、操作表

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 5 from sqlalchemy.orm import sessionmaker, relationship
 6 from sqlalchemy import create_engine
 7 
 8 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
 9 
10 Base = declarative_base()
11 
12 # 创建单表
13 class Users(Base):
14     __tablename__ = 'users'
15     id = Column(Integer, primary_key=True)
16     name = Column(String(32))
17     extra = Column(String(16))
18 
19     __table_args__ = (
20     UniqueConstraint('id', 'name', name='uix_id_name'),
21         Index('ix_id_name', 'name', 'extra'),
22     )
23 
24     def __repr__(self):
25         return "%s-%s" %(self.id, self.name)
26 
27 # 一对多
28 class Favor(Base):
29     __tablename__ = 'favor'
30     nid = Column(Integer, primary_key=True)
31     caption = Column(String(50), default='red', unique=True)
32 
33     def __repr__(self):
34         return "%s-%s" %(self.nid, self.caption)
35 
36 class Person(Base):
37     __tablename__ = 'person'
38     nid = Column(Integer, primary_key=True)
39     name = Column(String(32), index=True, nullable=True)
40     favor_id = Column(Integer, ForeignKey("favor.nid"))
41     # 与生成表结构无关，仅用于查询方便
42     favor = relationship("Favor", backref='pers')
43 
44 # 多对多
45 class ServerToGroup(Base):
46     __tablename__ = 'servertogroup'
47     nid = Column(Integer, primary_key=True, autoincrement=True)
48     server_id = Column(Integer, ForeignKey('server.id'))
49     group_id = Column(Integer, ForeignKey('group.id'))
50     group = relationship("Group", backref='s2g')
51     server = relationship("Server", backref='s2g')
52 
53 class Group(Base):
54     __tablename__ = 'group'
55     id = Column(Integer, primary_key=True)
56     name = Column(String(64), unique=True, nullable=False)
57     port = Column(Integer, default=22)
58     # group = relationship('Group',secondary=ServerToGroup,backref='host_list')
59 
60 
61 class Server(Base):
62     __tablename__ = 'server'
63 
64     id = Column(Integer, primary_key=True, autoincrement=True)
65     hostname = Column(String(64), unique=True, nullable=False)
66 
67 
68 
69 
70 def init_db():
71     Base.metadata.create_all(engine)
72 
73 
74 def drop_db():
75     Base.metadata.drop_all(engine)
76 
77 
78 Session = sessionmaker(bind=engine)
79 session = Session()
80 
81 表结构 + 数据库连接
表结构 + 数据库连接

增

1 obj = Users(name="alex0", extra='sb')
2 session.add(obj)
3 session.add_all([
4     Users(name="alex1", extra='sb'),
5     Users(name="alex2", extra='sb'),
6 ])
7 session.commit()
View Code

删

1 session.query(Users).filter(Users.id > 2).delete()
2 session.commit()
View Code

改

1 session.query(Users).filter(Users.id > 2).update({"name" : "099"})
2 session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
3 session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
4 session.commit()
View Code

查

1 ret = session.query(Users).all()
2 ret = session.query(Users.name, Users.extra).all()
3 ret = session.query(Users).filter_by(name='alex').all()
4 ret = session.query(Users).filter_by(name='alex').first()
View Code

其他

 1 #　条件
 2 ret = session.query(Users).filter_by(name='alex').all()
 3 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
 4 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
 5 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
 6 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
 7 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
 8 from sqlalchemy import and_, or_
 9 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
10 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
11 ret = session.query(Users).filter(
12     or_(
13         Users.id < 2,
14         and_(Users.name == 'eric', Users.id > 3),
15         Users.extra != ""
16     )).all()
17 
18 
19 # 通配符
20 ret = session.query(Users).filter(Users.name.like('e%')).all()
21 ret = session.query(Users).filter(~Users.name.like('e%')).all()
22 
23 # 限制
24 ret = session.query(Users)[1:2]
25 
26 # 排序
27 ret = session.query(Users).order_by(Users.name.desc()).all()
28 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
29 
30 # 分组
31 from sqlalchemy.sql import func
32 
33 ret = session.query(Users).group_by(Users.extra).all()
34 ret = session.query(
35     func.max(Users.id),
36     func.sum(Users.id),
37     func.min(Users.id)).group_by(Users.name).all()
38 
39 ret = session.query(
40     func.max(Users.id),
41     func.sum(Users.id),
42     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()
43 
44 # 连表
45 
46 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
47 
48 ret = session.query(Person).join(Favor).all()
49 
50 ret = session.query(Person).join(Favor, isouter=True).all()
51 
52 
53 # 组合
54 q1 = session.query(Users.name).filter(Users.id > 2)
55 q2 = session.query(Favor.caption).filter(Favor.nid < 2)
56 ret = q1.union(q2).all()
57 
58 q1 = session.query(Users.name).filter(Users.id > 2)
59 q2 = session.query(Favor.caption).filter(Favor.nid < 2)
60 ret = q1.union_all(q2).all()
View Code

 

更多功能参见文档，猛击这里下载PDF

Paramiko

paramiko模块，基于SSH用于连接远程服务器并执行相关操作。

一、安装

1 pip3 install paramiko

二、使用

SSHClient

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

基于用户名密码连接：

 1 import paramiko
 2    
 3 # 创建SSH对象
 4 ssh = paramiko.SSHClient()
 5 # 允许连接不在know_hosts文件中的主机
 6 ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
 7 # 连接服务器
 8 ssh.connect(hostname='c1.salt.com', port=22, username='wupeiqi', password='123')
 9    
10 # 执行命令
11 stdin, stdout, stderr = ssh.exec_command('ls')
12 # 获取命令结果
13 result = stdout.read()
14    
15 # 关闭连接
16 ssh.close()
View Code
 1 import paramiko
 2 
 3 transport = paramiko.Transport(('hostname', 22))
 4 transport.connect(username='wupeiqi', password='123')
 5 
 6 ssh = paramiko.SSHClient()
 7 ssh._transport = transport
 8 
 9 stdin, stdout, stderr = ssh.exec_command('df')
10 print stdout.read()
11 
12 transport.close()
13 
14 SSHClient 封装 Transport
SSHClient 封装 Transport

基于公钥密钥连接：

 1 import paramiko
 2   
 3 private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa')
 4   
 5 # 创建SSH对象
 6 ssh = paramiko.SSHClient()
 7 # 允许连接不在know_hosts文件中的主机
 8 ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
 9 # 连接服务器
10 ssh.connect(hostname='c1.salt.com', port=22, username='wupeiqi', key=private_key)
11   
12 # 执行命令
13 stdin, stdout, stderr = ssh.exec_command('df')
14 # 获取命令结果
15 result = stdout.read()
16   
17 # 关闭连接
18 ssh.close()
View Code
 1 import paramiko
 2 
 3 private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa')
 4 
 5 transport = paramiko.Transport(('hostname', 22))
 6 transport.connect(username='wupeiqi', pkey=private_key)
 7 
 8 ssh = paramiko.SSHClient()
 9 ssh._transport = transport
10 
11 stdin, stdout, stderr = ssh.exec_command('df')
12 
13 transport.close()
14 
15 SSHClient 封装 Transport
SSHClient 封装 Transport

SFTPClient

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

基于用户名密码上传下载：

 1 import paramiko
 2   
 3 transport = paramiko.Transport(('hostname',22))
 4 transport.connect(username='wupeiqi',password='123')
 5   
 6 sftp = paramiko.SFTPClient.from_transport(transport)
 7 # 将location.py 上传至服务器 /tmp/test.py
 8 sftp.put('/tmp/location.py', '/tmp/test.py')
 9 # 将remove_path 下载到本地 local_path
10 sftp.get('remove_path', 'local_path')
11   
12 transport.close()
View Code

基于公钥密钥上传下载：

 1 import paramiko
 2   
 3 private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa')
 4   
 5 transport = paramiko.Transport(('hostname', 22))
 6 transport.connect(username='wupeiqi', pkey=private_key )
 7   
 8 sftp = paramiko.SFTPClient.from_transport(transport)
 9 # 将location.py 上传至服务器 /tmp/test.py
10 sftp.put('/tmp/location.py', '/tmp/test.py')
11 # 将remove_path 下载到本地 local_path
12 sftp.get('remove_path', 'local_path')
13   
14 transport.close()
View Code
 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import paramiko
 4 import uuid
 5 
 6 class SSHConnection(object):
 7 
 8     def __init__(self, host='172.16.103.191', port=22, username='wupeiqi',pwd='123'):
 9         self.host = host
10         self.port = port
11         self.username = username
12         self.pwd = pwd
13         self.__k = None
14 
15     def create_file(self):
16         file_name = str(uuid.uuid4())
17         with open(file_name,'w') as f:
18             f.write('sb')
19         return file_name
20 
21     def run(self):
22         self.connect()
23         self.upload('/home/wupeiqi/tttttttttttt.py')
24         self.rename('/home/wupeiqi/tttttttttttt.py', '/home/wupeiqi/ooooooooo.py)
25         self.close()
26 
27     def connect(self):
28         transport = paramiko.Transport((self.host,self.port))
29         transport.connect(username=self.username,password=self.pwd)
30         self.__transport = transport
31 
32     def close(self):
33 
34         self.__transport.close()
35 
36     def upload(self,target_path):
37         # 连接，上传
38         file_name = self.create_file()
39 
40         sftp = paramiko.SFTPClient.from_transport(self.__transport)
41         # 将location.py 上传至服务器 /tmp/test.py
42         sftp.put(file_name, target_path)
43 
44     def rename(self, old_path, new_path):
45 
46         ssh = paramiko.SSHClient()
47         ssh._transport = self.__transport
48         # 执行命令
49         cmd = "mv %s %s" % (old_path, new_path,)
50         stdin, stdout, stderr = ssh.exec_command(cmd)
51         # 获取命令结果
52         result = stdout.read()
53 
54     def cmd(self, command):
55         ssh = paramiko.SSHClient()
56         ssh._transport = self.__transport
57         # 执行命令
58         stdin, stdout, stderr = ssh.exec_command(command)
59         # 获取命令结果
60         result = stdout.read()
61         return result
62         
63 
64 
65 ha = SSHConnection()
66 ha.run()
67 
68 Demo
Demo
1 # 对于更多限制命令，需要在系统中设置
2 /etc/sudoers
3   
4 Defaults    requiretty
5 Defaults:cmdb    !requiretty
View Code
 1 import paramiko
 2 import uuid
 3 
 4 class SSHConnection(object):
 5 
 6     def __init__(self, host='192.168.11.61', port=22, username='alex',pwd='alex3714'):
 7         self.host = host
 8         self.port = port
 9         self.username = username
10         self.pwd = pwd
11         self.__k = None
12 
13     def run(self):
14         self.connect()
15         pass
16         self.close()
17 
18     def connect(self):
19         transport = paramiko.Transport((self.host,self.port))
20         transport.connect(username=self.username,password=self.pwd)
21         self.__transport = transport
22 
23     def close(self):
24         self.__transport.close()
25 
26     def cmd(self, command):
27         ssh = paramiko.SSHClient()
28         ssh._transport = self.__transport
29         # 执行命令
30         stdin, stdout, stderr = ssh.exec_command(command)
31         # 获取命令结果
32         result = stdout.read()
33         return result
34 
35     def upload(self,local_path, target_path):
36         # 连接，上传
37         sftp = paramiko.SFTPClient.from_transport(self.__transport)
38         # 将location.py 上传至服务器 /tmp/test.py
39         sftp.put(local_path, target_path)
40 
41 ssh = SSHConnection()
42 ssh.connect()
43 r1 = ssh.cmd('df')
44 ssh.upload('s2.py', "/home/alex/s7.py")
45 ssh.close()
46 
47 Demo
Demo

堡垒机

 

堡垒机执行流程：

  1. 管理员为用户在服务器上创建账号（将公钥放置服务器，或者使用用户名密码）
  2. 用户登陆堡垒机，输入堡垒机用户名密码，现实当前用户管理的服务器列表
  3. 用户选择服务器，并自动登陆
  4. 执行操作并同时将用户操作记录

注：配置.brashrc实现ssh登陆后自动执行脚本，如：/usr/bin/python /home/wupeiqi/menu.py

实现过程

1、前戏

 1 import paramiko
 2 import sys
 3 import os
 4 import socket
 5 import select
 6 import getpass
 7  
 8 tran = paramiko.Transport(('10.211.55.4', 22,))
 9 tran.start_client()
10 tran.auth_password('wupeiqi', '123')
11  
12 # 打开一个通道
13 chan = tran.open_session()
14 # 获取一个终端
15 chan.get_pty()
16 # 激活器
17 chan.invoke_shell()
18  
19 #########
20 # 利用sys.stdin,肆意妄为执行操作
21 # 用户在终端输入内容，并将内容发送至远程服务器
22 # 远程服务器执行命令，并将结果返回
23 # 用户终端显示内容
24 #########
25  
26 chan.close()
27 tran.close()
View Code

2、肆意妄为（一）

 1 import paramiko
 2 import sys
 3 import os
 4 import socket
 5 import select
 6 import getpass
 7 from paramiko.py3compat import u
 8  
 9 tran = paramiko.Transport(('10.211.55.4', 22,))
10 tran.start_client()
11 tran.auth_password('wupeiqi', '123')
12  
13 # 打开一个通道
14 chan = tran.open_session()
15 # 获取一个终端
16 chan.get_pty()
17 # 激活器
18 chan.invoke_shell()
19  
20 while True:
21     # 监视用户输入和服务器返回数据
22     # sys.stdin 处理用户输入
23     # chan 是之前创建的通道，用于接收服务器返回信息
24     readable, writeable, error = select.select([chan, sys.stdin, ],[],[],1)
25     if chan in readable:
26         try:
27             x = u(chan.recv(1024))
28             if len(x) == 0:
29                 print('\r\n*** EOF\r\n')
30                 break
31             sys.stdout.write(x)
32             sys.stdout.flush()
33         except socket.timeout:
34             pass
35     if sys.stdin in readable:
36         inp = sys.stdin.readline()
37         chan.sendall(inp)
38  
39 chan.close()
40 tran.close()
View Code
 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 
 4 import paramiko
 5 import sys
 6 import os
 7 import socket
 8 import select
 9 import getpass
10 from paramiko.py3compat import u
11 
12 
13 default_username = getpass.getuser()
14 username = input('Username [%s]: ' % default_username)
15 if len(username) == 0:
16     username = default_username
17 
18 
19 hostname = input('Hostname: ')
20 if len(hostname) == 0:
21     print('*** Hostname required.')
22     sys.exit(1)
23 
24 tran = paramiko.Transport((hostname, 22,))
25 tran.start_client()
26 
27 default_auth = "p"
28 auth = input('Auth by (p)assword or (r)sa key[%s] ' % default_auth)
29 if len(auth) == 0:
30     auth = default_auth
31 
32 if auth == 'r':
33     default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')
34     path = input('RSA key [%s]: ' % default_path)
35     if len(path) == 0:
36         path = default_path
37     try:
38         key = paramiko.RSAKey.from_private_key_file(path)
39     except paramiko.PasswordRequiredException:
40         password = getpass.getpass('RSA key password: ')
41         key = paramiko.RSAKey.from_private_key_file(path, password)
42     tran.auth_publickey(username, key)
43 else:
44     pw = getpass.getpass('Password for %s@%s: ' % (username, hostname))
45     tran.auth_password(username, pw)
46 
47 
48 
49 # 打开一个通道
50 chan = tran.open_session()
51 # 获取一个终端
52 chan.get_pty()
53 # 激活器
54 chan.invoke_shell()
55 
56 while True:
57     # 监视用户输入和服务器返回数据
58     # sys.stdin 处理用户输入
59     # chan 是之前创建的通道，用于接收服务器返回信息
60     readable, writeable, error = select.select([chan, sys.stdin, ],[],[],1)
61     if chan in readable:
62         try:
63             x = u(chan.recv(1024))
64             if len(x) == 0:
65                 print('\r\n*** EOF\r\n')
66                 break
67             sys.stdout.write(x)
68             sys.stdout.flush()
69         except socket.timeout:
70             pass
71     if sys.stdin in readable:
72         inp = sys.stdin.readline()
73         chan.sendall(inp)
74 
75 chan.close()
76 tran.close()
77 
78 完整示例（一）
完整示例（一）

3、肆意妄为（二）

 1 import paramiko
 2 import sys
 3 import os
 4 import socket
 5 import select
 6 import getpass
 7 import termios
 8 import tty
 9 from paramiko.py3compat import u
10  
11 tran = paramiko.Transport(('10.211.55.4', 22,))
12 tran.start_client()
13 tran.auth_password('wupeiqi', '123')
14  
15 # 打开一个通道
16 chan = tran.open_session()
17 # 获取一个终端
18 chan.get_pty()
19 # 激活器
20 chan.invoke_shell()
21  
22  
23 # 获取原tty属性
24 oldtty = termios.tcgetattr(sys.stdin)
25 try:
26     # 为tty设置新属性
27     # 默认当前tty设备属性：
28     #   输入一行回车，执行
29     #   CTRL+C 进程退出，遇到特殊字符，特殊处理。
30  
31     # 这是为原始模式，不认识所有特殊符号
32     # 放置特殊字符应用在当前终端，如此设置，将所有的用户输入均发送到远程服务器
33     tty.setraw(sys.stdin.fileno())
34     chan.settimeout(0.0)
35  
36     while True:
37         # 监视 用户输入 和 远程服务器返回数据（socket）
38         # 阻塞，直到句柄可读
39         r, w, e = select.select([chan, sys.stdin], [], [], 1)
40         if chan in r:
41             try:
42                 x = u(chan.recv(1024))
43                 if len(x) == 0:
44                     print('\r\n*** EOF\r\n')
45                     break
46                 sys.stdout.write(x)
47                 sys.stdout.flush()
48             except socket.timeout:
49                 pass
50         if sys.stdin in r:
51             x = sys.stdin.read(1)
52             if len(x) == 0:
53                 break
54             chan.send(x)
55  
56 finally:
57     # 重新设置终端属性
58     termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
59  
60  
61 chan.close()
62 tran.close()
View Code
 1 import paramiko
 2 import sys
 3 import os
 4 import socket
 5 import select
 6 import getpass
 7 import termios
 8 import tty
 9 from paramiko.py3compat import u
10 
11 
12 default_username = getpass.getuser()
13 username = input('Username [%s]: ' % default_username)
14 if len(username) == 0:
15     username = default_username
16 
17 
18 hostname = input('Hostname: ')
19 if len(hostname) == 0:
20     print('*** Hostname required.')
21     sys.exit(1)
22 
23 tran = paramiko.Transport((hostname, 22,))
24 tran.start_client()
25 
26 default_auth = "p"
27 auth = input('Auth by (p)assword or (r)sa key[%s] ' % default_auth)
28 if len(auth) == 0:
29     auth = default_auth
30 
31 if auth == 'r':
32     default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')
33     path = input('RSA key [%s]: ' % default_path)
34     if len(path) == 0:
35         path = default_path
36     try:
37         key = paramiko.RSAKey.from_private_key_file(path)
38     except paramiko.PasswordRequiredException:
39         password = getpass.getpass('RSA key password: ')
40         key = paramiko.RSAKey.from_private_key_file(path, password)
41     tran.auth_publickey(username, key)
42 else:
43     pw = getpass.getpass('Password for %s@%s: ' % (username, hostname))
44     tran.auth_password(username, pw)
45 
46 # 打开一个通道
47 chan = tran.open_session()
48 # 获取一个终端
49 chan.get_pty()
50 # 激活器
51 chan.invoke_shell()
52 
53 
54 # 获取原tty属性
55 oldtty = termios.tcgetattr(sys.stdin)
56 try:
57     # 为tty设置新属性
58     # 默认当前tty设备属性：
59     #   输入一行回车，执行
60     #   CTRL+C 进程退出，遇到特殊字符，特殊处理。
61 
62     # 这是为原始模式，不认识所有特殊符号
63     # 放置特殊字符应用在当前终端，如此设置，将所有的用户输入均发送到远程服务器
64     tty.setraw(sys.stdin.fileno())
65     chan.settimeout(0.0)
66 
67     while True:
68         # 监视 用户输入 和 远程服务器返回数据（socket）
69         # 阻塞，直到句柄可读
70         r, w, e = select.select([chan, sys.stdin], [], [], 1)
71         if chan in r:
72             try:
73                 x = u(chan.recv(1024))
74                 if len(x) == 0:
75                     print('\r\n*** EOF\r\n')
76                     break
77                 sys.stdout.write(x)
78                 sys.stdout.flush()
79             except socket.timeout:
80                 pass
81         if sys.stdin in r:
82             x = sys.stdin.read(1)
83             if len(x) == 0:
84                 break
85             chan.send(x)
86 
87 finally:
88     # 重新设置终端属性
89     termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
90 
91 
92 chan.close()
93 tran.close()
94 
95 绝对不改版本
绝对不改版本
  1 import paramiko
  2 import sys
  3 import os
  4 import socket
  5 import getpass
  6 
  7 from paramiko.py3compat import u
  8 
  9 # windows does not have termios...
 10 try:
 11     import termios
 12     import tty
 13     has_termios = True
 14 except ImportError:
 15     has_termios = False
 16 
 17 
 18 def interactive_shell(chan):
 19     if has_termios:
 20         posix_shell(chan)
 21     else:
 22         windows_shell(chan)
 23 
 24 
 25 def posix_shell(chan):
 26     import select
 27 
 28     oldtty = termios.tcgetattr(sys.stdin)
 29     try:
 30         tty.setraw(sys.stdin.fileno())
 31         tty.setcbreak(sys.stdin.fileno())
 32         chan.settimeout(0.0)
 33         log = open('handle.log', 'a+', encoding='utf-8')
 34         flag = False
 35         temp_list = []
 36         while True:
 37             r, w, e = select.select([chan, sys.stdin], [], [])
 38             if chan in r:
 39                 try:
 40                     x = u(chan.recv(1024))
 41                     if len(x) == 0:
 42                         sys.stdout.write('\r\n*** EOF\r\n')
 43                         break
 44                     if flag:
 45                         if x.startswith('\r\n'):
 46                             pass
 47                         else:
 48                             temp_list.append(x)
 49                         flag = False
 50                     sys.stdout.write(x)
 51                     sys.stdout.flush()
 52                 except socket.timeout:
 53                     pass
 54             if sys.stdin in r:
 55                 x = sys.stdin.read(1)
 56                 import json
 57 
 58                 if len(x) == 0:
 59                     break
 60 
 61                 if x == '\t':
 62                     flag = True
 63                 else:
 64                     temp_list.append(x)
 65                 if x == '\r':
 66                     log.write(''.join(temp_list))
 67                     log.flush()
 68                     temp_list.clear()
 69                 chan.send(x)
 70 
 71     finally:
 72         termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
 73 
 74 
 75 def windows_shell(chan):
 76     import threading
 77 
 78     sys.stdout.write("Line-buffered terminal emulation. Press F6 or ^Z to send EOF.\r\n\r\n")
 79 
 80     def writeall(sock):
 81         while True:
 82             data = sock.recv(256)
 83             if not data:
 84                 sys.stdout.write('\r\n*** EOF ***\r\n\r\n')
 85                 sys.stdout.flush()
 86                 break
 87             sys.stdout.write(data)
 88             sys.stdout.flush()
 89 
 90     writer = threading.Thread(target=writeall, args=(chan,))
 91     writer.start()
 92 
 93     try:
 94         while True:
 95             d = sys.stdin.read(1)
 96             if not d:
 97                 break
 98             chan.send(d)
 99     except EOFError:
100         # user hit ^Z or F6
101         pass
102 
103 
104 def run():
105     tran = paramiko.Transport(('10.211.55.4', 22,))
106     tran.start_client()
107     tran.auth_password('wupeiqi', '123')
108 
109     # 打开一个通道
110     chan = tran.open_session()
111     # 获取一个终端
112     chan.get_pty()
113     # 激活器
114     chan.invoke_shell()
115 
116     interactive_shell(chan)
117 
118     chan.close()
119     tran.close()
120 
121 
122 if __name__ == '__main__':
123     run()
124 
125 打死也不改版本
打死也不改版本
  1 import paramiko
  2 import sys
  3 import os
  4 import socket
  5 import getpass
  6 
  7 from paramiko.py3compat import u
  8 
  9 # windows does not have termios...
 10 try:
 11     import termios
 12     import tty
 13     has_termios = True
 14 except ImportError:
 15     has_termios = False
 16 
 17 
 18 def interactive_shell(chan):
 19     if has_termios:
 20         posix_shell(chan)
 21     else:
 22         windows_shell(chan)
 23 
 24 
 25 def posix_shell(chan):
 26     import select
 27 
 28     oldtty = termios.tcgetattr(sys.stdin)
 29     try:
 30         tty.setraw(sys.stdin.fileno())
 31         tty.setcbreak(sys.stdin.fileno())
 32         chan.settimeout(0.0)
 33         log = open('handle.log', 'a+', encoding='utf-8')
 34         flag = False
 35         temp_list = []
 36         while True:
 37             r, w, e = select.select([chan, sys.stdin], [], [])
 38             if chan in r:
 39                 try:
 40                     x = u(chan.recv(1024))
 41                     if len(x) == 0:
 42                         sys.stdout.write('\r\n*** EOF\r\n')
 43                         break
 44                     if flag:
 45                         if x.startswith('\r\n'):
 46                             pass
 47                         else:
 48                             temp_list.append(x)
 49                         flag = False
 50                     sys.stdout.write(x)
 51                     sys.stdout.flush()
 52                 except socket.timeout:
 53                     pass
 54             if sys.stdin in r:
 55                 x = sys.stdin.read(1)
 56                 import json
 57 
 58                 if len(x) == 0:
 59                     break
 60 
 61                 if x == '\t':
 62                     flag = True
 63                 else:
 64                     temp_list.append(x)
 65                 if x == '\r':
 66                     log.write(''.join(temp_list))
 67                     log.flush()
 68                     temp_list.clear()
 69                 chan.send(x)
 70 
 71     finally:
 72         termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
 73 
 74 
 75 def windows_shell(chan):
 76     import threading
 77 
 78     sys.stdout.write("Line-buffered terminal emulation. Press F6 or ^Z to send EOF.\r\n\r\n")
 79 
 80     def writeall(sock):
 81         while True:
 82             data = sock.recv(256)
 83             if not data:
 84                 sys.stdout.write('\r\n*** EOF ***\r\n\r\n')
 85                 sys.stdout.flush()
 86                 break
 87             sys.stdout.write(data)
 88             sys.stdout.flush()
 89 
 90     writer = threading.Thread(target=writeall, args=(chan,))
 91     writer.start()
 92 
 93     try:
 94         while True:
 95             d = sys.stdin.read(1)
 96             if not d:
 97                 break
 98             chan.send(d)
 99     except EOFError:
100         # user hit ^Z or F6
101         pass
102 
103 
104 def run():
105     default_username = getpass.getuser()
106     username = input('Username [%s]: ' % default_username)
107     if len(username) == 0:
108         username = default_username
109 
110 
111     hostname = input('Hostname: ')
112     if len(hostname) == 0:
113         print('*** Hostname required.')
114         sys.exit(1)
115 
116     tran = paramiko.Transport((hostname, 22,))
117     tran.start_client()
118 
119     default_auth = "p"
120     auth = input('Auth by (p)assword or (r)sa key[%s] ' % default_auth)
121     if len(auth) == 0:
122         auth = default_auth
123 
124     if auth == 'r':
125         default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')
126         path = input('RSA key [%s]: ' % default_path)
127         if len(path) == 0:
128             path = default_path
129         try:
130             key = paramiko.RSAKey.from_private_key_file(path)
131         except paramiko.PasswordRequiredException:
132             password = getpass.getpass('RSA key password: ')
133             key = paramiko.RSAKey.from_private_key_file(path, password)
134         tran.auth_publickey(username, key)
135     else:
136         pw = getpass.getpass('Password for %s@%s: ' % (username, hostname))
137         tran.auth_password(username, pw)
138 
139     # 打开一个通道
140     chan = tran.open_session()
141     # 获取一个终端
142     chan.get_pty()
143     # 激活器
144     chan.invoke_shell()
145 
146     interactive_shell(chan)
147 
148     chan.close()
149     tran.close()
150 
151 
152 if __name__ == '__main__':
153     run()
154 
155 终极
终极

多参见：paramoko源码 https://github.com/paramiko/paramiko 

Alex堡垒机：http://www.cnblogs.com/alex3714/articles/5286889.html 

posted @ 2016-08-11 10:10  龙哥1995xyabc  阅读(104)  评论(0)  编辑  收藏  举报