CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemysqlalchemy-migrate
Mithu
Mithu

Reputation: 101

Sqlalchemy Foreign key relationship error while creating tables

Im creating tables using sqlalchemy and some of these tables has more than one foreign key relationship.Below is the code. I'm getting error:

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'project' and 'genre'

when try to run the script.

Could anyone explain me how tables are created in sqlalchemy, order of the creation if there are tables with more than one relation.

   import sqlalchemy
   from  sqlalchemy.orm import *
   from sqlalchemy.ext.declarative import declarative_base
   import datetime
   from sqlalchemy import *


   engine =               sqlalchemy.create_engine('postgresql+psycopg2://postgres:newpassword@localhost:5432/test');


   Session = sessionmaker(bind=engine)


   session = Session()

   metadata = MetaData(engine)


   metadata.drop_all(engine)

   Base = declarative_base(metadata=metadata)

   class User(Base):
        __tablename__ = 'users'
        id = Column(Integer,primary_key = True)
        email = Column(String(30),nullable = False)
        password = Column(String(30),nullable = False)

   class Genre(Base):
        __tablename__ = 'genre'
        id = Column(Integer,primary_key = True)
        title = Column(String(30),nullable = False)

   class Status(Base):
        __tablename__ = 'status'
        id = Column(Integer,primary_key = True)
        status = Column(String(30),nullable = False)

   class FilmType(Base):
        __tablename__ = 'filmtype'
        id = Column(Integer,primary_key = True)
        filmtype = Column(String(30),nullable = False)

   class Project(Base):
        __tablename__ = 'project'
        id = Column(Integer,primary_key = True)
        name = Column(String(30),nullable = False)
        poster = Column(Text, nullable = False)
        genre_id = Column(Integer,ForeignKey('genre.id'))
        genre_id = relationship('Genre',backref = 'genres')
        owner = Column(Integer,ForeignKey('users.id'))
        owner = relationship('Users',backref = 'users')


   class Videos(Base):
         __tablename__ = 'videos'
         id = Column(Integer,primary_key = True)
         dropbox_url = Column(Text)
         film_type = Column(Integer,ForeignKey('filmtype.id'))
         film_type = relationship('FilmType',backref = 'film_types')
         video_status = Column(Integer,ForeignKey('status.id'))
         video_status = relationship('Status',backref = 'video_statuses')
         project_id = Column(Integer,ForeignKey('project.id'))
         project_id = relationship('Project',backref = 'projetcs')
         user_id = Column(Integer,ForeignKey('users.id'))
         user_id = relationship('User', backref = 'video_owners')


  metadata.create_all(bind = engine)
  user_ = User(email='lala.com',password='lala')
  genre_ = Genre(title='Horror')
  status_ = Status(status='Active')
  filmtype_ = FilmType(filmtype ='lala')
  project_ = Project(name='lala',poster='mij.jpg')
  videos_ = Videos(dropbox_url='drop/vi.com')
  session.add(videos_)
  session.flush()
  session.commit()

Error

"sqlalchemy.exc.NoForeignKeysError: Could not determine join condition > between parent/child tables on relationship Project.genre_id - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression."

Upvotes: 7

Views: 18765

Answers (2)

Ricky Levi
Ricky Levi

Reputation: 7997

In my case i wrote the table but forgot the column:

owner_id = Column(Integer, ForeignKey('users'))

Instead of

owner_id = Column(Integer, ForeignKey('users.id'))

Upvotes: 1

tuomur
tuomur

Reputation: 7088

You're overwriting the column specification with your relationship objects, so SQLAlchemy never sees the columns. See if your database doesn't have the foreign key columns in tables and rename either the column or relationship class variables to have unique names. Maybe something like this:

owner_id = Column(Integer,ForeignKey('users.id'))
owner = relationship('Users',backref = 'users')

Upvotes: 10

Related Questions