|
http://liu-jiaqiang./blog/1985724 2013 # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to INSERT a record into the database. sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('%s', '%s', '%d', '%c', '%d' )" % ('Mac', 'Mohan', 20, 'M', 2000) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close() 讀取操作: fetchone(): 這種方法獲取查詢(xún)結(jié)果集的下一行。結(jié)果集是一個(gè)對(duì)象時(shí)，將返回一個(gè)游標(biāo)對(duì)象用于查詢(xún)表. fetchall(): 這種方法獲取結(jié)果集的所有行。如果某些行已經(jīng)從結(jié)果集中提取，fetchAll（）方法檢索結(jié)果集的其余行. rowcount: 這是一個(gè)只讀屬性，返回受影響的行數(shù)execute（）方法. #!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to INSERT a record into the database. sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000) try: # Execute the SQL command cursor.execute(sql) # Fetch all the rows in a list of lists. results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Now print fetched result print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income ) except: print "Error: unable to fecth data" # disconnect from server db.close() 更新操作: 對(duì)任何數(shù)據(jù)庫(kù)更新操作意味著更新已經(jīng)可以在數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)記錄。以下是更新所有的記錄為“M”SEX的過(guò)程。在這里，我們將所有男性年齡增加一年. #!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to UPDATE required records sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close() 刪除操作: DELETE操作是必需的，當(dāng)你想從數(shù)據(jù)庫(kù)中刪除一些記錄。以下是程序刪除雇員的所有記錄，其中年齡超過(guò)20歲. 例子: #!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to DELETE required records sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close() 執(zhí)行事務(wù): 事務(wù)是機(jī)制，以確保數(shù)據(jù)的一致性，事務(wù)應(yīng)該有以下四個(gè)屬性: 原子性: 無(wú)論是事務(wù)結(jié)束或什么也沒(méi)有發(fā)生在所有. 一致性: 必須啟動(dòng)一個(gè)事務(wù)一致的狀態(tài)和離開(kāi)系統(tǒng)是一致的狀態(tài). 隔離性: 在當(dāng)前事務(wù)外，事務(wù)的中間結(jié)果是不可見(jiàn)的. 持久性: 一旦事務(wù)提交，效果是持久的，即使系統(tǒng)發(fā)生故障后. 對(duì)Python DB API 2.0提供兩種方法來(lái)提交或回滾事務(wù). -------------------------------------------------------------------- import MySQLdb con = MySQLdb.connect(host='localhost', user='root', passwd='root', db='hr_resume_center', charset='utf8') cursor = con.cursor() sql = "INSERT INTO hr_resume_core (resume_id,name,mobile,email,add_time,sys_time,version) VALUES(%s,%s,%s,%s,%s,%s,%s)" param = [ (1,'bright','13641154657','jackieming\@sina.cn',1385625423,1385625423,1), (2,'bright','13641154657','jackieming\@sina.cn',1385625423,1385625423,1), (3,'bright','13641154657','jackieming\@sina.cn',1385625423,1385625423,1), ] cursor.execute(sql,param) cursor.close() con.close() |
|
|
