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

Python文件读写与数据库操作 主讲 刘军辉2018年6月26日 PythoN文件夹操作1 importosimportshutilrootdir 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文件夹操作2 1 创建单个目录 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读写文本文件1 file object open test txt rU try forlineinfile object print line finally file object close Python读写文本文件2 withopen test w asf foryyindataArr line forssinyy f write line Python读写文本文件3 read 一次性读取文件的所有内容放在一个大字符串中readline 逐行读取文本 结果是一个listreadlines 一次性读取文本的所有内容 结果是一个listfile write str 将字符串写入文件 返回的是写入的字符长度 file writelines sequence 向文件写入一个序列字符串列表 Python读写csv文件1 Impoercsvcsv 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文件2 python读写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文件3 importxlrdexcelFile 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操作sqlite1 importsqlite3conn sqlite3 connect test db print Openeddatabasesuccessfully c conn cursor c execute CREATETABLECOMPANY IDINTPRIMARYKEYNOTNULL NAMETEXTNOTNULL AGEINTNOTNULL ADDRESSCHAR 50 SALARYREAL print Tablecreatedsuccessfully mit conn close Python操作sqlite2 importsqlite3conn 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操作sqlite3 importsqlite3conn 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操作sqlite4 importsqlite3conn 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操作sqlite5 importsqlite3conn 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操作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操作Mysql2 importpymysqldb 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 mit except db rollback Python操作Mysql4 db 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操作Mysql5 importpymysqldb 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操作Mysql6 importpymysqldb pymysql connect localhost testuser test123 TESTDB cursor db cursor sql DELETEFROMEMPLOYEEWHEREAGE d 20 try cursor execute sql mit except db rollback db close Python数据库模块 Mysql PyMysqlOracle cx OracleSqlServer PymssqlSqlite Sq