Python之路_Day13

Python之路_Day13_课堂笔记

---

前期回顾

一、redis

发布订阅

二、rabbitMQ

原始队列

exchange

ex全部转发

ex，关键字

ex，模糊匹配

rpc

三、MySQL

四、Python MySQL

pymysql

excute    执行单条语句 ，返回受影响的行数

excutemany    执行多条语句，返回受影响的行数

fetchone

fetchall

fetchmany

scroll

lastrowid

五、SQLAlchemy

ORM框架

db first

code first

====> 我们以后通过类和对象操作数据库

code first

1、自定义生成表

2、使用类操作表

---

本节摘要

一、ORM

连表

一对多

多对多

二、Paramiko模块

链接：

堡垒机

三、前端

HTML

http://www.cnblogs.com/wupeiqi/articles/5699254.html

---

一、ORM—SQLAlchemy

连表

一对多

1、创建表，主动知道外键

2、操作：

类：repr

单表

连表

session.query(表1).join(表2).all()

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
6. from sqlalchemy import create_engine
7. from sqlalchemy.ext.declarative import declarative_base
8. from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
9. from sqlalchemy.orm import sessionmaker, relationship
10. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
12. Base = declarative_base()
14. class Test(Base):
15. __tablename__ = 'test'
16. nid = Column(Integer,primary_key=True,autoincrement=True)
17. name = Column(String(32))
19. class Group(Base):
20. __tablename__ = 'group'
21. nid = Column(Integer,primary_key=True,autoincrement=True)
22. caption = Column(String(32))
25. class User(Base):
26. __tablename__ = 'user'
27. nid = Column(Integer,primary_key=True,autoincrement=True)
28. username = Column(String(32))
29. group_id = Column(Integer,ForeignKey('group.nid'))
31. def __repr__(self):
32. temp = "%s - %s : %s" %(self.nid,self.username,self.group_id)
33. return temp
36. def init_db():
37. Base.metadata.create_all(engine)
39. def drop_db():
40. Base.metadata.drop_all(engine)
42. # init_db()
43. Session = sessionmaker(bind=engine)
44. session = Session()
46. # session.add(Group(caption='dba'))
47. # session.add(Group(caption='ddd'))
48. # session.commit()
50. # session.add_all([
51. # User(username='alex1',group_id=1),
52. # User(username='alex2',group_id=2)
53. # ])
54. # session.commit()
56. # 只是获取用户
57. # ret = session.query(User).filter(User.username == 'alex1').all()
58. # print(ret)
59. # ret = session.query(User).all()
60. # obj = ret[0]
61. # print(ret)
62. # print(obj)
63. # print(obj.nid)
64. # print(obj.username)
65. # print(obj.group_id)
67. # ret = session.query(User.username).all()
68. # print(ret)
69. sql = session.query(User,Group).join(Group, isouter=True)
70. print(sql)
71. ret = session.query(User,Group).join(Group, isouter=True).all()
72. print(ret)
73. # select * from user left join group on user.group_id = group.nid

