python16_day10【SelectWeb、SelectWget、paramiko、pymysql】

一、select实现Web框架(自定义WEB框架)

　　浏览器输入：http://127.0.0.1:8888/index.html

 1 import select
 2 import socket
 3 
 4 
 5 class Flask(object):
 6     def __init__(self, routers):
 7         self.routers = routers
 8 
 9     def process_data(self, client):
10         data = bytes()
11         while True:
12             try:
13                 trunk = client.recv(1024)  # 没有数据会报错, 用户断开也会报错.
14             except BlockingIOError as e:
15                 trunk = ""
16             if not trunk:
17                 break
18             data += trunk
19         data_str = str(data, encoding='utf8')
20         header, body = data_str.split('\r\n\r\n', 1)
21         header_list = header.split('\r\n')
22         header_dict = {}
23         for line in header_list:
24             value = line.split(":", 1)
25             if len(value) == 2:
26                 k, v = value
27                 header_dict[k] = v
28             else:
29                 header_dict['mothod'], header_dict['url'], header_dict['protocol'] = line.split(' ')
30 
31         return header_dict, body
32 
33     def run(self, host='127.0.0.1', port=8888):
34         sock = socket.socket()
35         sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
36         sock.setblocking(False)
37         sock.bind((host, port))
38         sock.listen(5)
39 
40         inputs = [sock, ]
41         while True:
42             rList, wList, eList = select.select(inputs, [], [], 0.5)
43             for client in rList:
44                 # 建立新的连接
45                 if client == sock:
46                     conn, addr = client.accept()
47                     conn.setblocking(False)
48                     inputs.append(conn)
49                 else:           # 用户发送数据
50                     header_dict, body = self.process_data(client)
51                     request_url = header_dict['url']
52                     func_name = None
53                     for item in self.routers:
54                         if item[0] == request_url:
55                             func_name = item[1]
56                             break
57                     if not func_name:
58                         client.sendall(b"404")
59                     else:
60                         result = func_name(header_dict, body)
61                         client.sendall(result.encode('utf8'))
62                     inputs.remove(client)
63                     client.close()
flask.py
 1 import flask
 2 
 3 
 4 def f1(header, body):
 5     return "from f1"
 6 
 7 
 8 def f2(header, body):
 9     return "from f2"
10 
11 routers = [
12     ('/index.html', f1),
13     ('/login.html', f2),
14 ]
15 
16 obj = flask.Flask(routers)
17 obj.run()

 

二、自定义Wget(爬虫)

　　select使用基类的fileno进行，使用foo在中间封装一层，从而增加更多的功能。

 1 #!/usr/bin/env python
 2 # -*-coding:utf8-*-
 3 # __author__ = "willian"
 4 import socket
 5 import select
 6 
 7 
 8 # 中间封装一层
 9 class Foo(object):
10     def __init__(self, sock, callback, url, host):
11         self.sock = sock
12         self.callback = callback
13         self.url = url
14         self.host = host
15 
16     def fileno(self):
17         return self.sock.fileno()
18 
19 
20 class NbIO(object):
21     def __init__(self):
22         self.fds = []
23         self.connections = []
24 
25     def connect(self, url_list):
26         for item in url_list:
27             conn = socket.socket()
28             conn.setblocking(False)
29             # 1. 发送链接请求
30             try:
31                 conn.connect((item['host'], 80))
32             except BlockingIOError as e:
33                 pass
34             obj = Foo(conn, item['callback'], item['url'], item['host'])
35             self.fds.append(obj)
36             self.connections.append(obj)
37 
38     def send(self):
39         while True:
40             if len(self.fds) == 0:
41                 break
42             # wList，有对象；当前socket已经创建链接
43             rList, wList, eList = select.select(self.fds, self.connections, self.fds, 0.5)
44 
45             for obj in rList:
46                 # 4.有数据响应回来了
47                 conn = obj.sock
48                 data = bytes()
49                 while True:
50                     try:
51                         d = conn.recv(1024)
52                         data = data + d
53                     except BlockingIOError as e:
54                         d = None
55                     if not d:
56                         break
57                 # print(data)
58                 obj.callback(data)  # 自定义操作 f1  f2
59                 self.fds.remove(obj)
60                 # print(len(self.fds),len(self.connections))
61                 # 执行当前请求 函数：f1  f2
62             # 【1,2,3，】
63             for obj in wList:
64                 # 2.已经连接上远程
65                 conn = obj.sock
66                 # 3. 发送数据
67                 # HTTP/1.1\r\nHost: %s\r\n\r\n
68                 template = "GET %s HTTP/1.1\r\nHost: %s\r\n\r\n" % (obj.url, obj.host,)
69                 # template = "POST %s HTTP/1.1\r\nHost: 127.0.0.1:8888\r\n\r\nk1=v1&k2=v2" %(obj.url,)
70                 conn.sendall(template.encode('utf-8'))
71                 self.connections.remove(obj)
基类
 1 import spider
 2 
 3 
 4 def f1(data):
 5     print("\033[31;1m{0}\033[0m".format(data))
 6 
 7 
 8 def f2(data):
 9     print("\033[32;1m{0}\033[0m".format(data))
