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

    多对多:
    1、创建表——额外的关系表
    2、filter()
    ==
    in_(都可以是另外一个查询)
    1. #!/usr/bin/env python
    2. # -.- coding:utf-8 -.-
    3. # By Sandler
    4. from sqlalchemy import create_engine
    5. from sqlalchemy.ext.declarative import declarative_base
    6. from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    7. from sqlalchemy.orm import sessionmaker, relationship
    8. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
    9. Base = declarative_base()
    10. ##############多对多######################
    11. class Host(Base):
    12. __tablename__ = 'host'
    13. nid = Column(Integer, primary_key=True,autoincrement=True)
    14. hostname = Column(String(32))
    15. port = Column(String(32))
    16. ip = Column(String(32))
    17. class HostUser(Base):
    18. __tablename__ = 'host_user'
    19. nid = Column(Integer, primary_key=True,autoincrement=True)
    20. username = Column(String(32))
    21. class HostToHostUser(Base):
    22. __tablename__ = 'host_to_host_user'
    23. nid = Column(Integer, primary_key=True,autoincrement=True)
    24. host_id = Column(Integer,ForeignKey('host.nid'))
    25. host_user_id = Column(Integer,ForeignKey('host_user.nid'))
    26. def init_db():
    27. Base.metadata.create_all(engine)
    28. def drop_db():
    29. Base.metadata.drop_all(engine)
    30. # init_db() # 创建表
    31. Session = sessionmaker(bind=engine)
    32. session = Session()
    33. # session.add_all([
    34. # Host(hostname='c1',port='22',ip='1.1.1.1'),
    35. # Host(hostname='c2',port='22',ip='1.1.1.2'),
    36. # Host(hostname='c3',port='22',ip='1.1.1.3'),
    37. # Host(hostname='c4',port='22',ip='1.1.1.4'),
    38. # Host(hostname='c5',port='22',ip='1.1.1.5'),
    39. # ])
    40. # session.commit()
    41. # session.add_all([
    42. # HostUser(username='root'),
    43. # HostUser(username='db'),
    44. # HostUser(username='nb'),
    45. # HostUser(username='sb'),
    46. # ])
    47. # session.commit()
    48. # session.add_all([
    49. # HostToHostUser(host_id=1,host_user_id=1),
    50. # HostToHostUser(host_id=1,host_user_id=2),
    51. # HostToHostUser(host_id=1,host_user_id=3),
    52. # HostToHostUser(host_id=2,host_user_id=2),
    53. # HostToHostUser(host_id=2,host_user_id=4),
    54. # HostToHostUser(host_id=2,host_user_id=3),
    55. # ])
    56. # session.commit()
    57. # 获取主机1中所有用户
    58. host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
    59. # host_obj.nid
    60. host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()
    61. print(host_2_host_user)
    62. r = zip(*host_2_host_user)
    63. # print(list(r)[0])
    64. users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
    65. print(users)

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

    4、更简单的方式
    A    关系(B,AB Table对象)
    AB == > fk
    B
    操作时,简单

    K]RQTGDX6468XZ_A2RB]SU9.png
    2H2UF~P(I)D4[B7]B9E}A87.png
    7GGZ]VV)`05EBYUGC%5EEWT.png
    K3HQY3LRLJT{I~HVW`S[_O6.png
    JXLH4YF{IEM`27~KU{`TZ4D.jpg

    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
    4. # paramiko模块
    5. import paramiko
    6. # 普通连接远程主机并执行一条命令
    7. # 创建SSH对象
    8. ssh = paramiko.SSHClient()
    9. # 允许连接不在know_hosts文件中的主机
    10. ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    11. # 连接服务器
    12. ssh.connect(hostname='192.168.111.2', port=22, username='root', password='111111')
    13. # 执行命令
    14. stdin, stdout, stderr = ssh.exec_command('ls -l')
    15. # 获取命令结果
    16. result = stdout.read()
    17. print(result)
    18. # 关闭连接
    19. ssh.close()

    通过Paramiko模块一次连接服务器实现执行命令,上传文件等多次操作:
    1. #!/usr/bin/env python
    2. # -.- coding:utf-8 -.-
    3. # By Sandler
    4. import paramiko
    5. import uuid
    6. # 实现一次链接执行命令、上传文件、执行命令
    7. class SSHConnection(object):
    8. def __init__(self, host='192.168.111.2', port=22, username='root',pwd='111111'):
    9. self.host = host
    10. self.port = port
    11. self.username = username
    12. self.pwd = pwd
    13. self.__k = None
    14. def run(self):
    15. self.connect()
    16. pass
    17. self.close()
    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. def close(self):
    23. self.__transport.close()
    24. def cmd(self, command):
    25. ssh = paramiko.SSHClient()
    26. ssh._transport = self.__transport
    27. # 执行命令
    28. stdin, stdout, stderr = ssh.exec_command(command)
    29. # 获取命令结果
    30. result = stdout.read()
    31. return result
    32. def upload(self,local_path, target_path):
    33. # 连接,上传
    34. sftp = paramiko.SFTPClient.from_transport(self.__transport)
    35. # 将location.py 上传至服务器 /tmp/test.py
    36. sftp.put(local_path, target_path)
    37. ssh = SSHConnection()
    38. ssh.connect()
    39. r1 = ssh.cmd('df')
    40. print(r1)
    41. ssh.upload('s1.py', "/root/s7.py")
    42. ssh.close()

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

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

    堡垒机,通过回车确定输入内容,并返回结果
    (终极版本,可以在windows和linux上执行):
    1. #!/usr/bin/env python
    2. # -.- coding:utf-8 -.-
    3. # By Sandler
    4. import paramiko
    5. import sys
    6. import os
    7. import socket
    8. import getpass
    9. from paramiko.py3compat import u
    10. # windows does not have termios...
    11. try:
    12. import termios
    13. import tty
    14. has_termios = True
    15. except ImportError:
    16. has_termios = False
    17. def interactive_shell(chan):
    18. if has_termios:
    19. posix_shell(chan)
    20. else:
    21. windows_shell(chan)
    22. def posix_shell(chan):
    23. import select
    24. oldtty = termios.tcgetattr(sys.stdin)
    25. try:
    26. tty.setraw(sys.stdin.fileno())
    27. tty.setcbreak(sys.stdin.fileno())
    28. chan.settimeout(0.0)
    29. log = open('handle.log', 'a+', encoding='utf-8')
    30. flag = False
    31. temp_list = []
    32. while True:
    33. r, w, e = select.select([chan, sys.stdin], [], [])
    34. if chan in r:
    35. try:
    36. x = u(chan.recv(1024))
    37. if len(x) == 0:
    38. sys.stdout.write(' *** EOF ')
    39. break
    40. if flag:
    41. if x.startswith(' '):
    42. pass
    43. else:
    44. temp_list.append(x)
    45. flag = False
    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. import json
    53. if len(x) == 0:
    54. break
    55. if x == ' ':
    56. flag = True
    57. else:
    58. temp_list.append(x)
    59. if x == ' ':
    60. log.write(''.join(temp_list))
    61. log.flush()
    62. temp_list.clear()
    63. chan.send(x)
    64. finally:
    65. termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
    66. def windows_shell(chan):
    67. import threading
    68. sys.stdout.write("Line-buffered terminal emulation. Press F6 or ^Z to send EOF. ")
    69. def writeall(sock):
    70. while True:
    71. data = sock.recv(256)
    72. if not data:
    73. sys.stdout.write(' *** EOF *** ')
    74. sys.stdout.flush()
    75. break
    76. sys.stdout.write(data)
    77. sys.stdout.flush()
    78. writer = threading.Thread(target=writeall, args=(chan,))
    79. writer.start()
    80. try:
    81. while True:
    82. d = sys.stdin.read(1)
    83. if not d:
    84. break
    85. chan.send(d)
    86. except EOFError:
    87. # user hit ^Z or F6
    88. pass
    89. def run():
    90. default_username = getpass.getuser()
    91. username = input('Username [%s]: ' % default_username)
    92. if len(username) == 0:
    93. username = default_username
    94. hostname = input('Hostname: ')
    95. if len(hostname) == 0:
    96. print('*** Hostname required.')
    97. sys.exit(1)
    98. tran = paramiko.Transport((hostname, 22,))
    99. tran.start_client()
    100. default_auth = "p"
    101. auth = input('Auth by (p)assword or (r)sa key[%s] ' % default_auth)
    102. if len(auth) == 0:
    103. auth = default_auth
    104. if auth == 'r':
    105. default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')
    106. path = input('RSA key [%s]: ' % default_path)
    107. if len(path) == 0:
    108. path = default_path
    109. try:
    110. key = paramiko.RSAKey.from_private_key_file(path)
    111. except paramiko.PasswordRequiredException:
    112. password = getpass.getpass('RSA key password: ')
    113. key = paramiko.RSAKey.from_private_key_file(path, password)
    114. tran.auth_publickey(username, key)
    115. else:
    116. pw = getpass.getpass('Password for %s@%s: ' % (username, hostname))
    117. tran.auth_password(username, pw)
    118. # 打开一个通道
    119. chan = tran.open_session()
    120. # 获取一个终端
    121. chan.get_pty()
    122. # 激活器
    123. chan.invoke_shell()
    124. interactive_shell(chan)
    125. chan.close()
    126. tran.close()
    127. if __name__ == '__main__':
    128. run()



    链接:
    堡垒机
    H5VJBC(7MI8BY(]BG(1X7D8.png
















  • 相关阅读:
    gulp备忘
    好文收藏
    妙味H5交互篇备忘
    [CSS3备忘] transform animation 等
    css选择器总结
    flexbox备忘
    函数
    继承2
    在 Swift 中实现单例方法
    浅谈 Swift 中的 Optionals
  • 原文地址:https://www.cnblogs.com/sandler613/p/5744659.html
Copyright © 2020-2023  润新知