Create parent child object simultaneously when both has no validation/uniqueness issue using encode/databases wrapper

Question

I have a following model where a parent can have many children. User can just create the parent or create a parent along with the child at the same time.

class Parent(Base):
  name = Column(String)
  slug = Column(String)
  values = relationship('Child', back_populates='parent')

class Child(Base):
  name = Column(String, unique=True)
  slug = Column(String)
  parent_id = Column(Integer, ForeignKey('parent.id'))
  parent = relationship('Parent', back_populates='values')

I need to create a parent but if values is send then create a child as well. The problem here is to create a Child, parent id is needed. If I create a parent and then get the id and while creating a child if there is any error like name uniquess for same parent id then there will be broken parent object. How can I create both parent and child once there is no issue in both table?

I am using encode/database for async database operation so I have no idea of doing such

parent = Parent(name='Parent name')
child = Child(name='Child name', slug='child-name')
session.add_all([parent, child])
session.commit()

when using encode database library https://github.com/encode/databases This is what I am trying

async def check_values_are_unique(db, child_value, parent):
    # get the list of slugs of particular attribute.
    # alternative of django values_list in sqlalchemy
    existing_values = await db.execute(select(parent.values.slug).distinct()).all()
    # some check is done here like Value already exists within this parent


async def create_parent(db, data):
  # data -> Parent(name='Parent name', values=[Child(name='Child name', value='child name')])
  values_input = data.values
  parent_qs = Parent.__table__.select().where(
        Parent.__table__.c.name == data.name
    )
  parent_exists = await db.fetch_one(query=parent_qs)
  if parent_exists:
      return Error(
          code="PARENT_ALREADY_EXIST",
          message=f"Parent with name {data.name} already exist",
      )
  else:
      # create an instance of attribute
      parent_input = data.__dict__
      del parent_input["values"]
      # create an instance 
      parent = Parent.__table__.insert().values(**parent_input)
      for val in values_input:
          setattr(val, "slug", slugify(val.name))
          child_value = Child.__table__.insert().values(
              **val.__dict__, parent=parent
          )
      check_values_are_unique(db, values_input, parent)

UPDATED CODE

async def create_parent(db, data):
  # data -> Parent(name='Parent name', values=[Child(name='Child name', value='child name')])
  values_input = data.values
  parent_qs = Parent.__table__.select().where(
        Parent.__table__.c.name == data.name
    )
  parent_exists = await db.fetch_one(query=parent_qs)
  if parent_exists:
      return Error(
          code="PARENT_ALREADY_EXIST",
          message=f"Parent with name {data.name} already exist",
      )
  else:
      # create an instance of attribute
      parent_input = data.__dict__
      del parent_input["values"]
      # create an instance 
      parent = Parent.__table__.insert().values(**parent_input)
      for val in values_input:
          setattr(val, "slug", slugify(val.name))
          child_value = Child.__table__.insert().values(
              **val.__dict__, parent=parent
          )
      async with db.transaction():
            # this will create a parent instance but wont get save to database
            parent = Parent(**parent_input)
            for val in values_input:
                setattr(val, "slug", slugify(val.name))
                child_value = Child(**val.__dict__, parent=parent)
                # child.values.append(child_value)
            print('now save')
            # NOW SAVE PARENT AND CHILD IN DB
            print("parent", parent.__dict__) # it has values as well
            # parent {'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7f51abda5430>, 'name': 'parent name', 'slug': 'parent-name', 'values': [<app.models.parent.parent.Child object at 0x7f51abda5520>, <app.models.parent.parent.Child object at 0x7f51abda5520>]}
            parent_id = await db.execute(
                Parent.__table__.insert().values(
                    name=parent.name,
                    slug=parent.slug
                )
            )
            print("parent_id", parent_id)
            response_payload = {
                **data.__dict__,
                "id": parent_id,
                "choices": values_input, # no idea to get list of values with id 
            }
            print("response payload", response_payload)

            return ParentPayload(**response_payload)

I could save Parent object but not child and you can see in the comment for print ('parent', parent.__dict__). It has list of values as well.

Answer 1

I don't have the setup to test it right now, but this should give you a guideline of how it should be done.

Here, the database object is the connected instance of the database. The operations that are performed within this context will be part of the same transaction.

else:
      # create an instance of attribute
      parent_input = data.__dict__
      del parent_input["values"]
      async with database.transaction():
          # create parent instance 
          parent = Parent.__table__.insert().values(**parent_input)
          for val in values_input:
              setattr(val, "slug", slugify(val.name))
              child_value = Child.__table__.insert().values(
                  **val.__dict__, parent=parent
              )
      check_values_are_unique(db, values_input, parent)