StandardError
|__Warning
|__Error
|__InterfaceError
|__DatabaseError
|__DataError
|__OperationalError
|__IntegrityError
|__InternalError
|__ProgrammingError
|__NotSupportedError

#! /usr/bin/env python
# -*- coding: utf-8 -*-

# *************************************************************
#  Filename @ operatemysql.py
#  Author @ Huoty
# Create date @ 2015-08-16 10:44:34
# Description @ 
# *************************************************************

import MySQLdb

# Script starts from here

# 連接數(shù)據(jù)庫
db_conn = MySQLdb.connect(host = 'localhost', user= 'root', passwd = '123456')

# 如果已經(jīng)創(chuàng)建了數(shù)據(jù)庫，可以直接用如下方式連接數(shù)據(jù)庫
#db_conn = MySQLdb.connect(host = "localhost", user = "root",passwd = "123456", db = "testdb")

"""
connect方法常用參數(shù):
 host: 數(shù)據(jù)庫主機(jī)名.默認(rèn)是用本地主機(jī)
 user: 數(shù)據(jù)庫登陸名.默認(rèn)是當(dāng)前用戶
 passwd: 數(shù)據(jù)庫登陸的秘密.默認(rèn)為空
 db: 要使用的數(shù)據(jù)庫名.沒有默認(rèn)值
 port: MySQL服務(wù)使用的TCP端口.默認(rèn)是3306
 charset: 數(shù)據(jù)庫編碼
"""

# 獲取操作游標(biāo) 
cursor = db_conn.cursor()

# 使用 execute 方法執(zhí)行SQL語句
cursor.execute("SELECT VERSION()")

# 使用 fetchone 方法獲取一條數(shù)據(jù)庫。
dbversion = cursor.fetchone()

print "Database version : %s " % dbversion

# 創(chuàng)建數(shù)據(jù)庫
cursor.execute("create database if not exists dbtest")

# 選擇要操作的數(shù)據(jù)庫
db_conn.select_db('dbtest');

# 創(chuàng)建數(shù)據(jù)表SQL語句
sql = """CREATE TABLE if not exists employee(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20),
   age INT, 
   sex CHAR(1),
   income FLOAT )"""

try:
 cursor.execute(sql)
except Exception, e:
 # Exception 是所有異常的基類，這里表示捕獲所有的異常
 print "Error to create table:", e

# 插入數(shù)據(jù)
sql = """INSERT INTO employee(first_name,
   last_name, age, sex, income)
   VALUES ('%s', '%s', %d, '%s', %d)"""

# Sex: Male男, Female女

employees = ( 
  {"first_name": "Mac", "last_name": "Mohan", "age": 20, "sex": "M", "income": 2000},
  {"first_name": "Wei", "last_name": "Zhu", "age": 24, "sex": "M", "income": 7500},
  {"first_name": "Huoty", "last_name": "Kong", "age": 24, "sex": "M", "income": 8000},
  {"first_name": "Esenich", "last_name": "Lu", "age": 22, "sex": "F", "income": 3500},
  {"first_name": "Xmin", "last_name": "Yun", "age": 31, "sex": "F", "income": 9500},
  {"first_name": "Yxia", "last_name": "Fun", "age": 23, "sex": "M", "income": 3500}
  )

try:
 # 清空表中數(shù)據(jù)
 cursor.execute("delete from employee")
 # 執(zhí)行 sql 插入語句
 for employee in employees:
  cursor.execute(sql % (employee["first_name"], \
   employee["last_name"], \
   employee["age"], \
   employee["sex"], \
   employee["income"]))
 # 提交到數(shù)據(jù)庫執(zhí)行
 db_conn.commit()
 # 對(duì)于支持事務(wù)的數(shù)據(jù)庫， 在Python數(shù)據(jù)庫編程中，
 # 當(dāng)游標(biāo)建立之時(shí)，就自動(dòng)開始了一個(gè)隱形的數(shù)據(jù)庫事務(wù)。
 # 用 commit 方法能夠提交事物
except Exception, e:
 # Rollback in case there is any error
 print "Error to insert data:", e
 #b_conn.rollback()

print "Insert rowcount:", cursor.rowcount
# rowcount 是一個(gè)只讀屬性，并返回執(zhí)行execute(方法后影響的行數(shù)。)

# 數(shù)據(jù)庫查詢操作:
# fetchone()  得到結(jié)果集的下一行 
# fetchmany([size=cursor.arraysize]) 得到結(jié)果集的下幾行 
# fetchall()  返回結(jié)果集中剩下的所有行 
try:
 # 執(zhí)行 SQL
 cursor.execute("select * from employee")

 # 獲取一行記錄
 rs = cursor.fetchone()
 print rs

 # 獲取余下記錄中的 2 行記錄
 rs = cursor.fetchmany(2)
 print rs

 # 獲取剩下的所有記錄
 ars = cursor.fetchall()
 for rs in ars:
  print rs
 # 可以用 fetchall 獲得所有記錄，然后再遍歷
except Exception, e:
 print "Error to select:", e

# 數(shù)據(jù)庫更新操作
sql = "UPDATE employee SET age = age + 1 WHERE sex = '%c'" % ('M')
try:
 # 執(zhí)行SQL語句
 cursor.execute(sql)
 # 提交到數(shù)據(jù)庫執(zhí)行
 db_conn.commit()
 cursor.execute("select * from employee")
 ars = cursor.fetchall()
 print "After update: ------"
 for rs in ars:
  print rs
except Exception, e:
 # 發(fā)生錯(cuò)誤時(shí)回滾
 print "Error to update:", e
 db.rollback()

# 關(guān)閉數(shù)據(jù)庫連接
db_conn.close()