深入浅出：Python访问MySQL数据库

最新推荐文章于 2025-06-16 10:14:12 发布

原创最新推荐文章于 2025-06-16 10:14:12 发布 · 1.5k 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #python #mysql

python 专栏收录该内容

64 篇文章

订阅专栏

深入浅出：Python访问MySQL数据库

为什么选择 MySQL？

MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），以其高性能、可靠性和易用性而闻名。它支持 SQL（结构化查询语言），提供了丰富的功能来管理和操作数据。以下是 MySQL 的一些优点：

高性能：MySQL 在处理大量数据时表现出色，适用于高并发场景。
可靠性：支持事务、ACID 属性，确保数据的一致性和完整性。
易用性：拥有直观的命令行工具和图形化界面，易于学习和使用。
社区支持：庞大的用户社区和丰富的文档资源，遇到问题容易找到解决方案。

使用 PyMySQL 连接 MySQL 数据库

PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，它允许我们直接在 Python 中执行 SQL 语句并与 MySQL 数据库进行交互。下面我们将详细介绍如何使用 PyMySQL 进行数据库操作。

安装 PyMySQL

首先，确保安装了 PyMySQL 库：

pip install pymysql

基本连接与操作

创建连接

要连接到 MySQL 数据库，我们需要提供数据库的主机地址、用户名、密码和数据库名称。以下是一个简单的连接示例：

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',       # 主机地址
    user='root',            # 用户名
    password='password',    # 密码
    database='test_db',     # 数据库名称
    charset='utf8mb4'       # 字符编码
)

try:
    with conn.cursor() as cur:
        # 执行 SQL 查询
        cur.execute("SELECT VERSION()")
        version = cur.fetchone()
        print(f"Database version: {version[0]}")
finally:
    # 关闭连接
    conn.close()

创建表

我们可以使用 CREATE TABLE 语句来创建一个新的表。以下是一个创建 users 表的示例：

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cur:
        # 创建表
        create_table_sql = '''
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT,
            email VARCHAR(100) UNIQUE
        )
        '''
        cur.execute(create_table_sql)
        conn.commit()
        print("Table created successfully.")
finally:
    # 关闭连接
    conn.close()

插入数据

使用 INSERT INTO 语句可以向表中插入新记录。为了防止 SQL 注入攻击，建议使用参数化查询。

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cur:
        # 插入数据
        insert_sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        cur.execute(insert_sql, ('Alice', 30, 'alice@example.com'))
        cur.execute(insert_sql, ('Bob', 25, 'bob@example.com'))
        conn.commit()
        print("Data inserted successfully.")
finally:
    # 关闭连接
    conn.close()

查询数据

使用 SELECT 语句可以从表中查询数据。我们可以使用 fetchone() 获取单条记录，或使用 fetchall() 获取所有记录。

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cur:
        # 查询数据
        select_sql = "SELECT * FROM users"
        cur.execute(select_sql)
        rows = cur.fetchall()
        for row in rows:
            print(row)
finally:
    # 关闭连接
    conn.close()

更新数据

使用 UPDATE 语句可以修改表中的现有记录。同样，建议使用参数化查询以防止 SQL 注入。

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cur:
        # 更新数据
        update_sql = "UPDATE users SET age = %s WHERE name = %s"
        cur.execute(update_sql, (31, 'Alice'))
        conn.commit()
        print("Data updated successfully.")
finally:
    # 关闭连接
    conn.close()

删除数据

使用 DELETE 语句可以从表中删除记录。同样，建议使用参数化查询以防止 SQL 注入。

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cur:
        # 删除数据
        delete_sql = "DELETE FROM users WHERE name = %s"
        cur.execute(delete_sql, ('Bob',))
        conn.commit()
        print("Data deleted successfully.")
finally:
    # 关闭连接
    conn.close()

使用 SQLAlchemy 进行 ORM 操作

SQLAlchemy 是一个功能强大的 ORM（对象关系映射）框架，它允许我们以面向对象的方式操作数据库。通过 SQLAlchemy，我们可以定义模型类来表示数据库表，并使用这些模型类进行增删改查操作。

安装 SQLAlchemy

首先，确保安装了 SQLAlchemy 和相应的数据库驱动（如 pymysql）：

pip install sqlalchemy pymysql

定义模型类

使用 SQLAlchemy 时，我们需要定义模型类来表示数据库表。每个模型类对应一个数据库表，类的属性对应表的字段。

示例：定义用户模型

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建引擎并连接到 MySQL 数据库
engine = create_engine('mysql+pymysql://root:password@localhost/test_db')

# 创建基类
Base = declarative_base()

# 定义用户模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    age = Column(Integer)
    email = Column(String(100), unique=True)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
new_user = User(name='Charlie', age=35, email='charlie@example.com')
session.add(new_user)
session.commit()

# 查询数据
users = session.query(User).all()
for user in users:
    print(user.name, user.age, user.email)

# 关闭会话
session.close()

使用上下文管理器简化操作

为了确保数据库连接在操作完成后正确关闭，可以使用上下文管理器（with 语句）来简化代码。

示例：使用上下文管理器

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建引擎并连接到 MySQL 数据库
engine = create_engine('mysql+pymysql://root:password@localhost/test_db')

