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