Python数据库操作教程：SQLite与MySQL实战详解

欢迎各位小伙伴来到golang学习网，相聚于此都是缘哈哈哈！今天我给大家带来《Python操作数据库教程：SQLite与MySQL实战指南》，这篇文章主要讲到等等知识，如果你对文章相关的知识非常感兴趣或者正在自学，都可以关注我，我会持续更新相关文章！当然，有什么建议也欢迎在评论留言提出！一起学习！

选择合适的数据库驱动需根据数据库类型和项目需求，如SQLite用自带sqlite3，MySQL选mysql-connector-python或pymysql，PostgreSQL用psycopg2，并综合考虑性能、兼容性、功能和易用性；操作流程包括安装驱动、建立连接、执行SQL、提交事务和关闭连接；为防止SQL注入，应使用参数化查询而非拼接SQL；为提升性能，可使用连接池（如mysql.connector.pooling）复用连接；事务处理通过commit提交或rollback回滚，确保数据一致性。

使用Python操作数据库，关键在于选择合适的数据库驱动，然后建立连接，执行SQL语句。不同的数据库，驱动不同，但操作流程大体一致。

解决方案

首先，你需要安装对应的数据库驱动。比如，如果是SQLite，Python自带sqlite3模块，无需额外安装。如果是MySQL，则需要安装mysql-connector-python或者pymysql。

以SQLite为例：

import sqlite3

# 连接到数据库（如果数据库不存在，则会创建一个）
conn = sqlite3.connect('mydatabase.db')

# 创建一个游标对象，用于执行SQL语句
cursor = conn.cursor()

# 执行SQL语句，创建一个表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    )
''')

# 插入数据
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")

# 提交事务，保存更改
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 关闭连接
conn.close()

如果是MySQL，流程类似，只是连接方式和驱动不同：

import mysql.connector

# 连接到MySQL数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

# 执行SQL语句
mycursor.execute("SELECT * FROM users")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

注意替换yourusername、yourpassword和mydatabase为你的实际MySQL配置。

如何选择合适的Python数据库驱动？

选择数据库驱动，主要看你的数据库类型和个人偏好。

• SQLite: sqlite3是Python自带的，简单易用，适合小型项目或本地存储。
• MySQL: mysql-connector-python是MySQL官方推荐的驱动，性能较好，但可能对某些高级特性支持不够完善。pymysql是纯Python实现的驱动，兼容性好，但性能可能稍逊。
• PostgreSQL: psycopg2是常用的PostgreSQL驱动，性能优秀，功能强大。
• 其他数据库: 还有诸如pymongo (MongoDB), redis-py (Redis) 等针对特定数据库的驱动。

选择时，可以考虑以下因素：

• 性能: 驱动的性能直接影响数据库操作的速度。
• 兼容性: 驱动是否与你的Python版本和数据库版本兼容。
• 功能: 驱动是否支持你需要的数据库特性，比如连接池、事务等。
• 易用性: 驱动的API是否易于理解和使用。

如何防止SQL注入攻击？

SQL注入是一种常见的安全漏洞，攻击者可以通过构造恶意的SQL语句来窃取或篡改数据库中的数据。

防止SQL注入的关键是不要直接将用户输入拼接到SQL语句中。应该使用参数化查询或预编译语句。

以SQLite为例：

# 错误示例（存在SQL注入风险）
username = input("Enter username: ")
sql = "SELECT * FROM users WHERE username = '" + username + "'"
cursor.execute(sql)

# 正确示例（使用参数化查询）
username = input("Enter username: ")
sql = "SELECT * FROM users WHERE username = ?"
cursor.execute(sql, (username,))

参数化查询会将用户输入作为参数传递给数据库，数据库会自动处理转义，防止恶意SQL语句的执行。

对于MySQL，mysql-connector-python和pymysql都支持参数化查询。

如何处理数据库连接池？

频繁地创建和关闭数据库连接会消耗大量的系统资源。使用数据库连接池可以有效地提高数据库操作的性能。

连接池维护着一组数据库连接，当需要连接时，从连接池中获取一个空闲连接；当连接使用完毕后，将其返回到连接池中，而不是直接关闭。

以mysql-connector-python为例，可以使用mysql.connector.pooling模块创建连接池：

import mysql.connector
from mysql.connector import pooling

# 创建连接池
pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name = "mypool",
    pool_size = 5,  # 连接池大小
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

# 从连接池中获取连接
conn = pool.get_connection()
cursor = conn.cursor()

# 执行SQL语句
cursor.execute("SELECT * FROM users")

# ...

# 释放连接
cursor.close()
conn.close()

pool_size参数指定连接池中连接的数量。合理设置连接池大小可以提高数据库操作的并发能力。

如何处理数据库事务？

事务是一组数据库操作的逻辑单元，要么全部执行成功，要么全部回滚。事务可以保证数据的一致性和完整性。

以SQLite为例：

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

try:
    # 开始事务
    cursor.execute("BEGIN TRANSACTION")

    # 执行一系列SQL语句
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")

    # 提交事务
    conn.commit()
    print("Transaction committed successfully.")

except Exception as e:
    # 回滚事务
    conn.rollback()
    print(f"Transaction failed: {e}")

finally:
    conn.close()

如果在事务执行过程中发生异常，conn.rollback()会将数据库恢复到事务开始前的状态。

对于MySQL，事务处理方式类似。

记住，掌握这些基本操作，你就能在Python中轻松地与各种数据库打交道了。 实践出真知，多写代码，才能真正理解其中的奥妙。

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