pythonsqlitebanking

sqlite withdraw/deposit function in python


The last stretch of my banking project I've been working on is the deposit/withdraw function. I've gotten most of the other bits working (outside of cleaning up the code) I'm not fully understanding how one adds and subtracts a number from a sql statement inside of python...so here's the part of the code I'm struggling with: here are my tables:

sqlite_file  = 'banking2_db.sqlite'
table_1      = 'Bartertown'
table_2      = 'PINs'
id_column    = 'Card_Numbers'
column_1     = 'Character_PINs'
column_2     = 'Balances'
column_3     = 'Card_Numbers'
column_4     = 'Characters'

class LoginPrompt: def Login(self): while True: print(menu[1]) self.Card_number=str(input('>> ')) print(menu[2])

        while True:
            try:
                self.Character_PINs = getpass.getpass('>>  ')
                self.one_row = c.execute('SELECT * FROM {tn} WHERE {cn}=? and {cnn}=?'.\
                            format(tn=table_1, cn=column_1, cnn=column_3), (self.Character_PINs, self.Card_number,))
                for row in self.one_row.fetchone():
                    print('Welcome: ', row)
                    input('Press any key to continue... ')
                    return
            except:
                    print('PIN incorrect; try again')
                    break 
            #MINOR ISSUE, ONLY QUERIES CHAR COLUMN
def loginMenu(self):
    while True:
        print(menu[5])
        print("\n1 - Deposit funds")
        print("2 - Withdraw funds")
        print("3 - Check balance")
        print("4 - Reset Pin")
        print("5 - Exit")            

        while True:
            try:
                choice = int(input("Please enter a number: "))
            except ValueError:
                print("Please choose a valid entry")
            if choice >= 1 and choice <=5:              
                choice == 1:
                    amount = input("\nPlease enter the deposit amount: ")
                    if amount != '' and amount.isdigit():
                        int(amount)

                        balance = c.execute('UPDATE {tn} SET {cn} = Balances +:amount WHERE Card_Numbers =:self.Card_number' .\
                                      format(tn=table_1, cn=column_2,))
                        new_bal = balance + (int(amount))
                        print('${} has been deposited to account {} and the new balance is ${}'.\
                              format(amount, self.Card_number, balance + (int(amount))))
                        for row in self.Balances.fetchone():
                            print('Your new balance is: ', new_bal)
                            return self.loginMenu()

basically, I'm trying to make sure the program can only pull the balance where the PIN and the Card Number are specified. It selects the balance (This part is working, it is another option in the menu) however, the UPDATE function is still a mystery to me. I understand how to update both the ENTIRE column...on accident, and also how to change the value presented in the Balance field to the value the user submitted ie: the user selects to deposit 100 caps, and then their balance becomes 100 caps. The column I'm trying to update is called Balances, Card_Numbers is the column containing the users "credit card" and amount is the value the user just entered. Thank you for your help.

edit: added tables, and initial input of the data.


Solution

  • If you want to update the column Balances then your statement should be:

    ...SET Balances = Balances + :amount...
    

    so do it like this:

    c.execute("UPDATE " + table_1 + " SET Balances = Balances + ? WHERE Card_Numbers = ?", (amount, self.Card_number,))