python文件读写与数据库操作上课讲义课件

Python文件读写

与数据库操作主讲：刘军辉2018年6月26日Python文件读写

与数据库操作主讲：刘军辉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.创建单个目录：os.mkdir(“test”)2.列出所有文件和目录名:os.listdir()3.检验给出的路径是否是一个文件：os.path.isfile()4.检验给出的路径是否是一个目录：os.path.isdir()5.函数用来删除一个文件: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.创建单个目录：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()一次性读取文件的所有内容放在一个大字符串中readline()逐行读取文本，结果是一个listreadlines()一次性读取文本的所有内容，结果是一个listfile.write(str)将字符串写入文件，返回的是写入的字符长度。file.writelines(sequence)向文件写入一个序列字符串列表，Python读写文本文件3read()一次性读取文件的所有内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)#逐行获取内容

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",