How do I use executemany to write the first values in each Key to a database

Question

I am new to Python and am trying to use a for loop to add the first and subsequent values, sequentially from a list within a dictionary to a database by using the cursor.executemany() function.

here is my code,

dict = {'yo': [1,2,3,4,5,6,7],'meh':[1,2,3,4,5,6,7],'blah':[1,2,3,4,5,6,7]}

conn = sqlite3.connect('tester.db')
cur = conn.cursor()

cur.executescript('''
DROP TABLE IF EXISTS;

CREATE TABLE Numbers (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    yo  INTEGER,
    meh INTEGER,
    blah    INTEGER
);

''')

for key,val in dict.items():
    i = 0

    cur.executemany('''INSERT OR IGNORE INTO Number_game (yo,meh,blah)
    VALUES (?,?,?)''', (val(i))
    i = i+1                     
    conn.commit()

I keep getting a syntax error on the i = i+1 iteration. I am trying to add all the 1's to the first row, then the 2's and so on. Can someone tell me what I am doing wrong? it feels like a glaring mistake im unable to pick up.

thanks in advance.

You're missing a parenthesis. – MoxieBall Jun 20 '18 at 20:23 — MoxieBall, Jun 20 '18 at 20:23

score 1 · Answer 1 · answered Jun 20 '18 at 20:26

executemany wants a list of tuples each one containing one row, that is, one element from yo, one from meh, and one from blah. That is not what .items() provide, since it will provide a key and the values associated with that key on each iteration.

Luckly the zip() function does exactly what you need:

cur.executemany('...insert...', zip(my_dict['yo'], my_dict['meh'], my_dict['blah']))

How do I use executemany to write the first values in each Key to a database

1 Answers1

Linked