Using an array of values to store into the database

Question

My goal: Storing the array of scores into the table.

Why an array? The array is used because I gathered the scores using pygame and saved each score that was earned each attempt in the array until 10 attempts were reached and passed the array to here.

I am expecting to get these scores in the array that was stored from the game and store it into the database. As you can see, the columns score1 to score10 and = ? as I don't know what to put in the question mark.

I tried inserting it into the database using INSERT INTO but I can't since I need to specify what ID I need it in. I am left with using UPDATE instead.

Is there a way I can use this array of scores to store it into the database?

Note: I can't just write the scores the player earned into the '?' because scores will vary. The SELECT is just there to see if it worked.

Many thanks.

import sqlite3
conn = sqlite3.connect(':memory:')
conn.cursor()
score = [100, 20, 1000, 1002, 129, 1039, 400, 30, 300, 30]
 
conn.execute("""UPDATE Defender
 SET score1=?, score2=?,
 score3=?, score4=?,
 score5=?, score6=?,
 score7=?, score8=?,
 score9=?, score10=?
 WHERE defender_id=1""")
conn.commit()
conn.execute("SELECT score1 FROM Defender WHERE defender_id=1")
print(conn.fetchall())
conn.close()

Answer 1

In general the execute() function can take two arguments: The query itself and the data (if needed) as a tuple:

conn.execute(query, data)

So you could say:

query = """UPDATE Defender
 SET score1=?, score2=?,
 score3=?, score4=?,
 score5=?, score6=?,
 score7=?, score8=?,
 score9=?, score10=?
 WHERE defender_id=1""" #your query
score = [100, 20, 1000, 1002, 129, 1039, 400, 30, 300, 30] #your data
conn.execute(query, tuple(score))