How do I update a SQLite3 database in Python with user input?

Question

I am trying to create a personal budget program in Python and I am having trouble updating my database of current bank balances with input from the user. I store the user inputs (cash balances) in variables but I can't seem to get the input from the variables into the database. I don't get any error messages but I can only store the variable names in the database, rather than the amounts from the user input that the variables represent.

Here is a simplified version of my code:

# import modules
import sqlite3

# user input
current_acc = float(input("Natwest Current A/C: "))
bills_acc = float(input("Natwest Bills A/C: "))

# update database
conn = sqlite3.connect("databases/bank_accs.db")
c = conn.cursor() 
c.execute("""UPDATE bank_accs SET amount = 'current_acc' WHERE rowid = 1"""), 
c.execute("""UPDATE bank_accs SET amount = 'bills_acc' WHERE rowid = 2""")

# query database
c.execute("SELECT rowid, * FROM bank_accs")
items = c.fetchall()
for item in items:
  print(item)

# commit and close
conn.commit()
conn.close()

I have googled this problem extensively but can't seem to find an answer. The only real clue I found was on Stack Overflow where it was suggested that the variable names should be replaced by placeholders '?', however it wasn't clear about how you replace the placeholders with the variable values.

I would really appreciate any help or insight into what I am doing wrong here. I am quite new to coding and this is my first question on here, I hope my question makes sense.

Thanks, Paul

Swagnar · Accepted Answer · 2022-04-15 02:06:18Z

1

as you mentioned you can use "?" sign as a placeholder for variables which would look like this:

sql = "UPDATE bank_accs SET amount = ? WHERE rowid = 1" 
c.execute(sql, (current_acc,))
sql = "UPDATE bank_accs SET amount = ? WHERE rowid = 2"
c.execute(sql, (bills_acc,))

answered Apr 15, 2022 at 2:06

Swagnar

12711 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paulj Over a year ago

Thank you for your answer. I think there is a gap in my understanding here. The solution you provided populates the database with the placeholders, which I understand; how do I then replace the placeholders with the values captured by the input variables?

Swagnar Over a year ago

the 'sql' variable is just a string containing the SQL query with the ? sign as a placeholder for where the variable data will be inserted. When using execute method of the SQL cursor (your named as 'c') you need to pass a SQL query to compute, but when using placeholders you also need to give this method a tuple of parameters that will replace these placeholders. You can read more about it here

Paulj Over a year ago

Thanks for your help Swagnar, that's really helpful. The code now works and I understand the explanation, cheers Paul

Collectives™ on Stack Overflow

How do I update a SQLite3 database in Python with user input?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related