Python之MySQL

標籤：not   操作   規範   資料庫   count   int   提交   iso   hone   

本文我們為大家介紹 Python3 使用 PyMySQL 串連資料庫，並實現簡單的增刪改查。

什麼是 PyMySQL？

PyMySQL 是在 Python3.x 版本中用於串連 MySQL 伺服器的一個庫，Python2中則使用mysqldb。

PyMySQL 遵循 Python 資料庫 API v2.0 規範，並包含了 pure-Python MySQL 用戶端庫。

資料庫連接

串連資料庫前，請先確認以下事項：

• 您已經建立了資料庫 TESTDB.
• 在TESTDB資料庫中您已經建立了表 EMPLOYEE
• EMPLOYEE表欄位為 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
• 串連資料庫TESTDB使用的使用者名稱為 "testuser" ，密碼為 "test123",你可以可以自己設定或者直接使用root使用者名稱及其密碼，Mysql資料庫使用者授權請使用Grant命令

執行個體：

以下執行個體連結Mysql的TESTDB資料庫：

#!/usr/bin/env python# -*- coding:utf-8 -*-# Author:Jerry Shiimport pymysql# 開啟資料庫連接db = pymysql.connect("192.168.1.3","root","123456","TESTDB" )# 使用 cursor() 方法建立一個遊標對象 cursorcursor = db.cursor()# 使用 execute()  方法執行 SQL 查詢cursor.execute("SELECT VERSION()")# 使用 fetchone() 方法擷取單條資料.data = cursor.fetchone()print ("Database version : %s " % data)# 關閉資料庫連接db.close()

建立資料庫表

如果資料庫連接存在我們可以使用execute()方法來為資料庫建立表，如下所示建立表EMPLOYEE：

#!/usr/bin/env python# -*- coding:utf-8 -*-# Author:Jerry Shiimport pymysql# 開啟資料庫連接db = pymysql.connect("192.168.1.3","root","123456","TESTDB" )# 使用 cursor() 方法建立一個遊標對象 cursorcursor = 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()

資料庫插入操作

以下執行個體使用執行 SQL INSERT 語句向表 EMPLOYEE 插入記錄：

#!/usr/bin/env python# -*- coding:utf-8 -*-# Author:Jerry Shiimport pymysql# 開啟資料庫連接db = pymysql.connect("192.168.1.3","root","123456","TESTDB" )# 使用 cursor() 方法建立一個遊標對象 cursorcursor = db.cursor()# SQL 插入語句sql = """INSERT INTO EMPLOYEE(FIRST_NAME,         LAST_NAME, AGE, SEX, INCOME)         VALUES (‘Mac‘, ‘Mohan‘, 20, ‘M‘, 2000)"""try:   # 執行sql語句   cursor.execute(sql)   # 提交到資料庫執行   db.commit()except:   # 如果發生錯誤則復原   db.rollback()# 關閉資料庫連接db.close()

資料庫查詢操作

Python查詢Mysql使用 fetchone() 方法擷取單條資料, 使用fetchall() 方法擷取多條資料。

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

執行個體：

查詢EMPLOYEE表中salary（工資）欄位大於1000的所有資料：

#!/usr/bin/env python# -*- coding:utf-8 -*-# Author:Jerry Shiimport pymysql# 開啟資料庫連接db = pymysql.connect("192.168.1.3","root","123456","TESTDB" )# 使用 cursor() 方法建立一個遊標對象 cursorcursor = db.cursor()# SQL 查詢語句sql = "SELECT * FROM EMPLOYEE        WHERE INCOME > ‘%d‘" % (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=%d,sex=%s,income=%d" %              (fname, lname, age, sex, income ))except:   print ("Error: unable to fecth data")# 關閉資料庫連接db.close()

資料庫更新操作

更新操作用於更新資料表的的資料，以下執行個體將 TESTDB表中的 SEX 欄位全部修改為 ‘M‘，AGE 欄位遞增1：

#!/usr/bin/env python# -*- coding:utf-8 -*-# Author:Jerry Shiimport pymysql# 開啟資料庫連接db = pymysql.connect("192.168.1.3","root","123456","TESTDB" )# 使用 cursor() 方法建立一個遊標對象 cursorcursor = 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()

刪除操作

刪除操作用於刪除資料表中的資料，以下執行個體示範了刪除資料表 EMPLOYEE 中 AGE 大於 20 的所有資料：

#!/usr/bin/env python# -*- coding:utf-8 -*-# Author:Jerry Shiimport pymysql# 開啟資料庫連接db = pymysql.connect("192.168.1.3","root","123456","TESTDB" )# 使用 cursor() 方法建立一個遊標對象 cursorcursor = db.cursor()# SQL 刪除語句sql = "DELETE FROM EMPLOYEE WHERE AGE > ‘%d‘" % (20)try:   # 執行SQL語句   cursor.execute(sql)   # 提交修改   db.commit()except:   # 發生錯誤時復原   db.rollback()# 關閉串連db.close()

執行事務

事務機制可以確保資料一致性。

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

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

 

Python之MySQL