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

Jonas · Accepted Answer · 2021-02-13 10:49:11Z

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

answered Feb 13, 2021 at 10:49

Jonas

1,7691 gold badge12 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Using an array of values to store into the database

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related