python學習筆記,python筆記
Python學習筆記,主要是用python做一些瑣碎的事情
讀寫txt、讀寫excel、讀寫xml、訪問資料庫
利用python讀寫excel,主要引用xlrd和xlwt,測試xls和xlsx都是可以開啟的,使用xlrd讀取excel檔案不是以獨佔的方式讀取的,讀取的時候excel仍然可以開啟。寫excel只能寫xls
import xlrdimport xlwtimport xmlfrom xml.dom import minidomimport os#簡單的IO操作儲存txtdef createtxt(): ls=os.linesep #get filename while True: fname=input('Enter filename:') if os.path.exists(fname): print("error:'%s' already exists"%fname) else: break; #get file content(text)lines all=[] print("\nEnter lines('.'by itself to quit).\n") #loop until user terminates input while True: entry=input('>') if entry=='.': break else: all.append(entry) #write ines to file with proper line-ending fobj=open(fname,'w') fobj.writelines(['%s%s'%(x,ls) for x in all]) fobj.close() print('DONE!') f=open(fname,'r') for l in f.readlines(): print(l,end='')#這個地方多出一個空行應該是windows下bom的原因 #讀寫exceldef rwexcel(): path=r'C:\Users\huach\Desktop\右岸交通洞-滲壓計.xlsx' file=xlrd.open_workbook(path)#開啟檔案 sheet=file.sheet_by_index(0)#根據索引擷取第一個sheet #sheet=file.sheet_by_name('Pe1-1') nc=sheet.ncols#擷取sheet的列數 nr=sheet.nrows#sheet 行數 newbook=xlwt.Workbook()#建立一個excel對象 nsheet=newbook.add_sheet('testsheet',cell_overwrite_ok=True)#添加一個sheet for i in range(20): row=sheet.row_values(i)#擷取第i行,這是個數組 for j in range(nc): print(row[j],end='') nsheet.write(i,j,row[j])#寫到新的sheet裡邊 print() newfile=r'C:\Users\huach\Desktop\test.xls' if os.path.exists(newfile):os.remove(newfile)#有就刪除了 newbook.save(newfile)#把新建立的excel對象儲存#讀寫xmldef xml(): #import xml.dom.minidom impl = minidom.getDOMImplementation()#建立xml對象 dom = impl.createDocument(None, 'Root', None) #建立dom root = dom.documentElement#根節點 root.setAttribute('attr','root')#屬性 for i in range(1,3): employee=dom.createElement('employee')#添加子節點 employee.setAttribute('attr','child')#添加屬性 value=dom.createTextNode('value') employee.appendChild(value)#添加值 root.appendChild(employee) #儲存 file=r'C:\Users\huach\Desktop\test.xml' if os.path.exists(file):os.remove(file)#有就刪除了 f=open(file,'a') dom.writexml(f, addindent=' ', newl='\n') f.close() print('xml寫入成功') #讀取 dom = minidom.parse(file) root = dom.documentElement ems=root.getElementsByTagName('employee') for em in ems: attr=em.getAttribute("attr")#屬性 print('em.attr=%s'%attr) print('em.attr=%s'%em.firstChild.data)#值
#訪問mssql,引用pymssql,這裡是別人的簡單封裝,可以直接用,簡單的查詢增刪查改沒問題·謝謝原創,·import pymssqlclass MSSQL: """ 對pymssql的簡單封裝 pymssql庫,該庫到這裡下載:http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql 使用該庫時,需要在Sql Server Configuration Manager裡面將TCP/IP協議開啟 用法: """ def __init__(self,host,user,pwd,db): self.host = host self.user = user self.pwd = pwd self.db = db def __GetConnect(self): """ 得到串連資訊 返回: conn.cursor() """ if not self.db: raise(NameError,"沒有設定資料庫資訊") self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8") cur = self.conn.cursor() if not cur: raise(NameError,"串連資料庫失敗") else: return cur def ExecQuery(self,sql): """ 執行查詢語句 返回的是一個包含tuple的list,list的元素是記錄行,tuple的元素是每行記錄的欄位 調用樣本: ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics") resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser") for (id,NickName) in resList: print str(id),NickName """ cur = self.__GetConnect() cur.execute(sql) resList = cur.fetchall() #查詢完畢後必須關閉串連 self.conn.close() return resList def ExecNonQuery(self,sql): """ 執行非查詢語句 調用樣本: cur = self.__GetConnect() cur.execute(sql) self.conn.commit() self.conn.close() """ cur = self.__GetConnect() cur.execute(sql) self.conn.commit() self.conn.close()def main():## ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")## #返回的是一個包含tuple的list,list的元素是記錄行,tuple的元素是每行記錄的欄位## ms.ExecNonQuery("insert into WeiBoUser values('2','3')") ms = MSSQL(host="localhost",user="sa",pwd="sa",db="MWDatabase") resList = ms.ExecQuery("select * from Fiducial_Anchor_Cable") for li in resList: print(li) #本機資料sqliteimport sqlite3def insert(): conn=sqlite3.connect('test.db') cursor=conn.cursor() cursor.execute('create table user(id varchar(20) primary key,name varchar(20))') cursor.execute('insert into user(id,name) values (\'1\',\'Michael\')') cursor.rowcount cursor.close conn.commit() conn.close()def select(): conn=sqlite3.connect('test.db') cursor=conn.cursor() cursor.execute('select * from user where id=?',('1',)) values=cursor.fetchall() print(values) cursor.close() conn.close()if __name__=='__main__': xml()