0

I am coding with python and using SQLite. I need to update a table using the UPDATE and WHERE statements. The challenge is that both values for the WHERE and UPDATE statements must be variables. I have done some research and found this link Sqlite3 Updating Row Defined by a Variable and this https://www.tutorialspoint.com/sqlite/sqlite_update_query.htm

def studentlist():
    '''Checks if a student is checked in any class, inserts the status into the
    student list table Displays a list of all students and their class status'''
    c.execute("SELECT StudentID FROM StudentTable")
    all_students = c.fetchall()
    c.execute("SELECT StudentID FROM CheckedInTable")
    selected_student_id = c.fetchall()
    print("The student id in the studentlist is:", selected_student_id,)
    for i in all_students:
        if i in selected_student_id:
            student_attendance = 'Attending a class'
            c.execute("UPDATE StudentlistTable set ClassAttend = ?", (student_attendance), "WHERE StudentID = ?", (i))
            conn.commit()
        else:
            student_attendance = "Not in class"
            c.execute("UPDATE StudentlistTable set ClassAttend = ?", (student_attendance))
            conn.commit()
studentlist()

Upon running the code, I receive the following error

c.execute("UPDATE StudentlistTable set ClassAttend = ?", (student_attendance), "WHERE StudentID = ?", (i))
TypeError: function takes at most 2 arguments (4 given)

Any help would be highly appreciated.

Community
  • 1
  • 1
Confusion Matrix
  • 116
  • 2
  • 14
  • Can you show the actual UPDATE query which is being run? Off the top of my head, there could be a problem with your data such that the query is not doing what you expect. – Tim Biegeleisen Jan 18 '17 at 04:40
  • The i were empty tuples instead of actual values. Converting every i to an appropriate string variable worked as desired. @ Tim thanks – Confusion Matrix Jan 18 '17 at 06:16
  • This is a Q&A site, not a forum; do not change the question in a way that can invalidate existing answers. Your question has been answered; if you have a new question, ask a new question. – CL. Jan 18 '17 at 08:22

1 Answers1

3

Based on the error message you are receiving, the execute() function is expecting 2 arguments, the first for the query and the second a single tuple containing the parameters to be used in the query. Try this:

c.execute("UPDATE StudentlistTable SET ClassAttend = ? WHERE StudentID = ?",
          (student_attendance, i))

Here is a good reference question which discusses your problem in greater detail:

How to use variables in SQL statement in Python?

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360