0

I have the schema definition for postgres:

from sqlalchemy.orm import declarative_base
from services.db import DB
from sqlalchemy import Column, String, text, DateTime, ForeignKey
from sqlalchemy.orm import relationship
import uuid
Base = declarative_base()


class UserStatus(Base):
    __tablename__ = 'user_statuses'
    __table_args__ = {'schema': 'public'}  # Specify the schema name

    code = Column(String, primary_key=True)
    label = Column(String, nullable=False)
    users = relationship('User')


class User(Base):
    __tablename__ = 'users'
    __table_args__ = {'schema': 'public'}  # Specify the schema name

    id = Column(
        String,
        primary_key=True,
        default=uuid.uuid4()
    )
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)
    email = Column(String, nullable=False)
    password = Column(String, nullable=False)
    reset_token = Column(String, nullable=True)
    status_code = Column(String, ForeignKey(
        "user_statuses.code"))
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime, nullable=True)

The problem is I keep getting error that the relation like this:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'users.status_code' could not find table 'user_statuses' with which to generate a foreign key to target column 'code'

i am using alembic for migration autogeneration and even if i create the tables from db and try to insert i get failure for the relationship.

What am I doing wrong here?

the code can be found here: https://github.com/rode093/fastapi-strawberry-graphql-template

in the branch defining-db-relationship

Jemshit
  • 9,501
  • 5
  • 69
  • 106
Rode093
  • 396
  • 2
  • 11
  • 1
    if all your tables are in public schema, you need something like this `Base = declarative_base(metadata=MetaData(schema='public'))` `MetaData` is `from sqlalchemy import MetaData` – python_user Jul 10 '23 at 10:05
  • 1
    Thanks a lot mate. It helpled. I spend whole sunday on this shit and it was that stupid. – Rode093 Jul 10 '23 at 10:39
  • 1
    glad to have helped, also FYI, that change will make sure all tables and sequences (and possibly other objects I am not aware of) are created in public schema, if that is not what you want, in your case you could just do `ForeignKey("public.user_statuses.code")` instead, that could also work for you – python_user Jul 10 '23 at 11:21
  • I am new in SQLAlchemy .. I have to say it is not easy to learn. ForeignKey("public.user_statuses.code") i tried this, and did not work. I am yet to learn more to gain more clarity – Rode093 Jul 10 '23 at 13:13
  • that should have worked, not sure where I went wrong, if you are ok with everything being in public, you can use my earlier suggestion – python_user Jul 10 '23 at 13:16

0 Answers0