SQLite - Perl

SQLite - Python 集成

Python 是一种流行的现代语言，用于与数据库交互。本章演示了如何在 Python 程序中使用 SQLite。Python 的主要优势之一是其内置的 sqlite3 模块，这意味着无需安装外部库即可开始使用。

连接到数据库

连接到 SQLite 数据库只需一个函数调用。如果指定的数据库文件不存在，它将自动创建。最佳实践是使用 with 语句管理连接，这可以确保连接自动关闭。

import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    """ 创建一个到 SQLite 数据库的连接 """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(f"Connected to {db_file}, SQLite version: {sqlite3.version}")
    except Error as e:
        print(e)
    return conn

if __name__ == '__main__':
    conn = create_connection("py_company.db")
    if conn:
        conn.close()

执行查询：CRUD 操作

所有 SQL 操作都使用 cursor（游标）对象执行。最重要的最佳实践是使用参数化查询（带 ? 占位符）来防止 SQL 注入（SQL injection）漏洞。

CREATE：创建表

您可以使用 cursor.execute() 执行 CREATE TABLE 语句。

def execute_sql(conn, sql_statement):
    try:
        c = conn.cursor()
        c.execute(sql_statement)
    except Error as e:
        print(e)

conn = create_connection("py_company.db")
if conn:
    create_employees_table = """
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        position TEXT NOT NULL,
        salary REAL NOT NULL
    );
    """
    execute_sql(conn, create_employees_table)
    conn.close()

INSERT：添加数据

使用 ? 作为数据的占位符。这是插入数据的安全方式。

def add_employee(conn, employee):
    sql = 'INSERT INTO employees(name, position, salary) VALUES(?,?,?)'
    cur = conn.cursor()
    cur.execute(sql, employee)
    conn.commit() # 将更改提交到数据库
    return cur.lastrowid

conn = create_connection("py_company.db")
if conn:
    with conn:
        employee_data = ('Alice', 'Developer', 80000)
        employee_id = add_employee(conn, employee_data)
        print(f"Added employee with id: {employee_id}")

SELECT：读取数据

执行 SELECT 语句后，您可以使用 fetchone()、fetchall() 或通过迭代游标来检索结果。

def select_all_employees(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM employees")

    rows = cur.fetchall()

    for row in rows:
        print(row)

conn = create_connection("py_company.db")
if conn:
    with conn:
        print("All employees:")
        select_all_employees(conn)

输出：

All employees:
(1, 'Alice', 'Developer', 80000.0)

UPDATE：修改数据

更新时务必使用 WHERE 子句，以避免修改所有行。同样，请使用 ? 占位符。

def update_employee_salary(conn, data):
    sql = 'UPDATE employees SET salary = ? WHERE id = ?'
    cur = conn.cursor()
    cur.execute(sql, data)
    conn.commit()

conn = create_connection("py_company.db")
if conn:
    with conn:
        update_data = (85000, 1) # 新薪水，员工ID
        update_employee_salary(conn, update_data)
        print("Salary updated.")

DELETE：删除数据

DELETE 操作是破坏性的。使用 WHERE 子句至关重要。

def delete_employee(conn, id):
    sql = 'DELETE FROM employees WHERE id = ?'
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()

conn = create_connection("py_company.db")
if conn:
    with conn:
        delete_employee(conn, 1)
        print("Employee deleted.")

常见陷阱与调试

• 忘记 conn.commit()： 对于任何修改数据的操作（INSERT、UPDATE、DELETE），您都必须调用 conn.commit() 来保存更改。使用 with conn: 代码块可以在成功退出时自动处理此问题。
• SQL 注入（SQL Injection）： 永远不要使用 f-string 或字符串拼接来构建包含用户数据的查询（例如，f"SELECT * FROM users WHERE name = '{user_name}'"）。始终使用 ? 占位符。
• 未关闭的连接： 忘记调用 conn.close() 可能会导致数据库文件被锁定。with 语句是防止这种情况发生的最佳方法。