Как правильно создать и добавлять данные в промежуточную таблицу?

Question

[weranda]

Имеется три модели:

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))   


class Role(Base):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True, autoincrement=True)
    role = Column(String(50))


class UserRole(Base):
    __tablename__ = "users_roles"
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), primary_key=True)
    role_id = Column(Integer, ForeignKey("roles.id"), primary_key=True)

У одного пользователя может быть много ролей. Для этого и создал промежуточную таблицу. Не пойму как сформировать корректно модели и запросы так, чтобы:

1. При добавлении пользователя можно было бы назначать ему роль из существующих и добавлять пользователя в таблицу пользователей, а связку пользователь + роль в таблицу users_role.

2. Существующему пользователю можно было бы добавлять или убирать роли из существующих в таблице roles.

Подскажите, пожалуйста, решение.

Answer 1

[Vindicar]

У UserRole должен быть не обычный ключ - автоинкремент, а составной ключ user_id:role_id.
Тогда БД гарантирует, что в таблице UserRole будет не более одной записи для одной пары юзер-роль.
Создание такой записи будет означать, что у юзера есть роль, удаление - что у юзера роли нет.
Тогда и запросы будут тривиальными: для выдачи роли INSERT OR IGNORE, для снятия роли - DELETE FROM.

Единственно что, сначала добавляй пользователя и роль (если её нет), потом уже отношение.

Вообще я посоветую почитать документацию на отношения вида Many-To-Many.

Comments

[weranda]

В том то и дело, что прочитал, но не понял того, как сформировать корректно запросы. Прям совсем. Там показано создание моделей, а вот по добавление данных в эту промежуточную модель информации нет. Уже глаза на лоб лезут...

[Vindicar]

weranda, через back_populates или backref, как же ещё?

association_table = Table(
    "association",  # имя таблицы
    Base.metadata,
    Column("left_id", ForeignKey("left.id"), primary_key=True),  # поле ссылается на ключ Parent
    Column("right_id", ForeignKey("right.id"), primary_key=True),  # поле ссылается на ключ Child
)


class Parent(Base):
    __tablename__ = "left"  # обрати внимание на имя таблицы - сравни с ссылкой в association_table 
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",  # на какую таблицу мы ссылаемся?
        secondary=association_table,  # какую таблицу используем для связи?
        back_populates="parents"  # какое свойство в объектах таблицы на другом конце связи?
    )

class Child(Base):
    __tablename__ = "right"  # обрати внимание на имя таблицы - сравни с ссылкой в association_table 
    id = Column(Integer, primary_key=True)
    parents = relationship(
        "Parent",  # на какую таблицу мы ссылаемся?
        secondary=association_table,  # какую таблицу используем для связи?
        back_populates="children"  # какое свойство в объектах таблицы на другом конце связи?
    )

У родителя в примере будет свойство children, которое будет вести себя как коллекция объектов класса Child. Там и можно добавлять или удалять элементы.

Собственно ниже пример есть

# это удалит строку из связной таблицы
myparent.children.remove(somechild)
# а можно и так
somechild.parents.remove(myparent)

[weranda]

Vindicar,
У меня есть недопонимание процессов добавление, удаления, изменения данных. Приведу несколько примеров того, с чем столкнулся на примерах моделей авторов и их книг. Часть понимаю, остальную — нет. Если не затруднит, разъясните, пожалуйста.

# вот модели
class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    books = relationship("Book", secondary="authors_books", back_populates="authors")
    


class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    book = Column(String(50))
    authors = relationship("Author", secondary="authors_books", back_populates="books")


class AuthorBook(Base):
    __tablename__ = "authors_books"
    author_id = Column(Integer, ForeignKey("authors.id"), primary_key=True, )
    book_id = Column(Integer, ForeignKey("books.id"), primary_key=True,)

# 1. добавить автора
author = Author(
    name = "author 1"
)
session.add(author)
session.commit()

# тут все понятно

# 2. добавить книгу
book = Book(
    book = "book 1"
)
session.add(book)
session.commit()

# тут все понятно

# 3. добавить связь существующему автору с существующей книгой
get_author = session.query(Author).get(1)
get_book = session.query(Book).get(1)
author_book = AuthorBook(
    author_id = get_author.id,
    book_id = get_book.id
)
session.add(author_book)
session.commit()

# тут все понятно

# 4. добавить автора и связать его с существующей книгой
# и вот тут начинаются сложности: надо создать связь между моделью Author & AuthorBook > добавляю связь в модель Author (этот вариант работает, но как-то со крипом, кажется (если есть вариант корректнее, буду рад его увидеть))

class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    books = relationship("Book", secondary="authors_books", back_populates="authors")
    # добавляю связь
    authors_books = relationship("AuthorBook")


author = Author(
    name = "author 2"
)
get_book = session.query(Book).get(1)
author_book = AuthorBook(
    book_id = get_book.id
)
author.authors_books.append(author_book)


session.add(author)
session.commit()

# 5. создать автора, книгу и связать добавить их во все модели — как это надо сделать?

[Vindicar]

weranda, вот просто по аналогии с примером из документации:

author_book_table = Table(
    "AuthorBook",  # имя таблицы
    Base.metadata,
    Column("author", ForeignKey("author.id"), primary_key=True),
    Column("book", ForeignKey("book.id"), primary_key=True),
)


class Author(Base):
    __tablename__ = "author"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    books = relationship(
        "Book",  # на какую таблицу мы ссылаемся?
        secondary=author_book_table,  # какую таблицу используем для связи?
        back_populates="authors"  # какое свойство в объектах таблицы на другом конце связи?
    )

class Book(Base):
    __tablename__ = "book"
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    authors = relationship(
        "Author",  # на какую таблицу мы ссылаемся?
        secondary=author_book_table,  # какую таблицу используем для связи?
        back_populates="books"  # какое свойство в объектах таблицы на другом конце связи?
    )

Ну и при создании я бы действовал как-то так:

author = Author(name="author 2")
book = Book(title="Great Big Book of Everything")
session.add(author)
session.add(book)
book.authors.append(author)
session.commit()

[weranda]

Vindicar, Спасибо за объяснение. Какое-то у меня недопонимание процесса. Иногда не хватает реально простого объяснения, простым языком на элементарных примерах. Решение есть, а понимается плоховато — с чего «оно» добавляет данные в промежуточную таблицу, в три таблицы сразу. В две (author, book) понятно почему, а вот почему добавляет в третью (authors_books)... наверное можно упрощенно представить так: «если идет добавление в обе таблицы, у классов которых в аргументах есть связь с указанием на промежуточную таблицу, то срабатывает определенное условие и эти данные дополнительно заносятся в промежуточную таблицу...»

[Vindicar]

weranda, проще. Book.authors и Author.books - это не "тупые" списки, это коллекции, которые только притворяются списками - relationship. Эти объекты "знают" о связной таблице, которую мы указываем параметром secondary при вызове relationship(). Ну и соответственно, когда в коллекцию добавляется элемент (book.authors.append()), она тут же выполняет SQL запрос вставки в эту промежуточную таблицу, а при удалении элемента (book.authors.remove()) - запрос удаления.

[weranda]

Vindicar, спасибо за дополнительное объяснение.