python文件读写与数据库操作

1、PYTHON文件读写与数据库操作主讲：刘军辉2018年6月26日PYTHON文件夹操作1import osimport shutilrootdir = D:distjsdir=D:testlist = os.listdir(rootdir)for file in list: if file.endswith(.py): shutil.copy(os.path.join(rootdir,file),os.path.join(jsdir,file) if file.endswith(.txt): os.remove(os.path.join(rootdir,file)PYTHON文件夹操作21.创

2、建单个目录：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_objec

3、t = open(test.txt,rU)try: for line in file_object: print(line)finally: file_object.close()PYTHON读写文本文件2with open(test,w) as f: for yy in dataArr: line= for ss in yy: f.write(line)PYTHON读写文本文件3read()一次性读取文件的所有内容放在一个大字符串中readline()逐行读取文本，结果是一个listreadlines()一次性读取文本的所有内容，结果是一个listfile.write(str)将字符串写入文

4、件，返回的是写入的字符长度。file.writelines(sequence)向文件写入一个序列字符串列表，PYTHON读写CSV文件1Impoer csv csv_reader = csv.reader(open(data.file, encoding=utf-8) for row in csv_reader: print(row) d= (index,a_name,b_name)with open(test.csv,w) as csvfile: writer = csv.writer(csvfile) writer.writerow(index,a_name,b_name) writer.

5、writerows(0,1,3,1,2,3,2,3,4)PYTHON读写EXCEL文件2python读写excel文件要用到两个库：xlrd和xlwtimport xlrddata = xlrd.open_workbook(excelFile)table = data.sheets()0nrows = table.nrows ncols = table.ncols for i in xrange(0,nrows):rowValues= table.row_values(i) #逐行获取内容for item in rowValues:print item#table.cell(i,j).valu

6、e 可以直接读取某个单元格的值PYTHON读写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 i in xrange(1,11): for j in xrange(1,5): sheet.write(i,j,str(i

7、*j) wbk.save(excelFile)PYTHON操作SQLITE1import sqlite3conn = sqlite3.connect(test.db)print Opened database successfully;c = conn.cursor()c.execute(CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);)print Table created successfully;m

8、it()conn.close()PYTHON操作SQLITE2import sqlite3conn = sqlite3.connect(test.db)c = conn.cursor()print Opened database successfully;c.execute(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, Paul, 32, California, 20000.00 );c.execute(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2,

9、 Allen, 25, Texas, 15000.00 );mit()print Records created successfully;conn.close()PYTHON操作SQLITE3import sqlite3conn = sqlite3.connect(test.db)c = conn.cursor()print Opened database successfully;cursor = c.execute(SELECT id, name, address, salary from COMPANY)for row in cursor: print ID = , row0 prin

10、t NAME = , row1 print ADDRESS = , row2 print SALARY = , row3, nprint Operation done successfully;conn.close()PYTHON操作SQLITE4import sqlite3conn = sqlite3.connect(test.db)c = conn.cursor()print Opened database successfully;c.execute(UPDATE COMPANY set SALARY = 25000.00 where ID=1)mit()print Total numb

11、er of rows updated :, conn.total_changescursor = conn.execute(SELECT id, name, address, salary from COMPANY)for row in cursor: print NAME = , row1 print ADDRESS = , row2print Operation done successfully;conn.close()PYTHON操作SQLITE5import sqlite3conn = sqlite3.connect(test.db)c = conn.cursor()print Op

12、ened database successfully;c.execute(DELETE from COMPANY where ID=2;)mit()print Total number of rows deleted :, conn.total_changescursor = conn.execute(SELECT id, name, address, salary from COMPANY)for row in cursor: print NAME = , row1 print ADDRESS = , row2print Operation done successfully;conn.cl

13、ose()PYTHON操作MYSQL1连接Mysql,Python3中可以用pymysql,而Python2中则使用mysqldb。import pymysql db = pymysql.connect(localhost,testuser,test123,TESTDB )cursor = db.cursor()cursor.execute(SELECT VERSION()data = cursor.fetchone()print (Database version : %s % data)db.close()PYTHON操作MYSQL2import pymysqldb = pymysql.c

14、onnect(localhost,testuser,test123,TESTDB )cursor = db.cursor()cursor.execute(DROP TABLE IF EXISTS EMPLOYEE)sql = CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )cursor.execute(sql)db.close()PYTHON操作MYSQL3 db = pymysql.connect(localhost,te

15、stuser,test123,TESTDB ) cursor = db.cursor() sql = INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (Mac, Mohan, 20, M, 2000) try: cursor.execute(sql) mit() except: db.rollback() PYTHON操作MYSQL4db = pymysql.connect(localhost,testuser,test123,TESTDB )cursor = db.cursor()sql = SELEC

16、T * FROM EMPLOYEE WHERE INCOME %d % (1000)try: cursor.execute(sql) results = cursor.fetchall() for row in results: fname = row0 print (fname=%s %(fname)except: print (Error: unable to fetch data)db.close()PYTHON操作MYSQL5import pymysqldb = pymysql.connect(localhost,testuser,test123,TESTDB )cursor = db.cursor()sql = UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = %c % (M)try: cursor.execute(sql) mit()except: db.rollback()db.close()PYTHON操作MYSQL6import pymysqldb = pymysql.connect(localhost,testuser,test123,TESTDB )cursor = db.cursor()sql = DELETE FROM EMPLOYEE WHERE AGE %d % (20)try: