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); """)

Isn't all that stuff supposed to be done inside the with-block? — khelwood
– khelwood, Commented Jun 8, 2021 at 14:43
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. — raviadhikari418
– raviadhikari418, Commented Jun 8, 2021 at 14:46
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? — Qianghao Ken Wu
– Qianghao Ken Wu, Commented Jun 8, 2021 at 16:37

Gavin Wong · Accepted Answer · 2021-06-08 14:46:49Z

3

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()

answered Jun 8, 2021 at 14:46

Gavin Wong

1,2701 gold badge9 silver badges17 bronze badges

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

1 Comment

Sergey Ilyin Jan 16 at 13:28

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

Collectives™ on Stack Overflow

How to insert value into a SQLite3 database in Python?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related