python SQLite数据库基本操作

1. 创建数据库

• 导入sqlite模块

import sqlite3

• 创建数据库连接：connect(“数据库名称”)
  • 如果connect()内的数据库名称存在，则与此数据库建立连接，返回connect连接对象；
  • 如果connect()内的数据库名称不存在，则新建数据库，再建立连接，返回connect对象。
• 关闭数据库连接close()

import sqlite3

# 1.创建数据库连接
"""
如果connect()内的数据库名称存在，则与此数据库建立连接；
如果connect()内的数据库名称不存在，则新建数据库，再建立连接
"""
conn = sqlite3.connect(database='test.db')
# 2.关闭数据库连接
conn.close()

2. 创建数据库表

2.1 SQLite数据类型：

NULL：空值
INTEGER：整数，如0，1，2，3，4
REAL：浮点数，存储为8字节的IEEE浮点数。如1.5
TEXT：文本字符串
BLOB：blob（binary large object）二进制大对象数据，常用于存储二进制文件。如图片、音频等。

2.2 connect()对象的方法

• close()：关闭数据库连接
• commit()：提交，更新数据库内容
• cursor()：建立cursor游标对象
• execute()：执行SQL数据库指令、数据库创建、新增、删除、修改、提取

# author:mlnt
# createdate:2022/8/19
"""
SQLite数据类型：
    NULL：空值
    INTEGER：整数，如0，1，2，3，4
    REAL：浮点数，存储为8字节的IEEE浮点数。如1.5
    TEXT：文本字符串
    BLOB：blob（binary large object）二进制大对象数据，常用于存储二进制文件。如图片、音频等。
"""
import sqlite3

# 1.创建数据库连接
conn = sqlite3.connect(database='test.db')
# 2.创建cursor对象
cursor = conn.cursor()
# SQL指令
sql = """CREATE TABLE STUDENT(
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            NAME TEXT NOT NULL,
            AGE INT NOT NULL,
            GENDER CHAR(10) NOT NULL DEFAULT('male')
            );"""
# 执行SQL指令
cursor.execute(sql)
# 关闭cursor对象
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()

使用sqlite3查看。

3. 操作数据库表

3.1 往数据库表中添加记录–INSERT

# author:mlnt
# createdate:2022/8/19
import sqlite3

# 1.创建数据库连接
conn = sqlite3.connect('test.db')
# 2.创建游标
cursor = conn.cursor()
# 往数据库表中添加记录
sql1 = "INSERT INTO student(ID, NAME, AGE, GENDER) VALUES(1, 'Jack', 18, 'male')"
# 创建表时设置ID为自增，插入记录时可以不加
sql2 = "INSERT INTO student(NAME, AGE, GENDER) VALUES('Tom', 24, 'male')"
# 创建表时，设置了gender的默认值为‘male’，可以不加
sql3 = "INSERT INTO student(NAME, AGE) VALUES('Mike', 20)"
sql4 = "INSERT INTO student(NAME, AGE, GENDER) VALUES('Mary', 22, 'female')"
# 执行SQL语句
cursor.execute(sql1)
cursor.execute(sql2)
cursor.execute(sql3)
cursor.execute(sql4)
conn.commit()  # 更新数据库
cursor.close()  # 关闭cursor对象
conn.close()  # 关闭数据库连接

3.2 查询SQLite数据库表–SELECT

# author:mlnt
# createdate:2022/8/19
import sqlite3

# 1.创建数据库连接
conn = sqlite3.connect('test.db')
# 2.创建游标
cursor = conn.cursor()
# 查询数据库表
sql = "SELECT * FROM student"
results = cursor.execute(sql)
print(type(results))  # <class 'sqlite3.Cursor'>
# for result in results:
#     print(result)
# 转成元组组成的列表
students = results.fetchall()
print(type(students))  # <class 'list'>
for student in students:
    print(student)
cursor.close()  # 关闭cursor对象
conn.close()  # 关闭数据库连接

# (1, 'Jack', 18, 'male')
# (2, 'Tom', 24, 'male')
# (3, 'Mike', 20, 'male')
# (4, 'Mary', 22, 'female')

3.3 更新SQLite数据库表记录–UPDATE

# author:mlnt
# createdate:2022/8/19
import sqlite3

# 1.创建数据库连接
conn = sqlite3.connect('test.db')
# 2.创建游标
cursor = conn.cursor()
# 更新数据库表记录
sql = """UPDATE student 
        set age = 25 
        WHERE name = 'Jack'"""
results = cursor.execute(sql)
conn.commit()  # 更新数据库
results = cursor.execute("SELECT * FROM student")
students = results.fetchall()  # 结果转成元组
for student in students:
    print(student)
cursor.close()  # 关闭cursor对象
conn.close()  # 关闭数据库连接

