編程的生活愈發不容易了,工作越來越難找,說多了都是淚還是給大家貢獻些代碼比較實際。
python3 鏈接數據庫需要下載名為pymysql的第三方庫
python3 讀寫xlsx需要下載名為openpyxl的第三方庫
在此我只貢獻鏈接數據庫和寫入xlsx的代碼
import pymysql.cursors from fj.util import logger from openpyxl import Workbook from openpyxl.compat import range from openpyxl.utils import get_column_letter # 鏈接數據庫的遊標 connect = pymysql.Connect( host="localhost", port=3306, user='root', passwd='123456', db='zyDB', charset='utf8', ) cursor = connect.cursor() # 關閉數據庫鏈接操作 def clos_cursor(): return cursor.close(); # 讀取數據庫數據 def query_all(): select_sql = "select*from fj_date where fj_id not in" "( select a.fj_id from ( select * from fj_date where mj_id>0 ) a " "join ( SELECT * from fj_date where jb_id>0 ) b" " on a.fjzz = b.fjzz and a.fj_add=b.fj_add) and mj_id>0" cursor.execute(select_sql); return cursor.fetchall(); # 關閉數據庫鏈接操作 def clos_cursor(): cursor.close(); connect.close() def read_mysql_to_xlsx(): #要創建的xlsx名稱 dest_filename = 'jb_data.xlsx' wb = Workbook() ws1 = wb.active ws1.title = "fj_date" # 列名 ws1.cell(row=1,column=1,value="fj_id(數據庫編號)") ws1.cell(row=1,column=2,value="jb_id(疾病編號)") ws1.cell(row=1,column=3,value="mj_id(名醫編號)") ws1.cell(row=1,column=4,value="fj_name(方劑名稱)") ws1.cell(row=1,column=5,value="fjcc(出處)") ws1.cell(row=1,column=6,value="fjdm(代碼)") ws1.cell(row=1,column=7,value="fjzc(加減)") ws1.cell(row=1,column=8,value="fjgx(功效)") ws1.cell(row=1,column=9,value="fj_add(組成)") ws1.cell(row=1,column=10,value="fjjj(禁忌)") ws1.cell(row=1,column=11,value="fjzy(方劑治驗)") ws1.cell(row=1,column=12,value="fjzz(主治)") ws1.cell(row=1,column=13,value="fjyf(用法)") ws1.cell(row=1,column=14,value="ylzy(藥理作用)") ws1.cell(row=1,column=15,value="gjls(各家論述)") ws1.cell(row=1,column=16,value="fj(方解)") ws1.cell(row=1,column=17,value="ks(科室)") ws1.cell(row=1,column=18,value="ckzl(參考資料)") ws1.cell(row=1,column=19,value="lcyy(臨床應用)") ws1.cell(row=1,column=20,value="tjbq(推薦標籤)") ws1.cell(row=1,column=21,value="zysx(注意事項)") ws1.cell(row=1,column=22,value="fjzb(製備方法)") ws1.cell(row=1,column=23,value="fg(方歌)") ws1.cell(row=1,column=24,value="path(路徑)") # 循環數據寫入內容 jb_date_list = query_all() for i in range(2,len(jb_date_list)+1): ws1.cell(row=i, column=1, value=jb_date_list[i-1][0]) ws1.cell(row=i, column=2, value=jb_date_list[i-1][1]) ws1.cell(row=i, column=3, value=jb_date_list[i-1][2]) ws1.cell(row=i, column=4, value=jb_date_list[i-1][3]) ws1.cell(row=i, column=5, value=jb_date_list[i-1][4]) ws1.cell(row=i, column=6, value=jb_date_list[i-1][5]) ws1.cell(row=i, column=7, value=jb_date_list[i-1][6]) ws1.cell(row=i, column=8, value=jb_date_list[i-1][7]) ws1.cell(row=i, column=9, value=jb_date_list[i-1][8]) ws1.cell(row=i, column=10, value=jb_date_list[i-1][9]) ws1.cell(row=i, column=11, value=jb_date_list[i-1][10]) ws1.cell(row=i, column=12, value=jb_date_list[i-1][11]) ws1.cell(row=i, column=13, value=jb_date_list[i-1][12]) ws1.cell(row=i, column=14, value=jb_date_list[i-1][13]) ws1.cell(row=i, column=15, value=jb_date_list[i-1][14]) ws1.cell(row=i, column=16, value=jb_date_list[i-1][15]) ws1.cell(row=i, column=17, value=jb_date_list[i-1][16]) ws1.cell(row=i, column=18, value=jb_date_list[i-1][17]) ws1.cell(row=i, column=19, value=jb_date_list[i-1][18]) ws1.cell(row=i, column=20, value=jb_date_list[i-1][19]) ws1.cell(row=i, column=21, value=jb_date_list[i-1][20]) ws1.cell(row=i, column=22, value=jb_date_list[i-1][21]) ws1.cell(row=i, column=23, value=jb_date_list[i-1][22]) ws1.cell(row=i, column=24, value=jb_date_list[i-1][23]) # 創建xlsx wb.save(filename=dest_filename) if __name__ == '__main__': read_mysql_to_xlsx()
補充知識:Python 關閉文件釋放內存的疑惑
我用with語句打開了一個4g的文件讀取內容,然後程序末尾設置一個死循環,按理說with語句不是應該自動關閉文件釋放資源嗎?
但是系統內存一直沒有釋放。應該是被文件讀取到的變量content一直佔用嗎?把content刪除就會釋放內存。或者去掉死循環,程序退出資源就自動釋放了
既然這樣的話關閉文件貌似沒啥作用呢?具體釋放了什麼資源?
Python一直佔用著將近5G的內存:
官方文檔:
If you're not using the with keyword, then you should call f.close() to close the file and immediately free up any system resources used by it. If you don't explicitly close a file, Python's garbage collector will eventually destroy the object and close the open file for you, but the file may stay open for a while. Another risk is that different Python implementations will do this clean-up at different times.
After a file object is closed, either by a with statement or by calling f.close(), attempts to use the file object will automatically fail.
代碼如下:
import sys with open(r'H:BaiduNetdiskDownload4K.mp4','rb') as f: print(f.closed) content=f.read() print(f.closed) print(sys.getrefcount(f)) while True: pass
[retouched ] python3 使用openpyxl將mysql數據寫入xlsx的操作已經有261次圍觀