data_garden data_garden - 1 month ago 11
Python Question

sqlite - Inserting many values at once

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?

Answer

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