Skip to content

Relationship Attributes don't work to define relation between (new) Hero and (new) Team #373

Closed
Closed
Relationship Attributes don't work to define relation between (new) Hero and (new) Team#373
Labels
questionFurther information is requested

Description

@Dopetob1
Dopetob1
opened on Jul 2, 2022

First Check

  • I added a very descriptive title to this issue.
  • I used the GitHub search to find a similar issue and didn't find it.
  • I searched the SQLModel documentation, with the integrated search.
  • I already searched in Google "How to X in SQLModel" and didn't find any information.
  • I already read and followed all the tutorial in the docs and didn't find an answer.
  • I already checked if it is not related to SQLModel but to Pydantic.
  • I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

  • I commit to help with one of those options 👆

Example Code

from typing import List, Optional

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[Team] = Relationship(back_populates="heroes")


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")

        hero_deadpond = Hero(
            name="Deadpond", secret_name="Dive Wilson", team=team_z_force
        )
        hero_rusty_man = Hero(
            name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers
        )
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Created hero:", hero_deadpond)
        print("Created hero:", hero_rusty_man)
        print("Created hero:", hero_spider_boy)

        hero_spider_boy.team = team_preventers
        session.add(hero_spider_boy)
        session.commit()


def main():
    create_db_and_tables()
    create_heroes()


if __name__ == "__main__":
    main()

Description

Follow the tutorial chapter: https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/create-and-update-relationships/#create-instances-with-relationship-attributes

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9.0

Additional Context

