MySQL——python交互

與python交互之前我們需要安裝一個MySQL的驅動模塊Connector，這個驅動模塊直接在cmd命令行輸入

pip install mysql.connector

安裝是否成功可以接著輸入

python
# 輸入之後，會進入python解釋器（前提是python已經配置好了環境變數
# 導入安裝的模塊，沒有報錯即安裝成功
import mysql.connector

進入自己的編輯器（pycharm）首先連接資料庫方式：

　　資料庫連接

　　　　　　1、當前模塊直接連接

import mysql.connector

# 連接資料庫
con = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="root",        # 可以填寫其他用戶，用戶的變數名是user
    password="XXXXXX",       # 這裡填寫上面用戶對應的密碼
    database="dbname"      # 需要連接的資料庫的名字
)
......
......
......
# 使用完資料庫之後，需要關閉資料庫，不要浪費資源
con.close()

　　　　　　2、導入配置文件進行連接

# 先創建一個配置文件 config.py
import  mysql.connector
# 下麵是字典類型
config = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "XXXXXX",
    "database": "dbname"
}

#再創建一個文件connect.py
import  mysql.connector
from config import config
# 使用配置文件連接
con = mysql.connector.connect(**config)

# 關閉連接
con.close()

　　游標

　　　　　　游標的作用是執行sql語句，或者保存sqi結果集。常用的方法：

　　　　　　　　cursor()創建游標（使用連接）
　　　　　　　　execute()執行sql語句；可以接受兩個參數，形如下麵涉及的預編譯

　　　　　　　　executemany()迴圈執行相同的sql語句第二個參數必須是一個列表

　　　　　　　　fetchall()/fetchone()獲取全部數據/一條數據

# 創建一個游標
cursor = con.cursor()

# 假設有兩個sql語句一個是查詢，一個是執行insert語句
cursor.execute(sql_select)
# 這個結果集是保存在cursor中，你可以通過fetechall()獲取全部數據或者fetchone()查詢第一條數據
data = cursor.fetchall()
# 得到的數據我們可以進行業務的處理

# 執行創建語句，不會結果集
sql = "insert into s_region values(%s, %s)"
data = [[1,"sd"],[2,"dfd"]]  # 或者裡面是元組的形式data = [(1,"sd"),(2,"dfd")]
cursor.execute(sql, data)

　　防止SQL註入攻擊方式

　　　　為什麼存在SQL註入攻擊？輸入一個不與數據表格的數據匹配也是可以繼續執行的。比如用戶輸入特定的錯誤字元串格式數據，還是可以通過驗證登錄進去

import mysql.connector

# 連接資料庫
# 1、本模塊創建連接並保存在變數中
con = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="briup2"
)
# 2、創建資料庫游標
cursor = con.cursor()
# 模擬登錄：賬戶：last_name,密碼：first_name
username = "1 or 1=1"
password = "1 or 1=1"
# 3、定義一個sql語句
sql = "select count(*) from user_table where user_id=%s and user_psd=%s"%(username, password)
# 4、執行sql語句
ursor.execute(sql )
# 5、獲取全部結果集
data = cursor.fetchall()
print(data)      # 總之結果不為0

　　　　　　上述結果不為0表示後臺可以查到某個結果集，即用戶是可以登錄進入（主頁）；如果可以註入攻擊就可以刪掉某個數據表的所有數據，這樣做是很危險的，所以需要後臺處理得到的數據

id = "1 or 1=1"
sql = "delete from tbname where id=%s" % (id)# 將訂單表數據全部刪除了--欲哭無淚
delete from tbname where id=1 or 1=1

　　　　　　防止SQL註入攻擊的三種方式：

　　　　　　　　1、通過單引號將獲取的值轉換為字元串：format格式

# 將上面的sql語句改成下麵這一語句，註意大括弧兩邊都有引號
sql = "select count(*) from user_table where user_id=’{}‘ and user_psd='{}'".format(username, password)

　　　　　　　　2、通過單引號將獲取的值轉換為字元串：字元串格式

# 將上面的sql語句改成下麵這一語句，註意%s兩邊都有引號
sql = "select count(*) from user_table where user_id=’%s‘ and user_psd='%s'"%(username, password)

　　　　　　　　3、通過預編譯的方式防止SQL註入攻擊（推薦使用）

# 將上面的sql語句改成下麵這一語句，註意%s兩邊都沒有引號
sql = "select count(*) from user_table where user_id=%s and user_psd=%s"

# 再將執行語句改成下麵這條
cursor.execute(sql, (username, password))

　　事務控制

　　　　事務的產生是因為對數據表操作之後，資料庫不會發生改變，若想發生改變，就需要事務三操作

　　　　事務控制一般有三步操作：

　　　　　　1、開啟事務：通過連接開啟事務，調用start_transaction()

　　　　　　2、提交事務：也是通過連接提交事務，一般都是適用於對數據表的增加、刪除、修改等操作，調用commit()

　　　　　　3、回滾事務：一般是發生異常之後才會執行的回滾，調用rollback()

　　　　這三步都應該在異常中執行

import mysql.connector

# 連接資料庫
try:
    """
    資料庫的連接，數據表的增加、刪除、修改操作
    都是需要在異常中處理
    """
    # 1、本模塊創建連接並保存在變數中
    con = mysql.connector.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="123456",
        database="briup2"
    )
    # 2、創建資料庫游標
    cursor = con.cursor()

    # 3、開啟事務
    con.start_transaction()

    # 4、執行一條update語句
    sql = "update user_table set username='aaa'"
    
    # 5、執行sql語句
    cursor.execute(sql)

    # 6、提交事務
    con.commit()
except:
    """
    在發生異常之前，還需判斷連接是否成功
    發生異常之後一般都是需要回滾，以免數據發生錯誤
    """
    if "con" in dir():
        con.rollback()
finally:
    """
    無論異常與否，都要執行finally語句
    """
    con.close()

　　資料庫連接池（緩存）

　　　　上述代碼的開頭都會寫創建資料庫的連接和最後也會寫資料庫的關閉，如果程式反覆創建和關閉，是會浪費大量的資源。而資料庫連接池是在程式運行之前就已經緩存好了，那麼就可以直接獲取連接就行，而且不使用資料庫也不用關閉，緩存機制會自動回收

# 先創建一個配置文件 config.py
config = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "briup2"
}

# 在創建一個測試文件 exam.py
import mysql.connector.pooling
from config import config
pool = mysql.connector.pooling.MySQLConnectionPool(
    **config,
    pool_size=10
)

try:
    # 2、先獲取一個連接
    con = pool.get_connection()
    # 3、開啟事務
    con.start_transaction()
    # 4、創建一個游標
    cursor = con.cursor()
    # 5、定義sql語句
    sql_create = "create table s_emp_new like s_emp;"
    sql_insert = """
        insert into s_emp_new 
        select * 
        from s_emp
        where dept_id in (      
            select dept_id
            from s_emp
            group by dept_id
                having avg(salary)>(    
                    select avg(salary)
                    from s_emp
                )
        )"""
    sql_update = "update s_emp_new set dept_id = 44"

    # 6、執行sql語句
    cursor.execute(sql_create)
    cursor.execute(sql_insert)
    cursor.execute(sql_update)
    # 7、提交事務
    con.commit()

    tip = "執行成功！"
except Exception as e:
    if "con" in dir():
        # 8、回滾事務
        con.rollback()
        tip = "執行失敗！"
    print(e)
finally:
    print(tip)