数据库操作入门

学习SQL基础和Python数据库操作

SQLite基础

SQLite是轻量级数据库，Python内置支持。

import sqlite3

# 连接数据库（不存在则创建）
conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER
    )
""")

# 插入数据
cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
               ("张三", "zhangsan@example.com", 25))

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

# 提交并关闭
conn.commit()
conn.close()

CRUD操作

class UserDB:
    def __init__(self, db_name="users.db"):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()
        self._create_table()

    def _create_table(self):
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                name TEXT,
                age INTEGER
            )
        """)
        self.conn.commit()

    def add(self, name, age):
        self.cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
        self.conn.commit()

    def get_all(self):
        self.cursor.execute("SELECT * FROM users")
        return self.cursor.fetchall()

    def update(self, user_id, name, age):
        self.cursor.execute("UPDATE users SET name=?, age=? WHERE id=?", (name, age, user_id))
        self.conn.commit()

    def delete(self, user_id):
        self.cursor.execute("DELETE FROM users WHERE id=?", (user_id,))
        self.conn.commit()

# 使用
db = UserDB()
db.add("张三", 25)
print(db.get_all())

本章小结

• SQLite：轻量级嵌入式数据库
• SQL语句：CREATE, INSERT, SELECT, UPDATE, DELETE
• 参数化查询：防止SQL注入

→ 继续阅读：33-API开发与调用