sqlite python -减少重复代码或处理数据库端的关系

SQLite 是一个轻量级的数据库引擎，它不需要单独的服务器进程，并且允许通过 Python 的标准库 sqlite3 模块直接访问数据库。在 Python 中使用 SQLite 可以有效地处理数据库端的关系，并且可以通过一些设计模式来减少重复代码。

基础概念

SQLite: 是一个嵌入式关系型数据库，它的特点是轻量级、自包含、高可靠性、零配置和事务性。

Python sqlite3 模块: Python 标准库中的一个模块，提供了对 SQLite 数据库的访问。

ORM (Object-Relational Mapping): 对象关系映射，是一种程序技术，用于将对象模型表示的数据映射到基于 SQL 的关系模型数据结构中去。

类型

表: 存储数据的结构。
视图: 虚拟表，其内容由查询定义。
索引: 加速查询操作的数据结构。
触发器: 在特定事件发生时自动执行的数据库操作。

应用场景

小型项目: 对于不需要复杂数据库管理的小型应用。
移动应用: 由于 SQLite 的轻量级特性，它非常适合移动平台。
嵌入式系统: 在资源受限的环境中。
测试环境: 快速搭建和销毁的测试数据库。

减少重复代码的方法

使用 ORM

ORM 可以将数据库表映射为 Python 类，表的字段映射为类的属性，从而减少直接编写 SQL 语句的需要。例如，使用 SQLAlchemy ORM：

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# 添加用户
ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
session.add(ed_user)
session.commit()

# 查询用户
our_user = session.query(User).filter_by(name='ed').first()
print(our_user)

封装数据库操作

创建一个数据库操作的封装类，将常用的数据库操作（如增删改查）封装成方法，以便复用。

import sqlite3

class Database:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def execute(self, query, params=()):
        self.cursor.execute(query, params)
        self.conn.commit()

    def fetch_all(self, query, params=()):
        self.cursor.execute(query, params)
        return self.cursor.fetchall()

    def close(self):
        self.conn.close()

# 使用封装的数据库类
db = Database('example.db')
db.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')
db.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
users = db.fetch_all('SELECT * FROM users')
print(users)
db.close()

遇到的问题及解决方法

问题：SQL 注入风险

原因: 直接拼接 SQL 字符串可能导致恶意用户输入被执行为 SQL 命令。

解决方法: 使用参数化查询或 ORM 来避免 SQL 注入。

# 参数化查询示例
db.execute('SELECT * FROM users WHERE name = ?', ('Alice',))

问题：事务处理不当

原因: 在多步骤操作中，如果没有正确使用事务，可能会导致数据不一致。

解决方法: 使用 with 语句来确保事务的正确提交或回滚。

try:
    with db.conn:
        db.execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
        db.execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2')
except sqlite3.Error as e:
    print(f"Transaction failed: {e}")

通过上述方法，可以有效地减少重复代码，并且处理数据库端的关系，同时确保代码的安全性和健壮性。