python连接mysql数据库操作

http://liu-jiaqiang./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(): 这种方法获取查询结果集的下一行。结果集是一个对象时，将返回一个游标对象用于查询表.

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