正反向查找

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
6. from sqlalchemy import create_engine
7. from sqlalchemy.ext.declarative import declarative_base
8. from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
9. from sqlalchemy.orm import sessionmaker, relationship
10. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
12. Base = declarative_base()
14. class Test(Base):
15. __tablename__ = 'test'
16. nid = Column(Integer,primary_key=True,autoincrement=True)
17. name = Column(String(32))
19. class Group(Base):
20. __tablename__ = 'group'
21. nid = Column(Integer,primary_key=True,autoincrement=True)
22. caption = Column(String(32))
25. class User(Base):
26. __tablename__ = 'user'
27. nid = Column(Integer,primary_key=True,autoincrement=True)
28. username = Column(String(32))
29. group_id = Column(Integer,ForeignKey('group.nid'))
30. group = relationship('Group',backref='uuu')
32. def __repr__(self):
33. temp = "%s - %s : %s" %(self.nid,self.username,self.group_id)
34. return temp
37. def init_db():
38. Base.metadata.create_all(engine)
40. def drop_db():
41. Base.metadata.drop_all(engine)
43. # init_db()
44. Session = sessionmaker(bind=engine)
45. session = Session()
47. # session.add(Group(caption='dba'))
48. # session.add(Group(caption='ddd'))
49. # session.commit()
51. # session.add_all([
52. # User(username='alex1',group_id=1),
53. # User(username='alex2',group_id=2)
54. # ])
55. # session.commit()
57. # 只是获取用户
58. # ret = session.query(User).filter(User.username == 'alex1').all()
59. # print(ret)
60. # ret = session.query(User).all()
61. # obj = ret[0]
62. # print(ret)
63. # print(obj)
64. # print(obj.nid)
65. # print(obj.username)
66. # print(obj.group_id)
68. # ret = session.query(User.username).all()
69. # print(ret)
70. # sql = session.query(User,Group).join(Group, isouter=True)
71. # print(sql)
72. # ret = session.query(User,Group).join(Group, isouter=True).all()
73. # print(ret)
74. # select * from user left join group on user.group_id = group.nid
76. # 原始方式
77. # ret = session.query(User.username,Group.caption).join(Group, isouter=True).all()
79. # 新方式（正向查询）
80. # ret = session.query(User).all()
81. # for obj in ret:
82. # obj代指user表的每一行数据
83. # obj.group代指group对象
84. # print(obj.nid,obj.username,obj.group_id,obj.group,obj.group.nid,obj.group.caption)
87. # 原始方式
88. # ret = session.query(User.username,Group.caption).join(Group,isouter=True).filter(Group.caption == 'DBA').all()
91. # 新方式（反向查询）
92. # obj = session.query(Group).filter(Group.caption == 'DBA').first()
93. # print(obj.nid)
94. # print(obj.caption)
95. # print(obj.uuu)

多对多：

1、创建表——额外的关系表

2、filter()

==

in_(都可以是另外一个查询)

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
6. from sqlalchemy import create_engine
7. from sqlalchemy.ext.declarative import declarative_base
8. from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
9. from sqlalchemy.orm import sessionmaker, relationship
10. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
12. Base = declarative_base()
14. ##############多对多######################
16. class Host(Base):
17. __tablename__ = 'host'
18. nid = Column(Integer, primary_key=True,autoincrement=True)
19. hostname = Column(String(32))
20. port = Column(String(32))
21. ip = Column(String(32))
23. class HostUser(Base):
24. __tablename__ = 'host_user'
25. nid = Column(Integer, primary_key=True,autoincrement=True)
26. username = Column(String(32))
28. class HostToHostUser(Base):
29. __tablename__ = 'host_to_host_user'
30. nid = Column(Integer, primary_key=True,autoincrement=True)
32. host_id = Column(Integer,ForeignKey('host.nid'))
33. host_user_id = Column(Integer,ForeignKey('host_user.nid'))
35. def init_db():
36. Base.metadata.create_all(engine)
38. def drop_db():
39. Base.metadata.drop_all(engine)
41. # init_db() # 创建表
44. Session = sessionmaker(bind=engine)
45. session = Session()
47. # session.add_all([
48. # Host(hostname='c1',port='22',ip='1.1.1.1'),
49. # Host(hostname='c2',port='22',ip='1.1.1.2'),
50. # Host(hostname='c3',port='22',ip='1.1.1.3'),
51. # Host(hostname='c4',port='22',ip='1.1.1.4'),
52. # Host(hostname='c5',port='22',ip='1.1.1.5'),
53. # ])
54. # session.commit()
57. # session.add_all([
58. # HostUser(username='root'),
59. # HostUser(username='db'),
60. # HostUser(username='nb'),
61. # HostUser(username='sb'),
62. # ])
63. # session.commit()
65. # session.add_all([
66. # HostToHostUser(host_id=1,host_user_id=1),
67. # HostToHostUser(host_id=1,host_user_id=2),
68. # HostToHostUser(host_id=1,host_user_id=3),
69. # HostToHostUser(host_id=2,host_user_id=2),
70. # HostToHostUser(host_id=2,host_user_id=4),
71. # HostToHostUser(host_id=2,host_user_id=3),
72. # ])
73. # session.commit()
75. # 获取主机1中所有用户
77. host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
78. # host_obj.nid
80. host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()
82. print(host_2_host_user)
83. r = zip(*host_2_host_user)
84. # print(list(r)[0])
86. users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
87. print(users)

