Updating table using sqlite3 and python

Question

I am trying to update a table titled 'stats' using sqlite3 and python. The table has the structure [username, correct, total]. This is the code I'm using:

        conn = sqlite3.connect(stats_db)  # connect to that database (will create if it doesn't already exist)
        c = conn.cursor()  # make cursor into database (allows us to execute commands)
        outs = ""

        try:
            c.execute('''CREATE TABLE stats (username text,correct int, total int);''') # run a CREATE TABLE command
            outs = "database constructed"

        except:
            things = c.execute('''SELECT * FROM stats;''').fetchall()
            output_list = []
            for x in things:
                output_list.append(x)
            new_correct = output_list[0][1] + int(correct)
            new_total = output_list[0][2] + 1
            c.execute('''UPDATE stats SET correct = (?) and total = (?) where username = (?);''',(new_correct, new_total, username))

            things = c.execute('''SELECT * FROM stats;''').fetchall() #
            outs = "Things:\n"
            output_list = []
            for x in things:
                output_list.append(x)
            outs += str(output_list)
            conn.commit() # commit commands
            conn.close() # close connection to database
        return outs

However, the values in the table never get updated. new_correct and new_total are returning correctly. Also, is there a way to update without first calling the previous values? I just need to increment total by 1 and correct by either 0 or 1 depending on what the input of correct is?

taurus · Accepted Answer · 2018-03-10 20:53:39Z

1

Resolved, I had to change the and in my update command to a comma. Now it reads:

c.execute('''UPDATE stats SET correct = ?, total = ? where username = ?;''',(new_correct, new_total, username))

answered Mar 10, 2018 at 20:53

taurus

5108 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Updating table using sqlite3 and python

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