Python SQLite银行账户系统如何校验数据库用户名与PIN正确性

核心需求 #

• 登录环节增加数据库校验：用户输入的用户名或PIN与数据库存储不匹配时，返回无效提示
• 校验通过后才开放存款、取款业务权限
• 完整支持新账户创建、登录办理业务的全流程

当前已完成进度 #

• 第一阶段（文件I/O与基础逻辑）：完成余额文件读取初始化、Bank_Account核心类开发，已实现菜单跳转、存款、取款、余额展示的基础方法
• 第二阶段（数据库对接）：引入sqlite3模块，完成Bank_Users.db数据库连接、bank用户表建表（字段：PIN为主键、username为用户名文本），已插入5条初始测试用户数据
• 待开发内容：登录环节的用户名+PIN数据库匹配校验逻辑

原有实现代码 #

# PHASE 1 (FILE I/O and Logic of System)
# Initialization of Current Balance ** Current Balance is the same for all users for the sake of simplicity ** 
myMoney = open("current_balance.txt")
currentBalance = int(myMoney.readline())

# Imports 
import sqlite3

# Creation of Bank Account and Notifying User(s) of Current Balance
class Bank_Account:
    def __init__(self):
        self.balance= currentBalance
        print("Welcome to Your Bank Account System!")
    # If statements for first screen
    def options_1(self):
        ch = int(input("1. Create an Account\n2. Log into Your Account\nEnter a Choice: "))
        if ch == 1: 
            self.create()
        if ch == 2: 
            self.Log_in()
    def options_2(self): 
        ch= int(input("1. Withdraw Money from Your Account\n2. Deposit Money to Your Account\nEnter a Choice: "))
        if ch == 1: 
            self.withdraw()
        if ch == 2: 
            self.deposit()
    # Function to Create an Account 
    def create(self): 
        user_create = str(input("Enter a Username:"))
        pin_create = int(input("Enter a Pin Number:" ))
        print("Account successfully created!")
     # Function to Log into Account 
    def Log_in(self):
        user = str(input("Enter your Username:"))
        pin = int(input("Enter your Pin Number:")) 
        print("Welcome", user, "!")
    # Function to Deposit Money 
    def deposit(self):
        amount=float(input("Enter the amount you want to deposit: "))
        self.balance += amount
        print("Amount Deposited: ",amount)
    # Function to Withdraw Money
    def withdraw(self):
        amount = float(input("Enter the amount you want to withdraw: "))
        if self.balance>=amount:
            self.balance-=amount
            print("You withdrew: ",amount)
        else:
            print("Insufficient balance ")

    def display(self):
        print("Net Available Balance=",self.balance)

# Creating an object of class
self = Bank_Account()
# Calling functions with that class
self.options_1()
self.options_2()
self.display()



# PHASE 2 (With Database) SQLite 3 

# Define Connection and Cursor 
connection = sqlite3.connect('Bank_Users.db')
cursor = connection.cursor()
connection.commit()

# Create Users Table 
command1 = """ CREATE TABLE IF NOT EXISTS 
bank(pin INTEGER PRIMARY KEY , username text )"""
cursor.execute(command1)

# Add to Users/Bank
cursor.execute("INSERT INTO bank VALUES (7620, 'Kailas Kurup')")
cursor.execute("INSERT INTO bank VALUES (4638, 'Bethany Watkins')")
cursor.execute("INSERT INTO bank VALUES (3482, 'John Hammond')")
cursor.execute("INSERT INTO bank VALUES (3493, 'Melissa Rodriguez')")
cursor.execute("INSERT INTO bank VALUES (9891, 'Kevin Le')")

# Get Results / Querying Database
cursor.execute("SELECT * FROM bank")
results = cursor.fetchall()
print(results)

逻辑修正方案 #

原有代码存在几个明显问题：

• 数据库逻辑和业务类完全分离，建表、插数逻辑每次运行都会重复执行，会触发PIN主键重复报错
• 账户创建方法没有把新用户信息写入数据库
• 登录方法没有做任何校验，输入任意内容都能直接进入业务菜单
• 流程控制有问题，不管选创建账户还是登录，都会直接跳转到存取款菜单

修正后完整代码 #

import sqlite3