3、relationship

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
6. from sqlalchemy import create_engine
7. from sqlalchemy.ext.declarative import declarative_base
8. from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
9. from sqlalchemy.orm import sessionmaker, relationship
10. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
12. Base = declarative_base()
15. ##############多对多######################
17. class Host(Base):
18. __tablename__ = 'host'
19. nid = Column(Integer, primary_key=True,autoincrement=True)
20. hostname = Column(String(32))
21. port = Column(String(32))
22. ip = Column(String(32))
24. class HostUser(Base):
25. __tablename__ = 'host_user'
26. nid = Column(Integer, primary_key=True,autoincrement=True)
27. username = Column(String(32))
29. class HostToHostUser(Base):
30. __tablename__ = 'host_to_host_user'
31. nid = Column(Integer, primary_key=True,autoincrement=True)
33. host_id = Column(Integer,ForeignKey('host.nid'))
34. host_user_id = Column(Integer,ForeignKey('host_user.nid'))
36. host = relationship('Host',backref = 'h')
37. host_user = relationship('HostUser',backref = 'u')
39. def init_db():
40. Base.metadata.create_all(engine)
42. def drop_db():
43. Base.metadata.drop_all(engine)
45. # init_db() # 创建表
48. Session = sessionmaker(bind=engine)
49. session = Session()
51. # session.add_all([
52. # Host(hostname='c1',port='22',ip='1.1.1.1'),
53. # Host(hostname='c2',port='22',ip='1.1.1.2'),
54. # Host(hostname='c3',port='22',ip='1.1.1.3'),
55. # Host(hostname='c4',port='22',ip='1.1.1.4'),
56. # Host(hostname='c5',port='22',ip='1.1.1.5'),
57. # ])
58. #
59. #
60. # session.add_all([
61. # HostUser(username='root'),
62. # HostUser(username='db'),
63. # HostUser(username='nb'),
64. # HostUser(username='sb'),
65. # ])
66. #
67. # session.add_all([
68. # HostToHostUser(host_id=1,host_user_id=1),
69. # HostToHostUser(host_id=1,host_user_id=2),
70. # HostToHostUser(host_id=1,host_user_id=3),
71. # HostToHostUser(host_id=2,host_user_id=2),
72. # HostToHostUser(host_id=2,host_user_id=4),
73. # HostToHostUser(host_id=2,host_user_id=3),
74. # ])
75. # session.commit()
77. # 获取主机1中所有用户
79. # host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
80. # host_obj.nid
82. # host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()
84. # print(host_2_host_user)
85. # r = zip(*host_2_host_user)
86. # print(list(r)[0])
88. # users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
89. # print(users)
91. host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
92. for item in host_obj.h:
93. print(item.host_user.username)

4、更简单的方式

A    关系(B，AB Table对象)

AB == > fk

B

操作时，简单

SQLAlchemy总结：

1、创建表

2、操作表

单表操作

连表操作

.join

关系：

一对多

fk，关系

多对多

多一张表，fk

1、关系表：关系

2、在某一张表：关系；A：关系，（B，AB）

---

二、Paramiko模块

使用Paramiko模块连接远程服务器并执行命令：

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
5. # paramiko模块
7. import paramiko
10. # 普通连接远程主机并执行一条命令
12. # 创建SSH对象
13. ssh = paramiko.SSHClient()
14. # 允许连接不在know_hosts文件中的主机
15. ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
16. # 连接服务器
17. ssh.connect(hostname='192.168.111.2', port=22, username='root', password='111111')
18. # 执行命令
19. stdin, stdout, stderr = ssh.exec_command('ls -l')
20. # 获取命令结果
21. result = stdout.read()
22. print(result)
23. # 关闭连接
24. ssh.close()

