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

1、PYTHON文件讀寫與數(shù)據(jù)庫操作主講：劉軍輝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.創(chuàng)

2、建單個目錄：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.復(fù)制文件： 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()一次性讀取文件的所有內(nèi)容放在一個大字符串中readline()逐行讀取文本，結(jié)果是一個listreadlines()一次性讀取文本的所有內(nèi)容，結(jié)果是一個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) #逐行獲取內(nè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: