How to insert value into a SQLite3 database in Python?

Question

I have the following function which inserts two values into my database, however, no matter how many times I try, the data is just not inserted. newname and newgrade are also successfully fetched in this function, just not passed to the database.

def addtolist():
 with sqlite3.connect("TestScore.db") as db:
 cursor = db.cursor()
 newname = sname.get()
 newgrade = sgrade.get()
 cursor.execute("""INSERT INTO Scores(name,score) VALUES (?,?)""", (newname, newgrade))
 db.commit
 sname.delete(0, END)
 sgrade.delete(0, END)
 sname.focus()

And I created the database like following

cursor.execute(""" CREATE TABLE IF NOT EXISTS Scores (id integer PRIMARY KEY, name text, score integer); """)

• khelwood: Isn't all that stuff supposed to be done inside the with-block?
• juanpa.arrivillaga: Yes, and also, you aren't actually calling .commit
• raviadhikari418: The problem here is the cursor exists between the with indentation. You could push everything inside the with block, or maintain an initial connection and finally close with .close() function.
• Qianghao Ken Wu: Thank you so much! I didn't know that they need to be indented, can you please just explain why they need to be indented?

Answer 1

You need to indent all your code to be inside the with block, and call the commit() function with parenthesis. Also, you should probably close the cursor once you're done inserting values.

Try the code below to see if it works:

def addtolist():
 with sqlite3.connect("TestScore.db") as db:
 cursor = db.cursor()
 newname = sname.get()
 newgrade = sgrade.get()
 cursor.execute("""INSERT INTO Scores(name,score) VALUES (?,?)""", (newname, newgrade))
 db.commit()
 sname.delete(0, END)
 sgrade.delete(0, END)
 sname.focus()
 cursor.close()

• Sergey Ilyin: i think you may remove cursor.close() from your code because with... - its context manager