MySQL/pymysql

PyMySQL 是在 Python3連接 MySQL 伺服器的一個庫，Python2中則使用mysqldb。 PyMySQL 遵循 Python 資料庫 API v2.0 規範，並包含了 pure-Python MySQL 客戶端庫。

連接資料庫

import pymysql
 
# 打开数据库连接
pymysql.connect(host="10.219.55.186",user="UserName",password="HisPassWord",db="MyDatabaseName")
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

創建資料庫的表

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
 
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
 
cursor.execute(sql)
 
# 关闭数据库连接
db.close()

資料庫的插入

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

'''
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
'''
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

游標（cursor）

游標是為用戶開設的一個數據緩衝區，存放SQL語句的執行結果。用戶可以用SQL語句逐一從游標中獲取記錄，並賦給主變量，交由python進一步處理，一組主變量一次只能存放一條記錄。

就本質而言，游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。游標總是與一條SQL 查詢你語句相關聯。因為游標由結果集（可以是零條、一條或由相關的選擇語句檢索出的多條記錄）和結果集中指向特定記錄的游標位置組成。當決定對結果集進行處理時，必須聲明一個指向該結果集的游標。

常用方法:

cursor():創建游標對象
close():關閉此游標對象

資料庫查詢操作

fetchone(): 該方法獲取下一個查詢結果集。結果集是一個對象
fetchall(): 接收全部的返回結果行.
rowcount: 這是一個只讀屬性，並返回執行execute()方法後影響的行數。

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
 
# 关闭数据库连接
db.close()

資料庫更新操作

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

資料庫的刪除操作

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭连接
db.close()

事務機制

事務應該具有4個屬性：原子性、一致性、隔離性、持久性。這四個屬性通常稱為ACID特性。

原子性（atomicity）。一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
一致性（consistency）。事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
隔離性（isolation）。一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的數據對並發的其他事務是隔離的，並發執行的各個事務之間不能互相干擾。
持久性（durability）。持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中數據的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

Python DB API 2.0 的事務提供了兩個方法 commit 或 rollback。

# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

設置事務機制：

建立連接時，包含參數autocommit=True
建立連接後，conn.autocommit(True)

事務相關的API函數：

start transaction 開啟事務
Rollback 回滾事務,即撤銷指定的sql語句(只能回退insert delete update語句)，回滾到上一次commit的位置
Commit 提交事務，提交未存儲的事務
savepoint 保留點，事務處理中設置的臨時占位符你可以對它發布回退(與整個事務回退不同)

# coding=utf-8
import pymysql
 
#添加数据
 
conn = pymysql.connect(host="10.219.55.186",user="GoldenSampleUser",password="GoldenSampleUser",db="goldensample")
 
try:
    createSQL = "create table account(id int primary key auto_increment,name varchar (25),balance double);"
    insertSQL0="INSERT INTO ACCOUNT (name,balance) VALUES ('bart',1000)"
    insertSQL1="UPDATE account set balance=balance-30 WHERE name='alex'"
    insertSQL2="UPDATE account set balance=balance+30 WHERE name='ego'"
 
    cursor = conn.cursor()
 
    cursor.execute(createSQL)
    
    cursor.execute(insertSQL0)
    conn.commit()
 
    cursor.execute(insertSQL1)
    raise Exception        #模拟出现错误
    cursor.execute(insertSQL2)
    cursor.close()
    conn.commit() 
except Exception as e: 
    conn.rollback()      #回滚
    conn.commit() 
 
cursor.close()

conn.close()

錯誤處理

標題文本
異常	描述
Warning	當有嚴重警告時觸發，例如插入數據是被截斷等等。必須是 StandardError 的子類。
Error	警告以外所有其他錯誤類。必須是 StandardError 的子類。
InterfaceError	當有資料庫接口模塊本身的錯誤（而不是資料庫的錯誤）發生時觸發。必須是Error的子類。
DatabaseError	和資料庫有關的錯誤發生時觸發。必須是Error的子類。
DataError	當有數據處理時的錯誤發生時觸發，例如：除零錯誤，數據超範圍等等。必須是DatabaseError的子類。
OperationalError	指非用戶控制的，而是操作資料庫時發生的錯誤。例如：連接意外斷開、資料庫名未找到、事務處理失敗、內存分配錯誤等等操作資料庫是發生的錯誤。必須是DatabaseError的子類。
IntegrityError	完整性相關的錯誤，例如外鍵檢查失敗等。必須是DatabaseError子類。
InternalError	資料庫的內部錯誤，例如游標（cursor）失效了、事務同步失敗等等。必須是DatabaseError子類。
ProgrammingError	程序錯誤，例如數據表（table）沒找到或已存在、SQL語句語法錯誤、參數數量錯誤等等。必須是DatabaseError的子類。
NotSupportedError	不支持錯誤，指使用了資料庫不支持的函數或API等。例如在連接對象上使用.rollback()函數，然而資料庫並不支持事務或者事務已關閉。必須是DatabaseError的子類。