一、day06作业分析
'''
1.写一个函数，传入一个表名,把这个表里面的数据导出到excel中
def export_excel('students'):
pass
students.xls
'''
import tools def main(): table_name = input('请输入你要导出的表名：').strip() #判断数据库中是否有查询的表 table_exist_sql = "SELECT table_name FROM information_schema.TABLES WHERE table_name ='%s';" % table_name if tools.execute_sql(table_exist_sql): query_sql = 'select * from %s;' % table_name data = tools.execute_sql(query_sql) if data: tools.write_excel(table_name,data) print('导出完成') else: print('表中无数据，无法到导出') else: print('表不存在！') if __name__ == '__main__': main()
作业2：
#简单的方法 ''' 2.修改商品管理的作业，把数据改为从数据库中获取 需要自己建表，相同的功能定义函数 ''' import tools def read_products(): sql = 'select * from tmz_ljj_commodity;' data = tools.execute_sql(sql) #[{'id':1,'trade_name':'xxx','count':xx,'price':xx}, {'id':1,'trade_name':'xxx','count':xx,'price':xx}] d = {} for item in data: name = item.get('trade_name') count = item.get('count') price = float(item.get('price')) d[name] = {'count':count,'price':price} return d def write_products(data): truncate_sql = 'truncate table tmz_ljj_commodity;' tools.execute_sql(truncate_sql) for k,value in data.items(): count = value.get('count') price = value.get('price') insert_sql = 'insert into tmz_ljj_commodity (trade_name,count,price) ' 'values ("%s",%s,%s);' %(k,count,price) tools.execute_sql(insert_sql)
作业2-2：
#效率高的 import tools def get_single_product(name): sql = 'select * from tmz_ljj_commodity where trade_name = "%s";' % name return tools.execute_sql(sql) def is_digit(number): s = str(number) if s.isdigit(): if int(s) > 0: return True def is_price(price): #>0的整数和小数都可以 #1.7 s = str(price) if is_digit(s): return True else: if s.count('.') == 1: # 1.3 left, right = s.split('.') if left.isdigit() and right.isdigit(): # 正小数 #0.0 if float(s)>0: return True def show_product(): product_name = input('请输入商品名称:').strip() if product_name: if product_name == 'all': sql = 'select * from tmz_ljj_commodity;' print(tools.execute_sql(sql)) else: product = get_single_product(product_name) if product: print('商品信息：',product) else: print('你输入的商品不存在') else: print('不能为空') def add_product(): product_name = input('请输入商品名称:').strip() price = input('请输入商品价格:').strip() count = input('请输入商品数量:').strip() if product_name and price and count: if is_price(price) and is_digit(count): if get_single_product(product_name): print('商品已经存在') else: insert_sql = 'insert into tmz_ljj_commodity (trade_name,count,price) ' 'values ("%s",%s,%s);' % (product_name, count, price) tools.execute_sql(insert_sql) print('商品新增成功！') else: print('价格/数量不合法') else: print('不能为空') def modify_product(): product_name = input('请输入商品名称:').strip() price = input('请输入商品价格:').strip() count = input('请输入商品数量:').strip() if product_name and price and count: if is_price(price) and is_digit(count): if get_single_product(product_name): sql = 'update tmz_ljj_commodity set price=%s,count=%s where trade_name="%s";' %( price,count,product_name ) tools.execute_sql(sql) print('商品修改成功') else: print('商品不存在') else: print('价格/数量不合法') else: print('不能为空') def delete_product(): product_name = input('请输入商品名称:').strip() if product_name: if get_single_product(product_name): sql='delete from tmz_ljj_commodity where trade_name="%s"; ' % product_name tools.execute_sql(sql) print('商品已删除') else: print('商品不存在') else: print('不能为空') choice = input('1、查看商品 2、新增 3、修改 4、删除： ') func_map = {'1':show_product,'2':add_product,'3':modify_product,'4':delete_product} if choice in func_map: func_map[choice]() else: print('请输入正确的选项！') # if choice == '1': # show_product() # elif choice == '2': # add_product()
注意：
判断数据库中是否有查询的表
SELECT table_name FROM information_schema.TABLES WHERE table_name ='yourname';
二、加密
import hashlib s = '123456' s = 'huoziyang123'+'sssss#@%%^&&#'#后面的是随机字符串 #盐，一 般反向解密不出来 s = s.encode() m = hashlib.md5(s) #bytes,不可逆 m = hashlib.sha3_256(s)#sha加密方式 result = m.hexdigest() print(result) #同样的字符串，md5出来的结果都是一样的 # create table md5_password( # string varchar(50), # md5_str varchar(100), # ); #下载文件的时候也会使用md5，下载完成后会比对md5值 #加盐 def my_md5(s): s = str(s) s = s.encode() m = hashlib.md5(s) #bytes,不可逆 result = m.hexdigest() print(result)
三、mock-fastapi
import fastapi from fastapi import Form import uvicorn import tools server = fastapi.FastAPI() # @server.get('/login') # def login(username:str,password:str): # return {'username':username,'password':password} # uvicorn.run(server,port=8888,debug=True) #http://127.0.0.1:8888/login?username=abc&password=122345 # @server.get('/test') # def test(): # return {'msg':'你好'} # uvicorn.run(server,port=8888,debug=True) @server.get('/login') def login(username:str,password:str): if username.strip() and password.strip():#用户名和密码不为空 p = tools.my_md5(password)#给密码加密 #在数据库app_myuser中找到姓名+密码的用户 query_sql = 'select * from app_myuser where username = "%s" and passwd=%s;' % (username,p) if tools.execute_sql(query_sql): return {'code':'0','msg':'登录成功'} else: return {'code':'-1','msg':'输入的用户名或者密码错误'} else: return {'code':'-1','msg':'不能为空'} @server.get('/product') def product(): return {'code':0, 'data':[ {'product_name':'eat eat eat','status':0}, {'product_name': 'eat eat eat', 'status': 1}, {'product_name': 'eat eat eat', 'status': 2} ] } # uvicorn.run(server,port=8888,debug=True) # uvicorn.run(server,port=8080,debug=True,host = '0.0.0.0')#增加host可以让其他人访问，并且是在同一个局域网中访问 @server.get('/pay') def pay(money:float,status:str): if status =='0': return {'code':1,'status':'fail'} elif status == '1': return {'code':0,'status':'success','balance':money} # uvicorn.run(server,port=8888,debug=True) @server.post('/reg') def reg(username:str,password:str,cpassword:str): if username.strip() and password.strip() and cpassword.strip(): if password.strip()!= cpassword.strip(): return {'code':-1,'msg':'两次输入的密码不一致'} else: sql = 'select * from app_myuser where username ="%s";'% username if tools.execute_sql(sql): return {'code': -1, 'msg': '用户已经存在'} else: p = tools.my_md5(password) insert_sql = 'insert into app_myuser(username,passwd) value("%s","%s");' % (username,p) tools.execute_sql(insert_sql) return {'code':0,'msg':'注册成功！'} else: return {'code':-1,'msg':'必填参数不能为空！'} uvicorn.run(server,port=8888,debug=True)
mock-flask
import flask #轻量级的web开发框架 import tools import json server = flask.Flask(__name__) #登录 @server.route('/login',methods=['post','get']) def login(): username = flask.request.values.get('username','') password = flask.request.values.get('password','') # print(flask.request.cookies.get('PHPSESSID')) # print('json',flask.request.json) # flask.json.get('xxxx')#如果入参是json类型的话，这么搞 # flask.request.cookies.get('xxx')#获取cookie里面的数据 # flask.request.headers.get('xx') # flask.request.files.get("xxx")#文件 if username.strip() and password.strip(): p = tools.my_md5(password) query_sql = 'select * from app_myuser where username= "%s" and passwd="%s";' % (username, p) print(query_sql) if tools.execute_sql(query_sql): return json.dumps({'code': '0', 'msg': '登录成功','sessionid':'xxxx'},ensure_ascii=False) else: return json.dumps({'code': '-1', 'msg': '输入的用户名/密码错误'}) else: return json.dumps({'code': '-1', 'msg': '不能为空'}) #注册 @server.route('/reg',methods=['post','get']) def reg(): username = flask.request.values.get('username') password = flask.request.values.get('password') cpassword = flask.request.values.get('cpassword') if username.strip() and password.strip() and cpassword.strip(): if password.strip() != cpassword.strip(): return json.dumps({'code': -1, 'msg': '两次输入的密码不一样'}) else: sql='select * from app_myuser where username="%s";'%username if tools.execute_sql(sql): return json.dumps({'code':-1,'msg':'用户已经存在'}) else: p = tools.my_md5(password) insert_sql = 'insert into app_myuser (username,passwd) value ("%s","%s");'%(username,p) tools.execute_sql(insert_sql) return json.dumps({'code':0,'msg':'注册成功！'},ensure_ascii=False) else: return json.dumps({'code':-1,'msg':'必填参数不能为空'}) server.run(host='127.0.0.1',port=8999,debug=True)
四、redis：缓存数据库
string类型
import redis import time r = redis.Redis(host = '118.24.3.40', password='HK139bc&*', port=6379, db=14, decode_responses=True #自动返回字符串 ) #db=14 是因为redis有17个数据库，#0-16，取第14个数据库 #string set and get r.set('矿泉水','{"price":111,"count":11}') print(r.get('矿泉水')) #结果：#b'{"price":111,"count":11}'# #bytes字节类型==二进制类型 #什么时候会遇到这种类型：上传下载 图片或者文件 #f = open('a.jpg','wb') data = r.get('矿泉水') r.delete('矿泉水')#删除 r.set('矿泉水','xxx')#修改 expire_time = 60*60*24 r.set('fd_session','sdfdgdgfgsdfdfs',expire_time) print(data.decode())#bytes类型转成字符串 s = '你好啊' print(s.encode())#字符串转成bytes
hash类型
#{"kqs":{xxxx},"笔"：{xxx}} # r.hset('students','fd','{"money":19999}') # r.hset('students','nhy','{"money":19999}') # r.hset('students','ljj','{"money":19999}') print(r.hget('students','fd')) print(r.hgetall('students')) #结果 # b'{"money":19999}' # {b'fd': b'{"money":19999}', b'ds': b'{"money":19111999}', b'lhy': b'{"money":19111999}', b'ljj': b'{"money":19111999}'} #redis ->mysql #获取student中fd的信息 # a = r.hget('students','fd') # print(a.decode())#转成字符串格式 # #获取students中所有的信息，然后用for循环把bytes类型转换成字符串类型 # d = r.hgetall('students') # new_d = {} # for k,v in d.items(): # new_d[k.decode()] = v.decode() # print(new_d) # #删除students中fd的信息 # r.hdel('students','fd') # r.delete('students') # r.expire('students',100)#指定某个key的过期时间，100秒，只能对students设置过期时间，对里面的fd是不可以设置的
其他操作
# key是什么 key的类型什么（string，hash） print(r.keys())#查找所有key print(r.keys('s*'))#查找s的数据 print(r.type('stu'))#查看key的类型 string print(r.type('students'))#hash print(r.flushdb()) #清空当前数据库里面所有的key print(r.flushall())#清空所有数据库里面所有的key
管道
#管道，批量操作 p = r.pipeline()#建立管道 p.exists('students123') p.hgetall('students') p.hset('students','fd','{"money":19999}') p.hset('students','ds','{"money":19111999}') p.hset('students','lhy','{"money":19111999}') p.hset('students','ljj','{"money":19111999}') s = p.execute() #执行,返回一个list，这个list里面是每个命令执行的结果 print(s) print(r.exists('students123')) start_time = time.time() for i in range(100): r.set('key%s'%i,'%s'%i) print('不用管道的时间',time.time() - start_time) start_time = time.time() p = r.pipeline() for i in range(100): p.set('pipline_key%s'%i,'%s'%i) p.execute() print('用管道的时间',time.time() - start_time)
迁移redis
#a服务器 -》迁移 #b服务器，redis r = redis.Redis(host='118.24.3.40', password='HK139bc&*', port=6379, db=14, decode_responses=True ) r2 = redis.Redis(host='118.24.3.40', password='HK139bc&*', port=6378, db=14, decode_responses=True ) p = r2.pipeline() for k in r.keys(): key_type = r.type(k) if key_type == 'string': value = r.get(k) p.set(k,value) elif key_type=='hash': hash_data = r.hgetall(k) #{'xx':xxx} for filed,data in hash_data.items(): p.hset(k,filed,data) p.execute()
五：网络编程
requests：get
#request请求 #get post cookie、文件、json、headers url = 'http://127.0.0.1:89999/login' data = {'username':'fd','password':'1'} r = requests.get(url,data) print(r.json)#字典 d.get() print(r.text)#字符串格式 打开的是网站或者报错到json文件李敏啊 print(r.content)#bytes类型的 图片 print(r.status_code)#返回状态码 #http://doc.nnzhp.cn/index.php?s=/6&page_id=8 #get请求 r = requests.get(url,data)
post
#Cookie: url = 'http://127.0.0.1:89999/login' data = {'username':'fd','password':'1'} cookie = {'wp-settings-1':'1','PHPSESSID':'xxxxx'} headers = { 'user-agent':'xxxx', 'cookie':'wp-settings-1=libraryContent%3Dbrowse%26posts_list_mode%3Dexcerpt%26editor%3Dtinymce%26post_dfw%3Doff%26imgsize%3Dfull%26editor_plain_text_paste_warning%3D1%26hidetb%3D1; wp-settings-time-1=1573143656; comment_author_8ec14a05b6903cd9021ece26c7b908a0=111; PHPSESSID=2e33445700b8381f67cafb40ee147480'} r = requests.post(url,data=data,params={"version":1.0},cookies=cookie) r = requests.post(url,data=data,params={"version":1.0},headers=headers) #params是把参数传到url后头的 r = requests.post(url,json=data)
案例
url = 'http://api.nnzhp.cn/api.file/file_upload' data = {'file':open('笔记','rb')} r = requests.post(url,files=data) r = requests.get('https://ss2.bdstatic.com/70cFvnSh_Q1YnxGkpoWK1HF6hhy/it/u=293407302,1362956882&fm=26&gp=0.jpg',verify=False) # print(r.json() ) #->字典 #d.get('') # print(r.text) #字符串格式 print(r.content) #bytes类型的 f = open('jpg/a.jpg','wb') f.write(r.content) f.close() print(r.status_code)