• Python之路_Day12

    Python之路_Day12_课堂笔记

    上节回顾

    一、线程
    线程
    基本线程使用

    队列-消息队列

    线程池
    进程
    基本使用
    进程数据共享
    进程池
    协程
    更适用IO操作


    二、Memcache、Redis
    Memcache
    集群：
    (C1,1)
    (C2,2)
    (C3,1)
    [C1,C2,C2,C3]
    gets/cas

    Redis
    一、
    默认支持连接池
    支持事务
    发布和订阅
    二、Redis基本操作
    三、自定义
    Redis列表类型
    默认全部取
    根据索引取值



    本节预告

    一、线程池

    二、redis，发布订阅

    三、rabbitMQ

    四、MySQL

    五、python pymysql

    六、Python ORM >SQLAchemy

    七、Paramiko

    八、堡垒机、跳板机



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

    Redis：http://www.cnblogs.com/wupeiqi/articles/5132791.html

    线程池：http://www.cnblogs.com/wupeiqi/articles/4839959.html


    一、线程池

    1、上下文管理

    线程自动关闭示例：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import contextlib
    7. @contextlib.contextmanager
    8. def work_state(state_list, worker_thread):
    9.     state_list.append(work_state)
    10.     try:
    11.         yield
    12.     finally:
    13.         state_list.remove(work_state)
    15. free_list = []
    16. current_thread = 'sandler'
    17. with work_state(free_list, current_thread):
    18.     print(123)
    19.     print(456)

    socket自动关闭示例：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import contextlib
    6. import socket
    8. @contextlib.contextmanager
    9. def context_socket(host, port):
    10.     sk = socket.socket()
    11.     sk.bind((host, port))
    12.     sk.listen(5)
    13.     try:
    14.         yield sk
    15.     finally:
    16.         sk.close()
    18. with context_socket('127.0.0.1', 8888) as sock:
    19.     print(sock)

    2、终止线程池操作




    二、redis，发布订阅

    1、连接池

    2、自定义列表操作

    3、事务的操作-原子性操作

    4、发布订阅

    redis服务端
    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import redis
    7. class RedisHelper:
    9.     def __init__(self):
    10.         self.__conn = redis.Redis(host='192.168.11.87')
    12.     def public(self, msg, chan):
    13.         self.__conn.publish(chan, msg)
    14.         return True
    16.     def subscribe(self, chan):
    17.         pub = self.__conn.pubsub()
    18.         pub.subscribe(chan)
    19.         pub.parse_response()
    20.         return pub

    redis发布端

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import s3
    7. obj = s3.RedisHelper()
    8. obj.public('alex db', 'fm111.7')

    redis订阅端

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import s3
    7. obj = s3.RedisHelper()
    8. data = obj.subscribe('fm111.7')
    9. print(data.parse_response())




    三、rabbitMQ

    RabbitMQ是一个在AMQP基础上完整的，可复用的企业消息系统。他遵循Mozilla Public License开源协议。

    MQ全称为Message Queue, 消息队列（MQ）是一种应用程序对应用程序的通信方法。应用程序通过读写出入队列的消息（针对应用程序的数据）来通信，而无需专用连接来链接它们。消 息传递指的是程序之间通过在消息中发送数据进行通信，而不是通过直接调用彼此来通信，直接调用通常是用于诸如远程过程调用的技术。排队指的是应用程序通过 队列来通信。队列的使用除去了接收和发送应用程序同时执行的要求。


    Python需要安装的模块pika


    生产者消费者示例：

    生产者：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    7. ##############生产者###################
    9. connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87'))
    11. channel = connection.channel()
    13. channel.queue_declare(queue='hello1')
    15. channel.basic_publish(exchange='', routing_key='hello1', body='Hello World!')
    17. print(" [X] Sent 'Hello World!'")
    18. connection.close()

    消费者：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    7. ###############消费者####################
    9. connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87'))
    10. channel = connection.channel()
    12. channel.queue_declare(queue='hello1')
    14. def callback(ch, method, properties, body):
    15.     print('[x] Received %r' % body)
    17. channel.basic_consume(callback, queue='hello1', no_ack=True)
    18. print(' [*] Waiting for messages. To exit press CTRL+C')
    19. channel.start_consuming()


    发布订阅：

    发布者exchange示例：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    6. import sys
    8. #############发布端######################
    10. connection = pika.BlockingConnection(pika.ConnectionParameters(
    11.         host='192.168.11.87'))
    12. channel = connection.channel()
    14. channel.exchange_declare(exchange='logs_fanout',
    15.                          type='fanout')
    17. # message = ' '.join(sys.argv[1:]) or "info: Hello World!"
    18. message = 'test'
    19. channel.basic_publish(exchange='logs_fanout',
    20.                       routing_key='',
    21.                       body=message)
    22. print(" [x] Sent %r" % message)
    23. connection.close()


    订阅者exchange示例：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    7. ##################订阅者#####################
    9. connection = pika.BlockingConnection(pika.ConnectionParameters(
    10.         host='192.168.11.87'))
    11. channel = connection.channel()
    13. channel.exchange_declare(exchange='logs_fanout',
    14.                          type='fanout')
    16. result = channel.queue_declare(exclusive=True)
    17. queue_name = result.method.queue
    19. channel.queue_bind(exchange='logs_fanout',
    20.                    queue=queue_name)
    22. print(' [*] Waiting for logs. To exit press CTRL+C')
    24. def callback(ch, method, properties, body):
    25.     print(" [x] %r" % body)
    27. channel.basic_consume(callback,
    28.                       queue=queue_name,
    29.                       no_ack=True)
    31. channel.start_consuming()


    关键字方式示例：

    生产者：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    6. import sys
    8. #############生产者##############
    10. connection = pika.BlockingConnection(pika.ConnectionParameters(
    11.         host='192.168.11.87'))
    12. channel = connection.channel()
    14. channel.exchange_declare(exchange='direct_logs',
    15.                          type='direct')
    17. # severity = sys.argv[1] if len(sys.argv) > 1 else 'info'
    18. # message = ' '.join(sys.argv[2:]) or 'Hello World!'
    19. severity = 'info'
    20. message = '123'
    21. channel.basic_publish(exchange='direct_logs',
    22.                       routing_key=severity,
    23.                       body=message)
    24. print(" [x] Sent %r:%r" % (severity, message))
    25. connection.close()


    消费者1：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    6. import sys
    8. ################消费者######################
    10. connection = pika.BlockingConnection(pika.ConnectionParameters(
    11.         host='192.168.11.87'))
    12. channel = connection.channel()
    14. channel.exchange_declare(exchange='direct_logs',
    15.                          type='direct')
    17. result = channel.queue_declare(exclusive=True)
    18. queue_name = result.method.queue
    20. # severities = sys.argv[1:]
    21. # if not severities:
    22. #     sys.stderr.write("Usage: %s [info] [warning] [error]
