|
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(): 這種方法獲取查詢結果集的下一行。結果集是一個對象時，將返回一個游標對象用于查詢表. fetchall(): 這種方法獲取結果集的所有行。如果某些行已經(jīng)從結果集中提取，fetchAll（）方法檢索結果集的其余行. rowcount: 這是一個只讀屬性，返回受影響的行數(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() 更新操作: 對任何數(shù)據(jù)庫更新操作意味著更新已經(jīng)可以在數(shù)據(jù)庫中的一個或多個記錄。以下是更新所有的記錄為“M”SEX的過程。在這里，我們將所有男性年齡增加一年. #!/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操作是必需的，當你想從數(shù)據(jù)庫中刪除一些記錄。以下是程序刪除雇員的所有記錄，其中年齡超過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í)行事務: 事務是機制，以確保數(shù)據(jù)的一致性，事務應該有以下四個屬性: 原子性: 無論是事務結束或什么也沒有發(fā)生在所有. 一致性: 必須啟動一個事務一致的狀態(tài)和離開系統(tǒng)是一致的狀態(tài). 隔離性: 在當前事務外，事務的中間結果是不可見的. 持久性: 一旦事務提交，效果是持久的，即使系統(tǒng)發(fā)生故障后. 對Python DB API 2.0提供兩種方法來提交或回滾事務. -------------------------------------------------------------------- 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() |
|
|
