sqlite一次插入多个值

Question

我有一个dictionary结构的数据，我想用它来给一个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}}

然后将数据组织到单独的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]

并创建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)
''')

使用逻辑columns = ['user', 'track_names', 'counts']，

如何将所有值同时插入到playlistsdb中？

Answer 1

只需使用嵌套列表理解来构建元组列表。但是，与主键相关的一个关键项，请将类型更改为INTEGER，因为TEXT不能用于自动增量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()

输出

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)