python网络编程进阶 day12
概述
一、线程池
上下文管理
终止线程
二、redis
发布订阅
连接池
服务器列表操作
事物
三、rebbitMQ
基础
四、MySQL
1、创建文件夹和文件
2、文件内部数据
表内部数据
inner join
left join
right join
五、python pymysql
六、sqlachemy
七、python ORM
八、Paramiko
线程池里的with
import contextlib
@contextlib.contextmanager #加上这个装饰器 下面with就可以调用 worker-state函数 管理上下文
def worker_state(state_list,worker_thread):
"""
用于记录线程中正在等待的线程数
:param state_list: 相当于free_list
:param worker_thread: 当前线程
:return:
"""
state_list.append(worker_thread)
try:
yield
finally:
state_list.remove(worker_thread)
free_list = []
current_thread = "alex"
with worker_state(free_list,current_thread):
print(123)
print(456)
#执行顺序：
＃先执行with一行-->然后执行函数worker_state里的state_list.append-->yield-->出去执行with里的print-->再回到函数里执行finally下的state_list.remove
自定义用with管理socket，不用关闭
import contextlib
import socket
@contextlib.contextmanager
def context_socket(host,port):
sk = socket.socket()
sk.bind((host,port))
sk.listen(5)
try:
yield sk #相当于把sk返回给as sock的sock建立了连接
finally:
sk.close()
with context_socket('127.0.0.1',8888) as sock:
print(sock)
以后如果涉及到需要close就可以用with不用关闭
终止线程
设置空标识
上面把self.terminal 设置为False，如果想停止线程，就设置为True，让event为空。
import queue
import threading
import contextlib
import time
StopEvent = object() #可以设置为空，是为了终止进程
class ThreadPool():
def __init__(self, max_num, max_task_num = None):
if max_task_num: #如果max_task_num有值就创建队列
self.q = queue.Queue(max_task_num) #创建队列，最多放max_task_num个任务
else: #若max_task_num无值，则不定义最大长度
self.q = queue.Queue() #创建先进先出队列
self.max_num = max_num #表示最多有多少个线程
self.cancel = False
self.terminal = False
self.generate_list = [] #表示当前已经创建多少个线程
self.free_list = [] #当前还空闲多少个线程
def run(self, func, args, callback=None):
"""
线程池执行一个任务
:param func: 任务函数
:param args: 任务函数所需参数
:param callback: 任务执行失败或成功后执行的回调函数，回调函数有两个参数1、任务函数执行状态；2、任务函数返回值（默认为None，即：不执行回调函数）
:return: 如果线程池已经终止，则返回True否则None
"""
if self.cancel:
return
# 判断 没有空闲线程并且已经创建的线程小于最大线程数
if len(self.free_list) == 0 and len(self.generate_list) < self.max_num:
self.generate_thread() #调用创建线程的方法
w = (func, args, callback,) #把传过来的三个参数放到一个元组里
self.q.put(w) #把任务放到队列中
def generate_thread(self):
"""
创建一个线程
"""
t = threading.Thread(target=self.call)#创建的线程执行call方法
t.start() #执行
def call(self):
"""
循环去获取任务函数并执行任务函数
"""
current_thread = threading.currentThread #返回当前线程对象
self.generate_list.append(current_thread) #把当前创建的线程放到列表里
event = self.q.get() #获取队列中的任务
while event != StopEvent: #任务不等于后面的值就会一直循环
func, arguments, callback = event
try: #捕捉异常
result = func(*arguments) #执行action函数
success = True
except Exception as e:
success = False
result = None
if callback is not None:
try: #捕捉异常
callback(success, result)
except Exception as e:
pass
#使线程变为空闲，以便下次再取任务
#等待着，等待着......
#有任务来了
#当前线程状态，不再空闲
with self.worker_state(self.free_list, current_thread):
if self.terminal: ###########让线程终止############
event = StopEvent
else:
event = self.q.get()
else:
self.generate_list.remove(current_thread) #移除当前线程 终止线程
def close(self):
"""
执行完所有的任务后，所有线程停止
"""
self.cancel = True
full_size = len(self.generate_list) #获取创建的线程个数
while full_size:
self.q.put(StopEvent) #往队列里放入空值，空值的个数和创建线程的个数相等
full_size -= 1
def terminate(self):
"""
无论是否还有任务，终止线程
"""
self.terminal = True
while self.generate_list: #若列表还有创建的线程
self.q.put(StopEvent) #把空值放入队列中
self.q.empty() #队列为空返回True，否则返回False
@contextlib.contextmanager
def worker_state(self, state_list, worker_thread):
"""
用于记录线程中正在等待的线程数
"""
state_list.append(worker_thread)
try:
yield
finally:
state_list.remove(worker_thread)
pool = ThreadPool(5) #线程池里只能放5个
def callback(status, result):
# status, execute action status
# result, execute action return value
pass
def action(i):
print(i)
for i in range(300):
ret = pool.run(action, (i,), callback) #执行run方法，参数（函数，i,函数名）
rabbitMQ
RabbitMQ是一个在AMQP基础上完整的，可复用的企业消息系统。他遵循Mozilla Public License开源协议。
MQ全称为Message Queue, 消息队列（MQ）是一种应用程序对应用程序的通信方法。应用程序通过读写出入队列的消息（针对应用程序的数据）来通信，而无需专用连接来链接它们。消 息传递指的是程序之间通过在消息中发送数据进行通信，而不是通过直接调用彼此来通信，直接调用通常是用于诸如远程过程调用的技术。排队指的是应用程序通过 队列来通信。队列的使用除去了接收和发送应用程序同时执行的要求。
RabbitMQ安装
安装配置epel源 $ rpm -ivh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm 安装erlang $ yum -y install erlang 安装RabbitMQ $ yum -y install rabbitmq-server
启动停止服务：service rabbitmq-server start/stop
安装API
pip install pika or easy_install pika or 源码 https://pypi.python.org/pypi/pika
使用API操作RabbitMQ
可以基于Queue实现生产者消费者模型
对于RabbitMQ来说，生产和消费不再针对内存里的一个Queue对象，而是某台服务器上的RabbitMQ Server实现的消息队列。
import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) channel = connection.channel() channel.queue_declare(queue='hello2') #ch=频道，method=队列名字，properties=属性,body=取到的内容 def callback(ch,method,properties,body): print("[x]Received%r"%body) #到hello2中取数据，取完调用callback函数 channel.basic_consume(callback,queue='hello2',no_ack=True) print('[*]Waiting for messages. To exit press CTRL+C') channel.start_consuming()
import pika #创建连接 connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) #创建频道 channel = connection.channel() #定义队列，如果存在忽略，不存在就创建 channel.queue_declare(queue='hello2') #往hello2队列中发内容hello world！ channel.basic_publish(exchange='',routing_key='hello2',body='hello world!') print("[x]Sent 'hello world!'") connection.close()
acknowledgment 消息不丢失
no-ack ＝ False，如果消费者遇到情况(its channel is closed, connection is closed, or TCP connection is lost)挂掉了，那么，RabbitMQ会重新将该任务添加到队列中。
import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) channel = connection.channel() channel.exchange_declare(exchange='logs_fanout',type='fanout') # 随机创建队列 result = channel.queue_declare(exclusive=True) queue_name = result.method.queue # exchange和队列做绑定 channel.queue_bind(exchange='logs_fanout',queue=queue_name) print(' [*] Waiting for logs. To exit press CTRL+C') def callback(ch, method, properties, body): print(" [x] %r" % body) channel.basic_consume(callback,queue=queue_name,no_ack=True) channel.start_consuming()
情况一：no_ack=true （此时为自动应答）
在这种情况下，consumer 会在接收到 Basic.Deliver + Content-Header + Content-Body 之后，立即回复 Ack 。而这个 Ack 是 TCP 协议中的 Ack 。此 Ack 的回复不关心 consumer 是否对接收到的数据进行了处理，当然也不关心处理数据所需要的耗时。
情况二：no_ack=false （此时为手动应答）
在这种情况下，要求 consumer 在处理完接收到的 Basic.Deliver + Content-Header + Content-Body 之后才回复 Ack 。而这个 Ack 是 AMQP 协议中的 Basic.Ack 。此 Ack 的回复是和业务处理相关的，所以具体的回复时间应该要取决于业务处理的耗时。
随机创建队列
import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) channel = connection.channel() channel.exchange_declare(exchange='logs_fanout',type='fanout') # 随机创建队列 result = channel.queue_declare(exclusive=True) queue_name = result.method.queue # exchange和队列做绑定 channel.queue_bind(exchange='logs_fanout',queue=queue_name) print(' [*] Waiting for logs. To exit press CTRL+C') def callback(ch, method, properties, body): print(" [x] %r" % body) channel.basic_consume(callback,queue=queue_name,no_ack=True) channel.start_consuming()
import pika #创建连接 connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) #创建频道 channel = connection.channel() #创建exchange channel.exchange_declare(exchange='logs_fanout',type='fanout') message = '123' #发布 channel.basic_publish(exchange='logs_fanout',routing_key='',body=message) print("[x] Sent %r"%message) connection.close()
消息获取顺序
默认消息队列里的数据是按照顺序被消费者拿走，例如：消费者1 去队列中获取 奇数 序列的任务，消费者1去队列中获取 偶数 序列的任务。
channel.basic_qos(prefetch_count=1) 表示谁来谁取，不再按照奇偶数排列
#!/usr/bin/env python # -*- coding:utf-8 -*- import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='10.211.55.4')) channel = connection.channel() # make message persistent channel.queue_declare(queue='hello') def callback(ch, method, properties, body): print(" [x] Received %r" % body) import time time.sleep(10) print 'ok' ch.basic_ack(delivery_tag = method.delivery_tag) channel.basic_qos(prefetch_count=1) channel.basic_consume(callback, queue='hello', no_ack=False) print(' [*] Waiting for messages. To exit press CTRL+C') channel.start_consuming()
发布订阅
发布订阅和简单的消息队列区别在于，发布订阅会将消息发送给所有的订阅者，而消息队列中的数据被消费一次便消失。所以，RabbitMQ实现发布和订阅时，会为每一个订阅者创建一个队列，而发布者发布消息时，会将消息放置在所有相关队列中。
exchange type = fanout
#!/usr/bin/env python import pika import sys connection = pika.BlockingConnection(pika.ConnectionParameters( host='localhost')) channel = connection.channel() channel.exchange_declare(exchange='logs', type='fanout') message = ' '.join(sys.argv[1:]) or "info: Hello World!" channel.basic_publish(exchange='logs', routing_key='', body=message) print(" [x] Sent %r" % message) connection.close()
#!/usr/bin/env python import pika connection = pika.BlockingConnection(pika.ConnectionParameters( host='localhost')) channel = connection.channel() channel.exchange_declare(exchange='logs', type='fanout') result = channel.queue_declare(exclusive=True) queue_name = result.method.queue channel.queue_bind(exchange='logs', queue=queue_name) print(' [*] Waiting for logs. To exit press CTRL+C') def callback(ch, method, properties, body): print(" [x] %r" % body) channel.basic_consume(callback, queue=queue_name, no_ack=True) channel.start_consuming()
关键字发送
exchange type = direct
之前事例，发送消息时明确指定某个队列并向其中发送消息，RabbitMQ还支持根据关键字发送，即：队列绑定关键字，发送者将数据根据关键字发送到消息exchange，exchange根据 关键字 判定应该将数据发送至指定队列。
import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) channel = connection.channel() channel.exchange_declare(exchange='direct_logs_test',type='direct') result = channel.queue_declare(exclusive=True) queque_name = result.method.queue severities = ['error','info','warning'] for severity in severities: channel.queue_bind(exchange='direct_logs_test',queue=queque_name,routing_key=severity) print('[*] Waiting for logs. To exit press CTRL+C') def calback(ch,method,properties,body): print("[x] %r:%r"%(method.routing_key,body)) channel.basic_consume(calback,queue=queque_name,no_ack=True) channel.start_consuming()
import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) channet = connection.channel() channet.exchange_declare(exchange='direct_logs_test',type='direct') result = channet.queue_declare(exclusive=True) queque_name = result.method.queue severities = ['error',] for severity in severities: channet.queue_bind(exchange='direct_logs_test',queue=queque_name,routing_key=severity) print('[*] Waiting for logs. To exit press CTRL+C') def calback(ch,method,properties,body): print("[x] %r:%r"%(method.routing_key,body)) channet.basic_consume(calback,queue=queque_name,no_ack=True) channet.start_consuming()
import pika connection = pika.BlockingConnection(pika.ConnectionParameters(host='192.168.11.87')) channel = connection.channel() channel.exchange_declare(exchange='direct_logs_test',type='direct') severity = 'error' message = '123' channel.basic_publish(exchange='direct_logs_test',routing_key=severity,body=message) print("[x] Sent %r:%r"%(severity,message)) connection.close()
模糊匹配
exchange type = topic
在topic类型下，可以让队列绑定几个模糊的关键字，之后发送者将数据发送到exchange，exchange将传入”路由值“和 ”关键字“进行匹配，匹配成功，则将数据发送到指定队列。
- # 表示可以匹配 0 个 或 多个 单词
- * 表示只能匹配 一个 单词
发送者路由值 队列中 old.boy.python old.* -- 不匹配 old.boy.python old.# -- 匹配
#!/usr/bin/env python import pika import sys connection = pika.BlockingConnection(pika.ConnectionParameters( host='localhost')) channel = connection.channel() channel.exchange_declare(exchange='topic_logs', type='topic') result = channel.queue_declare(exclusive=True) queue_name = result.method.queue binding_keys = sys.argv[1:] if not binding_keys: sys.stderr.write("Usage: %s [binding_key]...\n" % sys.argv[0]) sys.exit(1) for binding_key in binding_keys: channel.queue_bind(exchange='topic_logs', queue=queue_name, routing_key=binding_key) print(' [*] Waiting for logs. To exit press CTRL+C') def callback(ch, method, properties, body): print(" [x] %r:%r" % (method.routing_key, body)) channel.basic_consume(callback, queue=queue_name, no_ack=True) channel.start_consuming()
#!/usr/bin/env python import pika import sys connection = pika.BlockingConnection(pika.ConnectionParameters( host='localhost')) channel = connection.channel() channel.exchange_declare(exchange='topic_logs', type='topic') routing_key = sys.argv[1] if len(sys.argv) > 1 else 'anonymous.info' message = ' '.join(sys.argv[2:]) or 'Hello World!' channel.basic_publish(exchange='topic_logs', routing_key=routing_key, body=message) print(" [x] Sent %r:%r" % (routing_key, message)) connection.close()
Demo:
##############producer.py################
import pika
import sys
#创建连接connection到localhost
con = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
#创建虚拟连接channel
cha = con.channel()
#创建队列anheng,durable参数为真时，队列将持久化；exclusive为真时，建立临时队列
result=cha.queue_declare(queue='anheng',durable=True,exclusive=False)
#创建名为yanfa,类型为fanout的exchange，其他类型还有direct和topic，如果指定durable为真，exchange将持久化
cha.exchange_declare(durable=False,
exchange='yanfa',
type='direct',)
#绑定exchange和queue,result.method.queue获取的是队列名称
cha.queue_bind(exchange='yanfa',
queue=result.method.queue,
routing_key='',)
#公平分发，使每个consumer在同一时间最多处理一个message，收到ack前，不会分配新的message
cha.basic_qos(prefetch_count=)
#发送信息到队列‘anheng'
message = ' '.join(sys.argv[:])
#消息持久化指定delivery_mode=；
cha.basic_publish(exchange='',
routing_key='anheng',
body=message,
properties=pika.BasicProperties(
delivery_mode = ,
))
print '[x] Sent %r' % (message,)
#关闭连接
con.close()
################consumer.py##################
# 博客 http://www.cnblogs.com/duanv/
import pika
#建立连接connection到localhost
con = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
#创建虚拟连接channel
cha = con.channel()
#创建队列anheng
result=cha.queue_declare(queue='anheng',durable=True)
#创建名为yanfa,类型为fanout的交换机，其他类型还有direct和topic
cha.exchange_declare(durable=False,
exchange='yanfa',
type='direct',)
#绑定exchange和queue,result.method.queue获取的是队列名称
cha.queue_bind(exchange='yanfa',
queue=result.method.queue,
routing_key='',)
#公平分发，使每个consumer在同一时间最多处理一个message，收到ack前，不会分配新的message
cha.basic_qos(prefetch_count=)
print ' [*] Waiting for messages. To exit press CTRL+C'
#定义回调函数
def callback(ch, method, properties, body):
print " [x] Received %r" % (body,)
ch.basic_ack(delivery_tag = method.delivery_tag)
cha.basic_consume(callback,
queue='anheng',
no_ack=False,)
cha.start_consuming()
一、概念：
Connection： 一个TCP的连接。Producer和Consumer都是通过TCP连接到RabbitMQ Server的。程序的起始处就是建立这个TCP连接。
Channels： 虚拟连接。建立在上述的TCP连接中。数据流动都是在Channel中进行的。一般情况是程序起始建立TCP连接，第二步就是建立这个Channel。
二、队列：
首先建立一个Connection，然后建立Channels，在channel上建立队列
建立时指定durable参数为真，队列将持久化；指定exclusive为真，队列为临时队列，关闭consumer后该队列将不再存在，一般情况下建立临时队列并不指定队列名称，rabbitmq将随机起名，通过result.method.queue来获取队列名：
result = channel.queue_declare(exclusive=True)
result.method.queue
区别：durable是队列持久化与否，如果为真，队列将在rabbitmq服务重启后仍存在，如果为假，rabbitmq服务重启前不会消失，与consumer关闭与否无关；
而exclusive是建立临时队列，当consumer关闭后，该队列就会被删除
三、exchange和bind
Exchange中durable参数指定exchange是否持久化，exchange参数指定exchange名称，type指定exchange类型。Exchange类型有direct，fanout和topic。
Bind是将exchange与queue进行关联，exchange参数和queue参数分别指定要进行bind的exchange和queue，routing_key为可选参数。
Exchange的三种模式：
Direct：
任何发送到Direct Exchange的消息都会被转发到routing_key中指定的Queue 1.一般情况可以使用rabbitMQ自带的Exchange：””(该Exchange的名字为空字符串)； 2.这种模式下不需要将Exchange进行任何绑定(bind)操作； 3.消息传递时需要一个“routing_key”，可以简单的理解为要发送到的队列名字； 4.如果vhost中不存在routing_key中指定的队列名，则该消息会被抛弃。 Demo中虽然声明了一个exchange='yanfa'和queue='anheng'的bind，但是在后面发送消息时并没有使用该exchange和bind，而是采用了direct的模式，没有指定exchange，而是指定了routing_key的名称为队列名，消息将发送到指定队列。 如果一个exchange 声明为direct，并且bind中指定了routing_key,那么发送消息时需要同时指明该exchange和routing_key.
Fanout:
任何发送到Fanout Exchange的消息都会被转发到与该Exchange绑定(Binding)的所有Queue上 1.可以理解为路由表的模式 2.这种模式不需要routing_key 3.这种模式需要提前将Exchange与Queue进行绑定，一个Exchange可以绑定多个Queue，一个Queue可以同多个Exchange进行绑定。 4.如果接受到消息的Exchange没有与任何Queue绑定，则消息会被抛弃。 Demo中创建了一个将一个exchange和一个queue进行fanout类型的bind.但是发送信息时没有用到它，如果要用到它，只要在发送消息时指定该exchange的名称即可，该exchange就会将消息发送到所有和它bind的队列中。在fanout模式下，指定的routing_key是无效的 。
Topic：
任何发送到Topic Exchange的消息都会被转发到所有关心routing_key中指定话题的Queue上 1.这种模式较为复杂，简单来说，就是每个队列都有其关心的主题，所有的消息都带有一个“标题”(routing_key)，Exchange会将消息转发到所有关注主题能与routing_key模糊匹配的队列。 2.这种模式需要routing_key，也许要提前绑定Exchange与Queue。 3.在进行绑定时，要提供一个该队列关心的主题，如“#.log.#”表示该队列关心所有涉及log的消息(一个routing_key为”MQ.log.error”的消息会被转发到该队列)。 4.“#”表示0个或若干个关键字，“*”表示一个关键字。如“log.*”能与“log.warn”匹配，无法与“log.warn.timeout”匹配；但是“log.#”能与上述两者匹配。 5.同样，如果Exchange没有发现能够与routing_key匹配的Queue，则会抛弃此消息。
四、任务分发
1.Rabbitmq的任务是循环分发的，如果开启两个consumer，producer发送的信息是轮流发送到两个consume的。 2.在producer端使用cha.basic_publish()来发送消息，其中body参数就是要发送的消息，properties=pika.BasicProperties(delivery_mode = 2,)启用消息持久化，可以防止RabbitMQ Server 重启或者crash引起的数据丢失。 3.在接收端使用cha.basic_consume()无限循环监听，如果设置no-ack参数为真，每次Consumer接到数据后，而不管是否处理完成，RabbitMQ Server会立即把这个Message标记为完成，然后从queue中删除了。为了保证数据不被丢失，RabbitMQ支持消息确认机制，即acknowledgments。为了保证数据能被正确处理而不仅仅是被Consumer收到，那么我们不能采用no-ack。而应该是在处理完数据后发送ack。 在处理数据后发送的ack，就是告诉RabbitMQ数据已经被接收，处理完成，RabbitMQ可以去安全的删除它了。如果Consumer退出了但是没有发送ack，那么RabbitMQ就会把这个Message发送到下一个Consumer。这样就保证了在Consumer异常退出的情况下数据也不会丢失。 这里并没有用到超时机制。RabbitMQ仅仅通过Consumer的连接中断来确认该Message并没有被正确处理。也就是说，RabbitMQ给了Consumer足够长的时间来做数据处理。 Demo的callback方法中ch.basic_ack(delivery_tag = method.delivery_tag)告诉rabbitmq消息已经正确处理。如果没有这条代码，Consumer退出时，Message会重新分发。然后RabbitMQ会占用越来越多的内存，由于RabbitMQ会长时间运行，因此这个“内存泄漏”是致命的。去调试这种错误，可以通过一下命令打印un-acked Messages： sudo rabbitmqctl list_queues name messages_ready messages_unacknowledged 4.公平分发：设置cha.basic_qos(prefetch_count=1)，这样RabbitMQ就会使得每个Consumer在同一个时间点最多处理一个Message。换句话说，在接收到该Consumer的ack前，他它不会将新的Message分发给它。
五、注意：
生产者和消费者都应该声明建立队列，网上教程上说第二次创建如果参数和第一次不一样，那么该操作虽然成功，但是queue的属性并不会被修改。
可能因为版本问题，在我的测试中如果第二次声明建立的队列属性和第一次不完全相同，将报类似这种错406, "PRECONDITION_FAILED - parameters for queue 'anheng' in vhost '/' not equivalent"
如果是exchange第二次创建属性不同，将报这种错406, "PRECONDITION_FAILED - cannot redeclare exchange 'yanfa' in vhost '/' with different type, durable, internal or autodelete value"
如果第一次声明建立队列也出现这个错误，说明之前存在名字相同的队列且本次声明的某些属性和之前声明不同，可通过命令sudo rabbitmqctl list_queues查看当前有哪些队列。解决方法是声明建立另一名称的队列或删除原有队列，如果原有队列是非持久化的，可通过重启rabbitmq服务删除原有队列，如果原有队列是持久化的，只能删除它所在的vhost,然后再重建vhost,再设置vhost的权限（先确认该vhost中没有其他有用队列）。
MySQL
用户授权
用户管理：
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
授权管理：
show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
对于目标数据库以及内部其他： 数据库名.* 数据库中的所有 数据库名.表 指定数据库中的某张表 数据库名.存储过程 指定数据库中的存储过程 *.* 所有数据库
用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
grant all privileges on db1.tb1 TO '用户名'@'IP' grant select on db1.* TO '用户名'@'IP' grant select,insert on *.* TO '用户名'@'IP' revoke select on db1.tb1 from '用户名'@'IP'
表操作
1、创建表
是否可空，null表示空，非字符串 not null - 不可空 null - 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) 注意：1、对于自增列，必须是索引（含主键）。 2、对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) )
外键，一个特殊的索引，只能是指定内容 creat table color( nid int not null primary key, name char(16) not null ) create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid) )
2、删除表
drop table 表名
3、清空表
delete from 表名 truncate table 表名
4、修改表
添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5、基本数据类型
MySQL的数据类型大致分为：数值、时间和字符串
http://www.runoob.com/mysql/mysql-data-types.html
Windows可以安装Navicat for mysql操作数据库。
insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表
delete from 表 delete from 表 where id＝1 and name＝'alex'
update 表 set name ＝ 'alex' where id>1
select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1
1、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表) 2、通配符 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） select * from 表 where name like 'ale_' - ale开头的所有（一个字符） 3、限制 select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行 4、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 5、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10 特别的：group by 必须在where之后，order by之前 6、连表 无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid 7、组合 组合，自动处理重合 select nickname from A union select name from B 组合，不处理重合 select nickname from A union all select name from B
pymsql
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。
一、下载安装：
pip3 install pymysql
二、使用
1、执行SQL
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
2、获取新创建数据自增ID
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
# 获取最新自增ID
new_id = cursor.lastrowid
3、获取查询数据
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
4、fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
result = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
SQLAlchemy
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
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
步骤一：
使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)
engine.execute(
"INSERT INTO ts_test (a, b) VALUES ('2', 'v1')"
)
engine.execute(
"INSERT INTO ts_test (a, b) VALUES (%s, %s)",
((555, "v1"),(666, "v1"),)
)
engine.execute(
"INSERT INTO ts_test (a, b) VALUES (%(id)s, %(name)s)",
id=999, name="v1"
)
result = engine.execute('select * from ts_test')
result.fetchall()
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) # 事务操作 with engine.begin() as conn: conn.execute("insert into table (x, y, z) values (1, 2, 3)") conn.execute("my_special_procedure(5)") conn = engine.connect() # 事务操作 with conn.begin(): conn.execute("some statement", {'x':5, 'y':10})
注：查看数据库连接：show status like 'Threads%';
步骤二：
使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象，之后通过SQL Expression Language将该对象转换成SQL语句，然后通过 ConnectionPooling 连接数据库，再然后通过 Dialect 执行SQL，并获取结果。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
user = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
)
color = Table('color', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
)
engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)
metadata.create_all(engine)
# metadata.clear()
# metadata.remove()
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey metadata = MetaData() user = Table('user', metadata, Column('id', Integer, primary_key=True), Column('name', String(20)), ) color = Table('color', metadata, Column('id', Integer, primary_key=True), Column('name', String(20)), ) engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) conn = engine.connect() # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name) conn.execute(user.insert(),{'id':7,'name':'seven'}) conn.close() # sql = user.insert().values(id=123, name='wu') # conn.execute(sql) # conn.close() # sql = user.delete().where(user.c.id > 1) # sql = user.update().values(fullname=user.c.name) # sql = user.update().where(user.c.name == 'jack').values(name='ed') # sql = select([user, ]) # sql = select([user.c.id, ]) # sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id) # sql = select([user.c.name]).order_by(user.c.name) # sql = select([user]).group_by(user.c.name) # result = conn.execute(sql) # print result.fetchall() # conn.close()
更多内容详见：
http://www.jianshu.com/p/e6bba189fcbd
http://docs.sqlalchemy.org/en/latest/core/expression_api.html
注：SQLAlchemy无法修改表结构，如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成。
步骤三：
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50))
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
# Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# ########## 增 ##########
# u = User(id=2, name='sb')
# session.add(u)
# session.add_all([
# User(id=3, name='sb'),
# User(id=4, name='sb')
# ])
# session.commit()
# ########## 删除 ##########
# session.query(User).filter(User.id > 2).delete()
# session.commit()
# ########## 修改 ##########
# session.query(User).filter(User.id > 2).update({'cluster_id' : 0})
# session.commit()
# ########## 查 ##########
# ret = session.query(User).filter_by(name='sb').first()
# ret = session.query(User).filter_by(name='sb').all()
# print ret
# ret = session.query(User).filter(User.name.in_(['sb','bb'])).all()
# print ret
# ret = session.query(User.name.label('name_label')).all()
# print ret,type(ret)
# ret = session.query(User).order_by(User.id).all()
# print ret
# ret = session.query(User).order_by(User.id)[1:3]
# print ret
# session.commit()
二、ORM功能使用
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。
1、创建表
#!/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(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine)
2、操作表
#!/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'), ) def __repr__(self): return "%s-%s" %(self.id, self.name) # 一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption) 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")) # 与生成表结构无关，仅用于查询方便 favor = relationship("Favor", backref='pers') # 多对多 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')) group = relationship("Group", backref='s2g') server = relationship("Server", backref='s2g') class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) port = Column(Integer, default=22) # group = relationship('Group',secondary=ServerToGroup,backref='host_list') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session()
增
obj = Users(name="alex0", extra='sb') session.add(obj) session.add_all([ Users(name="alex1", extra='sb'), Users(name="alex2", extra='sb'), ]) session.commit()
删
session.query(Users).filter(Users.id > 2).delete()
session.commit()
改
session.query(Users).filter(Users.id > 2).update({"name" : "099"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
session.commit()
查
ret = session.query(Users).all() ret = session.query(Users.name, Users.extra).all() ret = session.query(Users).filter_by(name='alex').all() ret = session.query(Users).filter_by(name='alex').first()
其他
# 条件 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()
Paramiko
paramiko模块，基于SSH用于连接远程服务器并执行相关操作。
一、安装
pip3 install paramiko
二、使用
SSHClient
用于连接远程服务器并执行基本命令
基于用户名密码连接：
import paramiko
# 创建SSH对象
ssh = paramiko.SSHClient()
# 允许连接不在know_hosts文件中的主机
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# 连接服务器
ssh.connect(hostname='c1.salt.com', port=22, username='wupeiqi', password='123')
# 执行命令
stdin, stdout, stderr = ssh.exec_command('ls')
# 获取命令结果
result = stdout.read()
# 关闭连接
ssh.close()
