pip install pymysql

import pymysql
from pymysql.cursors import DictCursor 可選，用于獲取字典格式的結(jié)果

1. 建立數(shù)據(jù)庫連接

connection = pymysql.connect(

    host='localhost',      數(shù)據(jù)庫服務(wù)器地址，本地可用 localhost 或 127.0.0.1

    user='your_username',  用戶名

    password='your_password', 密碼

    database='your_database_name', 要連接的數(shù)據(jù)庫名

    charset='utf8mb4',     字符集，支持中文和表情符號推薦 utf8mb4

    cursorclass=DictCursor 可選：設(shè)置游標(biāo)類型，返回字典而非元組

)

try:

    2. 創(chuàng)建一個游標(biāo)對象，用于執(zhí)行SQL語句

    with connection.cursor() as cursor:

        示例1: 執(zhí)行一條 SELECT 查詢

        sql = "SELECT `id`, `name` FROM `users` WHERE `email` = %s"

        cursor.execute(sql, ('user@example.com',)) 使用參數(shù)化查詢，防止SQL注入！

        

        獲取結(jié)果

        result = cursor.fetchone() 獲取單條記錄

        print(result) 如果是DictCursor，輸出: {'id': 1, 'name': 'John'}

        如果用默認游標(biāo)，輸出: (1, 'John')

        

        獲取所有記錄

        results = cursor.fetchall()

        for row in results:

           print(row)

    示例2: 執(zhí)行一條 INSERT 語句

    with connection.cursor() as cursor:

        sql = "INSERT INTO `users` (`name`, `email`) VALUES (%s, %s)"

        cursor.execute(sql, ('Alice', 'alice@example.com'))

    

    3. 提交事務(wù)！對于INSERT/UPDATE/DELETE等寫操作，必須提交才會生效。

    connection.commit()

    示例3: 獲取剛插入數(shù)據(jù)的主鍵ID

    with connection.cursor() as cursor:

        sql = "INSERT INTO `users` (`name`, `email`) VALUES (%s, %s)"

        cursor.execute(sql, ('Bob', 'bob@example.com'))

        connection.commit()

        print(f"新插入記錄的ID是: {cursor.lastrowid}")

finally:

    4. 關(guān)閉連接，釋放資源

    connection.close()

import pymysql

使用 with 語句自動管理連接和游標(biāo)的關(guān)閉

try:

    with pymysql.connect(host='localhost', user='root', password='password', database='test') as conn:

        with conn.cursor() as cursor:

            cursor.execute("SELECT VERSION()")

            result = cursor.fetchone()

            print(f"Database version: {result[0]}")

        連接結(jié)束時，如果沒有異常，會自動 commit(); 如果有異常，會自動 rollback()

except pymysql.Error as e:

    print(f"Database error: {e}")

pip install sqlalchemy pymysql

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

1. 定義數(shù)據(jù)庫連接字符串

格式: dialect+driver://username:password@host:port/database

DB_URI = 'mysql+pymysql://username:password@localhost:3306/your_database?charset=utf8mb4'

2. 創(chuàng)建引擎 (Engine)，它是ORM和數(shù)據(jù)庫的連接核心

engine = create_engine(DB_URI, echo=True) echo=True 會打印執(zhí)行的SQL，調(diào)試時有用

3. 創(chuàng)建基類

Base = declarative_base()

4. 定義映射類（模型）

class User(Base):

    __tablename__ = 'users' 指定映射的表名

    id = Column(Integer, primary_key=True, autoincrement=True)

    name = Column(String(50), nullable=False)

    email = Column(String(100), unique=True)

5. 創(chuàng)建所有表（如果不存在）

Base.metadata.create_all(engine)

6. 創(chuàng)建會話工廠 (Session)

Session = sessionmaker(bind=engine)

7. 使用會話進行增刪改查

新增數(shù)據(jù)

with Session() as session:

    new_user = User(name='Charlie', email='charlie@example.com')

    session.add(new_user)

    session.commit() 提交事務(wù)

    print(f"New user ID: {new_user.id}") 提交后，id自動賦值

查詢數(shù)據(jù)

with Session() as session:

    查詢所有用戶

    users = session.query(User).all()

    for user in users:

        print(user.id, user.name, user.email)

    條件查詢

    user = session.query(User).filter_by(name='Charlie').first()

    if user:

        print(f"Found user: {user.name}")

        pip install python-dotenv

        .env 文件

        DB_HOST=localhost

        DB_USER=root

        DB_PASSWORD=your_secure_password

        DB_NAME=my_app

        ```

        ```python

        app.py

        from dotenv import load_dotenv

        import os

        import pymysql

        load_dotenv() 加載 .env 文件中的環(huán)境變量

        connection = pymysql.connect(

            host=os.getenv('DB_HOST'),

            user=os.getenv('DB_USER'),

            password=os.getenv('DB_PASSWORD'),

            database=os.getenv('DB_NAME')

        )