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：

^{pr2}$

并创建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不能用于自动增量标识：

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

输出

^{pr2}$