# (1, 'Jack', 25, 'male')
# (2, 'Tom', 24, 'male')
# (3, 'Mike', 20, 'male')
# (4, 'Mary', 22, 'female')

3.4 删除SQLite数据库表记录

# author:mlnt
# createdate:2022/8/19
import sqlite3

# 1.创建数据库连接
conn = sqlite3.connect('test.db')
# 2.创建游标
cursor = conn.cursor()

# 删除数据库表记录
sql = """DELETE 
        FROM student 
        WHERE name = 'Jack'"""
results = cursor.execute(sql)
conn.commit()  # 更新数据库
results = cursor.execute("SELECT * FROM student")
students = results.fetchall()  # 结果转成元组
for student in students:
    print(student)
cursor.close()  # 关闭cursor对象
conn.close()  # 关闭数据库连接
# (2, 'Tom', 24, 'male')
# (3, 'Mike', 20, 'male')
# (4, 'Mary', 22, 'female')

4. 综合应用

简易学员信息管理系统

系统功能：

• 添加学员信息
• 删除学员信息
• 修改学员信息
• 查询学员信息
• 显示所有学员信息

# author:mlnt
# createdate:2022/8/19

# 定义学员类类
import sqlite3

# 创建数据库连接
conn = sqlite3.connect(database='stuInfo.db')
cursor = conn.cursor()


def dropTable():
    """删除数据库表"""
    # 如果表存在，则删除
    sql = "DROP TABLE IF EXISTS STUDENT;"
    # 执行SQL指令
    cursor.execute(sql)


def createTable():
    """创建数据库表"""
    # 如果表不存在，则创建
    sql = """CREATE TABLE IF NOT EXISTS STUDENT(
                stuNumber CHAR(10) PRIMARY KEY NOT NULL,
                name TEXT NOT NULL,
                age INT NOT NULL,
                gender CHAR(10) NOT NULL,
                tel CHAR(11) NOT NULL
                );"""
    # 执行SQL指令
    cursor.execute(sql)


def selectAll():
    """查询所有"""
    # 查询数据库表
    sql = "SELECT * FROM student"
    results = cursor.execute(sql)
    # 转成元组组成的列表
    students = results.fetchall()
    return students


def checkExists(stuNumber):
    """判断学员是否存在"""
    # 查询数据库表
    sql = f"""SELECT * 
            FROM student
            WHERE stuNumber = '{stuNumber}';"""
    results = cursor.execute(sql)
    # 转成元组组成的列表
    students = results.fetchall()
    if len(students) != 0:
        return True
    else:
        return False


def insert(stuNumber, name, age, gender, tel):
    """插入数据"""
    info = (stuNumber, name, age, gender, tel)
    # 往数据库表中添加记录
    sql = """INSERT INTO student(
            stuNumber, name, age, gender, tel) 
            VALUES(?,?,?,?,?);"""
    # print(sql)
    # 执行SQL语句
    cursor.execute(sql, info)
    conn.commit()  # 更新数据库


def selectByStuNum(stuNumber):
    """根据学号查询"""
    # 查询数据库表
    sql = f"""SELECT * 
            FROM student
            WHERE stuNumber = '{stuNumber}';"""
    # print(sql)
    results = cursor.execute(sql)
    # 转成元组组成的列表
    students = results.fetchall()
    return students


def selectByName(name):
    """根据名字查询"""
    # 查询数据库表
    sql = f"""SELECT * 
            FROM student
            WHERE name = '{name}';"""
    # print(sql)
    results = cursor.execute(sql)
    # 转成元组组成的列表
    students = results.fetchall()
    return students


def update(stuNumber, name, age, gender, tel):
    """更新记录"""
    # 更新数据库表记录
    sql = f"""UPDATE student 
            SET name = '{name}', age = {age}, gender = '{gender}', tel = '{tel}'
            WHERE stuNumber = '{stuNumber}'"""
    # print(sql)
    cursor.execute(sql)
    conn.commit()  # 更新数据库


def deleteByName(name):
    """删除数据"""
    # 删除数据库表记录
    sql = f"""DELETE 
            FROM student 
            WHERE name = '{name}'"""
    cursor.execute(sql)
    conn.commit()  # 更新数据库

"""
系统功能：
    - 添加学员
    - 删除学员
    - 修改学员
    - 查询学员信息
    - 显示所有学员信息
"""
from student import *


