Sqlalchemy Foreign key relationship error while creating tables

Question

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."

Answer 1

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')