|
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(): 這種方法獲取結果集的所有行。如果某些行已經從結果集中提取，fetchAll（）方法檢索結果集的其余行. rowcount: 這是一個只讀屬性，返回受影響的行數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() 更新操作: 對任何數據庫更新操作意味著更新已經可以在數據庫中的一個或多個記錄。以下是更新所有的記錄為“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操作是必需的，當你想從數據庫中刪除一些記錄。以下是程序刪除雇員的所有記錄，其中年齡超過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í)行事務: 事務是機制，以確保數據的一致性，事務應該有以下四個屬性: 原子性: 無論是事務結束或什么也沒有發(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() |
|
|
