SQLite3数据库文件存在检查及Python操作代码修复

问题说明 #

需要创建SQLite3数据库，要求检查数据库文件是否存在，但现有代码运行异常：创建数据库后功能选项失效，无法验证变量有效性，尝试多个教程未解决，需修正代码及相关建议。

原代码 #

import sqlite3
from replit import clear
import time as t

bd = sqlite3.connect('Builds.db')

cursor = bd.cursor()

cursor.execute('''
    CREATE TABLE Builds (
        Build TEXT,
        Weapon TEXT,
        Aspect TEXT,
        Keepsakes TEXT,
        Arcana TEXT,
        Boons TEXT,
        Hammers TEXT
    )
''')


data = [
    ('Pan Snipe', ' Sister Blades', ' Aspect of Pan',' Keepsakes: Transcendant Embryo, Aromatic Phial, Knuckle Bones or Luckier Tooth', ' Arcana Cards: None', ' Boons: Poseidon- Wave Flourish (Heroic), Hera- Engangment Ring (Rare), Artemis- Death Warrant', ' Hammers: Spiral Knives and Dancing Knives')
]


cursor.executemany('INSERT INTO Builds (Build, Weapon, Aspect, Keepsakes, Arcana, Boons, Hammers) VALUES (?, ?, ?, ?, ?, ?, ?)', data)

while True:
    clear()
    selection = input("""
    1) View list of builds
    2) Add to build list
    3) Search for name of build
    4) Delete build from build lsit
    5) Quit

    Enter your selection: """)

    if selection == "1":
        cursor.execute('SELECT * FROM Builds')
        for row in cursor.fetchall():
            print(row)
            t.sleep(15)
    elif selection == "2":
        build_name = input("Build name: ")
        weapon = input("Enter Weapon: ")
        aspect = input("Enter Aspect: ")
        keepsake = input("Enter Keepsakes: ")
        arcana = input("Enter Arcana: ")
        boons = input("Enter Boons: ")
        hammers = input("Enter Hammers: ")
        print("")
        new_data = (None, build_name, weapon, aspect, keepsake, arcana, boons, hammers)
        cursor.execute('INSERT INTO Names VALUES (?, ?, ?, ?, ?, ?, ?, ?)', new_data)
        bd.commit()
    elif selection == "3":
        search_term = input("Enter build to search: ")
        print("")
        cursor.execute('SELECT * FROM Builds WHERE Build = ?', (search_term,))
        for row in cursor.fetchall():
            print(row)
            t.sleep(15)
    elif selection == "4":
        id_to_delete = input("Enter build to delete: ")
        print("")
        cursor.execute('DELETE FROM Builds WHERE Buils = ?', (id_to_delete,))
        bd.commit()
    elif selection == "5":
        break
    else:
        print("")
        print("Incorrect selection, please try again.")
        print("")


bd.close()

修正后的代码 #

import sqlite3
import os
from replit import clear
import time as t

# 检查数据库文件是否存在
db_file = 'Builds.db'
if not os.path.exists(db_file):
    print(f"数据库文件{db_file}不存在，将创建新文件")

bd = sqlite3.connect(db_file)
cursor = bd.cursor()

# 仅当表不存在时创建
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Builds (
        Build TEXT,
        Weapon TEXT,
        Aspect TEXT,
        Keepsakes TEXT,
        Arcana TEXT,
        Boons TEXT,
        Hammers TEXT
    )
