python操作MySQL資料庫連線(pymysql)

目錄

• 一:python操作MySQL
  • 1.python如何操作MySQL？
  • 2.什麼是 PyMySQL？
• 二:PyMySQL 安裝
  • 1.方法一:
  • 2.方法二:
• 三:pyMySQL模組基本使用
  • 1.pyMySQL模組基本使用
• 三:pymysql主要方法
  • 1.pymysql主要方法
  • 2.內容解析:
• 四:fetchall對資料存在游標的概念
  • 1.驗證游標的存在
  • 2.fetchone異常返回結果原因
  • 3.解決游標移動問題
  • 4.相對移動(相對當前位置移動)
• 五:SQL隱碼攻擊問題
  • 1.驗證注入問題存在
  • 2.漏洞1
  • 3.漏洞2
  • 4.綜合以上漏洞
  • 5.造成sql語句漏洞的原因(sql注入問題)
• 六:execute方法(解決sql注入問題)
  • 1.execute方法
  • 2.解決注入問題
• 七:conn.commit()二次確認
  • 1.二次確認作用
  • 2.驗證不使用二次確認情況
  • 3.查詢表內資料 無影響
  • 2.插入語句能夠執行 但是並沒有影響資料
  • 3.更新語句能夠執行，但是並沒有影響表的資料
• 4.刪除語句能夠執行，但是並沒有影響資料
  • 5.總結:
  • 6.不能實現增刪改原因
  • 7.conn.commit()二次確認
  • 8.驗證二次確認(手動測試)
  • 9.解決手動操作二次確認(自動化二次確認)
• 八:executemany()同時插入多個資料
  • 1.同時插入多個資料

一:python操作MySQL

1.python如何操作MySQL？

為了使python連線上資料庫，你需要一個驅動，這個驅動是用於與資料庫互動的庫。

PyMySQL ： 這是一個使Python連線到MySQL的庫，它是一個純Python庫(資料夾)。

PyMySQL 是一個純 Python 實現的 MySQL 客戶端操作庫，支援事務、存取過程、批量執行，實現增刪改查等。

2.什麼是 PyMySQL？

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

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

二:PyMySQL 安裝

1.方法一:

1.命令列輸入(下載模組 pymysql)
pip3 install pymysql

2.方法二:

切換下載的源(倉庫)
pip3 install pymysql -i 源地址

下載第三方模組的方式

• 下載模組與切換源詳細教程:https://www.cnblogs.com/goOJBK/p/15621209.html

三:pyMySQL模組基本使用

1.pyMySQL模組基本使用

import pymysql

# 建立連結
conn = pymysql.connect(  # 賦值給 conn連線物件
    host='127.0.0.1',  # 本地迴環地址
    port=3306,  # 預設埠
    user='root',  # 使用者名稱
    password='123',  # 密碼
    database='db_01',  # 連線資料庫名稱
    charset='utf8'  # 編碼 不能寫utf-8
)

# 生成一個遊標物件(相當於cmd開啟mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
# 定義SQL語句
sql = 'select * from egg'
# 執行SQL語句
cursor.execute(sql)
# 獲取返回結果
res = cursor.fetchall()
print(res)

三:pymysql主要方法

1.pymysql主要方法

import pymysql

# 建立連結
conn = pymysql.connect(  # 賦值給 conn連線物件
    host='127.0.0.1',  # 本地迴環地址
    port=3306,  # 預設埠
    user='root',  # 使用者名稱
    password='123',  # 密碼
    database='db_01',  # 連線資料庫名稱
    charset='utf8'  # 編碼 不能寫utf-8
)

# 生成一個遊標物件(相當於cmd開啟mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
# 定義SQL語句
sql = 'select * from egg'
# 執行SQL語句
ret = cursor.execute(sql)

print(ret)  # 返回值是執行SQL語句之後受影響的行數

# 獲取返回結果
# res = cursor.fetchall()  # 列表套字典
# res = cursor.fetchone()  # 資料字典
# res = cursor.fetchmany(3)  # 列表套字典
print(res)

2.內容解析:

1.execute返回值是執行SQL語句之後受影響的行數

2.fetchall()獲取所有的結果
fetchone()獲取結果集第一個結果
fetchmany()括號內可以指定獲取幾個結果集

四:fetchall對資料存在游標的概念

1.驗證游標的存在

import pymysql

# 建立連結
conn = pymysql.connect(  # 賦值給 conn連線物件
    host='127.0.0.1',  # 本地迴環地址
    port=3306,  # 預設埠
    user='root',  # 使用者名稱
    password='123',  # 密碼
    database='db_01',  # 連線資料庫名稱
    charset='utf8'  # 編碼 不能寫utf-8
)

# 生成一個遊標物件(相當於cmd開啟mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
# 定義SQL語句
sql = 'select * from egg'
# 執行SQL語句
ret = cursor.execute(sql)
print(ret)  # 返回值是執行SQL語句之後受影響的行數
# 獲取返回結果
res = cursor.fetchall()
res1 = cursor.fetchall()
print(res)
print(res1)

2.fetchone異常返回結果原因

解析:
	上述方法對資料的獲取也存在游標的概念

fetchall()獲取所有的結果

3.解決游標移動問題

4.相對移動(相對當前位置移動)

import pymysql

# 建立連結
conn = pymysql.connect(  # 賦值給 conn連線物件
    host='127.0.0.1',  # 本地迴環地址
    port=3306,  # 預設埠
    user='root',  # 使用者名稱
    password='123',  # 密碼
    database='db_01',  # 連線資料庫名稱
    charset='utf8'  # 編碼 不能寫utf-8
)

