python - 如何在说明已插入的关系行的同时向与另一个表有关系的现有表插入新行

问题描述

考虑有两个表叫做Parent，Child最重要的是它们之间有关系。每个表中已经存在数据。

如何将数据自动插入到每个表中，并且不复制另一个表中的行，该行说明与 Sqlalchemy 中其他表的关系。例如，我将Father行插入到Parent表中并说明了它与表中的Child1行的关系Child，但是一旦我尝试插入Mother到Parent表中并且母行也与 other 中的同一子行有关系Child1，Session就会Child1重复。

您可以使用此示例代码

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import Session
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite+pysqlite:///:memory:", 
                       echo=True)

Session = scoped_session(sessionmaker(autocommit=False,
                                        autoflush=False,
                                        bind=engine))

# declarative base class
Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    surname = Column(String)
    age = Column(Integer)
    sex = Column(String)
    nationality = Column(Integer)

    children = relationship("Child", back_populates="parent")
    
    def __repr__(self):
        return f"id: {self.id} name: {self.name} surname: {self.surname} age: {self.age} sex: {self.sex} nationality: {self.nationality}"

# child class

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    surname = Column(String)
    age = Column(Integer)
    sex = Column(String)
    nationality = Column(Integer)

    parent_id = Column(Integer, ForeignKey('parent.id'))

    parent = relationship("Parent", back_populates="children")
    def __repr__(self):
        return f"id: {self.id} name: {self.name} surname: {self.surname} age: {self.age} sex: {self.sex} nationality: {self.nationality} parent_id: {self.parent_id}"



# create all configuration
Base.metadata.create_all(engine)

child1 = Child(name = "child1_name",
               surname= "child1_name",
               age= 3,
               sex = "female",
               nationality = "solar_system")


parent1 = Parent(name = "father_name",
                surname = "father_surname",
                age = 275,
                sex = "male",
                nationality = "Martian",
                child = child1)

parent2 = Parent(name = "mother_name",
                surname = "mother_surname",
                age = 75,
                sex = "female",
                nationality = "Swiss",
                child = child1)


def insert_multi_data_to_db(data_collection:list):
    for data in data_collection:
        session.add(data)

        session.flush()
        session.commit()
    
        session.close()

# insert_multi_data_to_db(data_collection=[child1])

编辑：我向大家表示诚挚的歉意。上面的代码示例有一些表，它们的关系没有正确配置。我已经更新了它们。

标签： pythonpostgresqlsqlalchemyflask-sqlalchemy

python - 如何在说明已插入的关系行的同时向与另一个表有关系的现有表插入新行

问题描述

解决方案

推荐阅读