http://liu-jiaqiang.iteye.com/blog/1985724
2013
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 = "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)結果集的下一行。結果集是一個(gè)對象時(shí)，將返回一個(gè)游標對象用于查詢(xún)表.
fetchall(): 這種方法獲取結果集的所有行。如果某些行已經(jīng)從結果集中提取，fetchAll（）方法檢索結果集的其余行.
rowcount: 這是一個(gè)只讀屬性，返回受影響的行數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()
更新操作:
對任何數據庫更新操作意味著(zhù)更新已經(jīng)可以在數據庫中的一個(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操作是必需的，當你想從數據庫中刪除一些記錄。以下是程序刪除雇員的所有記錄，其中年齡超過(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()
執行事務(wù):
事務(wù)是機制，以確保數據的一致性，事務(wù)應該有以下四個(gè)屬性:
原子性: 無(wú)論是事務(wù)結束或什么也沒(méi)有發(fā)生在所有.
一致性: 必須啟動(dòng)一個(gè)事務(wù)一致的狀態(tài)和離開(kāi)系統是一致的狀態(tài).
隔離性: 在當前事務(wù)外，事務(wù)的中間結果是不可見(jiàn)的.
持久性: 一旦事務(wù)提交，效果是持久的，即使系統發(fā)生故障后.
對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()
