Sqlite3 not inserting values into the database

Question

I have made a program allows a user to manipulate a database, I have a part where the user can add multiple entries into a certain database (anywhere between 1 to 10 entries) I have created another program (below) that I use to "reset" the database, using similar principles to what my main program does.

import sqlite3, time
schoolDBConn = sqlite3.connect("SCHOOL_DB.db")
schoolDBCursor = schoolDBConn.cursor()

schoolDBCursor.execute(
                            """
                               CREATE TABLE IF NOT EXISTS USER_DETAILS 
                               (
                               username text,
                               password text,
                               clearance int,
                               classes int
                               )
                            """
                            )

schoolDBCursor.execute("SELECT * FROM USER_DETAILS")
print(schoolDBCursor.fetchall())

if input("Delete all user_details?").upper() == "Y":
    schoolDBCursor.execute("DELETE FROM USER_DETAILS")
if input("add items to user_details?").upper() == "Y":
    user_list = [("foo","bar",3,0),("bar","foo",3,0),("Terri","Pass",2,0),
                 ("Chris","Pass",2,0),("Tony","Pass",2,0),("Emma","Pass",2,0),
                 ("Thomas","Pass",1,0),("Penny","Pass",1,0),("Kamryn","Pass",1,0),
                 ("Kelsie","Pass",1,0),("James","Pass",1,0),("Connor","Pass",1,0),
                 ("Steve","Pass",1,0),("Bob","Pass",2,0),("Elon","Pass",1,0)]
    for i in user_list:
        schoolDBCursor.execute("""INSERT INTO USER_DETAILS VALUES (?,?,?,?)""",
                               (i[0],i[1],i[2],i[3]))

schoolDBCursor.execute("SELECT * FROM USER_DETAILS")
print(schoolDBCursor.fetchall())

If I run this function, all the items in the existing database are printed, then if I choose to only delete what exists in the database, the print function returns nothing. If I choose to delete and then "add" the items to the database, the print function returns the items I added

However, when I re-run the program, the first print statement returns the original items in the database (not the deleted table, or the items in the users_list) and this is of course not helpful.

My main program is affected by this too, and it causes problems as I need data to be updated quickly. I also use an application "SQlite manager" to view my tables, and they do not update in that either

James Green · Accepted Answer · 2018-11-07 20:39:56Z

1

Turns out I did not commit the data, I had to do:

schoolDBConn.commit()

or

with schoolDBConn:

answered Nov 7, 2018 at 20:39

James Green

12513 bronze badges

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

Collectives™ on Stack Overflow

Sqlite3 not inserting values into the database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related