# 初始化余额
with open("current_balance.txt", "r") as myMoney:
    currentBalance = int(myMoney.readline())

class Bank_Account:
    def __init__(self):
        self.balance = currentBalance
        # 初始化数据库连接
        self.conn = sqlite3.connect('Bank_Users.db')
        self.cursor = self.conn.cursor()
        # 建表（如果不存在）
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS bank(
            pin INTEGER PRIMARY KEY , 
            username text 
        )""")
        # 初始化测试用户，用INSERT OR IGNORE避免重复插入报错
        test_users = [
            (7620, 'Kailas Kurup'),
            (4638, 'Bethany Watkins'),
            (3482, 'John Hammond'),
            (3493, 'Melissa Rodriguez'),
            (9891, 'Kevin Le')
        ]
        self.cursor.executemany("INSERT OR IGNORE INTO bank VALUES (?, ?)", test_users)
        self.conn.commit()
        self.current_user = None
        print("欢迎使用银行账户系统！")

    # 一级菜单
    def options_1(self):
        while True:
            ch = int(input("1. 创建账户\n2. 登录账户\n请输入选项："))
            if ch == 1: 
                self.create()
                break
            elif ch == 2: 
                login_success = self.Log_in()
                if login_success:
                    break
            else:
                print("输入无效，请重新选择")

    # 二级业务菜单（仅登录后可进入）
    def options_2(self): 
        while True:
            ch= int(input("1. 取款\n2. 存款\n3. 查询余额\n4. 退出\n请输入选项："))
            if ch == 1: 
                self.withdraw()
            elif ch == 2: 
                self.deposit()
            elif ch == 3:
                self.display()
            elif ch ==4:
                print("已退出系统")
                self.conn.close()
                break
            else:
                print("输入无效，请重新选择")

    # 创建账户逻辑
    def create(self): 
        user_create = str(input("请输入用户名："))
        pin_create = int(input("请设置6位PIN码：" ))
        try:
            self.cursor.execute("INSERT INTO bank VALUES (?, ?)", (pin_create, user_create))
            self.conn.commit()
            print("账户创建成功！请登录")
            # 创建完成后跳回登录
            self.Log_in()
        except sqlite3.IntegrityError:
            print("该PIN码已被注册，请更换后重试")

    # 登录校验逻辑
    def Log_in(self):
        user = str(input("请输入用户名："))
        pin = int(input("请输入PIN码：")) 
        # 查询数据库匹配用户名和PIN
        self.cursor.execute("SELECT * FROM bank WHERE pin = ? AND username = ?", (pin, user))
        res = self.cursor.fetchone()
        if res:
            self.current_user = user
            print(f"欢迎你，{user}！")
            return True
        else:
            print("用户名或PIN码错误，请重试")
            return False

    # 存款逻辑
    def deposit(self):
        amount=float(input("请输入存款金额："))
        if amount <=0:
            print("存款金额必须大于0")
            return
        self.balance += amount
        print(f"成功存款：{amount}元")
        # 同步更新余额文件
        with open("current_balance.txt", "w") as f:
            f.write(str(self.balance))

    # 取款逻辑
    def withdraw(self):
        amount = float(input("请输入取款金额："))
        if amount <=0:
            print("取款金额必须大于0")
            return
        if self.balance>=amount:
            self.balance-=amount
            print(f"成功取款：{amount}元")
            # 同步更新余额文件
            with open("current_balance.txt", "w") as f:
                f.write(str(self.balance))
        else:
            print("余额不足，取款失败")

    # 余额展示
    def display(self):
        print(f"当前可用余额：{self.balance}元")

if __name__ == "__main__":
    bank_sys = Bank_Account()
    bank_sys.options_1()
    # 只有登录成功才会进入业务菜单
    if bank_sys.current_user:
        bank_sys.options_2()

核心校验逻辑说明 #

• 登录时使用参数化查询SELECT * FROM bank WHERE pin = ? AND username = ?匹配用户输入，避免SQL注入风险
• 查询返回结果为空时，直接提示用户名或PIN错误，不开放业务权限
• 只有校验通过、current_user赋值成功后，才会跳转到存取款业务菜单
• 新增了PIN重复校验、金额合法性校验、余额文件同步更新的逻辑，解决原有代码的运行bug

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