python串連mysql資料庫操作

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(): 這種方法擷取查詢結果集的下一行。結果集是一個對象時，將返回一個遊標對象用於查詢表.

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()

執行事務:

事務是機制，以確保資料的一致性，事務應該有以下四個屬性:

原子性: 無論是事務結束或什麼也沒有發生在所有.

一致性: 必須啟動一個事務一致的狀態和離開系統是一致的狀態.

隔離性: 在當前事務外，事務的中間結果是不可見的.

持久性: 一旦事務提交，效果是持久的，即使系統發生故障後.

對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()

