Python - 資料庫訪問

上一頁
下一頁

Python 中的資料庫訪問

Python 中的資料庫訪問用於與資料庫互動，允許應用程式一致地儲存、檢索、更新和管理資料。各種關係資料庫管理系統 (RDBMS) 都支援這些任務，每個系統都需要特定的 Python 包來進行連線。

  • GadFly
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Informix
  • Oracle
  • Sybase
  • SQLite
  • 還有更多…

程式執行期間輸入和生成的資料儲存在 RAM 中。如果要持久儲存，則需要將其儲存在資料庫表中。

關係資料庫使用 SQL（結構化查詢語言）對資料庫表執行 INSERT/DELETE/UPDATE 操作。但是，SQL 的實現方式因資料庫型別而異。這會導致相容性問題。一個數據庫的 SQL 指令與另一個數據庫不匹配。

DB-API（資料庫 API）

為了解決這個問題，Python 增強提案 (PEP) 249 引入了一個標準化的介面，稱為 DB-API。此介面為資料庫驅動程式提供了一個一致的框架，確保不同資料庫系統的行為一致。它透過建立一組通用的規則和方法，簡化了在不同資料庫之間切換的過程。

driver_interfaces

使用 Python 和 SQLite

Python 的標準庫包含 **sqlite3** 模組，這是一個與 DB_API 相容的 SQLite3 資料庫驅動程式。它作為 DB-API 的參考實現。對於其他型別的資料庫，您需要安裝相關的 Python 包。

資料庫 Python 包
Oracle cx_oracle, pyodbc
SQL Server pymssql, pyodbc
PostgreSQL PostgreSQL
MySQL MySQL Connector/Python, pymysql

使用 SQLite

由於內建的 **sqlite3** 模組，使用 Python 和 SQLite 非常容易。該過程包括：

  • **建立連線 -** 使用 sqlite3.connect() 建立連線物件，提供必要的連線憑據，例如伺服器名稱、埠、使用者名稱和密碼。

  • 事務管理 − 連線物件管理資料庫操作，包括開啟、關閉和事務控制（提交或回滾事務）。

  • 遊標物件 − 從連線中獲取遊標物件以執行SQL查詢。遊標充當資料庫上CRUD（建立、讀取、更新、刪除）操作的閘道器。

在本教程中，我們將學習如何使用Python訪問資料庫，如何將Python物件的資料儲存到SQLite資料庫中，以及如何從SQLite資料庫中檢索資料並使用Python程式進行處理。

sqlite3 模組

SQLite是一個無伺服器的、基於檔案的輕量級事務關係資料庫。它不需要任何安裝，也不需要使用者名稱和密碼等憑據來訪問資料庫。

Python的sqlite3模組包含SQLite資料庫的DB-API實現。它由Gerhard Häring編寫。讓我們學習如何使用sqlite3模組進行Python資料庫訪問。

讓我們從匯入sqlite3並檢查其版本開始。

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.39.4'

連線物件

連線物件由sqlite3模組中的connect()函式設定。此函式的第一個位置引數是一個字串，表示SQLite資料庫檔案的路徑（相對路徑或絕對路徑）。該函式返回一個引用資料庫的連線物件。

>>> conn=sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>

連線類中定義了各種方法。其中一種是cursor()方法，它返回一個遊標物件，我們將在下一節中瞭解。事務控制透過連線物件的commit()和rollback()方法實現。連線類具有重要的方法，用於定義要在SQL查詢中使用的自定義函式和聚合函式。

遊標物件

接下來，我們需要從連線物件中獲取遊標物件。在對資料庫執行任何CRUD操作時，它是您對資料庫的控制代碼。連線物件上的cursor()方法返回遊標物件。

>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>

現在，我們可以使用遊標物件可用的execute()方法執行所有SQL查詢操作。此方法需要一個字串引數，該引數必須是有效的SQL語句。

建立資料庫表

