Python操作Excel, 開發(fā)和調(diào)用接口，發(fā)送郵件
接口開發(fā)：
import flask import tools import json,redis import random server = flask.Flask(__name__) #新建一個(gè)服務(wù)，把當(dāng)前這個(gè)python文件當(dāng)做一個(gè)服務(wù) ip = '118.24.3.40' password='HK139bc&*' r = redis.Redis(host=ip,password=password,port=6379,db=10, decode_responses=True)#連接redis #登錄接口 @server.route('/login',methods=['get']) def hello(): uname = flask.request.values.get('username') pd = flask.request.values.get('passwd') # sql = 'select * from app_myuser where username="%s"'%uname # res = tools.my_db(sql) key='nhy:%s'%uname res = r.get(key) if res: res = json.loads(res) if tools.my_md5(pd) == res.get('passwd'): res = {"code":0,"msg":"登錄成功！"} else: res = {"code":1,"msg":"密碼錯(cuò)誤！"} else: res = {'code':2,"msg":"用戶不存在"} return json.dumps(res,ensure_ascii=False,indent=4) #注冊(cè)接口 @server.route('/reg',methods=['post']) def reg(): uname = flask.request.values.get('username') pd = flask.request.values.get('passwd') cpd = flask.request.values.get('cpwd') key='nhy:%s'%uname res = r.get(key) if res: res = {'code': 2, "msg": "用戶已存在"} else: md5_password = tools.my_md5(pd) res = {'id':random.randint(100,9999),'username':uname,'passwd':md5_password,'is_admin':1} r.set('nhy:%s'%uname,json.dumps(res))# res = {"code":0,"msg":"注冊(cè)成功！"} return json.dumps(res,ensure_ascii=False,indent=4) #查詢信息接口 @server.route('/api/stu') def get_stu(): username = flask.request.values.get('name')#默認(rèn)get不到的話，返回的值就是None age = flask.request.values.get('age') if username and age: sql = "select * from app_student where name='%s' and age='%s'" % (username, age) elif not username and age: sql = "select * from app_student where age='%s'" % age elif username and not age: sql = "select * from app_student where name='%s'" % username else: sql = "select * from app_student" res = tools.my_db2(sql) return json.dumps(res,ensure_ascii=False,indent=4) server.run(host='0.0.0.0',port=8999,debug=True) #ip:8000/login #127.0.0.1
牛刀小試：
1. 傳入一個(gè)數(shù)據(jù)庫(kù)中的表名，然后把表里的數(shù)據(jù)導(dǎo)出到excel里面
寫excel xlwt
連接數(shù)據(jù)庫(kù) pymysql
思路：
1、連接上數(shù)據(jù)庫(kù)，寫好 sql ='select * from %s;'%table_name
2、獲取到數(shù)據(jù) [[id name passwd is_admin] [1,nhy,xdfsdfsd,1],[2,nhy2,xdfsdfsd,1] ]
3、循環(huán)寫入excel
4、寫表頭 hhh
import pymysql,xlwt conn = pymysql.connect(host='118.24.3.40',user='jxz',password='123456',db='jxz') cur = conn.cursor() table_name = input('請(qǐng)輸入你要導(dǎo)出的表名:').strip() cur.execute('select * from %s;'%table_name) res = list(cur.fetchall()) fields = [ field[0] for field in cur.description ] # cur.description獲取到表結(jié)構(gòu) res.insert(0,fields) book = xlwt.Workbook() sheet = book.add_sheet('sheet1') for index,value in enumerate(res): for index2,v2 in enumerate(value): sheet.write(index,index2,v2) book.save('%s.xls'%table_name) cur.close() conn.close()
2. 獲取到數(shù)據(jù)庫(kù)里面的數(shù)據(jù)
{'username':'lyl','password':xxx,'id':111,'addr':'xxx'}
然后存到redis里面
set(lyl,{'username':'lyl','password':xxx,'id':111,'addr':'xxx'})
get(key)
import pymysql,redis,json conn = pymysql.connect(host='118.24.3.40',user='jxz',password='123456',db='jxz') cur = conn.cursor(pymysql.cursors.DictCursor) cur.execute('select * from app_myuser;') data = cur.fetchall() cur.close() conn.close() ip = '118.24.3.40' password='HK139bc&*' r = redis.Redis(host=ip,password=password,port=6379,db=10, decode_responses=True)#連接redis for d in data: # {'username':'lyl','password':xxx,'id':111,'addr':'xxx'} key = 'nhy:%s'%d.get("username") r.set(key,json.dumps(d))
fw = open('a.txt','w',encoding='utf-8')
fw.write('時(shí)代峰峻圣誕節(jié)瘋狂樓上的房間考慮到雙方就上課了的房間開連鎖店是的范德薩')
fw = open('b.txt','w',encoding='gbk')
fw.write('時(shí)代峰峻圣誕節(jié)瘋狂樓上的房間考慮到雙方就上課了的房間開連鎖店是的范德薩')
import chardet
f= open('b.txt','rb')
res = f.read()
print(chardet.detect(res))
print(res.decode('gbk'))
python utf-8 gbk GB2312字符集
1121 牛
發(fā)送郵件：需要安裝，pip install yagmail
import yagmail username='uitestp4p@163.com' password='houyafan123'#生成授權(quán)碼，qq、163、126都是授權(quán)碼 mail_server = 'smtp.163.com' # mail_server = 'smtp.qq.com' # mail_server = 'smtp.126.com' m = yagmail.SMTP(user=username,password=password,host=mail_server) # smtp_ssl=True，如果郵箱使用了安全協(xié)議，就需要加這個(gè) #qq郵箱就是使用了安全協(xié)議 to = ['1137944722@qq.com','wangmei416516@163.com','511402865@qq.com'] cc = ['61378317@qq.com','1196842722@qq.com','1365834704@qq.com'] m.send(to=to,cc=cc,subject='今天吃了嗎', contents='今天吃魚肉了嗎，吃飽沒', attachments=r'tools.py')
發(fā)送網(wǎng)絡(luò)請(qǐng)求：
1. 用標(biāo)準(zhǔn)庫(kù)：
from urllib.request import urlopen #python自帶的，不好用，只需了解。urllib可以發(fā)送網(wǎng)絡(luò)請(qǐng)求,urlopen可以發(fā)送接口請(qǐng)求 from urllib.parse import urlencode #用于post接口請(qǐng)求，urlencode可以把json字符串轉(zhuǎn)化成k=v形式 #評(píng)語(yǔ)：這個(gè)模塊要求類型，二進(jìn)制換來?yè)Q去，很麻煩。 #功能：get url request url='http://127.0.0.1:8999/api/login?username=testuser1&passwd=111111' res = urlopen(url) #發(fā)送接口請(qǐng)求 print(res.read().decode()) #read獲取請(qǐng)求返回內(nèi)容，但返回二進(jìn)制數(shù)據(jù)，所以再decode一下。 #功能：post url request url='http://127.0.0.1:8999/api/login' data = {'username':'testuser1','passwd':'111111'} s = urlencode(data) #把字典變成k=v形式,username=testuser1,passwd=111111 #注：‘username=testuser1,passwd=111111’，是個(gè)字符串，encode()后，變?yōu)閎'username=testuser1,passwd=111111' res = urlopen(url,s.encode()) #post請(qǐng)求,第二個(gè)參數(shù)要求是二進(jìn)制類型，所以再encode一下 print(res.read().decode())
2. pip install requests
import requests import random url='http://127.0.0.1:8999/api/upload' data = {'username':'testuser1','passwd':'111111'} r = requests.get(url,params=data) #發(fā)get請(qǐng)求 r = requests.post(url,data=data) #發(fā)post請(qǐng)求 data = { "session_id":"6ab8785039dcf50fb11c53acc1db7648", "name":"zhouyongbo%s"%random.randint(1,99), "phone":"111211345%02d"%random.randint(1,99), "grade":"天秤座" } r = requests.post(url,json=data) #入?yún)⑹莏son類型的 #上傳文件的 r = requests.post(url, data={'session_id':'6ab8785039dcf50fb11c53acc1db7648'}, files={'file_name':open('account.xls','rb') } ) # 添加header requests.get(url,headers={'cookie':'pt2gguin=o0511402865; RK=JQZpwBp1by; ptcz=6c30e26a9ed6be93d3de9e4c4aca3e55650cf99fcffa64729bd1d58a5fb209d9; pgv_pvi=779236352; pgv_pvid=6970909788; qb_qua=; qb_guid=818de686e29d412fa4ee9e99905ea166; Q-H5-GUID=818de686e29d412fa4ee9e99905ea166; NetType=; pgv_si=s4499960832; FTN5K=0138ca95; pgv_info=ssid=s4269564370; luin=o0511402865; uin=o0511402865; lskey=00010000efc2701412d3429029ac9366e4ba98f0e978e0ae4a9c684101a7b22df0695f534bc242c8d4ff386d; skey=@0sHtvhTsD; ptisp=cnc; p_uin=o0511402865; pt4_token=wGU2YAaM0uu7LbcNjuDcLN-TPrEy7AZw4gcL5TEcKxw_; p_skey=1zg7yvF5wr6l43mfr-BvNHxuVDtybKpR5RbCrgC8weQ_'}) requests.get(url,cookies={'pt2ggui':'o0511402865','RK':'JQZpwBp1by'}) print(r.text) #結(jié)果返回的就是字符串 print(r.json()) #結(jié)果返回的就是字典,必須返回的是json，才能轉(zhuǎn)成字典 #下載文件 url='https://q4.qlogo.cn/g?b=qq&nk=1834364415&s=140' url='https://qiniuuwmp3.changba.com/1127063572.mp4' r = requests.get(url) #返回的就是二進(jìn)制的 r.cookies #返回cookie r.status_code #返回的狀態(tài)碼200 r.content #可以返回圖片、音樂等 f = open('sdfsdf.mp4','wb') f.write(r.content) f.close()
接口返回值處理：
- json_dic=res.json() #返回為json格式，直接轉(zhuǎn)成字典
- str=res.text #返回為字符串
- file_content=res.content #二進(jìn)制，下載圖片、文件時(shí)用，可以直接新建文件再保存內(nèi)容：with open('a.jpg','wb')as f: f.write(r.content)
- r.cookies #請(qǐng)求返回的cookie
- r.status_code #狀態(tài)碼
寫日志： pip install nnlog
import nnlog nnlog.Logger.words='哈哈哈哈' log = nnlog.Logger('book_server.log','warn',when='S',backCount=5) #默認(rèn)debug級(jí)別,自動(dòng)清理日志，5條就刪除 # debug 打印一些調(diào)試信息，非常多 # info 打印走到哪兒了 # warning # error #這一個(gè)語(yǔ)句，可以放在需要的地方，比如登錄是，將誰(shuí)在登錄寫入日志 log.debug('xxx值是什么') log.info('調(diào)用了什么xxx') log.warning('xx警告！') log.error('xxx出錯(cuò)！')
代碼文件：
tools.py import time import os def timestampToStr(timestamp=None,format='%Y-%m-%d %H:%M:%S'): #時(shí)間戳轉(zhuǎn)格式化好的時(shí)間 if timestamp: time1 = time.localtime(timestamp) res = time.strftime(format, time1) else: res = time.strftime(format) return res #20180304153958 def strTotimestamp(str=None,format='%Y%m%d%H%M%S'): #格式化的時(shí)間轉(zhuǎn)時(shí)間戳 if str: timep = time.strptime(str, format) res = time.mktime(timep) else: res = time.time() return int(res) def clean_log(path,day=3): print('調(diào)用了') for cur_path, dirs, files in os.walk(path): for file in files: if file.endswith('log'): f_time = file.split('.')[0].split('_')[-1] file_timestamp = strTotimestamp(f_time,'%Y-%m-%d') cur_timestamp = strTotimestamp(time.strftime('%Y-%m-%d'),'%Y-%m-%d') if (cur_timestamp - file_timestamp) >= 60*60*24*day:#判斷文件的時(shí)間是否大于3天 os.remove(os.path.join(cur_path,file)) import pymysql def my_db(sql): conn = pymysql.connect(host='118.24.3.40',user='jxz',password='123456', db='jxz',port=3306,charset='utf8',autocommit=True) cur = conn.cursor(pymysql.cursors.DictCursor) cur.execute(sql) res = cur.fetchone() #{'username':'nhy'} {} cur.close() conn.close() return res def my_db2(sql): conn = pymysql.connect(host='118.24.3.40',user='jxz',password='123456', db='jxz',port=3306,charset='utf8',autocommit=True) cur = conn.cursor(pymysql.cursors.DictCursor) cur.execute(sql) res = cur.fetchall() #{'username':'nhy'} {} cur.close() conn.close() return res import hashlib def my_md5(s,salt=''): s = s+salt news = str(s).encode() m = hashlib.md5(news) return m.hexdigest() if __name__ == '__main__': #判斷如果是在別的文件里面導(dǎo)入這個(gè)python文件的話，就不執(zhí)行下面的代碼 print(strTotimestamp()) print(clean_log('.')) print(clean_log('.',2))
一個(gè)python項(xiàng)目的文件結(jié)構(gòu)：
-
book_server/: 項(xiàng)目名 -
conf/:存放配置文件
-
data/:存放sql文件
-
lib/: 存放項(xiàng)目的所有源代碼。 -
logs/:存放日志文件
-
uploads/:存放下載的文件
-
start.py: 程序啟動(dòng)腳本 -
readme.txt: 項(xiàng)目說明文件。
浙公網(wǎng)安備 33010602011771號(hào)