本文详细介绍使用Python的pymysql库操作MySQL数据库的方法，包括执行SQL、获取自增ID及查询数据等。同时，介绍了Python的ORM框架SQLAlchemy的基本使用，包括底层处理和ORM功能。此外，还讲解了如何利用Paramiko模块实现SSH远程控制，包括基于用户名密码和公钥密钥的连接方式，以及上传下载操作。
今天主要是通过Python来对Mysql数据库进行操作，以及通过paramiko模块远程对主机进行操作，下面开始介绍今天的内容。
一、通过pymsql对数据库进行操作
pymysql是Python中操作MySQL模块，其使用方法和MySQLdb几乎相同。
1、下载安装：
|
1
2
3
4
|
#源码安装：
https:
/
/
pypi.python.org
/
pypi
/
PyMySQL3
#pip安装：
pip install pymysql
|
2、执行SQL：
|
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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
#Author:HaiFeng Di
import
pymysql
#创建连接
conn
=
pymysql.connect(host
=
'127.0.0.1'
,port
=
3306
,user
=
'root'
,passwd
=
'123456'
,db
=
'mydata'
)
#创建游标
cursor
=
conn.cursor()
#执行SQL语句，并返回受影响的行数
#单条数据更新操作
# effect_row = cursor.execute("update hosts set host = '192.168.1.8'")
#添加where条件
# effect_row = cursor.execute("update hosts set host = '192.168.1.8' where id > %s",(1,))
#插入多条数据
effect_row
=
cursor.executemany(
"insert into hosts(id,host)values(%s,%s)"
, [(
6
,
"192.168.1.9"
),(
7
,
"192.168.1.10"
)])
#提交，不然无法保存新建或者修改过的数据
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()
|
3、获取新创建数据自增ID
|
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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import
pymysql
#创建连接
conn
=
pymysql.connect(host
=
'127.0.0.1'
,port
=
3306
,user
=
'root'
,passwd
=
'123456'
,db
=
'mydata'
)
#创建游标
cursor
=
conn.cursor()
#插入多条数据
effect_row
=
cursor.executemany(
"insert into hosts(id,host)values(%s,%s)"
, [(
6
,
"192.168.1.9"
),(
7
,
"192.168.1.10"
)])
#提交，不然无法保存新建或者修改过的数据
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()
#获取最新自增ID
new_id
=
cursor.lastrowid
print
(new_id)
|
4、获取查询数据
|
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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import
pymysql
#创建连接
conn
=
pymysql.connect(host
=
'127.0.0.1'
,port
=
3306
,user
=
'root'
,passwd
=
'123456'
,db
=
'mydata'
)
#创建游标
cursor
=
conn.cursor()
cursor.execute(
"select * from hosts"
)
# 获取第一行数据
# row_1 = cursor.fetchone()
# print(row_1) #获取的结果以元组方式展示
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# print(row_2)
# 获取所有数据
row_3
=
cursor.fetchall()
print
(row_3)
#提交，不然无法保存新建或者修改过的数据
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()
|
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
-
cursor.scroll(1,mode='relative') #相对当前位置移动；
-
cursor.scroll(2,mode='absolute') #相对绝对位置移动。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import
pymysql
conn
=
pymysql.connect(host
=
'127.0.0.1'
,port
=
3306
,user
=
'root'
,passwd
=
'123456'
,db
=
'mydata'
)
cursor
=
conn.cursor()
cursor.execute(
"select * from hosts"
)
cursor.scroll(
4
,mode
=
'relative'
)
# cursor.scroll(4,mode='absolute')
row_1
=
cursor.fetchone()
print
(row_1)
conn.commit()
cursor.close()
conn.close()
|
5，fetch数据类型
关于默认获取的数据类型是元组类型，如果想要转换成字典类型方便处理，请看下面代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import
pymysql
conn
=
pymysql.connect(host
=
'127.0.0.1'
,port
=
3306
,user
=
'root'
,passwd
=
'123456'
,db
=
'mydata'
)
cursor
=
conn.cursor()
# 游标设置为字典类型
cursor
=
conn.cursor(cursor
=
pymysql.cursors.DictCursor)
cursor.execute(
"select * from hosts"
)
row_1
=
cursor.fetchone()
print
(row_1)
conn.commit()
cursor.close()
conn.close()
#结果：
{
'id'
:
1
,
'host'
:
'192.168.10.131'
}
|
二、Python的ORM框架-SQLAlchemy
SQLAlchemy是Python世界中最广泛使用的ORM工具之一，它采用了类似与Java里Hibernate的数据映射模型，而不是其他ORM框架采用的Active Record模型，SQLAlchemy分为两部分，一个是常用的ORM对象映射，另一个是核心的SQL expression。第一个很好理解，纯粹的ORM，后面这个不是ORM，而是DBAPI的封装，通过一些sql表达式来避免了直接写sql语句。简单的概括一下就是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
下面来一下看一下SQLAlchemy的ORM的实现机制，内部封装了些什么：
上图中的Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
MySQL
-
Python
mysql
+
mysqldb:
/
/
<user>:<password>@<host>[:<port>]
/
<dbname>
pymysql
mysql
+
pymysql:
/
/
<username>:<password>@<host>
/
<dbname>[?<options>]
MySQL
-
Connector
mysql
+
mysqlconnector:
/
/
<user>:<password>@<host>[:<port>]
/
<dbname>
cx_Oracle
oracle
+
cx_oracle:
/
/
user:
pass
@host:port
/
dbname[?key
=
value&key
=
value...]
更多详见：http:
/
/
docs.sqlalchemy.org
/
en
/
latest
/
dialects
/
index.html
|
1、底层处理
使用Engine、ConnectionPooling、Dialect进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。
|
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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from
sqlalchemy
import
create_engine
engine
=
create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/mydata"
, max_overflow
=
5
)
#执行SQL
cur
=
engine.execute(
"INSERT INTO hosts (id,host) VALUES ('1.1.1.22', 8)"
)
# 新插入行自增ID
# cur.lastrowid
# 执行SQL
# cur = engine.execute(
# "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
# 执行SQL
# cur = engine.execute(
# "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
# host='1.1.1.99', color_id=3
# )
# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()
|
这种方法底层实际是调用了上面的pymysql模块，没有实现通过实例化对象来对数据库进行操作。
2、ORM功能使用
使用ORM、Schema Type、SQL Expression Language、Engine、ConnectionPooling、Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。
创建表：
|
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
Column, Integer, String, ForeignKey, UniqueConstraint, Index
from
sqlalchemy.orm
import
sessionmaker, relationship
from
sqlalchemy
import
create_engine
#初始化数据库连接
engine
=
create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/t1"
, max_overflow
=
5
)
#创建对象的基类
Base
=
declarative_base()
# 创建单表
class
Users(Base):
__tablename__
=
'users'
#表名
id
=
Column(Integer, primary_key
=
True
)
#表结构
name
=
Column(String(
32
))
extra
=
Column(String(
16
))
__table_args__
=
(
#设置索引
UniqueConstraint(
'id'
,
'name'
, name
=
'uix_id_name'
),
Index(
'ix_id_name'
,
'name'
,
'extra'
),
)
# 一对多
class
Favor(Base):
__tablename__
=
'favor'
nid
=
Column(Integer, primary_key
=
True
)
caption
=
Column(String(
50
), default
=
'red'
, unique
=
True
)
class
Person(Base):
__tablename__
=
'person'
nid
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
32
), index
=
True
, nullable
=
True
)
favor_id
=
Column(Integer, ForeignKey(
"favor.nid"
))
# 多对多
class
Group(Base):
__tablename__
=
'group'
id
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
64
), unique
=
True
, nullable
=
False
)
port
=
Column(Integer, default
=
22
)
class
Server(Base):
__tablename__
=
'server'
id
=
Column(Integer, primary_key
=
True
, autoincrement
=
True
)
hostname
=
Column(String(
64
), unique
=
True
, nullable
=
False
)
class
ServerToGroup(Base):
#将前两张表做关联，创建外键
__tablename__
=
'servertogroup'
nid
=
Column(Integer, primary_key
=
True
, autoincrement
=
True
)
server_id
=
Column(Integer, ForeignKey(
'server.id'
))
group_id
=
Column(Integer, ForeignKey(
'group.id'
))
def
init_db():
"""
创建表
:return:
"""
Base.metadata.create_all(engine)
def
drop_db():
"""
删除表
:return:
"""
Base.metadata.drop_all(engine)
init_db()
|
操作表：
下面例子主要以单表操作为例，分别对数据库进行增删改查，请看下例：
-
增加数据
|
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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
Column, Integer, String, ForeignKey, UniqueConstraint, Index
from
sqlalchemy.orm
import
sessionmaker, relationship
from
sqlalchemy
import
create_engine
#初始化数据库连接
engine
=
create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/mydata"
, max_overflow
=
5
)
#创建对象的基类
Base
=
declarative_base()
# 创建单表
class
Users(Base):
__tablename__
=
'users'
id
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
32
))
__table_args__
=
(
UniqueConstraint(
'id'
,
'name'
, name
=
'uix_id_name'
),
Index(
'ix_id_name'
,
'name'
),
)
def
init_db():
Base.metadata.create_all(engine)
#创建DBSession类型:
DBSession
=
sessionmaker(bind
=
engine)
#创建session对象:
session
=
DBSession()
# 创建新User对象:
# new_user = Users(id='1', name='Bob')
#
# 添加到一条session:
# session.add(new_user)
#添加多条数据
session.add_all([
Users(
id
=
"2"
, name
=
'jack'
),
Users(
id
=
"3"
, name
=
'eric'
),
])
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
Column, Integer, String, ForeignKey, UniqueConstraint, Index
from
sqlalchemy.orm
import
sessionmaker, relationship
from
sqlalchemy
import
create_engine
#初始化数据库连接
engine
=
create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/mydata"
, max_overflow
=
5
)
#创建对象的基类
Base
=
declarative_base()
# 创建单表
class
Users(Base):
__tablename__
=
'users'
id
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
32
))
__table_args__
=
(
UniqueConstraint(
'id'
,
'name'
, name
=
'uix_id_name'
),
Index(
'ix_id_name'
,
'name'
),
)
def
init_db():
Base.metadata.create_all(engine)
#创建DBSession类型:
DBSession
=
sessionmaker(bind
=
engine)
#创建session对象:
session
=
DBSession()
#删除id大于1的数据
session.query(Users).
filter
(Users.
id
>
1
).delete()
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
Column, Integer, String, ForeignKey, UniqueConstraint, Index
from
sqlalchemy.orm
import
sessionmaker, relationship
from
sqlalchemy
import
create_engine
#初始化数据库连接
engine
=
create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/mydata"
, max_overflow
=
5
)
#创建对象的基类
Base
=
declarative_base()
# 创建单表
class
Users(Base):
__tablename__
=
'users'
id
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
32
))
__table_args__
=
(
UniqueConstraint(
'id'
,
'name'
, name
=
'uix_id_name'
),
Index(
'ix_id_name'
,
'name'
),
)
def
init_db():
Base.metadata.create_all(engine)
#创建DBSession类型:
DBSession
=
sessionmaker(bind
=
engine)
#创建session对象:
session
=
DBSession()
# session.query(Users).filter(Users.id > 2).update({"name" : "henry"})
#修改id号，让其加99，结果id号为102
# session.query(Users).filter(Users.id > 2).update({Users.id: Users.id + "099"}, synchronize_session=False)
session.query(Users).
filter
(Users.
id
>
2
).update({
"id"
: Users.
id
+
1
}, synchronize_session
=
"evaluate"
)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.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
|
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from
sqlalchemy.ext.declarative
import
declarative_base
from
sqlalchemy
import
Column, Integer, String, ForeignKey, UniqueConstraint, Index
from
sqlalchemy.orm
import
sessionmaker, relationship
from
sqlalchemy
import
create_engine
#初始化数据库连接
engine
=
create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/mydata"
, max_overflow
=
5
)
#创建对象的基类
Base
=
declarative_base()
# 创建单表
class
Users(Base):
__tablename__
=
'users'
id
=
Column(Integer, primary_key
=
True
)
name
=
Column(String(
32
))
__table_args__
=
(
UniqueConstraint(
'id'
,
'name'
, name
=
'uix_id_name'
),
Index(
'ix_id_name'
,
'name'
),
)
def
init_db():
Base.metadata.create_all(engine)
#创建session对象:
Session
=
sessionmaker(bind
=
engine)
session
=
Session()
#
# for name in session.query(Users).all():
# print(name)
# ret = session.query(Users.id, Users.name).all()
# ret = session.query(Users).filter_by(name='jack').all()
ret
=
session.query(Users).filter_by(name
=
'bob'
).first()
print
(ret.
id
,ret.name)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.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
55
56
57
58
59
60
|
# 条件
ret
=
session.query(Users).filter_by(name
=
'alex'
).
all
()
ret
=
session.query(Users).
filter
(Users.
id
>
1
, Users.name
=
=
'eric'
).
all
()
ret
=
session.query(Users).
filter
(Users.
id
.between(
1
,
3
), Users.name
=
=
'eric'
).
all
()
ret
=
session.query(Users).
filter
(Users.
id
.in_([
1
,
3
,
4
])).
all
()
ret
=
session.query(Users).
filter
(~Users.
id
.in_([
1
,
3
,
4
])).
all
()
ret
=
session.query(Users).
filter
(Users.
id
.in_(session.query(Users.
id
).filter_by(name
=
'eric'
))).
all
()
from
sqlalchemy
import
and_, or_
ret
=
session.query(Users).
filter
(and_(Users.
id
>
3
, Users.name
=
=
'eric'
)).
all
()
ret
=
session.query(Users).
filter
(or_(Users.
id
<
2
, Users.name
=
=
'eric'
)).
all
()
ret
=
session.query(Users).
filter
(
or_(
Users.
id
<
2
,
and_(Users.name
=
=
'eric'
, Users.
id
>
3
),
Users.extra !
=
""
)).
all
()
# 通配符
ret
=
session.query(Users).
filter
(Users.name.like(
'e%'
)).
all
()
ret
=
session.query(Users).
filter
(~Users.name.like(
'e%'
)).
all
()
# 限制
ret
=
session.query(Users)[
1
:
2
]
# 排序
ret
=
session.query(Users).order_by(Users.name.desc()).
all
()
ret
=
session.query(Users).order_by(Users.name.desc(), Users.
id
.asc()).
all
()
# 分组
from
sqlalchemy.sql
import
func
ret
=
session.query(Users).group_by(Users.extra).
all
()
ret
=
session.query(
func.
max
(Users.
id
),
func.
sum
(Users.
id
),
func.
min
(Users.
id
)).group_by(Users.name).
all
()
ret
=
session.query(
func.
max
(Users.
id
),
func.
sum
(Users.
id
),
func.
min
(Users.
id
)).group_by(Users.name).having(func.
min
(Users.
id
) >
2
).
all
()
# 连表
ret
=
session.query(Users, Favor).
filter
(Users.
id
=
=
Favor.nid).
all
()
ret
=
session.query(Person).join(Favor).
all
()
ret
=
session.query(Person).join(Favor, isouter
=
True
).
all
()
# 组合
q1
=
session.query(Users.name).
filter
(Users.
id
>
2
)
q2
=
session.query(Favor.caption).
filter
(Favor.nid <
2
)
ret
=
q1.union(q2).
all
()
q1
=
session.query(Users.name).
filter
(Users.
id
>
2
)
q2
=
session.query(Favor.caption).
filter
(Favor.nid <
2
)
ret
=
q1.union_all(q2).
all
()
|
参考链接：
http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html
http://www.pycoding.com/2016/03/07/sqlalchemy.html
三、Paramiko模块
parmiko模块，基于SSH协议，用于链接远程服务器并执行相关操作。
1，安装使用
Paramiko安装很简单，可以使用pip直接安装：pip3 install paramiko,下面主要介绍一下如何使用：
SSHclient：用于连接远程服务器并执行基本命令
|
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
|
###############################基于用户名密码连接##############################
import
paramiko
#创建SSH对象
ssh
=
paramiko.SSHClient()
#允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
#连接服务器
ssh.connect(hostname
=
'192.168.10.131'
,port
=
22
,username
=
'haifeng'
,password
=
'haifeng'
)
#执行命令
stdin,stdout,stderr
=
ssh.exec_command(
'ls'
)
#获取命令返回结果
result
=
stdout.readlines()
print
(result)
#关闭连接
ssh.close()
#结果：
[
'Desktop\n'
,
'Documents\n'
,
'Downloads\n'
,
'examples.desktop\n'
,
'memcached-1.4.29\n'
,
'Music\n'
,
'netdata-1.0.0\n'
,
'netdata-1.0.0_(1).tar.gz\n'
,
'Pictures\n'
,
'Public\n'
,
'python_script\n'
,
'redis-3.0.6\n'
,
'redis-3.0.6.tar.gz\n'
,
'Templates\n'
,
'Videos\n'
]
|
SSHclient封装Transport：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
import
paramiko
transport
=
paramiko.Transport((
'192.168.10.131'
,
22
))
transport.connect(username
=
'haifeng'
, password
=
'haifeng'
)
ssh
=
paramiko.SSHClient()
ssh._transport
=
transport
stdin, stdout, stderr
=
ssh.exec_command(
'df'
)
print
(stdout.read())
transport.close()
#结果：
b
'Filesystem 1K-blocks Used Available Use% Mounted on\nudev 484420 0 484420 0% /dev\ntmpfs 100748 11616 89132 12% /run\n/dev/sda1 19478204 5967848 12497876 33% /\ntmpfs 503728 352 503376 1% /dev/shm\ntmpfs 5120 4 5116 1% /run/lock\ntmpfs 503728 0 503728 0% /sys/fs/cgroup\ntmpfs 100748 56 100692 1% /run/user/1000\n'
|
基于公钥密钥连接：
|
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
=
'c1.salt.com'
, port
=
22
, username
=
'wupeiqi'
, key
=
private_key)
# 执行命令
stdin, stdout, stderr
=
ssh.exec_command(
'df'
)
# 获取命令结果
result
=
stdout.read()
# 关闭连接
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((
'hostname'
,
22
))
transport.connect(username
=
'wupeiqi'
, 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
13
14
|
###############################基于用户名密码上传下载###########################
import
paramiko
transport
=
paramiko.Transport((
'192.168.10.131'
,
22
))
transport.connect(username
=
'haifeng'
,password
=
'haifeng'
)
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
|
###############################基于公钥密钥上传下载##############################
import
paramiko
private_key
=
paramiko.RSAKey.from_private_key_file(
'/home/auto/.ssh/id_rsa'
)
transport
=
paramiko.Transport((
'hostname'
,
22
))
transport.connect(username
=
'wupeiqi'
, 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()
|
下面是一个上传下载的daemon供参考：
|
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
55
56
57
58
59
60
61
62
63
64
65
66
|
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import
paramiko
import
uuid
class
SSHConnection(
object
):
def
__init__(
self
, host
=
'172.16.103.191'
, port
=
22
, username
=
'wupeiqi'
,pwd
=
'123'
):
self
.host
=
host
self
.port
=
port
self
.username
=
username
self
.pwd
=
pwd
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(
'/home/wupeiqi/tttttttttttt.py'
)
self
.rename(
'/home/wupeiqi/tttttttttttt.py'
, '
/
home
/
wupeiqi
/
ooooooooo.py)
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
,target_path):
# 连接，上传
file_name
=
self
.create_file()
sftp
=
paramiko.SFTPClient.from_transport(
self
.__transport)
# 将location.py 上传至服务器 /tmp/test.py
sftp.put(file_name, target_path)
def
rename(
self
, old_path, new_path):
ssh
=
paramiko.SSHClient()
ssh._transport
=
self
.__transport
# 执行命令
cmd
=
"mv %s %s"
%
(old_path, new_path,)
stdin, stdout, stderr
=
ssh.exec_command(cmd)
# 获取命令结果
result
=
stdout.read()
def
cmd(
self
, command):
ssh
=
paramiko.SSHClient()
ssh._transport
=
self
.__transport
# 执行命令
stdin, stdout, stderr
=
ssh.exec_command(command)
# 获取命令结果
result
=
stdout.read()
return
result
ha
=
SSHConnection()
ha.run()
|
对于更多限制命令，需要在系统中设置：
|
1
2
3
4
5
|
/
etc
/
sudoers
Defaults requiretty
Defaults:cmdb !requiretty
|
扫一扫
636
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
