第二个表的大容量保存对象SQLAlchemy会创建多余的id

Question

表：

class Person(Base):
__tablename__ = 'person'
id = Column('id', Integer, primary_key=True)
gender = Column(String)
name = relationship("Name", uselist=False, back_populates="person")
location = relationship("Location", uselist=False, back_populates="person")
email = Column(String)
login = relationship("Login", uselist=False, back_populates="person")
dob = relationship("Dob", uselist=False, back_populates="person")
registered = relationship("Registered", uselist=False, back_populates="person")
phone = Column(String)
cell = Column(String)
id_person = relationship("IdPerson", uselist=False, back_populates="person")
nat = Column(String)
def __repr__(self):
return f'(id:{self.id}, gender:{self.name})'

我有一个JSON文件，它包含1000行个人数据(以及双嵌套和三嵌套的"表"(。

我创建了一个函数，从JSON文件中创建一个性别列表。我创建了一个函数，将该列表中的数据插入数据库。

def bulk_save_obj_gender():
index = 0
persons_gender = []
for element in range(count_indexes()):
per = Person(gender=all_genders[index])
persons_gender.append(per)
index += 1
session.bulk_save_objects(persons_gender, return_defaults=True)
for gender in persons_gender:
assert gender.id is not None
session.add_all(persons_gender)
session.commit()
def bulk_save_obj_email():
index = 0
persons_emails = []
for element in range(count_indexes()):
per = Person(email=all_emails[index])
persons_emails.append(per)
index += 1
session.bulk_save_objects(persons_emails, return_defaults=True)
for email in persons_emails:
assert email.id is not None
session.add_all(persons_emails)
session.commit()

我在*.db中看到的是，我有1-1000个性别id(和NULL电子邮件列(和1001-2000个电子邮件id。

我想知道如何创建一个函数，将这些电子邮件插入到从1到2000的现有id中。通过id连接性别和电子邮件。

我不确定我是否能很好地描述它。

数据库的屏幕截图

当我将代码更改为简单的SQL查询时，它运行良好：

def populate_data_using_sqlite_query():
conn = sqlite3.connect('persons.db')
c = conn.cursor()
for i in range(count_indexes()):
gend = all_genders[i]
email = all_emails[i]
params = (i, gend, email)
c.execute("INSERT INTO person (id, gender, email) VALUES (?,?,?)", params)
conn.commit()

populate_data_using_sqlite_query()

但我真的很想用ORM来做。

请帮助：(

Answer 1

您可以这样做，以避免电子邮件中的空列，并确保它们与性别映射。

def bulk_save_persons():
index = 0
persons = []
for element in range(count_indexes()):
person = Person(gender=all_genders[index], email=all_emails[index])
persons.append(person)
index += 1
session.add_all(persons)
session.commit()