python文件讀寫與數(shù)據(jù)庫操作上課講義課件

Python文件讀寫

與數(shù)據(jù)庫操作主講：劉軍輝2018年6月26日Python文件讀寫

與數(shù)據(jù)庫操作主講：劉軍輝1PythoN文件夾操作1importosimportshutilrootdir='D:\\dist'jsdir="D:\\test"list=os.listdir(rootdir)forfileinlist:iffile.endswith('.py'):shutil.copy(os.path.join(rootdir,file),os.path.join(jsdir,file))iffile.endswith('.txt'):os.remove(os.path.join(rootdir,file))PythoN文件夾操作1importos2PythoN文件夾操作21.創(chuàng)建單個目錄：os.mkdir(“test”)2.列出所有文件和目錄名:os.listdir()3.檢驗給出的路徑是否是一個文件：os.path.isfile()4.檢驗給出的路徑是否是一個目錄：os.path.isdir()5.函數(shù)用來刪除一個文件:os.remove()6.檢驗給出的路徑是否真地存:os.path.exists()7..分離擴展名：os.path.splitext()8.獲取路徑名：os.path.dirname()9.獲取文件名：os.path.basename()10.復制文件：shutil.copy(file1,file2)PythoN文件夾操作21.創(chuàng)建單個目錄：os.mkdir(3Python讀寫文本文件1file_object=open('test.txt','rU')try:forlineinfile_object:print(line)finally:file_object.close()Python讀寫文本文件14Python讀寫文本文件2withopen(‘test','w')asf:

foryyindataArr:

line=""

forssinyy:

f.write(line)Python讀寫文本文件2withopen(‘test',5Python讀寫文本文件3read()一次性讀取文件的所有內(nèi)容放在一個大字符串中readline()逐行讀取文本，結果是一個listreadlines()一次性讀取文本的所有內(nèi)容，結果是一個listfile.write(str)將字符串寫入文件，返回的是寫入的字符長度。file.writelines(sequence)向文件寫入一個序列字符串列表，Python讀寫文本文件3read()一次性讀取文件的所有內(nèi)6Python讀寫csv文件1Impoercsvcsv_reader=csv.reader(open('data.file',encoding='utf-8'))forrowincsv_reader:print(row)

d=(["index","a_name","b_name"])withopen("test.csv","w")ascsvfile:writer=csv.writer(csvfile)writer.writerow(["index","a_name","b_name"])writer.writerows([[0,1,3],[1,2,3],[2,3,4]])Python讀寫csv文件1Impoercsv7Python讀寫excel文件2python讀寫excel文件要用到兩個庫：xlrd和xlwtimport

xlrddata=xlrd.open_workbook(excelFile)table=data.sheets()[0]nrows=table.nrowsncols=table.ncolsforiinxrange(0,nrows):

rowValues=table.row_values(i)#逐行獲取內(nèi)容

foriteminrowValues:

print

item#table.cell(i,j).value可以直接讀取某個單元格的值Python讀寫excel文件2python讀寫excel文8Python讀寫excel文件3import

xlrdexcelFile=unicode(strFile,"utf8")wbk=xlwt.Workbook()sheet=wbk.add_sheet('sheet1',cell_overwrite_ok=True)headList=['標題1','標題2','標題3','標題4','總計']rowIndex=0WriteSheetRow(sheet,headList,rowIndex,True)

for

iin

xrange(1,11):

for

jin

xrange(1,5):

sheet.write(i,j,str(i*j))

wbk.save(excelFile)Python讀寫excel文件3import

xlrd9Python操作sqlite1importsqlite3conn=sqlite3.connect('test.db')print"Openeddatabasesuccessfully";c=conn.cursor()c.execute('''CREATETABLECOMPANY(IDINTPRIMARYKEYNOTNULL,NAMETEXTNOTNULL,AGEINTNOTNULL,ADDRESSCHAR(50),SALARYREAL);''')print"Tablecreatedsuccessfully";mit()conn.close()Python操作sqlite1importsqlite310Python操作sqlite2importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()print"Openeddatabasesuccessfully";c.execute("INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)\VALUES(1,'Paul',32,'California',20000.00)");c.execute("INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)\VALUES(2,'Allen',25,'Texas',15000.00)");mit()print"Recordscreatedsuccessfully";conn.close()Python操作sqlite2importsqlite311Python操作sqlite3importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()print"Openeddatabasesuccessfully";cursor=c.execute("SELECTid,name,address,salaryfromCOMPANY")forrowincursor:print"ID=",row[0]print"NAME=",row[1]print"ADDRESS=",row[2]print"SALARY=",row[3],"\n"print"Operationdonesuccessfully";conn.close()Python操作sqlite3importsqlite312Python操作sqlite4importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()print"Openeddatabasesuccessfully";c.execute("UPDATECOMPANYsetSALARY=25000.00whereID=1")mit()print"Totalnumberofrowsupdated:",conn.total_changescursor=conn.execute("SELECTid,name,address,salaryfromCOMPANY")forrowincursor:print"NAME=",row[1]print"ADDRESS=",row[2]print"Operationdonesuccessfully";conn.close()Python操作sqlite4importsqlite313Python操作sqlite5importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()print"Openeddatabasesuccessfully";c.execute("DELETEfromCOMPANYwhereID=2;")mit()print"Totalnumberofrowsdeleted:",conn.total_changescursor=conn.execute("SELECTid,name,address,salaryfromCOMPANY")forrowincursor:print"NAME=",row[1]print"ADDRESS=",row[2]print"Operationdonesuccessfully";conn.close()Python操作sqlite5importsqlite314Python操作Mysql1連接Mysql,Python3中可以用pymysql,而Python2中則使用mysqldb。importpymysqldb=pymysql.connect("localhost","testuser","test123","TESTDB")cursor=db.cursor()cursor.execute("SELECTVERSION()")data=cursor.fetchone()print("Databaseversion:%s"%data)db.close()Python操作Mysql1連接Mysql,Python3中15Python操作Mysql2importpymysqldb=pymysql.connect("localhost","testuser","test123","TESTDB")cursor=db.cursor()cursor.execute("DROPTABLEIFEXISTSEMPLOYEE")sql="""CREATETABLEEMPLOYEE(FIRST_NAMECHAR(20)NOTNULL,LAST_NAMECHAR(20),AGEINT,SEXCHAR(1),INCOMEFLOAT)"""cursor.execute(sql)db.close()Python操作Mysql2importpymysql16Python操作Mysql3db=pymysql.connect("localhost","testuser","test123","TESTDB")cursor=db.cursor()sql="""INSERTINTOEMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES('Mac','Mohan',20,'M',2000)"""try:cursor.execute(sql)mit()except:db.rollback()

Python操作Mysql3db=pymysql.con17Python操作Mysql4db=pymysql.connect("localhost","testuser","test123","TESTDB")cursor=db.cursor()sql="SELECT*FROMEMPLOYEEWHEREINCOME>'%d'"%(1000)try:cursor.execute(sql)results=cursor.fetchall()forrowinresults:fname=row[0]print("fname=%s"%(fname))except:print("Error:unabletofetchdata")db.close()Python操作Mysql4db=pymysql.con18Python操作Mysql5importpymysqldb=pymysql.connect("localhost","testuser","test123","TESTDB")cursor=db.cursor()sql="UPDATEEMPLOYEESETAGE=AGE+1WHERESEX='%c'"%('M')try:cursor.execute(sql)mit()except:db.rollback()db.close()Python操作Mysql5importpymysql19Python操作Mysql6importpymysqldb=pymysql.connect("localhost","testuser",