# 生成一個遊標物件(相當於cmd開啟mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
# 定義SQL語句
sql = 'select * from egg'
# 執行SQL語句
ret = cursor.execute(sql)
print(ret)  # 返回值是執行SQL語句之後受影響的行數
# 獲取返回結果
res = cursor.fetchone()  # 獲取結果集的第一個結果
# cursor.scroll(1, 'relative')  # 相對當前位置移動(相對移動)
# cursor.scroll(0, 'absolute')  # 相對資料開頭位置移動(絕對移動)
res1 = cursor.fetchall()  # 獲取所有結果
print(res)
print(res1)

相對移動

絕對移動

五:SQL隱碼攻擊問題

1.驗證注入問題存在

import pymysql

# 建立連結
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8'
)

# 生成一個遊標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典

# 獲取使用者名稱和密碼
username = input('username>>>:').strip()
password = input('password>>>:').strip()

# 構造SQL語句
sql = "select * from egg where name='%s' and password='%s'"%(username,password)
print(sql)
# 執行sql語句
cursor.execute(sql)
# 獲取所有返回結果
res = cursor.fetchall()
# if判斷
if res:
    print(res)
    print('登入成功')
else:
    print('使用者名稱或密碼錯誤')

目前的pymysq程式碼使用者登入是否安全了？
讓我們進行測試pymysql程式碼(安全性)

2.漏洞1

3.漏洞2

4.綜合以上漏洞

1.使用者名稱不正確 不需要密碼也能登入
2.使用者名稱和密碼都不需要也能登入

5.造成sql語句漏洞的原因(sql注入問題)

SQL隱碼攻擊問題的產生
	就是通過一線特殊符號的組合 達到某些特定的效果從而避免常規的邏輯
SQL隱碼攻擊問題如何解決
    execute方法自動幫你解決

六:execute方法(解決sql注入問題)

1.execute方法

execute方法，自動將 使用者名稱和密碼放在對應的%s內，並且放之前會自動對使用者名稱和密碼做特殊符號的校驗，確保安全性。

2.解決注入問題

import pymysql

# 建立連結
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8'
)

# 生成一個遊標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典

登入功能
1.獲取使用者名稱和密碼
2.基於使用者名稱和密碼直接精準查詢
username = input('username>>>:').strip()
password = input('password>>>:').strip()

# 構造SQL語句
sql = "select * from egg where name=%s and password=%s"  # 針對核心資料 不要自己拼接 交由execute方法幫你篩選再拼接
print(sql)
# 執行sql語句
cursor.execute(sql,(username, password))
# 獲取所有返回結果
res = cursor.fetchall()
# if判斷
if res:
    print(res)
    print('登入成功')
else:
    print('使用者名稱或密碼錯誤')

七:conn.commit()二次確認

1.二次確認作用

二次確認作用:
針對增刪改操作 需要二次確認才可生效

2.驗證不使用二次確認情況

import pymysql

# 建立連結
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8'
)

# 生成一個遊標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
sql1 = 'select * from egg'  # 查詢表內資料
sql2 = 'insert into egg(name,password) values(%s,%s)'  # 插入資料
sql3 = 'update egg set name=jasonNB where id=1'  # 修改資料
sql4 = 'delete from egg where id=2'  # 刪除資料

3.查詢表內資料 無影響

cursor.execute(sql1)
print(cursor.fetchall())

2.插入語句能夠執行 但是並沒有影響資料

cursor.execute(sql2,('objk',123))

3.更新語句能夠執行，但是並沒有影響表的資料

res = cursor.execute(sql3)
print(res)

4.刪除語句能夠執行，但是並沒有影響資料

res = cursor.execute(sql4)
print(res)

5.總結:

1.查詢語句可以正常執行並獲取結果
cursor.execute(sql1)
2.插入語句能夠執行 但是並沒有影響表資料
cursor.execute(sql2,('jackson',666))
3.更新語句能夠執行 但是並沒有影響表資料
res = cursor.execute(sql3)
print(res)
4.刪除語句能夠執行 但是並沒有影響表資料
res = cursor.execute(sql4)
print(res)

6.不能實現增刪改原因

因為，該四項操作重要程度不一樣，增刪改查的級別不一樣，查的級別是最低的，所有可以操作，但是，增刪改的級別最高的，pymysq沒有許可權去操作，需要使用到二次確認才能進行增刪改操作。

'''針對增刪改操作 需要二次確認才可生效 '''

7.conn.commit()二次確認

二次確認關鍵字:
commit()
作用:
	針對增刪改操作 需要二次確認才可生效

8.驗證二次確認(手動測試)

'''針對增刪改操作 需要二次確認才可生效'''
# 1.插入資料
cursor.execute(sql2,('tony',123))
# 二次確認
conn.commit()

# 2.修改資料
cursor.execute(sql3)
# 二次確認
conn.commit()

# 3.刪除資料
cursor.execute(sql4)
# 二次確認
conn.commit()

9.解決手動操作二次確認(自動化二次確認)

自動二次確認:
autocommmit=True  # 涉及到增刪改查 自動二次確認

import pymysql

# 建立連結
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_01',
    charset='utf8',
    autocommit=True  # 涉及到增刪改 自動二次確認
)

# 生成一個遊標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
sql1 = 'select * from egg'
sql2 = 'insert into egg(name,password) values(%s,%s)'
sql3 = 'update egg set name="jaonNB" where id=2'
sql4 = 'delete from egg where id=1'

'''針對增刪改操作 需要二次確認才可生效'''
# 1.插入資料
cursor.execute(sql2,('laike',123))

# 2.修改資料
cursor.execute(sql3)

# 3.刪除資料
cursor.execute(sql4)


主動關閉連結 釋放資源
conn.close()

八:executemany()同時插入多個資料

1.同時插入多個資料

# 同時插入多個資料
cursor.executemany(sql2,[('jason1',1),('jason2',2),('jason3',3)])