Python操作SQLite数据库

SQLite是一个轻量级的数据库，被广泛应用于各种嵌入式系统中。它不需要服务器，也不需要复杂的配置，只需一个数据库文件即可。Python作为一门流行的编程语言，与SQLite的结合使用，可以轻松地进行数据的存储、查询和更新等操作。本文将详细介绍如何使用Python操作SQLite数据库，包括创建表、新增、查询、更新和删除数据等操作，并介绍常用的数据类型和约束。

一、准备工作

在开始之前，请确保您的计算机上已经安装了Python。SQLite是一个内置在Python标准库中的模块，因此不需要额外安装。接下来，我们将创建一个SQLite数据库文件，并连接到该数据库。

1. 导入SQLite模块：
```
import sqlite3
```

2. 连接到SQLite数据库：

# 连接到数据库文件，如果文件不存在，会自动创建
conn = sqlite3.connect('example.db')

3. 创建一个Cursor对象，用于执行SQL语句：
```
cursor = conn.cursor()
```

二、创建表

创建表是数据库操作的第一步。下面是一个创建表的示例：

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

"""注释
CREATE TABLE IF NOT EXISTS: 创建表的SQL命令，如果表已经存在，则不会创建新表
user: 表的名称
id, name, age, email: 表中的列（字段）名称
INTEGER PRIMARY KEY AUTOINCREMENT: 定义id列的数据类型为整数，并且作为主键自动递增
TEXT: 定义文本类型的数据
NOT NULL: 约束，表示该列的值不能为NULL
UNIQUE: 约束，表示该列的值必须唯一，不能有重复
"""

在这个示例中，我们创建了一个名为user的表，包含四个字段：id、name、age和email。其中，id是主键，并且自动增长；name字段不能为空；email字段是唯一的。

三、新增数据

向表中新增数据，可以使用INSERT语句。下面是一个新增数据的示例：

# 新增数据
cursor.execute("INSERT INTO user (name, age, email) VALUES ('Alice', 25, 'alice@example.com')")
cursor.execute("INSERT INTO user (name, age, email) VALUES ('Bob', 30, 'bob@example.com')")
cursor.execute("INSERT INTO user (name, age, email) VALUES ('Cathy', 28, 'cathy@example.com')")
# 提交事务
conn.commit()

新增数据后，需要调用commit()方法提交事务，否则数据不会保存到数据库。

四、查询数据

查询数据是数据库操作中最常用的操作之一。下面是一个查询数据的示例：

"""编写SELECT查询语句
        SELECT: 查询命令，用于从数据库表中选择数据
        name, age: 字段名，表示要查询的字段
        FROM: 指定查询的数据来自哪个表
        user: 表名，表示要查询的表
        WHERE: 条件语句，用于过滤数据
        age > 25: 条件，表示只选择年龄大于25的记录
        ORDER BY: 排序命令，用于对查询结果进行排序
        age DESC: 表示按年龄降序排序
    """

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

    # 查询指定列
    cursor.execute("SELECT name, age FROM user")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # 查询指定条件的数据
    cursor.execute("SELECT * FROM user WHERE age > 25")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # 查询指定列，指定条件的数据
    cursor.execute("SELECT name, age FROM user WHERE age > 25")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # 查询指定列，指定条件的数据，排序
    cursor.execute("SELECT name, age FROM user WHERE age > 25 ORDER BY age DESC")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

在这个示例中，我们查询了年龄大于等于25的所有用户。查询结果存储在rows变量中，可以通过循环遍历输出。

五、更新数据

更新数据使用UPDATE语句。下面是一个更新数据的示例：

# 更新数据
cursor.execute("UPDATE user SET age = 31 WHERE name = 'Bob'")
# 提交事务
conn.commit()

在这个示例中，我们将名为Bob的用户的年龄更新为31岁。

六、删除数据

删除数据使用DELETE语句。下面是一个删除数据的示例：

# 删除数据
cursor.execute("DELETE FROM user WHERE name = 'Alice'")
# 提交事务
conn.commit()

在这个示例中，我们删除了名为Alice的用户。

七、常用的数据类型和约束

数据类型	描述
INTEGER	整数
REAL	浮点数
TEXT	文本字符串
BLOB	二进制数据
DATE	日期，格式为YYYY-MM-DD
TIME	时间，格式为HH:MM:SS

约束	描述
CHECK	检查约束，确保字段值满足条件
PRIMARY KEY	主键约束，唯一标识一行数据
AUTOINCREMENT	自动增长，通常与主键一起使用
NOT NULL	非空约束，该字段的值不能为空
UNIQUE	唯一约束，该字段的值必须唯一
FOREIGN KEY	外键约束，用于建立表之间的关系

八，案例

案例：使用 Python 操作 SQLite 数据库实现一个简单的学生信息管理系统

1. 创建数据库和表使用 Python 中的 sqlite3 模块创建一个名为 students.db 的数据库，并在其中创建一个名为 students 的表，用于存储学生信息。

import sqlite3
# 连接到数据库（如果数据库不存在，会自动创建）
conn = sqlite3.connect('students.db')
# 创建一个Cursor对象，用于执行SQL语句
cursor = conn.cursor()
# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    gender TEXT
)
''')
# 提交事务
conn.commit()
# 关闭Cursor对象
cursor.close()
# 关闭Connection对象
conn.close()

2. 添加学生信息编写一个函数 add_student，用于向 students 表中添加学生信息。

def add_student(name, age, gender):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()
    
    cursor.execute('INSERT INTO students (name, age, gender) VALUES (?, ?, ?)', (name, age, gender))
    
    conn.commit()
    cursor.close()
    conn.close()

3. 查询学生信息编写一个函数 get_students，用于查询 students 表中的所有学生信息。

def get_students():
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()
    
    cursor.execute('SELECT * FROM students')
    students = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return students

4. 更新学生信息编写一个函数 update_student，用于更新 students 表中的学生信息。

def update_student(student_id, name, age, gender):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()
    
    cursor.execute('UPDATE students SET name=?, age=?, gender=? WHERE id=?', (name, age, gender, student_id))
    
    conn.commit()
    cursor.close()
    conn.close()

5. 删除学生信息编写一个函数 delete_student，用于删除 students 表中的学生信息。

def delete_student(student_id):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()
    
    cursor.execute('DELETE FROM students WHERE id=?', (student_id,))
    
    conn.commit()
    cursor.close()
    conn.close()

6. 主程序在主程序中，调用以上函数，实现一个简单的学生信息管理系统。

def main():
    print("学生信息管理系统")
    print("1. 添加学生信息")
    print("2. 查询学生信息")
    print("3. 更新学生信息")
    print("4. 删除学生信息")
    print("5. 退出")
    
    while True:
        choice = input("请选择操作：")
        
        if choice == '1':
            name = input("请输入学生姓名：")
            age = int(input("请输入学生年龄："))
            gender = input("请输入学生性别：")
            add_student(name, age, gender)
            print("添加成功！")
        
        elif choice == '2':
            students = get_students()
            print("学生信息如下：")
            for student in students:
                print(student)
        
        elif choice == '3':
            student_id = int(input("请输入要更新的学生ID："))
            name = input("请输入新的学生姓名：")
            age = int(input("请输入新的学生年龄："))
            gender = input("请输入新的学生性别：")
            update_student(student_id, name, age, gender)
            print("更新成功！")
        
        elif choice == '4':
            student_id = int(input("请输入要删除的学生ID："))
            delete_student(student_id)
            print("删除成功！")
        
        elif choice == '5':
            break
        
        else:
            print("无效的操作，请重新选择！")
if __name__ == '__main__':
    main()