10 
11 url_list = [
12     {'host': "www.baidu.com", 'url': '/', 'callback': f1},   # socket
13     {'host': "www.bing.com", 'url': '/', 'callback': f2},
14     {'host': "www.cnblogs.com", 'url': '/wupeiqi', 'callback': f1},
15     {'host': "www.oldboyedu.com", 'url': '/', 'callback': f1},
16 ]
17 obj = spider.NbIO()
18 obj.connect(url_list)
19 obj.send()

 

三、paramiko实现SSH登录

 1 #!/usr/bin/env python
 2 # -*-coding:utf8-*-
 3 # __author__ = "willian"
 4 
 5 import paramiko
 6 
 7 
 8 class SshHelper(object):
 9     def __init__(self, host, port, username, pwd):
10         self.host = host
11         self.port = port
12         self.username = username
13         self.pwd = pwd
14         self.transport = None
15 
16     def connect(self):
17         transport = paramiko.Transport((self.host, self.port,))
18         transport.connect(username=self.username, password=self.pwd)
19         self.transport = transport
20 
21     def put(self, local, target):
22         sftp = paramiko.SFTPClient.from_transport(self.transport)
23         # 将location.py 上传至服务器 /tmp/test.py
24         sftp.put(local, target)
25 
26     def get(self, remote, local):
27         sftp = paramiko.SFTPClient.from_transport(self.transport)
28         sftp.get(remote, local)
29 
30     def cmd(self, cmd):
31         ssh = paramiko.SSHClient()
32         ssh._transport = self.transport
33         stdin, stdout, stderr = ssh.exec_command(cmd)
34         stdout.read()
35 
36     def close(self):
37         self.transport.close()
38 
39 if __name__ == '__main__':
40     obj = SshHelper('1.1.1.1')
41     obj.connect()
42     obj.close()
ssh封装类

 

四、MySQL

　　1.mysql认识和基本操作

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

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

 1 a、条件
 2     select * from 表 where id > 1 and name != 'alex' and num = 12;
 3  
 4     select * from 表 where id between 5 and 16;
 5  
 6     select * from 表 where id in (11,22,33)
 7     select * from 表 where id not in (11,22,33)
 8     select * from 表 where id in (select nid from 表)
 9  
10 b、通配符
11     select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
12     select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
13  
14 c、限制
15     select * from 表 limit 5;            - 前5行
16     select * from 表 limit 4,5;          - 从第4行开始的5行
17     select * from 表 limit 5 offset 4    - 从第4行开始的5行
18  
19 d、排序
20     select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
21     select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
22     select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
23  
24 e、分组
25     select num from 表 group by num
26     select num,nid from 表 group by num,nid
27     select num,nid from 表  where nid > 10 group by num,nid order nid desc
28     select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
29  
30     select num from 表 group by num having max(id) > 10
31  
32     特别的：group by 必须在where之后，order by之前
33  
34 f、连表
35     无对应关系则不显示
36     select A.num, A.name, B.name
37     from A,B
38     Where A.nid = B.nid
39  
40     无对应关系则不显示
41     select A.num, A.name, B.name
42     from A inner join B
43     on A.nid = B.nid
44  
45     A表所有显示，如果B中无对应关系，则值为null
46     select A.num, A.name, B.name
47     from A left join B
48     on A.nid = B.nid
49  
50     B表所有显示，如果B中无对应关系，则值为null
51     select A.num, A.name, B.name
52     from A right join B
53     on A.nid = B.nid
54  
55 g、组合
56     组合，自动处理重合
57     select nickname
58     from A
59     union
60     select name
61     from B
62  
63     组合，不处理重合
64     select nickname
65     from A
66     union all
67     select name
68     from B
复杂查询

　　2.python操作MySQL

　　对于Python操作MySQL主要使用两种方式：

  • 原生pymysql
  • ORM方式sqlAlchemy

　　安装：pip3 install pymysql

 1 import pymysql
 2   
 3 # 创建连接
 4 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 5 # 创建游标
 6 cursor = conn.cursor()
 7   
 8 # 执行SQL，并返回收影响行数
 9 effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
10   
11 # 执行SQL，并返回受影响行数
12 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
13   
14 # 执行SQL，并返回受影响行数
15 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
16   
17   
18 # 提交，不然无法保存新建或者修改的数据
19 conn.commit()
20   
21 # 关闭游标
22 cursor.close()
23 # 关闭连接
24 conn.close()
执行SQL
1 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
2 cursor = conn.cursor()
3 cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
4 conn.commit()
5 cursor.close()
6 conn.close()
7   
8 # 获取最新自增ID
9 new_id = cursor.lastrowid
获取自增ID
 1 import pymysql
 2   
 3 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 4 cursor = conn.cursor()
 5 cursor.execute("select * from hosts")
 6   
 7 # 获取第一行数据
 8 row_1 = cursor.fetchone()
 9   
10 # 获取前n行数据
11 # row_2 = cursor.fetchmany(3)
12 # 获取所有数据
13 # row_3 = cursor.fetchall()
14   
15 conn.commit()
16 cursor.close()
17 conn.close()
获取数据

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

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

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

 1 import pymysql
 2   
 3 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 4   
 5 # 游标设置为字典类型
 6 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 7 r = cursor.execute("call p1()")
 8   
 9 result = cursor.fetchone()
10   
11 conn.commit()
12 cursor.close()
13 conn.close()
获取数据类型

 

MySQL练习：

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

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

posted @ 2017-03-28 21:06  willianflasky  阅读(183)  评论(0)  编辑  收藏  举报