pip install pymysql

import pymysql
 
# 創(chuàng)建連接
connection = pymysql.connect(
    host='localhost',      # 數(shù)據(jù)庫(kù)主機(jī)地址
    user='your_username',  # 數(shù)據(jù)庫(kù)用戶名
    password='your_password', # 數(shù)據(jù)庫(kù)密碼
    database='your_dbname',   # 選擇的數(shù)據(jù)庫(kù)
    charset='utf8mb4',     # 指定字符集
    cursorclass=pymysql.cursors.DictCursor  # 返回字典格式的數(shù)據(jù)
)
 
# 創(chuàng)建游標(biāo)
cursor = connection.cursor()
 
# 關(guān)閉游標(biāo)和連接
cursor.close()
connection.close()

try:
    # 創(chuàng)建游標(biāo)
    with connection.cursor() as cursor:
        # SQL 查詢
        sql = "SELECT * FROM users WHERE age > %s"
        cursor.execute(sql, (25,))  # 使用參數(shù)化查詢防止 SQL 注入
        # 獲取結(jié)果
        results = cursor.fetchall()  # 返回所有結(jié)果
        for row in results:
            print(row)
finally:
    # 關(guān)閉連接
    connection.close()

try:
    with connection.cursor() as cursor:
        # SQL 插入語(yǔ)句
        sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        # 執(zhí)行單條插入
        cursor.execute(sql, ('John Doe', 30, 'john.doe@example.com'))
        
        # 執(zhí)行多條插入
        users = [
            ('Alice', 25, 'alice@example.com'),
            ('Bob', 28, 'bob@example.com')
        ]
        cursor.executemany(sql, users)
    
    # 提交更改
    connection.commit()
finally:
    connection.close()

try:
    with connection.cursor() as cursor:
        # SQL 更新語(yǔ)句
        sql = "UPDATE users SET email = %s WHERE name = %s"
        cursor.execute(sql, ('new.email@example.com', 'John Doe'))
    
    # 提交更改
    connection.commit()
finally:
    connection.close()

try:
    with connection.cursor() as cursor:
        # SQL 刪除語(yǔ)句
        sql = "DELETE FROM users WHERE name = %s"
        cursor.execute(sql, ('John Doe',))
    
    # 提交更改
    connection.commit()
finally:
    connection.close()

try:
    # 關(guān)閉自動(dòng)提交
    connection.autocommit(False)
 
    with connection.cursor() as cursor:
        # SQL 插入語(yǔ)句
        sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        cursor.execute(sql, ('Chris', 27, 'chris@example.com'))
 
        # 人為錯(cuò)誤，測(cè)試回滾
        raise Exception("人為觸發(fā)異常")
 
    # 提交事務(wù)
    connection.commit()
except Exception as e:
    print(f"出現(xiàn)錯(cuò)誤: {e}")
    # 回滾事務(wù)
    connection.rollback()
finally:
    connection.close()

# 字典游標(biāo)
connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_dbname',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor  # 使用字典游標(biāo)
)

# 不安全的寫法
sql = f"SELECT * FROM users WHERE name = '{name}'"  # 容易導(dǎo)致 SQL 注入
 
# 安全的寫法
sql = "SELECT * FROM users WHERE name = %s"
cursor.execute(sql, (name,))

import pymysql
 
try:
    connection = pymysql.connect(host='localhost', user='root', password='', database='test_db')
    cursor = connection.cursor()
except pymysql.MySQLError as e:
    print(f"數(shù)據(jù)庫(kù)連接失敗: {e}")