批量插入带有外键字段的表 [英] Bulk inserting on table with foreign key field

问题描述

给定以下模型，

class Server(BaseModel):
    name = peewee.CharField(unique=True)

class Member(BaseModel):
    name = peewee.CharField(unique=True)
    server = peewee.ForeignKeyField(Server, related_name='members')

和一个字典，键是 Server 名称，值是 Member 名称的元组，

and a dictionary with keys being Server names and values being tuples of Member names,

data = {
    'Server01': ('Laurence', 'Rose'),
    'Server02': ('Rose', 'Chris'),
    'Server03': ('Isaac',)
}

使用 peewee 批量插入 Member 的最快方法是什么?似乎应该使用 Model.insert_many() 此处，但由于 Member.server 需要 Server 或 Server.id，因此需要迭代通过 data.items() 并为每个名称选择一个 Server.

what is the fastest way of bulk inserting Members using peewee? It seems like one should use Model.insert_many() here, but since Member.server expects a Server or Server.id, that would require iterating over data.items() and selecting a Server for each name.

for server_name, member_names in data.items():
    server = Server.select().where(Server.name == server_name)
    member_data = []
    for name in member_names:
        member_data.append({'name': name, 'server': server})

    with db.atomic():
        Member.insert_many(member_data)

不用说，这是非常低效的.有没有更好的方法?

Needless to say, this is terribly inefficient. Is there a better way of doing it?

推荐答案

好吧，如果您事先不知道数据库中存在哪些服务器，那么您的问题似乎是您正在使用的数据结构.将 server_name -> member_names 保存在这样的字典中并尝试一次性插入所有内容并不是关系数据库的工作方式.

Well, if you don't know ahead of time which servers are present in the DB it seems like your problem is the data-structure you're using. Keeping server_name -> member_names in a dict like that and trying to insert it all in one go is not how relational databases work.

试试这个:

server_to_id = {}
for server_name in data:
    if server_name not in server_to_id:
        server = Server.create(name=server_name)
        server_to_id[server_name] = server.id

for server_name, member_names in data.items():
    server_id = server_to_id[server_name]
    member_data = [{'name': name, 'server': server_id} for name in member_names]
    Member.insert_many(member_data).execute()

注意:不要忘记在使用 insert() 或 insert_many() 时调用 .execute().

Note: don't forget to call .execute() when using insert() or insert_many().

这篇关于批量插入带有外键字段的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！