It is not possible to assign a new hero to a new team with relationship attributes (see https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/create-and-update-relationships/#create-instances-with-relationship-attributes). The provided code (https://github.com/tiangolo/sqlmodel/blob/main/docs_src/tutorial/relationship_attributes/define_relationship_attributes/tutorial001.py) does not work as expected.

Activity

  1. Dopetob1
    added
    questionFurther information is requested
    on Jul 2, 2022

  2. RobertRosca commented on Jul 2, 2022

    @RobertRosca
    RobertRosca
    on Jul 2, 2022
    Contributor

    This is probably related to #371, can you try downgrading the version of SQLAlchemy to 1.4.35 and see if that solves the issue?

  3. Dopetob1 commented on Jul 2, 2022

    @Dopetob1
    Dopetob1
    on Jul 2, 2022
    Author

    This is probably related to #371, can you try downgrading the version of SQLAlchemy to 1.4.35 and see if that solves the issue?

    In my case I am using SQLAlchemy 1.4.32.
    Upgrading to 1.4.32 doesnt help.

    edit:
    Even going back to SQLAlchemy 1.4.29 (the latest version when tutorial001.py has been committed) doesn't help here.

  4. RobertRosca commented on Jul 3, 2022

    @RobertRosca
    RobertRosca
    on Jul 3, 2022
    Contributor

    That is odd, for me it works on SQLAlchemy 1.4.35, as well as 1.4.32 and 1.4.29.

    Could you run python3 -m pip freeze and reply with the output? Also, delete any existing database.db files, execute the tutorial file with python3 -Werror ./tutorial001.py two times in a row, and include the output in your reply?

    For reference here is the output I get:

    `python3 -m pip freeze` 
    greenlet==1.1.2
pydantic==1.9.1
SQLAlchemy==1.4.35
sqlalchemy2-stubs==0.0.2a24
sqlmodel==0.0.6
typing_extensions==4.3.0
    First run 
    2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("team")
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("team")
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("hero")
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("hero")
2022-07-03 19:10:33,008 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-03 19:10:33,009 INFO sqlalchemy.engine.Engine
CREATE TABLE team (
	id INTEGER,
	name VARCHAR NOT NULL,
	headquarters VARCHAR NOT NULL,
	PRIMARY KEY (id)
)


2022-07-03 19:10:33,009 INFO sqlalchemy.engine.Engine [no key 0.00005s] ()
2022-07-03 19:10:33,018 INFO sqlalchemy.engine.Engine CREATE INDEX ix_team_name ON team (name)
2022-07-03 19:10:33,018 INFO sqlalchemy.engine.Engine [no key 0.00010s] ()
2022-07-03 19:10:33,022 INFO sqlalchemy.engine.Engine
CREATE TABLE hero (
	id INTEGER,
	name VARCHAR NOT NULL,
	secret_name VARCHAR NOT NULL,
	age INTEGER,
	team_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(team_id) REFERENCES team (id)
)


2022-07-03 19:10:33,022 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2022-07-03 19:10:33,025 INFO sqlalchemy.engine.Engine CREATE INDEX ix_hero_age ON hero (age)
2022-07-03 19:10:33,025 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2022-07-03 19:10:33,028 INFO sqlalchemy.engine.Engine CREATE INDEX ix_hero_name ON hero (name)
2022-07-03 19:10:33,028 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2022-07-03 19:10:33,030 INFO sqlalchemy.engine.Engine COMMIT
2022-07-03 19:10:33,034 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-03 19:10:33,035 INFO sqlalchemy.engine.Engine INSERT INTO team (name, headquarters) VALUES (?, ?)
2022-07-03 19:10:33,035 INFO sqlalchemy.engine.Engine [generated in 0.00011s] ('Z-Force', 'Sister Margaret’s Bar')
2022-07-03 19:10:33,035 INFO sqlalchemy.engine.Engine INSERT INTO team (name, headquarters) VALUES (?, ?)
2022-07-03 19:10:33,035 INFO sqlalchemy.engine.Engine [cached since 0.0005196s ago] ('Preventers', 'Sharp Tower')
2022-07-03 19:10:33,036 INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
2022-07-03 19:10:33,036 INFO sqlalchemy.engine.Engine [generated in 0.00011s] ('Deadpond', 'Dive Wilson', None, 1)
2022-07-03 19:10:33,036 INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
2022-07-03 19:10:33,036 INFO sqlalchemy.engine.Engine [cached since 0.0002956s ago] ('Rusty-Man', 'Tommy Sharp', 48, 2)
2022-07-03 19:10:33,036 INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
2022-07-03 19:10:33,036 INFO sqlalchemy.engine.Engine [cached since 0.0003978s ago] ('Spider-Boy', 'Pedro Parqueador', None, None)
2022-07-03 19:10:33,037 INFO sqlalchemy.engine.Engine COMMIT
2022-07-03 19:10:33,039 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-03 19:10:33,040 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
2022-07-03 19:10:33,041 INFO sqlalchemy.engine.Engine [generated in 0.00009s] (1,)
2022-07-03 19:10:33,041 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
2022-07-03 19:10:33,041 INFO sqlalchemy.engine.Engine [cached since 0.0006923s ago] (2,)
2022-07-03 19:10:33,042 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
2022-07-03 19:10:33,042 INFO sqlalchemy.engine.Engine [cached since 0.001124s ago] (3,)
Created hero: secret_name='Dive Wilson' name='Deadpond' team_id=1 id=1 age=None
Created hero: secret_name='Tommy Sharp' name='Rusty-Man' team_id=2 id=2 age=48
Created hero: secret_name='Pedro Parqueador' name='Spider-Boy' team_id=None id=3 age=None
2022-07-03 19:10:33,043 INFO sqlalchemy.engine.Engine SELECT team.id AS team_id, team.name AS team_name, team.headquarters AS team_headquarters
FROM team
WHERE team.id = ?
2022-07-03 19:10:33,043 INFO sqlalchemy.engine.Engine [generated in 0.00009s] (2,)
2022-07-03 19:10:33,043 INFO sqlalchemy.engine.Engine UPDATE hero SET team_id=? WHERE hero.id = ?
2022-07-03 19:10:33,044 INFO sqlalchemy.engine.Engine [generated in 0.00007s] (2, 3)
2022-07-03 19:10:33,044 INFO sqlalchemy.engine.Engine COMMIT
    Second run 
    2022-07-03 19:10:40,277 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-03 19:10:40,277 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("team")
2022-07-03 19:10:40,277 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-03 19:10:40,278 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("hero")
2022-07-03 19:10:40,278 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-03 19:10:40,278 INFO sqlalchemy.engine.Engine COMMIT
2022-07-03 19:10:40,281 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-03 19:10:40,282 INFO sqlalchemy.engine.Engine INSERT INTO team (name, headquarters) VALUES (?, ?)
2022-07-03 19:10:40,282 INFO sqlalchemy.engine.Engine [generated in 0.00010s] ('Z-Force', 'Sister Margaret’s Bar')
2022-07-03 19:10:40,282 INFO sqlalchemy.engine.Engine INSERT INTO team (name, headquarters) VALUES (?, ?)
2022-07-03 19:10:40,282 INFO sqlalchemy.engine.Engine [cached since 0.000503s ago] ('Preventers', 'Sharp Tower')
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine [generated in 0.00008s] ('Deadpond', 'Dive Wilson', None, 3)
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine [cached since 0.00028s ago] ('Rusty-Man', 'Tommy Sharp', 48, 4)
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine [cached since 0.0003812s ago] ('Spider-Boy', 'Pedro Parqueador', None, None)
2022-07-03 19:10:40,283 INFO sqlalchemy.engine.Engine COMMIT
2022-07-03 19:10:40,292 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-03 19:10:40,293 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
2022-07-03 19:10:40,293 INFO sqlalchemy.engine.Engine [generated in 0.00010s] (4,)
2022-07-03 19:10:40,294 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
2022-07-03 19:10:40,294 INFO sqlalchemy.engine.Engine [cached since 0.0007627s ago] (5,)
2022-07-03 19:10:40,294 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
2022-07-03 19:10:40,294 INFO sqlalchemy.engine.Engine [cached since 0.001091s ago] (6,)
Created hero: secret_name='Dive Wilson' name='Deadpond' team_id=3 id=4 age=None
Created hero: secret_name='Tommy Sharp' name='Rusty-Man' team_id=4 id=5 age=48
Created hero: secret_name='Pedro Parqueador' name='Spider-Boy' team_id=None id=6 age=None
2022-07-03 19:10:40,295 INFO sqlalchemy.engine.Engine SELECT team.id AS team_id, team.name AS team_name, team.headquarters AS team_headquarters
FROM team
WHERE team.id = ?
2022-07-03 19:10:40,295 INFO sqlalchemy.engine.Engine [generated in 0.00009s] (4,)
2022-07-03 19:10:40,296 INFO sqlalchemy.engine.Engine UPDATE hero SET team_id=? WHERE hero.id = ?
2022-07-03 19:10:40,296 INFO sqlalchemy.engine.Engine [generated in 0.00007s] (4, 6)
2022-07-03 19:10:40,296 INFO sqlalchemy.engine.Engine COMMIT

  5. Dopetob1 commented on Jul 3, 2022

    @Dopetob1
    Dopetob1
    on Jul 3, 2022
    Author

    That is odd, for me it works on SQLAlchemy 1.4.35, as well as 1.4.32 and 1.4.29.

    Could you run python3 -m pip freeze and reply with the output? Also, delete any existing database.db files, execute the tutorial file with python3 -Werror ./tutorial001.py two times in a row, and include the output in your reply?

    For reference here is the output I get:

    python3 -m pip freeze
    First run
    Second run

    You are absolutely right. I was messing around in the wrong environment. I can confirm that everything works fine with SQLAlchemy==1.4.35.
    Thank's allot for the hint!

  6. Dopetob1
    closed this as completedon Jul 3, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    questionFurther information is requested

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions