35. PyMySQL

hbutmeng發表於2024-10-31
MySql

1. PyMySQL模組介紹

1.1 什麼是DB-API

Python標準資料庫規範為 DB-API， DB-API定義了一系列必須的物件和資料庫操作方式，以便為各種資料庫系統和資料庫訪問程式提供一致的訪問介面。

1.2 資料庫操作模組

DB-API介面封裝成資料庫操作模組，PyMySQL是python操作MySQL資料庫的一種模組。

PyMySQL官網

https://pypi.org/project/PyMySQL/

2. PyMySQL安裝

PyMySQL不是從官網安裝，python直譯器的安裝路徑已經被新增到系統環境變數，直接在全域性pip install PyMySQL即可

3. PyMySQL使用

3.1 操作前準備

import pymysql
from pymysql.cursors import DictCursor, Cursor

# 建立資料庫連線
conn_obj = pymysql.connect(
    user='root',
    password='123456789',
    database='test01',  # 資料庫名稱
    host='127.0.0.1',
    port=3306,
    charset='utf8mb4',  # 資料庫的編碼格式
    cursorclass=DictCursor  # DictCursor返回的資料是字典型別(帶有列名)，Cursor返回的資料是元組型別(不帶列名)

)

# 產生遊標物件
cursor_obj = conn_obj.cursor()

3.2 查詢資料

[1]查詢所有資料　　fetchall

DictCursor返回的資料是字典型別

sql = "select * from dep;"  # SQL語句會被高亮顯示
cursor_obj.execute(sql)  # 執行SQL語句
result = cursor_obj.fetchall()
print(result)

[2]查詢一條資料　　fetchone

每一個fetchone只獲取一條資料，下一個fetchone基於上一個fetchone向後獲取

sql = "select * from dep;"  # SQL語句會被高亮顯示
cursor_obj.execute(sql)  # 執行SQL語句
result = cursor_obj.fetchone()
print(result)
result2 = cursor_obj.fetchone()  # 基於上一個fetchone向後獲取
print(result2)

[3]查詢指定條數資料　　fetchmany

sql = "select * from dep;"  # SQL語句會被高亮顯示
cursor_obj.execute(sql)  # 執行SQL語句
result = cursor_obj.fetchmany()  # 預設獲取1條資料
print(result)
result2 = cursor_obj.fetchmany(2)  # 獲取指定條數2條
print(result2)

[4]移動游標　　scroll

cursor_obj.scroll(value, mode)

value---移動位置的數量

mode---relative：相對位置移動，absolute：以開頭為基準絕對位置移動

relative應用：

sql = "select * from dep;"  # SQL語句會被高亮顯示
cursor_obj.execute(sql)  # 執行SQL語句
res1 = cursor_obj.fetchone()
print(res1)
cursor_obj.scroll(1, 'relative')  # 游標向後移動了1個位置
res2 = cursor_obj.fetchone()
print(res2)

absolute應用：

sql = "select * from dep;"  # SQL語句會被高亮顯示
cursor_obj.execute(sql)  # 執行SQL語句
res1 = cursor_obj.fetchone()
print(res1)
cursor_obj.scroll(0, 'absolute')  # 相對於開頭移動了0個位置
res2 = cursor_obj.fetchone()
print(res2)
cursor_obj.scroll(1, 'absolute')  # 相對於開頭移動了1個位置
res3 = cursor_obj.fetchone()
print(res3)

3.3 插入資料

[1]表結構

[2]嘗試插入資料

sql = "insert into dep(name) values ('電腦')"
cursor_obj.execute(sql)  # 執行該sql語句之後在資料庫中並沒有插入資料

[3]需要commit提交事務才能成功插入資料

sql = "insert into dep(name) values ('電腦')"
cursor_obj.execute(sql)
conn_obj.commit()

由於步驟3是在pycharm中在步驟2的基礎上新增一行程式碼實現，快取存在，所以步驟3的id是在快取的基礎上增加

手動conn_obj.commit( )的效果與connect( )的預設引數autocommit=False改為True一致，二選一即可

[4]插入字串型別，一定要給值的中括號加引號' '

字串型別{ }不加引號會報錯

value1 = 205
value2 = '電腦'
sql = f"insert into dep(id, name) values({value1}, {value2})"
cursor_obj.execute(sql)
conn_obj.commit()

字串型別{ }加上引號正常插入資料