" % sys.argv[0])
    23. #     sys.exit(1)
    24. severities = ['error', 'info', 'warning']
    26. for severity in severities:
    27.     channel.queue_bind(exchange='direct_logs',
    28.                        queue=queue_name,
    29.                        routing_key=severity)
    31. print(' [*] Waiting for logs. To exit press CTRL+C')
    33. def callback(ch, method, properties, body):
    34.     print(" [x] %r:%r" % (method.routing_key, body))
    36. channel.basic_consume(callback,
    37.                       queue=queue_name,
    38.                       no_ack=True)
    40. channel.start_consuming()


    消费者2：

    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    5. import pika
    6. import sys
    8. ################消费者######################
    10. connection = pika.BlockingConnection(pika.ConnectionParameters(
    11.         host='192.168.11.87'))
    12. channel = connection.channel()
    14. channel.exchange_declare(exchange='direct_logs',
    15.                          type='direct')
    17. result = channel.queue_declare(exclusive=True)
    18. queue_name = result.method.queue
    20. # severities = sys.argv[1:]
    21. # if not severities:
    22. #     sys.stderr.write("Usage: %s [info] [warning] [error]
" % sys.argv[0])
    23. #     sys.exit(1)
    24. severities = ['error',]
    26. for severity in severities:
    27.     channel.queue_bind(exchange='direct_logs',
    28.                        queue=queue_name,
    29.                        routing_key=severity)
    31. print(' [*] Waiting for logs. To exit press CTRL+C')
    33. def callback(ch, method, properties, body):
    34.     print(" [x] %r:%r" % (method.routing_key, body))
    36. channel.basic_consume(callback,
    37.                       queue=queue_name,
    38.                       no_ack=True)
    40. channel.start_consuming()





    四、MySQL

    权限管理

    1、创建文件夹和文件：

    数据库、表

    2、文件内部数据

    表内部的数据






    Navicat for MySQL图形化管理MySQL工具


    五、python pymysql

    安装模块：pip3 install pymysql

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


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

    1、下载安装pymysql模块

    1. pip3 install pymysql

    2、使用操作

    执行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()

    获取新创建数据自增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

    获取查询数据：

    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()

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

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

    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()


    六、Python ORM >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

    1、内部处理

    使用 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()

    2、ORM功能使用

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

    创建表：

    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)
    注：设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])


    操作表：

    表结构 + 数据库连接

    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
    8. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
    10. Base = declarative_base()
    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))
    19.     __table_args__ = (
    20.     UniqueConstraint('id', 'name', name='uix_id_name'),
    21.         Index('ix_id_name', 'name', 'extra'),
    22.     )
    24.     def __repr__(self):
    25.         return "%s-%s" %(self.id, self.name)
    27. # 一对多
    28. class Favor(Base):
    29.     __tablename__ = 'favor'
    30.     nid = Column(Integer, primary_key=True)
    31.     caption = Column(String(50), default='red', unique=True)
    33.     def __repr__(self):
    34.         return "%s-%s" %(self.nid, self.caption)
    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')
    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')
    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')
    61. class Server(Base):
    62.     __tablename__ = 'server'
    64.     id = Column(Integer, primary_key=True, autoincrement=True)
    65.     hostname = Column(String(64), unique=True, nullable=False)
    70. def init_db():
    71.     Base.metadata.create_all(engine)
    74. def drop_db():
    75.     Base.metadata.drop_all(engine)
    78. Session = sessionmaker(bind=engine)
    79. session = Session()

    增加

    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()

    删除

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

    修改

    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()

    查找

    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()

    其他

    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()
    19. # 通配符
    20. ret = session.query(Users).filter(Users.name.like('e%')).all()
    21. ret = session.query(Users).filter(~Users.name.like('e%')).all()
    23. # 限制
    24. ret = session.query(Users)[1:2]
    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()
    30. # 分组
    31. from sqlalchemy.sql import func
    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()
    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()
    44. # 连表
    46. ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
    48. ret = session.query(Person).join(Favor).all()
    50. ret = session.query(Person).join(Favor, isouter=True).all()
    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()
    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()


    3、示例：


    1. #!/usr/bin/env python
    2. # -.- coding: utf-8 -.-
    3. # By sandler
    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. from sqlalchemy import create_engine
    10. engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s13", max_overflow=5)
    12. Base = declarative_base()
    14. # 创建单表
    15. class Users(Base):
    16.     __tablename__ = 'users'
    17.     id = Column(Integer, primary_key=True)
    18.     name = Column(String(32))
    19.     extra = Column(String(16))
    21.     __table_args__ = (
    22.     UniqueConstraint('id', 'name', name='uix_id_name'),
    23.         Index('ix_id_name', 'name', 'extra'),
    24.     )
    26. # 一对多
    27. class Favor(Base):
    28.     __tablename__ = 'favor'
    29.     nid = Column(Integer, primary_key=True)
    30.     caption = Column(String(50), default='red', unique=True)
    33. class Person(Base):
    34.     __tablename__ = 'person'
    35.     nid = Column(Integer, primary_key=True)
    36.     name = Column(String(32), index=True, nullable=True)
    37.     favor_id = Column(Integer, ForeignKey("favor.nid"))
    39. # 多对多
    40. class ServerToGroup(Base):
    41.     __tablename__ = 'servertogroup'
    42.     nid = Column(Integer, primary_key=True, autoincrement=True)
    43.     server_id = Column(Integer, ForeignKey('server.id'))
    44.     group_id = Column(Integer, ForeignKey('group.id'))
    46. class Group(Base):
    47.     __tablename__ = 'group'
    48.     id = Column(Integer, primary_key=True)
    49.     name = Column(String(64), unique=True, nullable=False)
    52. class Server(Base):
    53.     __tablename__ = 'server'
    55.     id = Column(Integer, primary_key=True, autoincrement=True)
    56.     hostname = Column(String(64), unique=True, nullable=False)
    57.     port = Column(Integer, default=22)
    59. # Base.metadata.create_all(engine)  # 创建所有
    60. # Base.metadata.drop_all(engine)    # 删除所有






    七、Paramiko





    八、堡垒机、跳板机
  • 相关阅读:
    https://github.com/zyq025/DotNetCoreStudyDemo/blob/main/HttpClientConsoleDemo/Program.cs
    NETCORE TASK多线程的使用
    .NetCore简单封装基于IHttpClientFactory的HttpClient请求
    Netcore中简单使用MemoryCache
    LinQ To Object 基本用法
    SelectMany 和 Select的区别
    List 转换成字符串
    【转】C#中enum的总结
    发个服务端包裹返回值的Response类和用法
    微软企业库 Microsoft Enterprise Library
  • 原文地址：https://www.cnblogs.com/sandler613/p/5720000.html
Copyright © 2020-2023  润新知