''')

data = [
    ('Pan Snipe', ' Sister Blades', ' Aspect of Pan',' Keepsakes: Transcendant Embryo, Aromatic Phial, Knuckle Bones or Luckier Tooth', ' Arcana Cards: None', ' Boons: Poseidon- Wave Flourish (Heroic), Hera- Engangment Ring (Rare), Artemis- Death Warrant', ' Hammers: Spiral Knives and Dancing Knives')
]

# 检查是否已有数据，避免重复插入
cursor.execute('SELECT * FROM Builds WHERE Build = ?', (data[0][0],))
if not cursor.fetchone():
    cursor.executemany('INSERT INTO Builds (Build, Weapon, Aspect, Keepsakes, Arcana, Boons, Hammers) VALUES (?, ?, ?, ?, ?, ?, ?)', data)
    bd.commit()

while True:
    clear()
    selection = input("""
    1) 查看Build列表
    2) 添加Build到列表
    3) 搜索Build名称
    4) 从列表删除Build
    5) 退出

    请输入你的选择: """)

    if selection == "1":
        cursor.execute('SELECT * FROM Builds')
        rows = cursor.fetchall()
        if rows:
            for row in rows:
                print(row)
                t.sleep(2)  # 缩短等待时间，提升体验
        else:
            print("暂无Build数据")
        input("按回车键继续...")
    elif selection == "2":
        build_name = input("Build名称: ")
        weapon = input("输入武器: ")
        aspect = input("输入Aspect: ")
        keepsake = input("输入Keepsakes: ")
        arcana = input("输入Arcana: ")
        boons = input("输入Boons: ")
        hammers = input("输入Hammers: ")
        print("")
        # 修正表名和数据格式，移除多余的None
        new_data = (build_name, weapon, aspect, keepsake, arcana, boons, hammers)
        cursor.execute('INSERT INTO Builds (Build, Weapon, Aspect, Keepsakes, Arcana, Boons, Hammers) VALUES (?, ?, ?, ?, ?, ?, ?)', new_data)
        bd.commit()
        print("Build已成功添加")
        t.sleep(2)
    elif selection == "3":
        search_term = input("输入要搜索的Build名称: ")
        print("")
        cursor.execute('SELECT * FROM Builds WHERE Build = ?', (search_term,))
        rows = cursor.fetchall()
        if rows:
            for row in rows:
                print(row)
                t.sleep(2)
        else:
            print(f"未找到名为{search_term}的Build")
        input("按回车键继续...")
    elif selection == "4":
        delete_term = input("输入要删除的Build名称: ")
        print("")
        # 修正拼写错误Buils为Build
        cursor.execute('DELETE FROM Builds WHERE Build = ?', (delete_term,))
        bd.commit()
        if cursor.rowcount > 0:
            print(f"名为{delete_term}的Build已删除")
        else:
            print(f"未找到名为{delete_term}的Build")
        t.sleep(2)
    elif selection == "5":
        print("退出程序...")
        break
    else:
        print("")
        print("输入错误，请重新选择。")
        print("")
        t.sleep(2)

bd.close()

关键修正点 #

• 数据库文件检查：新增os.path.exists判断数据库文件是否存在，明确提示创建状态
• 表创建逻辑：使用CREATE TABLE IF NOT EXISTS，避免重复创建表报错
• 初始数据插入优化：检查是否已存在初始数据，防止每次运行重复插入
• 功能选项修复：
  • 修正添加功能中的表名错误（Names改为Builds），移除多余的None参数，匹配表字段数量
  • 修正删除功能中的字段拼写错误（Buils改为Build）
• 用户体验优化：
  • 缩短sleep时长，避免过长等待
  • 增加空数据提示、操作结果反馈
  • 添加回车继续的交互，防止界面一闪而过
• 变量有效性验证：通过cursor.rowcount判断删除操作是否生效，通过fetchall()结果判断查询是否有数据

相关建议 #

• 添加主键：建议给Build字段设置为主键（Build TEXT PRIMARY KEY），避免重复添加同名Build
• 输入验证：对用户输入的内容做合法性检查，比如非空判断，防止插入空数据
• 异常处理：添加try-except块捕获数据库操作异常，避免程序崩溃
• 数据格式化：输出查询结果时可以格式化展示，提升可读性，比如按字段分行打印

内容的提问来源于stack exchange，提问作者HoloGrain