import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursoring cur帶()
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME,
'%s', '% d', '%c', '%d' )" %
('Mac', 'Mohan', 20, 'M', 2000)
try: # Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
讀取操作:
fetchone(): 這種方法會取得查詢結果集的下一行。結果集是一個物件時,將傳回一個遊標物件用於查詢表.
fetchall(): 這種方法會取得結果集的所有行。如果某些行已經從結果集中提取,fetchAll()方法檢索結果集的其餘行.
rowcount: 這是一個只讀屬性,傳回受影響的行數execute()方法.
#!/usr /bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) 花產
, cur. cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE
我 ute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetch
lname = row[1]
age = row[2]
sex = row[3]
income = row [4]
,income= %d" %
(fname, lname, age, sex, income )
except:
print "Error: unable to fp.
更新作業:
對任何資料庫更新操作意味著更新已經可以在資料庫中的一個或多個記錄。以下是更新所有的記錄為「M」SEX的過程。在這裡,我們將所有男性年齡增加一年.
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(”localhost","test"localhost","test" ,"TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE requirequire()
# Prepare SQL query to UPDATE required reAGEd reAGE WHERE SEX = '%c'" % ('M')
try:
# Execute the SQL command
cursor.execute()
# Commit in case there is any error
db.rollback()
# disconnect from server
db.close()
刪除作業:
DELETE操作是必要的,當你想刪除資料庫。以下是程式刪除員工的所有記錄,其中年齡超過20歲.
範例:
#!/usr/bin/python
import MySQLdb
蒟#base connection
import MySQLdb
"testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query toLE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db。 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,037575,0375,037,037,037,000,000,085,000,085,000,085,005,0025,037575,0375,0375,0375,0375,0025,0375,0375,0375,037,0025,0025,0375,0375,0375,037,0025,0375,0375,037,0025,0375,03757,0375,037)。 1385625423,1),
(3,'bright','13641154657','jackieming@sina.cn',1385625423,1385625423,1),
]v🜵聯聯7(ocur帶). con.close()