# 创建基类
Base = declarative_base()

# 定义用户模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    age = Column(Integer)
    email = Column(String(100), unique=True)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)

# 使用上下文管理器
with Session() as session:
    # 插入数据
    new_user = User(name='David', age=40, email='david@example.com')
    session.add(new_user)
    session.commit()

    # 查询数据
    users = session.query(User).all()
    for user in users:
        print(user.name, user.age, user.email)

实战案例：构建一个简单的用户管理系统

为了更好地理解 Python 访问 MySQL 数据库的应用，我们来看一个实战案例：构建一个简单的用户管理系统。这个系统将包含用户注册、登录、查询等功能，并使用 MySQL 作为后台数据库。

项目结构

假设我们有一个名为 user_management 的项目，其目录结构如下：

user_management/
  main.py
  database.py
  config.json

`config.json` 文件

在 config.json 中定义数据库配置，包括数据库文件路径等：

{
  "database": {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "name": "test_db"
  }
}

`database.py` 文件

在 database.py 中实现数据库操作逻辑：

import pymysql
import json

def load_config(config_file):
    """加载配置文件"""
    with open(config_file, 'r') as f:
        return json.load(f)

def init_db(config):
    """初始化数据库"""
    conn = pymysql.connect(
        host=config['database']['host'],
        user=config['database']['user'],
        password=config['database']['password'],
        database=config['database']['name'],
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cur:
            # 创建表
            cur.execute('''CREATE TABLE IF NOT EXISTS users (
                            id INT AUTO_INCREMENT PRIMARY KEY,
                            username VARCHAR(100) NOT NULL UNIQUE,
                            password VARCHAR(100) NOT NULL,
                            email VARCHAR(100) NOT NULL UNIQUE)''')
            conn.commit()
    finally:
        conn.close()

def add_user(config, username, password, email):
    """添加新用户"""
    conn = pymysql.connect(
        host=config['database']['host'],
        user=config['database']['user'],
        password=config['database']['password'],
        database=config['database']['name'],
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cur:
            # 插入数据
            cur.execute("INSERT INTO users (username, password, email) VALUES (%s, %s, %s)",
                        (username, password, email))
            conn.commit()
            return True
    except pymysql.IntegrityError:
        return False
    finally:
        conn.close()

def get_user(config, username):
    """根据用户名查询用户"""
    conn = pymysql.connect(
        host=config['database']['host'],
        user=config['database']['user'],
        password=config['database']['password'],
        database=config['database']['name'],
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cur:
            # 查询数据
            cur.execute("SELECT * FROM users WHERE username=%s", (username,))
            return cur.fetchone()
    finally:
        conn.close()

def update_user(config, username, new_password=None, new_email=None):
    """更新用户信息"""
    conn = pymysql.connect(
        host=config['database']['host'],
        user=config['database']['user'],
        password=config['database']['password'],
        database=config['database']['name'],
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cur:
            query = "UPDATE users SET "
            params = []
            if new_password:
                query += "password=%s, "
                params.append(new_password)
            if new_email:
                query += "email=%s, "
                params.append(new_email)
            if not params:
                return False
            query = query.rstrip(', ') + " WHERE username=%s"
            params.append(username)
            cur.execute(query, params)
            conn.commit()
            return True
    finally:
        conn.close()

def delete_user(config, username):
    """删除用户"""
    conn = pymysql.connect(
        host=config['database']['host'],
        user=config['database']['user'],
        password=config['database']['password'],
        database=config['database']['name'],
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cur:
            # 删除数据
            cur.execute("DELETE FROM users WHERE username=%s", (username,))
            conn.commit()
            return cur.rowcount > 0
    finally:
        conn.close()

`main.py` 文件

在 main.py 中实现用户管理系统的主逻辑：

import os
import json
from database import load_config, init_db, add_user, get_user, update_user, delete_user

def main():
    config = load_config('config.json')
    init_db(config)

    while True:
        print("\n1. 注册用户")
        print("2. 登录用户")
        print("3. 更新用户信息")
        print("4. 删除用户")
        print("5. 退出")
        choice = input("请选择操作: ")

        if choice == '1':
            username = input("请输入用户名: ")
            password = input("请输入密码: ")
            email = input("请输入邮箱: ")
            if add_user(config, username, password, email):
                print("用户注册成功")
            else:
                print("用户已存在")

        elif choice == '2':
            username = input("请输入用户名: ")
            user = get_user(config, username)
            if user:
                print(f"欢迎, {user[1]}!")
            else:
                print("用户不存在")

        elif choice == '3':
            username = input("请输入用户名: ")
            new_password = input("请输入新密码 (留空则不修改): ")
            new_email = input("请输入新邮箱 (留空则不修改): ")
            if update_user(config, username, new_password, new_email):
                print("用户信息更新成功")
            else:
                print("用户不存在或没有修改任何信息")

        elif choice == '4':
            username = input("请输入用户名: ")
            if delete_user(config, username):
                print("用户删除成功")
            else:
                print("用户不存在")

        elif choice == '5':
            print("再见！")
            break

        else:
            print("无效的选择，请重试")

if __name__ == "__main__":
    main()