python文件读写与数据库操作

PYTHON文件读写与数据库操作主讲：刘军辉2018年6月26日PYTHON文件夹操作1importosimportshutilrootdir='D:\\dist'jsdir=D:\\testlist=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文件夹操作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读写文本文件1file_object=open('test.txt','rU')try:forlineinfile_object:print(line)finally:file_object.close()PYTHON读写文本文件2withopen(‘test','w')asf:foryyindataArr:line=forssinyy:f.write(line)PYTHON读写文本文件3read()一次性读取文件的所有内容放在一个大字符串中readline()逐行读取文本，结果是一个listreadlines()一次性读取文本的所有内容，结果是一个listfile.write(str)将字符串写入文件，返回的是写入的字符长度。file.writelines(sequence)向文件写入一个序列字符串列表，PYTHON读写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读写EXCEL文件2python读写excel文件要用到两个库：xlrd和xlwtimportxlrddata=xlrd.open_workbook(excelFile)table=data.sheets()[0]nrows=table.nrowsncols=table.ncolsforiinxrange(0,nrows):rowValues=table.row_values(i)#逐行获取内容foriteminrowValues:printitem#table.cell(i,j).value可以直接读取某个单元格的值PYTHON读写EXCEL文件3importxlrdexcelFile=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)foriinxrange(1,11):forjinxrange(1,5):sheet.write(i,j,str(i*j))wbk.save(excelFile)PYTHON操作SQLITE1importsqlite3conn=sqlite3.connect('test.db')printOpeneddatabasesuccessfully;c=conn.cursor()c.execute('''CREATETABLECOMPANY(IDINTPRIMARYKEYNOTNULL,NAMETEXTNOTNULL,AGEINTNOTNULL,ADDRESSCHAR(50),SALARYREAL);''')printTablecreatedsuccessfully;conn.commit()conn.close()PYTHON操作SQLITE2importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()printOpeneddatabasesuccessfully;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));conn.commit()printRecordscreatedsuccessfully;conn.close()PYTHON操作SQLITE3importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()printOpeneddatabasesuccessfully;cursor=c.execute(SELECTid,name,address,salaryfromCOMPANY)forrowincursor:printID=,row[0]printNAME=,row[1]printADDRESS=,row[2]printSALARY=,row[3],\nprintOperationdonesuccessfully;conn.close()PYTHON操作SQLITE4importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()printOpeneddatabasesuccessfully;c.execute(UPDATECOMPANYsetSALARY=25000.00whereID=1)conn.commit()printTotalnumberofrowsupdated:,conn.total_changescursor=conn.execute(SELECTid,name,address,salaryfromCOMPANY)forrowincursor:printNAME=,row[1]printADDRESS=,row[2]printOperationdonesuccessfully;conn.close()PYTHON操作SQLITE5importsqlite3conn=sqlite3.connect('test.db')c=conn.cursor()printOpeneddatabasesuccessfully;c.execute(DELETEfromCOMPANYwhereID=2;)conn.commit()printTotalnumberofrowsdeleted:,conn.total_changescursor=conn.execute(SELECTid,name,address,salaryfromCOMPANY)forrowincursor:printNAME=,row[1]printADDRESS=,row[2]printOperationdonesuccessfully;conn.close()PYTHON操作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操作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操作MYSQL3db=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)db.commit()except:db.rollback()PYTHON操作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操作MYSQL5importpymysqldb=pymysql.connect(localhost,testuser,test123,TESTDB)cursor=db.cursor()sql=UPDATEEMPLOYEESETAGE=AGE+1WHERESEX='%c'%('M')try:cursor.execute(sql)db.commit()except:db.rollback()db.close()PYTHON操作MYSQL6importpymysqldb=pymysql.connect(localhost,testuser,test123,TESTDB)cursor=db.cursor()sql=DELETEFROMEMPLOYEEWHEREAGE'%d'%(20)try:cursor.execute(sql)db.commit()except:db.rollback()db.close()PYTHON数据库模块Mysql:PyMysqlOracle:cx_OracleSqlServer:PymssqlSqlite:Sqlite3PostGresql:Psycopg2Mongodb:PymongoRedis:Red