value1 = 205
value2 = '電腦'
sql = f"insert into dep(id, name) values({value1}, '{value2}')"
cursor_obj.execute(sql)
conn_obj.commit()

[5]為了解決步驟4給字串型別{ }加引號問題，pymysql提供了sql的格式化輸出語法

執行sql語句關鍵字execute的解釋

def execute(self, query, args=None):

　　:param query: Query to execute.　　query為需要執行的sql語句，字串型別
　　:type query: str

　　:param args: Parameters used with query. (optional)　　args可以是元組、列表、字典
　　:type args: tuple, list or dict

　　If args is a list or tuple, %s can be used as a placeholder in the query.　　

　　如果args是列表或元組，%s可以被用於佔位 按位置傳參
　　If args is a dict, %(key)s can be used as a placeholder in the query.

　　如果args是字典，則%（key）s可以用作查詢中的佔位符。　　按關鍵字傳參 　　

方法一：按位置傳入引數

value1 = 207
value2 = 'internet'
sql = f"insert into dep(id, name) values(%s, %s)"
cursor_obj.execute(sql, [value1, value2])
conn_obj.commit()

轉換說明符 　　　　　　解釋
%d、%i 　　 　　 轉換為帶符號的十進位制數
%o 　　　　　　 轉換為帶符號的八進位制數
%x、%X 　　　 轉換為帶符號的十六進位制數
%e 　　　　　　 轉化為科學計數法表示的浮點數（e 小寫）
%E 　　　　　　 轉化為科學計數法表示的浮點數（E 小寫）
%f、%F 　　　　 轉化為十進位制浮點數
%g 　　　　　　 智慧選擇使用 %f 或 %e 格式
%G 　　　　　　 智慧選擇使用 %F 或 %E 格式
%c 　　　　　　 格式化字元及其ASCII碼
%r 　　　　　　 使用 repr() 函式將表示式轉換為字串
%s 　　　　　　 使用 str() 函式將表示式轉換為字串

通常使用%s也能將python的int型別轉換為MySQL中的int型別

方法二：按關鍵字傳入引數

sql = f"insert into dep(id, name) values(%(v1)s, %(v2)s)"
cursor_obj.execute(sql, {'v1': 208, 'v2': 'develop'})
conn_obj.commit()

3.4 更新資料

sql = "update dep set name='research' where name='develop'"
cursor_obj.execute(sql)
conn_obj.commit()

3.5 刪除資料

sql = "delete from dep where id=207"
cursor_obj.execute(sql)
conn_obj.commit()

3.6 使用executemany一次插入多條資料

[1]按位置傳入

sql = "insert into dep(id, name) values(%s, %s);"
cursor_obj.executemany(sql, [(212, 'd'), (213, 'e'), (214, 'f')])
conn_obj.commit()

[2]按關鍵字傳入

sql = "insert into dep(id, name) values(%(v1)s, %(v2)s);"
cursor_obj.executemany(sql, [{'v1': 215, 'v2': 'g'}, {'v1': 216, 'v2': 'h'}, {'v1': 217, 'v2': 'i'}])
conn_obj.commit()

3.7 SQL隱碼攻擊問題

[1]字串格式化輸出+引號進行佔位傳值

模擬登入：輸入使用者名稱和密碼

login_name = input('請輸入使用者名稱:')
login_pwd = input('請輸入密碼:')
sql = f"select * from info where username='{login_name}' and password='{login_pwd}';"
print(f'sql語句為{sql}')
cursor_obj.execute(sql)
res = cursor_obj.fetchall()
print(res)
if res:
    print('登入成功')
else:
    print('登入失敗')

[2]正常輸入使用者名稱密碼進行登入

[3]SQL隱碼攻擊問題：-- 空格後面的內容全部為註釋內容

輸入使用者名稱時在使用者名稱後面加上-- ，--後面的內容全部變成了註釋，導致sql語句失效

[4]解決SQL隱碼攻擊問題的本質是處理特殊符號

execute方法自帶校驗SQL隱碼攻擊問題，自動處理特殊符號

login_name = input('請輸入使用者名稱:')
login_pwd = input('請輸入密碼:')
sql = f"select * from info where username=%s and password=%s;"
print(f'sql語句為{sql}')
cursor_obj.execute(sql, (login_name, login_pwd))
res = cursor_obj.fetchall()
print(res)
if res:
    print('登入成功')
else:
    print('登入失敗')

相關文章