我們現在將在新建立的'testdb.sqlite3'資料庫中新增Employee表。在下面的指令碼中，我們呼叫遊標物件的execute()方法，並向其提供一個包含CREATE TABLE語句的字串。

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry='''
CREATE TABLE Employee (
EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRST_NAME TEXT (20),
LAST_NAME TEXT(20),
AGE INTEGER,
SEX TEXT(1),
INCOME FLOAT
);
'''
try:
   cur.execute(qry)
   print ('Table created successfully')
except:
   print ('error in creating table')
conn.close()

執行上述程式時，將在當前工作目錄中建立包含Employee表的資料庫。

我們可以在SQLite控制檯中列出此資料庫中的表來進行驗證。

sqlite> .open mydb.sqlite
sqlite> .tables
Employee

INSERT 操作

當您想將記錄建立到資料庫表中時，需要INSERT操作。

示例

下面的示例執行SQL INSERT語句，以在EMPLOYEE表中建立記錄 −

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   cur.execute(qry)
   conn.commit()
   print ('Record inserted successfully')
except:
   conn.rollback()
print ('error in INSERT operation')
conn.close()

您也可以使用引數替換技術來執行INSERT查詢，如下所示 −

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES (?, ?, ?, ?, ?)"""
try:
   cur.execute(qry, ('Makrand', 'Mohan', 21, 'M', 5000))
   conn.commit()
   print ('Record inserted successfully')
except Exception as e:
   conn.rollback()
   print ('error in INSERT operation')
conn.close()

READ 操作

任何資料庫上的READ操作都意味著從資料庫中獲取一些有用的資訊。

一旦建立了資料庫連線，您就可以對該資料庫進行查詢。您可以使用fetchone()方法獲取單個記錄，也可以使用fetchall()方法從資料庫表中獲取多個值。

  • fetchone() − 它獲取查詢結果集的下一行。結果集是在使用遊標物件查詢表時返回的物件。

  • fetchall() − 它獲取結果集中的所有行。如果某些行已經從結果集中提取，則它將從結果集中檢索剩餘的行。

  • rowcount − 這是一個只讀屬性，返回受execute()方法影響的行數。

示例

在下面的程式碼中，遊標物件執行SELECT * FROM EMPLOYEE查詢。結果集使用fetchall()方法獲得。我們使用for迴圈列印結果集中的所有記錄。

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="SELECT * FROM EMPLOYEE"

try:
   # Execute the SQL command
   cur.execute(qry)
   # Fetch all the rows in a list of lists.
   results = cur.fetchall()
   for row in results:
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      # Now print fetched result
      print ("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income ))
except Exception as e:
   print (e)
   print ("Error: unable to fecth data")

conn.close()

它將產生以下輸出 −

fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0
fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0

UPDATE 操作

任何資料庫上的UPDATE操作都意味著更新資料庫中已有的一個或多個記錄。

以下過程更新所有income=2000的記錄。在這裡，我們將收入增加1000。

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"

try:
   # Execute the SQL command
   cur.execute(qry, (1000,))
   # Fetch all the rows in a list of lists.
   conn.commit()
   print ("Records updated")
except Exception as e:
   print ("Error: unable to update data")
conn.close()

DELETE 操作

當您想要從資料庫中刪除一些記錄時，需要DELETE操作。以下是刪除EMPLOYEE中INCOME小於2000的所有記錄的過程。

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="DELETE FROM EMPLOYEE WHERE INCOME<?"

try:
   # Execute the SQL command
   cur.execute(qry, (2000,))
   # Fetch all the rows in a list of lists.
   conn.commit()
   print ("Records deleted")
except Exception as e:
   print ("Error: unable to delete data")

conn.close()

執行事務

事務是一種確保資料一致性的機制。事務具有以下四個屬性 −

  • 原子性 − 事務要麼完成，要麼什麼也不發生。

  • 一致性 − 事務必須從一致狀態開始，並使系統保持一致狀態。

  • 隔離性 − 事務的中間結果在當前事務之外不可見。

  • 永續性 − 一旦事務提交，其效果將是持久的，即使在系統故障之後也是如此。

Performing Transactions

Python DB API 2.0 提供了兩種方法來提交或回滾事務。

示例

您已經知道如何實現事務。這是一個類似的示例 −

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > ?"
try:
   # Execute the SQL command
   cursor.execute(sql, (20,))
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

COMMIT 操作

Commit是一個操作，它向資料庫發出綠燈訊號，以完成更改，並且在此操作之後，無法撤消任何更改。

這是一個呼叫commit方法的簡單示例。

db.commit()

ROLLBACK 操作

如果您對一個或多個更改不滿意，並且想要完全撤消這些更改，則使用rollback()方法。

這是一個呼叫rollback()方法的簡單示例。

db.rollback()

PyMySQL 模組

PyMySQL 是一個用於從 Python 連線到 MySQL 資料庫伺服器的介面。它實現了 Python 資料庫 API v2.0，幷包含一個純 Python MySQL 客戶端庫。PyMySQL 的目標是成為 MySQLdb 的直接替代品。

安裝 PyMySQL

在繼續之前，請確保您的機器上已安裝 PyMySQL。只需在您的 Python 指令碼中鍵入以下內容並執行它 −

import PyMySQL

如果它產生以下結果，則表示未安裝MySQLdb模組 −

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import PyMySQL
ImportError: No module named PyMySQL

最新的穩定版本可在 PyPI 上獲得，並可以使用 pip 安裝 −

pip install PyMySQL

注意 − 確保您具有安裝上述模組的root許可權。

MySQL 資料庫連線

在連線到MySQL資料庫之前，請確保以下幾點 −

  • 您已建立資料庫TESTDB。

  • 您已在TESTDB中建立了一個表EMPLOYEE。

  • 此表包含欄位FIRST_NAME、LAST_NAME、AGE、SEX和INCOME。

  • 已設定使用者ID“testuser”和密碼“test123”以訪問TESTDB。

  • PyMySQL Python模組已正確安裝在您的機器上。

  • 您已學習MySQL教程以瞭解MySQL基礎知識。

示例

要在之前的示例中使用MySQL資料庫而不是SQLite資料庫，我們需要更改connect()函式，如下所示 −

import PyMySQL
# Open database connection
db = PyMySQL.connect("localhost","testuser","test123","TESTDB" )

除了此更改之外，每個資料庫操作都可以毫無困難地執行。

處理錯誤

錯誤有很多來源。一些示例包括執行的SQL語句中的語法錯誤、連線失敗或呼叫已取消或完成的語句控制代碼的fetch方法。

DB API定義了許多必須存在於每個資料庫模組中的錯誤。下表列出了這些異常。

序號 異常和描述
1

警告

用於非致命性問題。必須是StandardError的子類。
2

錯誤

錯誤的基類。必須是StandardError的子類。
3

InterfaceError

用於資料庫模組中的錯誤，而不是資料庫本身。必須是Error的子類。
4

DatabaseError

用於資料庫中的錯誤。必須是Error的子類。
5

DataError

DatabaseError的子類，指的是資料中的錯誤。
6

OperationalError

DatabaseError的子類，指的是錯誤，例如與資料庫的連線丟失。這些錯誤通常不受Python指令碼編寫者的控制。
7

IntegrityError

DatabaseError的子類，用於會破壞關係完整性的情況，例如唯一性約束或外部索引鍵。
8

InternalError

DatabaseError的子類，指的是資料庫模組內部的錯誤，例如遊標不再活動。
9

ProgrammingError

DatabaseError的子類，指的是錯誤，例如錯誤的表名和其他可以安全地歸咎於您的錯誤。
10

NotSupportedError

DatabaseError的子類，指的是嘗試呼叫不受支援的功能。
列印頁面
上一頁
下一頁
廣告