pythondatabasesqlalchemyflask-sqlalchemyrelationship

SQLAlchemy error creating relationship between tables


I'm trying to make a simple database using SQLAlchemy from two tables: Users and Couple. The Couple table must contain a "couple" of users from the Users table.

For example:

Users:

id user_name
1 user1
2 user2
3 user3
4 user4

Couple:

id first_user_ldap second_user_ldap
1 user1 user2
2 user3 user4

I tried to do this through “relationship” so that there was a one-to-many relationship, but it didn’t work. I ask for your help.

Here is an example of my models:

class Couple(Base):
    __tablename__ = 'couples'

    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    first_user_ldap = relationship('Users', back_populates='Couple',
                                   lazy='dynamic')
    second_user_ldap = relationship('Users', back_populates='Couple',
                                    lazy='dynamic')


class Users(Base):
    __tablename__ = 'users'
    id = Column(String, primary_key=True, unique=True)
    user_name = Column(String)


Solution

  • I solved the problem. one of the likely reasons was “dirty imports”, which... captured models from other files.
    Now:

    from sqlalchemy import create_engine
    from sqlalchemy.orm import Session, declarative_base
    from sqlalchemy import Column, String, ForeignKey, Integer
    from sqlalchemy.engine.url import URL
    

    Models:

    class Users(Base):
        __tablename__ = 'users'
        id = Column(String, primary_key=True, unique=True)
        user_name = Column(String)
    
    
    class Couple(Base):
        __tablename__ = 'couples'
    
        id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
        first_user_ldap = Column(String, ForeignKey('users.id'))
        second_user_ldap = Column(String, ForeignKey('users.id'))
    

    DataSet:

        db.insert(Users(id=1, user_name='user1'))
        db.insert(Users(id=2, user_name='user2'))
        db.insert(Users(id=3, user_name='user3'))
        db.insert(Users(id=4, user_name='user4'))
        db.insert(Users(id=5, user_name='user5'))
        db.insert(Users(id=6, user_name='user6'))
        db.insert(Couple(first_user_ldap=1, second_user_ldap=2))
        db.insert(Couple(first_user_ldap=3, second_user_ldap=4))
        db.insert(Couple(first_user_ldap=5, second_user_ldap=6))