Python PyMySQL操作MySQL數(shù)據(jù)庫的方法詳解

更新時間：2023年05月21日 16:14:05 作者：CodeDevMaster

PyMySQL是一個用于Python編程語言的純Python MySQL客戶端庫，它遵循Python標準DB API接口，并提供了許多方便的功能，本文就來和大家簡單介紹一下吧

PyMySQL

PyMySQL概述

PyMySQL是一個用于Python編程語言的純Python MySQL客戶端庫，它實現(xiàn)了MySQL數(shù)據(jù)庫協(xié)議的所有版本，并支持多線程應用程序和Python 3.x版本。PyMySQL遵循Python標準DB API接口，并提供了許多方便的功能，如MySQL連接池、SSL/TLS加密連接、SQL注入預防等。

GitHub：https://github.com/PyMySQL/PyMySQL

安裝PyMySQL庫

pip3 install pymysql

查看第三方包的信息

pip3 show pymysql

查看安裝的第三方包列表

pip3 list

PyMySQL的基本使用

API介紹

導入PyMySQL庫

import pymysql

使用PyMySQL建立與MySQL數(shù)據(jù)庫的連接

pymysql.connect(參數(shù)列表)
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='demo')

獲取游標對象

cursor =conn.cursor()

執(zhí)行SQL語句

row_count = cursor.execute(sql)

獲取查詢結(jié)果集

result = cursor.fetchall()

將修改操作提交到數(shù)據(jù)庫

conn.commit()

回滾數(shù)據(jù)

conn.rollback()

關(guān)閉游標

cursor.close()

關(guān)閉連接

conn.close()

查詢操作

# 導包
import pymysql
# 創(chuàng)建數(shù)據(jù)庫連接對象
# 連接數(shù)據(jù)庫時,除端口參數(shù)外,其余參數(shù)都要使用字符串類型指定
# host：連接的mysql主機，如果本機是'localhost'
# port：連接的mysql主機的端口，默認是3306
# user：連接的用戶名
# password：連接的密碼
# database：數(shù)據(jù)庫的名稱
# charset：通信采用的編碼方式，推薦使用utf8
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
# 獲取游標對象
# 一個數(shù)據(jù)庫連接對象,可以創(chuàng)建多個游標對象
# 一般情況下,同一時刻,只會建立一個進行操作
# 游標對象在創(chuàng)建的時候,默認會開啟一個事務環(huán)境
cur = connect_db.cursor()
# 寫SQL語句
sql_str = '''select * from item'''
# 執(zhí)行SQL語句
row_count = cur.execute(sql_str)
print(f'查詢到 {row_count} 條記錄')
print()
# 獲取一條查詢結(jié)果
result = cur.fetchone()
print(result)
print()
# 獲取指定條數(shù)的記錄
result = cur.fetchmany(4)
for t in result:
    print(t)
print()
# 獲取所有數(shù)據(jù)
result = cur.fetchall()
for t in result:
    print(t)
print()
# 移動游標的屬性
cur.rownumber = 0
result = cur.fetchall()
print(cur.rowcount)
for t in result:
    print(t)
# 關(guān)閉游標對象
cur.close()
# 關(guān)閉數(shù)據(jù)庫對象
connect_db.close()