# 定义学员管理类
class StudentManager(object):
    """学员管理类"""
    """
    管理系统框架
    需求：系统功能循环使用，用户输入不同的功能序号执行不同的功能。
    步骤：
    1.定义程序入口函数：
        - 加载数据
        - 显示功能菜单
        - 用户输入功能序号
        - 根据用户输入的功能序号执行不同功能
    2.定义系统功能函数，添加删除学员等
    """
    def __init__(self):
        # 重置数据库
        # 如果数据库表已存在，则删除
        dropTable()
        # 如果数据库表不存在，则创建
        createTable()

    # 程序入口函数，启动程序后执行的函数
    def run(self):
        while True:
            # 1.显示功能菜单
            self.show_menu()
            # 2.用户输入目标功能序号
            menu_num = int(input('请输入您需要的功能序号：'))

            # 3.根据用户输入的序号执行不同的功能
            if menu_num == 1:
                # 添加学员
                self.add_student()
            elif menu_num == 2:
                # 删除学员
                self.del_student()
            elif menu_num == 3:
                # 修改学员信息
                self.modify_student()
            elif menu_num == 4:
                # 查询学员信息
                self.search_student()
            elif menu_num == 5:
                # 显示所有学员信息
                self.show_student()
            elif menu_num == 6:
                confirm = input('确定要退出吗？（Y/N）')
                if confirm == 'Y':
                    break
                # 退出系统 -- 退出循环
                break
            else:
                print('输入的功能序号有误！')

    # 2.系统功能函数
    # 2.1 显示功能菜单
    @staticmethod
    def show_menu():
        """显示功能菜单"""
        print('*' * 8 + '欢迎使用学员管理系统' + '*' * 8)
        print('请选择如下功能：')
        print('1--添加学员')
        print('2--删除学员')
        print('3--修改学员信息')
        print('4--查询学员信息')
        print('5--显示所有学员信息')
        print('6--退出系统')

    # 2.2 添加学员
    def add_student(self):
        """
        需求：用户输入学员学号、姓名、年龄、性别、手机号，将学员添加到系统
        步骤：
            - 用户输入学号、姓名、年龄、性别、手机号
            - 创建该学员对象
            - 将该学员对象添加到列表
        """
        # 1.用户输入学号、姓名、年龄、性别、手机号
        stuNumber = input('请输入新学员学号：')
        if not stuNumber.isdigit():
            print('学号只能是纯数字！')
            return
        # 判断学号是否已存在
        if checkExists(stuNumber):
            print('该学员已存在！')
            return
        name = input('请输入新学员姓名：')
        age = input('请输入新学员年龄：')
        if not age.isdigit():
            print('年龄只能是纯数字！')
            return
        gender = input('请输入新学员性别：')
        tel = input('请输入新学员手机号：')
        if not tel.isdigit():
            print('手机号只能是纯数字！')
            return
        insert(stuNumber, name, age, gender, tel)
        print('添加成功！')

    # 2.3 删除学员
    def del_student(self):
        """删除学员"""
        # 1.用户输入目标学院姓名
        del_name = input('请输入要删除的学员姓名：')
        # 2.判断该学员是否存在，存在则删除，不存在则提示
        student_info = selectByName(del_name)
        if len(student_info) != 0:
            deleteByName(del_name)
        else:
            print('查无此人！')
        self.show_student()

    # 2.4 修改学员信息
    def modify_student(self):
        """修改学员信息"""
        # 1.用户输入目标学员学号
        modify_number = input('请输入要修改的学员的学号：')
        # 2.判断学员是否存在
        student_info = selectByStuNum(modify_number)
        if len(student_info) == 0:
            print('查无此人！')
        stu = student_info[0]
        print(f'学员信息：学号：{stu[0]}，姓名：{stu[1]}，年龄：{stu[2]}，性别：{stu[3]}，手机号：{stu[4]}')
        new_name = input('姓名：')
        new_age = input('年龄：')
        if not new_age.isdigit():
            print('年龄只能是纯数字！')
            return
        new_gender = input('性别：')
        new_tel = input('手机号：')
        if not new_tel.isdigit():
            print('手机号只能是纯数字！')
            return
        update(stu[0], new_name, new_age, new_gender, new_tel)

    # 2.5 查询学员信息
    def search_student(self):
        """查询学员信息"""
        # 1.输入目标学员姓名
        search_name = input('请输入要查询的学员姓名：')
        # 2.判断学员是否存在，存在则打印
        student_info = selectByName(search_name)
        if len(student_info) == 0:
            print('查无此人！')
        for stu in student_info:
            print(f'学号：{stu[0]}，姓名：{stu[1]}，年龄：{stu[2]}，性别：{stu[3]}，手机号：{stu[4]}')

    # 2.6 显示所有学员信息
    def show_student(self):
        students = selectAll()
        if len(students):
            print('学号\t姓名\t年龄\t性别\t手机号')
            for stu in students:
                print(f'{stu[0]}\t{stu[1]}\t{stu[2]}\t{stu[3]}\t{stu[4]}')
        else:
            print('暂无数据！')


# 启动学员管理系统
# 保证是当前文件运行才启动管理系统
if __name__ == '__main__':
    student_manager = StudentManager()
    student_manager.run()

---

参考：

• https://www.runoob.com/sqlite/sqlite-python.html
• https://blog.csdn.net/yanjinrong/article/details/125830789