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]\n" % 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]\n" % 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





八、堡垒机、跳板机



















posted @ 2016-07-30 00:08  滚动的沙砾  阅读(245)  评论(0)    收藏  举报