查詢到 10 條記錄
(1, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:////img13.360buyimg.com/n1/s450x450_jfs/t1/175088/12/11173/264547/60aa5dd6Efe2b408b/b91c5bdaf6918ffa.jpg', 4299.0, '聯(lián)想京東自營旗艦店', 'i5 16G 背光鍵盤', ' 聯(lián)想筆記本電腦 小新Air14 英特爾酷睿i5 14英寸輕薄本(i5 16G 512G 高色域 大電池)銀 全面屏商務辦公本 ', 'https://item.jd.com/100011483893.html')
(2, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:////img13.360buyimg.com/n1/s450x450_jfs/t1/113403/25/29544/70492/6302023dE572552c8/e7c452efb3616b70.jpg', 4999.0, '聯(lián)想京東自營旗艦店', 'i5 16G 背光鍵盤', '  聯(lián)想筆記本電腦小新Pro14 英特爾Evo平臺 14英寸游戲輕薄本(標壓i5 16G 512G 2.8K 90Hz護眼屏)全面屏辦公本 ', 'https://item.jd.com/100014546493.html')
(3, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:////img12.360buyimg.com/n1/jfs/t1/219048/2/15799/192377/623ed1d3Eb52b150d/f669f9891c915da0.jpg', 2798.0, 'HUWI旗艦店', '16G運行+1024G超速硬盤', ' HUWI 國行【2022款英特爾可選酷?！拷饘俟P記本電腦輕薄本大學生上網(wǎng)課設計學習商務辦公游戲手提 玫瑰金-高配英特爾四核12代+IPS屏窄邊全面屏 16G運行+1024G超速硬盤 ', 'https://item.jd.com/10044527344297.html')
(4, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:////img12.360buyimg.com/n1/s450x450_jfs/t1/42776/10/17345/131277/626bc1e3Eb2b0278a/1dc51316f3dc1898.jpg', 4799.0, '華為京東自營官方旗艦店', 'i5 16G 512G 皓月銀', ' 華為筆記本電腦MateBook D 14 2022款 14英寸 11代酷睿 i5 16G+512G 銳炬顯卡 輕薄本/護眼全面屏 銀 ', 'https://item.jd.com/100016960357.html')
(5, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:////img14.360buyimg.com/n1/s450x450_jfs/t1/69468/27/17416/141766/626baaecE202ac55d/d65dd608458c8c0b.jpg', 3999.0, '華為京東自營官方旗艦店', 'i5 8GB 512GB 皓月銀', '  華為筆記本電腦MateBook D 14 SE版 14英寸 11代酷睿 i5 銳炬顯卡 8G+512G 輕薄本/高清護眼防眩光屏 銀 ', 'https://item.jd.com/100032149194.html')
(6, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:////img14.360buyimg.com/n1/s450x450_jfs/t1/99321/22/30610/122437/62ac48e7Eaf3ef4ac/464d0f1f132c8d62.jpg', 9299.0, '惠普（HP）OMEN暗影精靈京東自營旗艦店', '【新12代i7】滿血3060+2K屏版', ' 惠普(HP)暗影精靈8Pro 16.1英寸游戲筆記本電腦(12代酷睿i7-12700H RTX3060 6G 16GDDR5 512G 2.5K 165Hz) ', 'https://item.jd.com/100021389129.html')
(7, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:////img12.360buyimg.com/n1/jfs/t1/47859/1/18224/153861/62a45618E5078f925/534299d323f12a2c.jpg', 1358.0, '智能駝數(shù)碼旗艦店', '8G內(nèi)存+128G固態(tài)硬盤', ' 【2022款英特爾+酷睿i7】15.6英寸筆記本電腦輕薄本網(wǎng)課設計學習商務辦公游戲大學生手提 智能駝 青春版【11代英特爾】全面屏+抗藍光 8G內(nèi)存+128G固態(tài)硬盤 ', 'https://item.jd.com/10033087405727.html')
(8, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:////img11.360buyimg.com/n1/s450x450_jfs/t1/195842/2/20561/168501/62ca4248Edde4fefe/bbe877dbc201290e.jpg', 5699.0, 'ThinkPad京東自營旗艦店', 'i5-12500H 2.8K 90Hz', ' 聯(lián)想ThinkBook 14+ 英特爾酷睿i5 筆記本電腦全新2022款 14英寸標壓輕薄本i5-12500H 16G 512G 2.8K 90Hz ', 'https://item.jd.com/100020078921.html')
(9, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:////img11.360buyimg.com/n1/s450x450_jfs/t1/115135/13/26808/193275/62f371acE21c52747/20f36c3f88327703.jpg', 5399.0, '惠普京東自營官方旗艦店', '【五代 新】i5 16G 512 高色域', '  惠普(HP)戰(zhàn)66 五代 14英寸輕薄筆記本電腦(英特爾12代酷睿 i5-1240P 16G 512G 高色域低功耗屏 1年上門) ', 'https://item.jd.com/100020480561.html')
(10, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:////img12.360buyimg.com/n1/s450x450_jfs/t1/7498/9/18671/72563/62fb8cc2E2a4bedc8/cf134acbac78ec40.jpg', 3999.0, '小米京東自營旗艦店', 'R5 16G 512G', '  小米 RedmiBookPro 14英寸 2.5K高色域視網(wǎng)膜屏 輕薄筆記本電腦(6核R5 16G 512G-SSD 指紋識別 DC調(diào)光) ', 'https://item.jd.com/100011526087.html')

插入操作

# 導包
import pymysql
# 連接數(shù)據(jù)庫
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
# 獲取游標對象
cur = connect_db.cursor()
# 執(zhí)行sql
sql_str = '''insert into tb_user(id ,user_name,password,name,age,email) values(null,'0001','123456','Jack',20,'123456789@qq.com'); '''
cur.execute(sql_str)
# 當在對數(shù)據(jù)庫做增刪改時,默認會在事務環(huán)境中進行操作,操作完成后要進行手動提交操作,如果不提交,程序默認操作為回滾
connect_db.commit()
# 關(guān)閉游標對象
cur.close()
# 關(guān)閉數(shù)據(jù)庫連接
connect_db.close()

批量插入操作

def main():
    # 創(chuàng)建Connection連接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
    # 獲得Cursor對象
    cursor = conn.cursor()
    # 插入1萬次數(shù)據(jù)
    for i in range(10000):
        cursor.execute("insert into tb_user values(null,'000%d','123456','Jack',20,'123456@qq.com')" % i)
    # 提交數(shù)據(jù)
    conn.commit()
if __name__ == "__main__":
    main()

刪除操作

import pymysql
# 連接數(shù)據(jù)庫
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
# 獲取游標對象
cur = connect_db.cursor()
sql_str = ''' delete from tb_user where user_name = '0001' '''
# 執(zhí)行sql
cur.execute(sql_str)
# 提交
connect_db.commit()
# 關(guān)閉游標對象
cur.close()
# 關(guān)閉數(shù)據(jù)庫連接
connect_db.close()

更新操作

import pymysql
# 連接數(shù)據(jù)庫
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
# 獲取游標對象
cur = connect_db.cursor()
# 執(zhí)行sql
sql_str = ''' update tb_user set password = '12345' where user_name = '0001' '''
cur.execute(sql_str)
# 提交
connect_db.commit()
# 關(guān)閉游標對象
cur.close()
# 關(guān)閉數(shù)據(jù)庫連接
connect_db.close()

SQL注入

什么是SQL注入

用戶提交帶有惡意的數(shù)據(jù)與SQL語句進行字符串方式的拼接，從而影響SQL語句的語義，最終產(chǎn)生數(shù)據(jù)泄露的現(xiàn)象。

import pymysql
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
cur = connect_db.cursor()
select_id = input('請輸入一個查詢ID:')
sql_str = ''' select * from tb_user where id = %s ''' % select_id
print(sql_str)
cur.execute(sql_str)
result = cur.fetchall()
print(result)
cur.close()
connect_db.close()

利用sql的合法規(guī)則，查詢到了不應該得到的數(shù)據(jù)，也就稱為SQL注入

請輸入一個查詢ID: 1 or 1=1
select * from tb_user where id = 1 or 1=1
((1, 'zhangsan', '123456', '小白', 20, 'test1@qq.com'), (2, 'lisi', '123456', '李四', 20, 'test2@qq.com'), (3, 'wangwu', '123456', '王五', 28, 'test3@qq.com'))

解決方案

SQL語句參數(shù)化，即SQL語言中的參數(shù)使用%s來占位，將SQL語句中%s占位所需要的參數(shù)存在一個列表中，把參數(shù)列表傳遞給execute方法中第二個參數(shù)

sql_str = ''' select * from tb_user where id = %s ''' % select_id
print(sql_str)
# 利用execute方法的第二個參數(shù),解決SQL注入的問題
cur.execute(sql_str, (select_id,))

請輸入一個查詢ID: 1 or 1=1
select * from tb_user where id = %s
((1, 'zhangsan', '123456', '小白', 20, 'test1@qq.com'),)

注意事項

保護敏感信息

在連接數(shù)據(jù)庫時，不要把敏感信息（例如用戶名和密碼）直接硬編碼到代碼中。而是應該將這些敏感信息存儲在安全的方式中，例如配置文件或環(huán)境變量中，并在代碼中動態(tài)加載。

1.創(chuàng)建config.ini的INI配置文件

[mysql]
user=root
password=123456
host=localhost
port=3306
database=mydb

2.讀取配置文件

ConfigParser 是 Python 標準庫中的一個模塊，它用于讀取和解析INI格式的配置文件?？梢允褂迷撃K輕松地讀取配置文件并將其加載到Python程序中進行進一步處理。

from configparser import ConfigParser
# 讀取配置文件
config = ConfigParser()
config.read('config.ini')
# 獲取MySQL登錄憑據(jù)
MYSQL_USER = config.get('mysql', 'user')  # 輸出：root
MYSQL_PASSWORD = config.get('mysql', 'password')
# 連接MySQL數(shù)據(jù)庫
db = mysql.connector.connect(host='127.0.0.1',
                             user=MYSQL_USER,
                             password=MYSQL_PASSWORD,
                             database='mydb')

使用連接池

在高負載情況下，頻繁地創(chuàng)建和釋放數(shù)據(jù)庫連接會降低性能并增加系統(tǒng)開銷。因此，建議使用連接池來管理數(shù)據(jù)庫連接，以便在需要時從連接池獲得可用連接，并在使用后將其返回到池中。

使用連接池有助于優(yōu)化數(shù)據(jù)庫連接的管理和復用，并減少了每個請求或操作啟動新連接的開銷，從而提高了系統(tǒng)的性能和響應速度。

1.安裝PyMySQL庫和pymysql-pool庫

pip install pymysql
pip install pymysql-pool

2.導入PyMySQL庫和連接池模塊。

import pymysql
from pymysqlpool import ConnectionPool

3.創(chuàng)建連接池對象并指定連接參數(shù)

db_config = {
    'max_connections': 10,
    'max_idle_time': 60,
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'password',
    'database': 'mydb'
}
pool = ConnectionPool(**db_config)

4.獲取連接對象和游標，并執(zhí)行SQL語句

with pool.get_conn() as conn:
    with conn.cursor() as cur:
        sql = "SELECT * FROM mytable WHERE name=%s"
        cur.execute(sql, ('John',))
        results = cur.fetchall()
        print(results)

5.關(guān)閉游標和連接，并歸還連接到連接池

cur.close()
pool.return_conn(conn)

異常處理

在與數(shù)據(jù)庫進行交互時，出錯是不可避免的。在產(chǎn)生異常時，需要及時捕獲和處理異常，以避免程序崩潰或泄露敏感信息。

try:
    # 操作數(shù)據(jù)庫
except Exception as e:
    print("Error:", e)
    # 處理異常
finally:
    # 關(guān)閉連接和游標
    cur.close()
    conn.close()

防止并發(fā)沖突

在多個進程或線程同時訪問和修改同一個數(shù)據(jù)時，可能會出現(xiàn)并發(fā)沖突，導致數(shù)據(jù)不一致或錯誤。為此，建議使用數(shù)據(jù)庫的事務支持來確保數(shù)據(jù)的一致性和正確性

try:
    # 開始事務
    conn.begin()
    # 操作數(shù)據(jù)庫
    # 提交事務
    conn.commit()
except Exception as e:
    print("Error:", e)
    # 回滾事務
    conn.rollback()
finally:
    # 關(guān)閉連接和游標
    cur.close()
    conn.close()

優(yōu)化查詢

查詢數(shù)據(jù)時，應盡可能減少查詢次數(shù)，可以通過使用索引、批量插入等方法優(yōu)化和加速查詢。此外，在INSERT、UPDATE和DELETE語句中，應使用參數(shù)化查詢來防止SQL注入攻擊。

# 參數(shù)化SQL查詢
sql = "SELECT * FROM tablename WHERE column1 = %s AND column2 = %s"
cur.execute(sql, ('value1', 'value2'))

以上就是Python PyMySQL操作MySQL數(shù)據(jù)庫的方法詳解的詳細內(nèi)容，更多關(guān)于PyMySQL操作MySQL的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频