42. 資料庫程式設計

星光映梦發表於2024-12-05
資料庫程式設計

一、資料庫程式設計介面

  程式執行的時候，資料都是在記憶體中的。當程式終止的時候，通常都需要將資料儲存到磁碟上。為了便於程式儲存的讀取資料，並能直接透過條件查詢跨快速查詢指定的資料，就出現了資料庫（Database）這種專門用於集中儲存和查詢的軟體。

  在 Python 中提供了資料庫連線物件，它提供了獲取資料庫遊標物件和提交/回滾事務的方法，以及如何關閉資料庫連線。

【1】、獲取連線物件

  我們可以使用 connect() 函式獲取連線物件，該函式有多個引數，具體使用哪個引數，取決於使用的資料庫型別。connect() 方法返回連線物件，這個物件表示目前和資料庫的會話。連線物件支援的方法如下：

close()                                 # 關閉資料庫連線
commit()                                # 提交當前事務
rollback()                              # 取消當前事務
cursor()                                # 使用該連線建立（並返回）一個遊標物件

【2】、獲取遊標物件

  遊標物件代表資料庫中的遊標，用於指示抓取資料操作的上下文。主要提供執行 SQL 語句、呼叫儲存過程、獲取查詢結果等方法。我們可以透過使用連線物件的 cursor() 方法獲取遊標物件。

  遊標物件的常用屬性如下：

arraysize               # 使用fetchmany()方法時，一次取出的結果行數，預設為1
connection              # 建立此遊標的連線
description             # 返回遊標活動狀態（7項元組）：(name,type_code,display_size,internal_size,precision,scale,null_ok)，只有name和type_code是必須的
lastrowid               # 上次修改行的ID，如果不支援行ID，則返回None
rowcount                # 上次execute()方法處理或影響的行數

  遊標物件的常用方法如下：

callproc(procname[,parameters])         # 呼叫儲存過程
close()                                 # 關閉遊標
execute(operation[,parameters])         # 執行資料庫查詢或命令
executemany(operation,seq_of_params)    # 批次執行資料庫查詢或命令
fetchone()                              # 獲取查詢結果集的下一條記錄
fetchmany(size)                         # 獲取指定數量的記錄
fetchall()                              # 獲取結果集的所有記錄
nextset()                               # 跳轉下一個可用的結果集（如果支援）
setinputsizes(sizes)                    # 設定在呼叫execute()方法是分配的記憶體區域大小
setoutputsize(sizes)                    # 設定列緩衝區大小

二、使用SQLite資料庫

  與許多其它資料庫管理系統不同，SQLite 不是一個客戶端/伺服器結構的資料庫引擎，而是一種嵌入式資料庫，它的資料庫就是一個檔案。SQLite 將整個資料庫，包括定義、表、索引以及資料本身，作為一個單獨的、可跨平臺使用的檔案儲存在主機中。

  由於 Python 中已經內建了 SQLite3，所以我們可以直接使用 import 語句匯入 SQLite3 模組，無需安裝任何其它的模組，可以直接使用。

import sqlite3

connection = sqlite3.connect("test.db")         # 獲取連線
cursor = connection.cursor()                    # 獲取遊標

# 執行SQL語句，建立表
cursor.execute("create table if not exists user(name varchar(20), age int)")

# 向表中插入資料，新增使用者資訊
cursor.execute("insert into user(name,age) values('Sakura',10)")
cursor.execute("insert into user(name,age) values('Mikoto',14)")
cursor.execute("insert into user(name,age) values('Shana',15)")

# 執行查詢結果
cursor.execute("select * from user")
# 獲取查詢結果
result = cursor.fetchone()                     # 使用fetchone()獲取一條記錄
print(result,end="\n\n")

result = cursor.fetchmany(2)                   # 使用fetchmany()獲取多條記錄
print(result,end="\n\n")

result = cursor.fetchall()                     # 使用fetchall()獲取全部查詢結果
print(result,end="\n\n")

# 修改使用者資訊
# 使用問號作為佔位符代替具體的數值，然後使用一個元組來替代問號
# 使用佔位符的方式可以避免SQL隱碼攻擊的風險
cursor.execute("update user set age = ? where name = ?",(12,"Sakura"))
cursor.execute("select * from user where name = ?",("Sakura",))
result = cursor.fetchall()
print(result,end="\n\n")

# 刪除使用者資料
cursor.execute("delete from user where name = ?",("Shana",))
cursor.execute("select * from user")
result = cursor.fetchall()
print(result,end="\n\n")

cursor.close()                                  # 關閉遊標
connection.close()                              # 關閉資料庫

三、使用MySQL資料庫

  在 Python 中支援 MySQL 的資料庫模組有很多，這裡我們選擇使用 pymysql。我們可以在終端中使用 pip 命令安裝：

pip intall pymysql

  在使用 pymysql 模組運算元據庫時，首先需要建立出資料庫。

CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8;

import pymysql

# 開啟資料庫連線
db = pymysql.connect(
    user="root",                                # 使用者名稱
    password="abc123",                          # 密碼
    host="localhost",                           # 主機地址
    port=3306,                                  # 埠號
    database="db_test",                         # 資料庫名
)

# 使用cursor()方法建立一個遊標物件cursor
cursor = db.cursor()

data = [
    ('Sakura',10),
    ('Mikoto',14),
    ('Shana',15),
]

try:
    # 使用execute()方法，執行SQL語句，建立表
    cursor.execute("create table if not exists user(name varchar(20), age int)")

    # 向表中插入資料，新增使用者資訊
    # 執行sql語句，插入多條記錄
    # 不管欄位是什麼型別的，佔位符統一是%s
    cursor.executemany("insert into user(name,age) values(%s,%s)",data)
    db.commit()                                 # 提交資料
except:
    db.rollback()                               # 發生錯誤時回滾

# 執行查詢結果
cursor.execute("select * from user")
# 獲取查詢結果
result = cursor.fetchone()                      # 使用fetchone()獲取一條記錄
print(result,end="\n\n")

result = cursor.fetchmany(2)                    # 使用fetchmany()獲取多條記錄
print(result,end="\n\n")

result = cursor.fetchall()                      # 使用fetchall()獲取全部查詢結果
print(result,end="\n\n")

# 修改使用者資訊
# 使用問號作為佔位符代替具體的數值，然後使用一個元組來替代問號
# 使用佔位符的方式可以避免SQL隱碼攻擊的風險
try:
    cursor.execute("update user set age = %s where name = %s",(12,"Sakura"))
    db.commit()
except:
    db.rollback()

cursor.execute("select * from user where name = %s",("Sakura",))
result = cursor.fetchall()
print(result,end="\n\n")

# 刪除使用者資料
try:
    cursor.execute("delete from user where name = %s","Shana")
    db.commit()
except:
    db.rollback()

cursor.execute("select * from user")
result = cursor.fetchall()
print(result,end="\n\n")

cursor.close()                                  # 關閉遊標
db.close()                                      # 關閉資料庫連線

相關文章