sqlite - Inserting many values at once

Question

I have data structured as a dictionary, and I want to use it to feed a database:

playlists={
'user1':{'Karma Police':3.0,'Roxanne':4.0,'Sonnet':5.0,'We Will Rock You':1.0,'Song 1': 1.0},
'user2':{'Karma Police':2.0,'Roxanne':3.0,'Sonnet':2.0,'We Will Rock You':3.0,'Song 2': 1.0},
'user3':{'Karma Police':8.0,'Roxanne':1.0,'Sonnet':6.0,'We Will Rock You':4.0,'Song 3': 1.0},
'user4':{'Karma Police':5.0,'Roxanne':2.0,'Sonnet':1.0,'We Will Rock You':6.0,'Song 4': 1.0},
'user5':{'Karma Police':9.0,'Roxanne':4.0,'Sonnet':7.0,'We Will Rock You':9.0,'Song 4': 1.0}}

then I organize the data into separate lists:

users = [k for k in playlists.keys()]
tracks = [track for track in playlists.values()]//won't need this
track_names = [t.keys() for t in tracks]
counts = [t.values() for t in tracks]

and create the database:

db = sqlite3.connect(':memory:')    
db = sqlite3.connect('users/playlistsdb')    
c = db.cursor()

c.execute('''
    CREATE TABLE playlists(
    id text primary key, 
    user TEXT,
    track_names TEXT,  
    count INTEGER)
''')

using the logic columns = ['user', 'track_names', 'counts'],

how do I insert all values into playlistsdb, all at once?

Not an answer for the question, but you can make your lists simpler with `users = list(playlists.keys())` and `tracks = list(playlists.values())` — viraptor, Nov 05 '16 at 00:37
I'm not exactly sure how those lists would map to your database table, but once you have a list of tuples that represent your database rows, you can use the [executemany](https://docs.python.org/3.7/library/sqlite3.html) method. — Wander Nauta, Nov 05 '16 at 00:39
The query should look like `INSERT INTO playlists (id, user, track_names, count) VALUES (%s, %s, %d), (%s, %s, %d), ...`. So make a query string that has as many values lists as `len(users)`, and then build a list of all the values fromthe dictionary — Barmar, Nov 05 '16 at 00:47
Possible duplicate of [How to use python mysqldb to insert many rows at once](http://stackoverflow.com/questions/14011160/how-to-use-python-mysqldb-to-insert-many-rows-at-once) — JazZ, Nov 05 '16 at 00:49

score 3 · Accepted Answer · answered Nov 05 '16 at 01:11

Simply use a nested list comprehension to build list of tuples. However, one crucial item related to your primary key, change the type to INTEGER since TEXT cannot be used to for autoincrement ids:

playlists={'user1':{'Karma Police':3.0,'Roxanne':4.0,'Sonnet':5.0,
                    'We Will Rock You':1.0,'Song 1': 1.0},
           'user2':{'Karma Police':2.0,'Roxanne':3.0,'Sonnet':2.0,
                    'We Will Rock You':3.0,'Song 2': 1.0},
           'user3':{'Karma Police':8.0,'Roxanne':1.0,'Sonnet':6.0,
                    'We Will Rock You':4.0,'Song 3': 1.0},
           'user4':{'Karma Police':5.0,'Roxanne':2.0,'Sonnet':1.0,
                    'We Will Rock You':6.0,'Song 4': 1.0},
           'user5':{'Karma Police':9.0,'Roxanne':4.0,'Sonnet':7.0,
                    'We Will Rock You':9.0,'Song 4': 1.0}}

sqltuples = [(k1, k2, v2) for k1, v1 in playlists.items() for k2, v2 in v1.items()]

c = db.cursor()

c.execute('''
    CREATE TABLE playlists(
    id INTEGER PRIMARY KEY,
    user TEXT,
    track_names TEXT,  
    count INTEGER)
''')
db.commit()

c.executemany('INSERT INTO playlists (user, track_names, count) VALUES (?,?,?)', sqltuples)
db.commit()

Output

c.execute("SELECT * FROM playlists;")
for row in c.fetchall():
    print(row)

# (1, 'user1', 'Sonnet', 5)
# (2, 'user1', 'Roxanne', 4)
# (3, 'user1', 'Karma Police', 3)
# (4, 'user1', 'Song 1', 1)
# (5, 'user1', 'We Will Rock You', 1)
# (6, 'user5', 'Sonnet', 7)
# (7, 'user5', 'Song 4', 1)
# (8, 'user5', 'Roxanne', 4)
# (9, 'user5', 'Karma Police', 9)
# (10, 'user5', 'We Will Rock You', 9)
# (11, 'user4', 'Sonnet', 1)
# (12, 'user4', 'Song 4', 1)
# (13, 'user4', 'Roxanne', 2)
# (14, 'user4', 'Karma Police', 5)
# (15, 'user4', 'We Will Rock You', 6)
# (16, 'user2', 'Sonnet', 2)
# (17, 'user2', 'Song 2', 1)
# (18, 'user2', 'Roxanne', 3)
# (19, 'user2', 'Karma Police', 2)
# (20, 'user2', 'We Will Rock You', 3)
# (21, 'user3', 'Sonnet', 6)
# (22, 'user3', 'Roxanne', 1)
# (23, 'user3', 'Karma Police', 8)
# (24, 'user3', 'Song 3', 1)
# (25, 'user3', 'We Will Rock You', 4)

sqlite - Inserting many values at once

1 Answers1