通过Paramiko模块一次连接服务器实现执行命令，上传文件等多次操作：

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
5. import paramiko
6. import uuid
8. # 实现一次链接执行命令、上传文件、执行命令
10. class SSHConnection(object):
12. def __init__(self, host='192.168.111.2', port=22, username='root',pwd='111111'):
13. self.host = host
14. self.port = port
15. self.username = username
16. self.pwd = pwd
17. self.__k = None
19. def run(self):
20. self.connect()
21. pass
22. self.close()
24. def connect(self):
25. transport = paramiko.Transport((self.host,self.port))
26. transport.connect(username=self.username,password=self.pwd)
27. self.__transport = transport
29. def close(self):
30. self.__transport.close()
32. def cmd(self, command):
33. ssh = paramiko.SSHClient()
34. ssh._transport = self.__transport
35. # 执行命令
36. stdin, stdout, stderr = ssh.exec_command(command)
37. # 获取命令结果
38. result = stdout.read()
39. return result
41. def upload(self,local_path, target_path):
42. # 连接，上传
43. sftp = paramiko.SFTPClient.from_transport(self.__transport)
44. # 将location.py 上传至服务器 /tmp/test.py
45. sftp.put(local_path, target_path)
47. ssh = SSHConnection()
48. ssh.connect()
50. r1 = ssh.cmd('df')
51. print(r1)
53. ssh.upload('s1.py', "/root/s7.py")
55. ssh.close()

堡垒机，通过回车确定输入内容，并返回结果

（一次输入一条命令返回结果，只可以在Linux上执行）：

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
5. # 堡垒机，通过回车确定输入内容，并返回结果
7. import paramiko
8. import sys
9. import os
10. import socket
11. import select
12. import getpass
13. from paramiko.py3compat import u
15. tran = paramiko.Transport(('192.168.111.2', 22,))
16. tran.start_client()
17. tran.auth_password('root', '111111')
19. # 打开一个通道
20. chan = tran.open_session()
21. # 获取一个终端
22. chan.get_pty()
23. # 激活器
24. chan.invoke_shell()
26. while True:
27. # 监视用户输入和服务器返回数据
28. # sys.stdin 处理用户输入
29. # chan 是之前创建的通道，用于接收服务器返回信息
30. readable, writeable, error = select.select([chan, sys.stdin, ], [], [], 1)
31. if chan in readable:
32. try:
33. x = u(chan.recv(1024))
34. if len(x) == 0:
35. print('\r\n*** EOF\r\n')
36. break
37. sys.stdout.write(x)
38. sys.stdout.flush()
39. except socket.timeout:
40. pass
41. if sys.stdin in readable:
42. inp = sys.stdin.readline()
43. chan.sendall(inp)
45. chan.close()
46. tran.close()

堡垒机，通过回车确定输入内容，并返回结果

（一次输入一个字符，可以通过tab补全，只可以在Linux上执行）：

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
5. import paramiko
6. import sys
7. import os
8. import socket
9. import select
10. import getpass
11. import termios
12. import tty
13. from paramiko.py3compat import u
15. tran = paramiko.Transport(('192.168.111.2', 22,))
16. tran.start_client()
17. tran.auth_password('root', '111111')
19. # 打开一个通道
20. chan = tran.open_session()
21. # 获取一个终端
22. chan.get_pty()
23. # 激活器
24. chan.invoke_shell()
26. # 获取原tty属性
27. oldtty = termios.tcgetattr(sys.stdin)
28. try:
29. # 为tty设置新属性
30. # 默认当前tty设备属性：
31. # 输入一行回车，执行
32. # CTRL+C 进程退出，遇到特殊字符，特殊处理。
34. # 这是为原始模式，不认识所有特殊符号
35. # 放置特殊字符应用在当前终端，如此设置，将所有的用户输入均发送到远程服务器
36. tty.setraw(sys.stdin.fileno())
37. chan.settimeout(0.0)
39. while True:
40. # 监视 用户输入 和 远程服务器返回数据（socket）
41. # 阻塞，直到句柄可读
42. r, w, e = select.select([chan, sys.stdin], [], [], 1)
43. if chan in r:
44. try:
45. x = u(chan.recv(1024))
46. if len(x) == 0:
47. print('\r\n*** EOF\r\n')
48. break
49. sys.stdout.write(x)
50. sys.stdout.flush()
51. except socket.timeout:
52. pass
53. if sys.stdin in r:
54. x = sys.stdin.read(1)
55. if len(x) == 0:
56. break
57. chan.send(x)
59. finally:
60. # 重新设置终端属性
61. termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
63. chan.close()
64. tran.close()

