当前位置：首页 > 文章列表 > 文章 > python教程 > Python数据库操作全攻略：SQLite/MySQL/PostgreSQL详解

Python数据库操作全攻略：SQLite/MySQL/PostgreSQL详解

2026-02-12 13:43:46 0浏览收藏

golang学习网今天将给大家带来《Python操作数据库全攻略：SQLite/MySQL/PostgreSQL详解》，感兴趣的朋友请继续看下去吧！以下内容将会涉及到等等知识点，如果你是正在学习文章或者已经是大佬级别了，都非常欢迎也希望大家都能给我建议评论哈~希望能帮助到大家！

Python操作数据库的核心思路是建立连接、获取游标、执行SQL、处理结果、提交事务和关闭连接。该流程适用于SQLite、MySQL和PostgreSQL，遵循DB-API 2.0规范，接口一致，仅连接参数和库不同。SQLite轻量，适合本地开发；MySQL广泛用于Web应用；PostgreSQL功能强大，适合复杂业务。安全性方面需使用参数化查询防SQL注入，验证输入，遵循最小权限原则，并妥善处理错误。连接池可提升高并发下的性能。

如何使用Python操作数据库（SQLite/MySQL/PostgreSQL）？

Python操作数据库的核心思路其实很简单：建立连接、获取游标、执行SQL语句、处理结果、提交或回滚事务，最后关闭连接。无论你是用SQLite、MySQL还是PostgreSQL，这一套流程都大同小异，主要区别在于使用的Python库和连接参数。掌握了这套范式，就能灵活地与各种关系型数据库打交道。

在Python中，与关系型数据库交互通常遵循DB-API 2.0规范，这使得不同数据库的客户端库（如sqlite3、PyMySQL、psycopg2）在接口上保持高度一致。这意味着你学会了一种，其他也就触类旁通了。

解决方案

让我们一步步看看如何具体操作。

1. SQLite：轻量级本地数据库

SQLite是Python标准库的一部分，无需额外安装。它以文件形式存储数据，非常适合本地开发、小型应用或作为配置存储。

import sqlite3

def operate_sqlite():
    conn = None # 初始化连接对象
    try:
        # 连接到数据库文件，如果文件不存在则创建
        conn = sqlite3.connect('my_database.db')
        cursor = conn.cursor()

        # 创建表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER
            )
        ''')
        print("表 'users' 创建或已存在。")

        # 插入数据
        # 注意使用参数化查询，防止SQL注入
        cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))
        cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 24))
        print("数据插入成功。")

        # 查询数据
        cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
        print("\n查询结果 (年龄 > 25):")
        for row in cursor.fetchall():
            print(row)

        # 更新数据
        cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, 'Alice'))
        print("数据更新成功。")

        # 删除数据
        cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',))
        print("数据删除成功。")

        # 再次查询，确认更新和删除
        cursor.execute("SELECT * FROM users")
        print("\n当前所有用户:")
        for row in cursor.fetchall():
            print(row)

        # 提交事务
        conn.commit()
        print("事务已提交。")

    except sqlite3.Error as e:
        print(f"数据库操作错误: {e}")
        if conn:
            conn.rollback() # 发生错误时回滚
            print("事务已回滚。")
    finally:
        if conn:
            conn.close() # 确保关闭连接
            print("数据库连接已关闭。")

# operate_sqlite()

2. MySQL：广泛使用的关系型数据库

操作MySQL需要安装第三方库，比如PyMySQL。

import pymysql

def operate_mysql():
    conn = None
    try:
        # 连接到MySQL服务器
        # 请替换为你的数据库信息
        conn = pymysql.connect(
            host='localhost',
            user='your_mysql_user',
            password='your_mysql_password',
            database='your_database_name',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor # 返回字典形式的行
        )
        cursor = conn.cursor()

        # 创建表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                price DECIMAL(10, 2)
            )
        ''')
        print("表 'products' 创建或已存在。")

        # 插入数据
        cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", ('Laptop', 1200.50))
        cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", ('Mouse', 25.99))
        print("数据插入成功。")

        # 查询数据
        cursor.execute("SELECT * FROM products WHERE price > %s", (100.00,))
        print("\n查询结果 (价格 > 100):")
        for row in cursor.fetchall():
            print(row)

        # 更新数据
        cursor.execute("UPDATE products SET price = %s WHERE name = %s", (1250.00, 'Laptop'))
        print("数据更新成功。")

        # 删除数据
        cursor.execute("DELETE FROM products WHERE name = %s", ('Mouse',))
        print("数据删除成功。")

        conn.commit()
        print("事务已提交。")

    except pymysql.Error as e:
        print(f"数据库操作错误: {e}")
        if conn:
            conn.rollback()
            print("事务已回滚。")
    finally:
        if conn:
            conn.close()
            print("数据库连接已关闭。")

