Python操作数据库的核心思路是建立连接、获取游标、执行SQL、处理结果、提交事务和关闭连接。该流程适用于SQLite、MySQL和PostgreSQL，遵循DB-API 2.0规范，接口一致，仅连接参数和库不同。SQLite轻量，适合本地开发；MySQL广泛用于Web应用；PostgreSQL功能强大，适合复杂业务。安全性方面需使用参数化查询防SQL注入，验证输入，遵循最小权限原则，并妥善处理错误。连接池可提升高并发下的性能。
Python操作数据库的核心思路其实很简单：建立连接、获取游标、执行SQL语句、处理结果、提交或回滚事务，最后关闭连接。无论你是用SQLite、MySQL还是PostgreSQL，这一套流程都大同小异，主要区别在于使用的Python库和连接参数。掌握了这套范式，就能灵活地与各种关系型数据库打交道。
在Python中，与关系型数据库交互通常遵循DB-API 2.0规范，这使得不同数据库的客户端库（如
sqlite3
、
PyMySQL
、
psycopg2
）在接口上保持高度一致。这意味着你学会了一种，其他也就触类旁通了。
解决方案
让我们一步步看看如何具体操作。
1. SQLite：轻量级本地数据库
立即学习“Python免费学习笔记（深入）”；
SQLite是Python标准库的一部分，无需额外安装。它以文件形式存储数据，非常适合本地开发、小型应用或作为配置存储。
import sqlite3def 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 pymysqldef 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 psycopg2def 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)
正确示例（参数化查询）：
# SQLitecursor.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 logginglogging.basicConfig(level=logging.ERROR, filename='app_errors.log')try: # 数据库操作 passexcept 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
以上就是如何使用Python操作数据库（SQLite/MySQL/PostgreSQL）？的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1369920.html
微信扫一扫 
支付宝扫一扫 
