Python pymsql模組

• 2020 年 9 月 4 日
• 筆記
• Python基礎

pymsql

　　 pymysql這款第三方庫可以幫助我們利用python語言與mysql進行鏈接

基本使用

　　 首先要下載pymysql

pip install pymsql

　　 以下是pymysql的基本使用

import pymysql

# 鏈接，C/S架構，TCP鏈接
conn = pymysql.connect(
        host="localhost",
        database="db1",
        charset="utf8mb4", 
        user="root",
        cursorclass=pymysql.cursors.DictCursor, # 記錄結果，字典顯示
        # password = "your password",
        )  

# 游標
cursor = conn.cursor()

# 執行sql
sql = "show tables"
res = cursor.execute(sql)  # 提交執行，返回sql影響成功的行數

print(res)  # 2 代表該資料庫下有2個表
print(cursor.fetchall())  # [{'Tables_in_db1': 't1'}, {'Tables_in_db1': 't2'}]

cursor.close()  # 關閉游標
conn.close()

游標概念

　　 可以看到在上面的示例中有一個游標的概念，其實這個也非常簡單，就等同於游標的上下移動，每移動一次代表一條記錄。

　　 在pymsql中，對於select等操作返回的結果都可以通過游標的移動配合相應方法函數來進行讀取。

sql注入

　　 如果你的某些sql語句要進行字元串拼接，那麼一定要使用pymysql提供的execute()方法進行拼接，不要去用python中的%或format()方法，這可能導致出現sql注入問題帶來不安全的隱患。

　　 注意：使用execute()時，不可傳入表名，資料庫名。否則會拋出語法錯誤，這是因為在拼接時會自動添加上“號

import pymysql

# 鏈接
conn = pymysql.connect(
        host="localhost",
        database="db1",
        charset="utf8mb4", 
        user="root",
        cursorclass=pymysql.cursors.DictCursor, # 記錄結果，字典顯示
        # password = "your password",
        )  

# 游標
cursor=conn.cursor()

# 執行sql
sql = "select * from t1 where id=%s"
res = cursor.execute(sql,("1",))  # 提交執行，返回sql影響成功的行數 這裡拼接能預防sql注入問題

print(res) # 1 查出一條記錄
print(cursor.fetchall()) # 拿到所有記錄的結果

cursor.close()  # 關閉游標
conn.close()

事務提交

　　 在執行UPDATE/INSERT/DELETE之類的操作，必須使用conn.commit()進行事務提交後方可生效。

　　 或者你可以在實例化conn對象時為他指定auto_commit參數為true即可自動提交事務。

import pymysql

# 鏈接
conn = pymysql.connect(
        host="localhost",
        database="db1",
        charset="utf8mb4", 
        user="root",
        cursorclass=pymysql.cursors.DictCursor, # 記錄結果，字典顯示
        autocommit = True, # 自動提交
        # password = "your password",
        )  

# 游標
cursor=conn.cursor()

# 執行sql
sql = "insert into t1(name) values(%s)"
res = cursor.execute(sql,("新記錄",))  # 提交執行，返回sql影響成功的行數 這裡拼接能預防sql注入問題

print(res) # 1 成功插入一條記錄
print(cursor.lastrowid) #在插入語句後查看，查看最後一條記錄的行號
print(cursor.fetchall())

# conn.commit() # 手動提交
cursor.close()  # 關閉游標
conn.close()

提交多條

　　 使用cursor.executemany()方法可一次性提交多條sql操作。

import pymysql

# 鏈接
conn = pymysql.connect(
        host="localhost",
        database="db1",
        charset="utf8mb4", 
        user="root",
        cursorclass=pymysql.cursors.DictCursor, # 記錄結果，字典顯示
        autocommit = True, # 自動提交
        # password = "your password",
        )  

# 游標
cursor=conn.cursor()

# 執行sql
sql = "insert into t1(name) values(%s)"  # 同一條命令，執行3次
res = cursor.executemany(sql,[("新記錄1"),("新紀錄2"),("新紀錄3")])  # 提交執行，返回sql影響成功的行數 這裡拼接能預防sql注入問題

print(res) # 3 成功插入三條記錄
print(cursor.lastrowid) #在插入語句後查看，查看最後一條記錄的行號
print(cursor.fetchall())

cursor.close()  # 關閉游標
conn.close()

游標相關

　　 獲取到一條記錄後，我們可以控制游標移動。

　　 也可以控制查看游標後的多少條記錄

　　 游標每移動一次代表一條記錄

　　 如果我們想獲取記錄，可使用以下三個方法

import pymysql

# 鏈接
conn = pymysql.connect(
        host="localhost",
        database="db1",
        charset="utf8mb4", 
        user="root",
        cursorclass=pymysql.cursors.DictCursor, # 記錄結果，字典顯示
        autocommit = True, # 自動提交
        # password = "your password",
        )  

# 游標
cursor=conn.cursor()

# 執行sql
sql = "select * from t1"  #  t1表中4條記錄

cursor.execute(sql)

print(cursor.fetchone()) 游標移動到2的位置
cursor.scroll(2,mode='relative') 向下移動2，當前游標為4
print(cursor.fetchone())

cursor.close()  # 關閉游標
conn.close()

"""

{'id': 1, 'name': '記錄1'}
{'id': 4, 'name': '記錄4'}

"""

插入行號

　　 如果執行的是INSERT操作，可以在插入後查看最後插入的ID行號

import pymysql

# 鏈接
conn = pymysql.connect(
        host="localhost",
        database="db1",
        charset="utf8mb4", 
        user="root",
        cursorclass=pymysql.cursors.DictCursor, # 記錄結果，字典顯示
        autocommit = True, # 自動提交
        # password = "your password",
        )  

# 游標
cursor=conn.cursor()

# 執行sql
sql = "insert into t1(name) values(%s)"  # 同一條命令，執行3次
res = cursor.executemany(sql,[("新記錄1"),("新紀錄2"),("新紀錄3")])  # 提交執行，返回sql影響成功的行數 這裡拼接能預防sql注入問題

print(res) # 3 成功插入三條記錄
print(cursor.lastrowid) #在插入語句後查看，查看最後一條記錄的行號
print(cursor.fetchall())

# conn.commit() # 手動提交
cursor.close()  # 關閉游標
conn.close()