# operate_mysql()

3. PostgreSQL：功能强大的企业级数据库

操作PostgreSQL需要安装psycopg2库。

import psycopg2

def operate_postgresql():
    conn = None
    try:
        # 连接到PostgreSQL服务器
        # 请替换为你的数据库信息
        conn = psycopg2.connect(
            host='localhost',
            database='your_pg_database_name',
            user='your_pg_user',
            password='your_pg_password'
        )
        cursor = conn.cursor()

        # 创建表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS orders (
                id SERIAL PRIMARY KEY,
                item_name VARCHAR(255) NOT NULL,
                quantity INTEGER
            )
        ''')
        print("表 'orders' 创建或已存在。")

        # 插入数据
        cursor.execute("INSERT INTO orders (item_name, quantity) VALUES (%s, %s)", ('Keyboard', 5))
        cursor.execute("INSERT INTO orders (item_name, quantity) VALUES (%s, %s)", ('Monitor', 2))
        print("数据插入成功。")

        # 查询数据
        cursor.execute("SELECT * FROM orders WHERE quantity > %s", (3,))
        print("\n查询结果 (数量 > 3):")
        for row in cursor.fetchall():
            print(row)

        # 更新数据
        cursor.execute("UPDATE orders SET quantity = %s WHERE item_name = %s", (6, 'Keyboard'))
        print("数据更新成功。")

        # 删除数据
        cursor.execute("DELETE FROM orders WHERE item_name = %s", ('Monitor',))
        print("数据删除成功。")

        conn.commit()
        print("事务已提交。")

    except psycopg2.Error as e:
        print(f"数据库操作错误: {e}")
        if conn:
            conn.rollback()
            print("事务已回滚。")
    finally:
        if conn:
            cursor.close()
            conn.close()
            print("数据库连接已关闭。")

# operate_postgresql()

Python数据库连接选择：SQLite、MySQL与PostgreSQL适用场景解析

选择哪种数据库，这其实是个很经典的“看菜吃饭”问题，没有绝对的优劣，只有适不适合你的项目。我个人觉得，很多人在项目初期可能并不会深究，但随着项目发展，数据库的选择会越来越凸显其重要性。

SQLite：嵌入式与轻量级之选
- 适用场景： 我通常在以下几种情况首选SQLite：
  - 桌面应用或移动应用： 作为本地数据存储，无需独立服务器进程，直接嵌入应用内部，比如一个简单的个人笔记应用。
  - 开发与测试： 项目初期快速原型开发，或者单元测试时，SQLite非常方便，因为它不需要复杂的配置，一个文件就是数据库。
  - 小型网站或个人博客： 如果访问量不大，数据量也不大，SQLite完全可以胜任，省去了维护数据库服务器的麻烦。
  - 配置管理或缓存： 存储一些不经常变动但需要持久化的配置信息，或者作为简单的本地缓存。
- 特点：
  - 零配置： 不需要安装和管理数据库服务器，数据存储在一个文件中。
  - 高可移植性： 数据库文件可以直接复制到任何地方使用。
  - 性能： 对于单用户或并发量不高的场景，性能表现优秀。
- 限制：
  - 并发写入： 对高并发写入支持不佳，因为锁是文件级别的。
  - 复杂权限管理： 缺乏细粒度的用户权限控制。
MySQL：Web应用与通用业务的主力军
- 适用场景： MySQL是我接触最多的数据库，尤其在Web开发领域。
  - 中小型到大型Web应用： 无论是博客、电商、社交平台，MySQL都是一个非常成熟且广泛的选择。
  - 通用业务系统： 大多数企业级应用，如果对事务一致性要求不是极致严格，MySQL都能很好地支撑。
  - LAMP/LEMP栈： 作为经典技术栈的一部分，拥有庞大的社区支持和丰富的工具链。
- 特点：
  - 成熟稳定： 经过长时间考验，非常稳定可靠。
  - 性能优异： 在读操作上表现出色，通过各种优化手段也能很好地处理写入。
  - 社区庞大： 遇到问题很容易找到解决方案，资源丰富。
  - 可扩展性： 支持主从复制、分库分表等多种扩展方案。
- 限制：
  - 事务处理： 相比PostgreSQL，在某些高级事务特性和严格的ACID合规性上略逊一筹。
  - SQL标准支持： 对SQL标准的某些高级特性支持不如PostgreSQL全面。
PostgreSQL：数据完整性与高级功能的王者
- 适用场景： 当我需要更强的数据完整性、更复杂的查询或者处理地理空间数据时，PostgreSQL是我的首选。
  - 金融、GIS（地理信息系统）等对数据一致性、完整性要求极高的领域： PostgreSQL的ACID特性非常强大。
  - 大数据分析与数据仓库： 支持更复杂的SQL查询，如窗口函数、CTE（Common Table Expressions），以及丰富的扩展（如PostGIS用于地理空间数据）。
  - 需要JSONB等高级数据类型： 对非结构化数据的支持也很好，可以更好地融合关系型和NoSQL的优点。
  - 企业级应用： 许多大型企业倾向于PostgreSQL，因为它提供了更强大的功能和更好的可扩展性。
- 特点：
  - ACID合规性： 严格遵循事务的原子性、一致性、隔离性、持久性。
  - 功能丰富： 支持更多高级SQL特性、自定义函数、存储过程、多种索引类型、丰富的数据类型（数组、JSONB等）。
  - 扩展性强： 拥有强大的扩展生态系统，可以轻松添加新功能。
  - 开源免费： 拥有BSD许可证，可以自由使用和修改。
- 限制：
  - 学习曲线： 功能强大也意味着学习成本相对较高。
  - 资源消耗： 在某些情况下，可能比MySQL占用更多资源。

总结一下，如果只是快速启动一个小项目或做本地数据存储，SQLite轻巧方便；如果构建常见的Web应用，MySQL是久经考验的可靠选择；而如果你的项目对数据完整性、复杂查询有高要求，或者需要处理特殊数据类型，PostgreSQL则提供了更强大的功能集。

提升Python数据库操作安全性：避免SQL注入与常见漏洞

在数据库操作中，安全性永远是绕不过去的话题，尤其是SQL注入，那简直是悬在程序员头上的达摩克利斯之剑。我见过太多因为忽视安全导致数据泄露的案例，所以这块内容我认为是重中之重。

1. 坚决使用参数化查询（Prepared Statements）

这是防御SQL注入最核心、最有效的手段，没有之一。它的原理很简单：将SQL语句和参数分开传递给数据库，数据库会先编译SQL模板，再将参数安全地绑定进去，确保参数不会被当作SQL代码的一部分执行。

错误示例（易受SQL注入）：

# 假设 username 和 password 来自用户输入
username = "admin"
password = "' OR '1'='1" # 恶意输入
sql = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
# 最终SQL可能变成：SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1'
# 导致无需密码即可登录
cursor.execute(sql)

正确示例（参数化查询）：

# SQLite
cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))

# MySQL (PyMySQL)
cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))

# PostgreSQL (psycopg2)
cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))

你会发现，不同数据库驱动的占位符可能不同（?、%s），但核心思想一致：不要直接拼接用户输入到SQL字符串中。Python DB-API规范强制要求所有数据库驱动都支持参数化查询，所以这是我们最可靠的武器。

2. 严格的输入验证与数据清洗

虽然参数化查询能防SQL注入，但这并不意味着你可以放松对用户输入的警惕。在数据进入数据库之前，对所有用户输入进行验证和清洗是另一个重要的安全层。

数据类型验证： 确保用户输入的年龄是整数，邮箱是有效的格式，电话号码是数字等。
长度限制： 防止过长的输入导致数据库字段溢出或DoS攻击。
字符过滤： 移除或转义不必要的特殊字符，例如HTML标签（防止XSS攻击）。

业务逻辑验证： 确保输入符合你的业务规则，例如，一个商品的库存不能是负数。

def validate_age(age_str):
    try:
        age = int(age_str)
        if 0 < age < 150: # 合理的年龄范围
            return age
        else:
            raise ValueError("年龄不在合理范围。")
    except ValueError:
        raise ValueError("年龄必须是有效的数字。")

# 使用前先验证
try:
    user_age = validate_age(request_data.get('age'))
    # 之后再将 user_age 传给参数化查询
except ValueError as e:
    print(f"输入验证失败: {e}")
    # 返回错误给用户

3. 最小权限原则（Principle of Least Privilege）

为不同的应用程序或服务创建专门的数据库用户，并只授予它们完成其任务所需的最小权限。

避免使用root用户连接应用： 这是一个非常常见的错误，一旦应用被攻破，攻击者就能获得数据库的完全控制权。

按需授权： 如果一个应用只需要读取数据，就只给它SELECT权限；如果需要写入，就给INSERT、UPDATE、DELETE。

-- 创建一个只读用户
CREATE USER 'readonly_app'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT ON your_database.* TO 'readonly_app'@'localhost';

-- 创建一个读写用户
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'another_secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'webapp_user'@'localhost';
FLUSH PRIVILEGES;

4. 错误处理与日志记录

不要在生产环境中向用户暴露详细的数据库错误信息，这可能会泄露数据库结构、表名、字段名等敏感信息。

捕获异常： 使用try...except块捕获数据库操作可能抛出的异常。
通用错误信息： 向用户显示友好的、通用的错误消息（例如：“操作失败，请稍后再试。”）。

详细日志： 将详细的错误信息记录到日志文件中，供开发人员和运维人员排查问题。

import logging
logging.basicConfig(level=logging.ERROR, filename='app_errors.log')

try:
    # 数据库操作
    pass
except SomeDatabaseError as e:
    logging.error(f"数据库操作失败: {e}")
    # 向用户返回一个通用错误消息
    return {"error": "Internal server error, please try again later."}

安全性是一个持续的过程，没有一劳永逸的解决方案。将这些实践融入你的开发流程，才能构建出更健壮、更值得信赖的应用程序。

数据库连接与事务管理：提升性能与数据一致性的关键实践

在实际项目中，尤其是在处理高并发或复杂业务逻辑时，仅仅知道如何执行SQL语句是远远不够的。如何高效地管理数据库连接，以及确保数据操作的原子性和一致性，才是真正考验开发者功力的地方。这就像是开车，你知道怎么踩油门刹车，但要开得又快又稳，还得懂交通规则和车辆维护。

1. 高效管理数据库连接：连接池（Connection Pooling）

每次与数据库建立连接都需要消耗时间和系统资源，包括TCP握手、认证等。如果每次请求都建立新连接，然后关闭，在高并发场景下会造成巨大的性能开销。连接池就是解决这个问题的利器。

工作原理： 连接池预先创建一定数量的数据库连接，并将它们保存在一个池子里。当应用程序需要连接时，它从池中“借用”一个已存在的连接；当操作完成后，连接不会被关闭，而是“归还”到池中，供其他请求复用。
为什么需要：
- 性能提升： 显著减少连接建立和关闭的开销。
- 资源控制： 限制并发连接数，防止数据库过载。
- 连接复用： 提高数据库服务器的效率。
如何在Python中使用：
- Web框架集成： 许多Web框架（如Django

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~

Python 数据库