Help With this code

Question

I tried writing a code that can create an account, make a withdrawal and also deposit but I think I am having issues putting placeholders inside the MySQL codes, so I am hoping someone can tell me what I am doing wrong

https://code.sololearn.com/cnW4Zd2gWE4u/?ref=app

Answer 1

Deposit:
sql = 'UPDATE banking SET Account_balance = Account_balance + ? WHERE Account_number = ?',(amount, Account_number)

The question mark notation should probably work the same as the %s style, try to stick with one of them...

Answer 2

When you insert, you should also enter the account balance (0) because it is defined as NOT NULL.
When you update, you used two ? placeholders but put only one variable in the parentheses after. You should put the amount and the account number as parameter, and in the SET clause you need to add or subtract the amount from/to the account balance field of the table.

Generally in python we follow the naming conventions that
- variable names are all lowercase
- OOP class names begin with capital letters then lowercase
- constants are full uppercase.
It doesn't impact the result but it makes your code more readable to others.

Answer 3

I noticed another problem that you define your cursor in the beginning
a = mydb.cursor()

But then you reassign the same variable name to a number inside the create function
a = 2000000000

So then you cannot refer back to the cursor when you want to execute the query.
Be consistent with your variable names :)

Answer 4

Tibor Santa it runs without error but I had to use %s, "?" Brings out error (not all arguments converted during string formatting).

Now one little issue

The code doesn't make changes to the database tho it runs without error

Answer 5

Well autocommit = true solved that... thank you very much, I'll write the code again , so it would be readable.

Answer 6

You can also issue manual commit against your database connection.. Like:
mydb.commit()

Answer 7

Tibor Santa sorry the code is a bit messed up...bout the reassigning of var "a", the var wasn't there , I added it coz I wanted the account number to have 10 digits and also wanted it to start with "2", didn't remember I have assigned it already.

I have pieces the code, while taking the functions one by one

For check_balance

Def check_balance():
acc_num = input("Enter your account number")
sql = ('SELECT Account_Balance FROM banking WHERE Account_Number LIKE %s',[acc_num])
af = a.execute(*sql)
data = a.fetchone()
return int (data[0]) # why I did that, it was return the result as a tuple.
""" That prints the account balance in the database, that works okay"""

Now the problem, the deposit function

Def Deposit ():
amount = input("Enter the amount you wish to deposit")
new_bal = str(check_balance() + int(amount))
sql = (UPDATE banking SET Account_Balance LIKE %s WHERE Account_Number LIKE %s',[new_bal,acc_num])
ad = a.execute(*sql)

I am getting a programming error

Answer 8

Right syntax to use near 'LIKE'... thought about inserting instead of updating, still couldn't make it work, the more I change it the more it gets complicated.

Just don't understand how to place variables inside the MySQL codes...if I can get the deposit function to work, would be able to do the withdrawal function coz it's similar