堡垒机，通过回车确定输入内容，并返回结果

（终极版本，可以在windows和linux上执行）：

1. #!/usr/bin/env python
2. # -.- coding:utf-8 -.-
3. # By Sandler
5. import paramiko
6. import sys
7. import os
8. import socket
9. import getpass
11. from paramiko.py3compat import u
13. # windows does not have termios...
14. try:
15. import termios
16. import tty
17. has_termios = True
18. except ImportError:
19. has_termios = False
22. def interactive_shell(chan):
23. if has_termios:
24. posix_shell(chan)
25. else:
26. windows_shell(chan)
29. def posix_shell(chan):
30. import select
32. oldtty = termios.tcgetattr(sys.stdin)
33. try:
34. tty.setraw(sys.stdin.fileno())
35. tty.setcbreak(sys.stdin.fileno())
36. chan.settimeout(0.0)
37. log = open('handle.log', 'a+', encoding='utf-8')
38. flag = False
39. temp_list = []
40. while True:
41. r, w, e = select.select([chan, sys.stdin], [], [])
42. if chan in r:
43. try:
44. x = u(chan.recv(1024))
45. if len(x) == 0:
46. sys.stdout.write('\r\n*** EOF\r\n')
47. break
48. if flag:
49. if x.startswith('\r\n'):
50. pass
51. else:
52. temp_list.append(x)
53. flag = False
54. sys.stdout.write(x)
55. sys.stdout.flush()
56. except socket.timeout:
57. pass
58. if sys.stdin in r:
59. x = sys.stdin.read(1)
60. import json
62. if len(x) == 0:
63. break
65. if x == '\t':
66. flag = True
67. else:
68. temp_list.append(x)
69. if x == '\r':
70. log.write(''.join(temp_list))
71. log.flush()
72. temp_list.clear()
73. chan.send(x)
75. finally:
76. termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
79. def windows_shell(chan):
80. import threading
82. sys.stdout.write("Line-buffered terminal emulation. Press F6 or ^Z to send EOF.\r\n\r\n")
84. def writeall(sock):
85. while True:
86. data = sock.recv(256)
87. if not data:
88. sys.stdout.write('\r\n*** EOF ***\r\n\r\n')
89. sys.stdout.flush()
90. break
91. sys.stdout.write(data)
92. sys.stdout.flush()
94. writer = threading.Thread(target=writeall, args=(chan,))
95. writer.start()
97. try:
98. while True:
99. d = sys.stdin.read(1)
100. if not d:
101. break
102. chan.send(d)
103. except EOFError:
104. # user hit ^Z or F6
105. pass
108. def run():
109. default_username = getpass.getuser()
110. username = input('Username [%s]: ' % default_username)
111. if len(username) == 0:
112. username = default_username
115. hostname = input('Hostname: ')
116. if len(hostname) == 0:
117. print('*** Hostname required.')
118. sys.exit(1)
120. tran = paramiko.Transport((hostname, 22,))
121. tran.start_client()
123. default_auth = "p"
124. auth = input('Auth by (p)assword or (r)sa key[%s] ' % default_auth)
125. if len(auth) == 0:
126. auth = default_auth
128. if auth == 'r':
129. default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')
130. path = input('RSA key [%s]: ' % default_path)
131. if len(path) == 0:
132. path = default_path
133. try:
134. key = paramiko.RSAKey.from_private_key_file(path)
135. except paramiko.PasswordRequiredException:
136. password = getpass.getpass('RSA key password: ')
137. key = paramiko.RSAKey.from_private_key_file(path, password)
138. tran.auth_publickey(username, key)
139. else:
140. pw = getpass.getpass('Password for %s@%s: ' % (username, hostname))
141. tran.auth_password(username, pw)
143. # 打开一个通道
144. chan = tran.open_session()
145. # 获取一个终端
146. chan.get_pty()
147. # 激活器
148. chan.invoke_shell()
150. interactive_shell(chan)
152. chan.close()
153. tran.close()
156. if __name__ == '__main__':
157. run()

链接：

堡垒机

---

---

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/sandler613/p/5744659.html