sqlalchemy: как объединить несколько таблиц одним запросом?

ProgramBox

sqlalchemy: как объединить несколько таблиц одним запросом?

Post author:admin
Запись опубликована:26 апреля, 2023
Post category:Вопросы по программированию

#python #sql #join #sqlalchemy

#python #sql #Присоединиться #sqlalchemy

Вопрос:

У меня есть следующие сопоставленные классы SQLAlchemy:

 class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author = Column(String, ForeignKey("users.email"))

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)

    document = Column(String, ForeignKey("documents.name"))

Мне нужно получить такую таблицу для user.email = "user@email.com" :

 email | name | document_name | document_readAllowed | document_writeAllowed

Как это можно сделать, используя один запрос запроса для SQLAlchemy? Приведенный ниже код у меня не работает:

 result = session.query(User, Document, DocumentPermission).filter_by(email = "user@email.com").all()

Спасибо,

1. Я обнаружил, что для объединения двух таблиц работает следующее: result = session.query(User, Document).select_from(join(User, Document)).filter(User.email=='user@email.com').all() Но мне пока не удалось, как сделать аналогичную работу для трех таблиц (включить DocumentPermissions). Есть идеи?

2. Когда я выполняю аналогичную задачу, я получаю SyntaxError: ключевое слово не может быть выражением

Ответ №1:

Попробуйте это

 q = Session.query(
         User, Document, DocumentPermissions,
    ).filter(
         User.email == Document.author,
    ).filter(
         Document.name == DocumentPermissions.document,
    ).filter(
        User.email == 'someemail',
    ).all()

1. Что join это делает? внутренний, внешний, перекрестный или какой?

2. На самом деле это вообще не выполняет объединение, оно возвращает объекты строк в виде кортежа. В этом случае это вернет [(<user>, <document>, <documentpermissions>),...] /

3. «вообще не выполняет объединение» — это немного вводит в заблуждение. Он будет иметь sql like select x from a, b ,c , который является перекрестным соединением. Затем фильтры преобразуют это во внутреннее соединение.

4. Вы можете распечатать запрос, пропустив .all() . Итак, print Session.query.... чтобы точно увидеть, что он делает.

5. Я новичок в SQLAlchemy. Я заметил, что .filter() можно получить несколько критериев, если разделять их запятой. Предпочтительнее использовать одну .filter() с разделением запятой внутри круглых скобок или использовать несколько .filter() , как в приведенном выше ответе?

Ответ №2:

Как сказал @letitbee, лучше всего назначать первичные ключи таблицам и правильно определять связи, чтобы обеспечить надлежащее выполнение запросов ORM. Это, как говорится…

Если вы заинтересованы в написании запроса в соответствии с:

 SELECT
    user.email,
    user.name,
    document.name,
    documents_permissions.readAllowed,
    documents_permissions.writeAllowed
FROM
    user, document, documents_permissions
WHERE
    user.email = "user@email.com";

Тогда вам следует выбрать что-то вроде:

 session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "user@email.com"
).all()

Если вместо этого вы хотите сделать что-то вроде:

 SELECT 'all the columns'
FROM user
JOIN document ON document.author_id = user.id AND document.author == User.email
JOIN document_permissions ON document_permissions.document_id = document.id AND document_permissions.document = document.name

Затем вы должны сделать что-то вроде:

 session.query(
    User
).join(
    Document
).join(
    DocumentsPermissions
).filter(
    User.email == "user@email.com"
).all()

Одно замечание по этому поводу…

 query.join(Address, User.id==Address.user_id) # explicit condition
query.join(User.addresses)                    # specify relationship from left to right
query.join(Address, User.addresses)           # same, with explicit target
query.join('addresses')                       # same, using a string

Для получения дополнительной информации посетите документы.

1. СДЕЛАЙТЕ ЭТО. Смотрите — в объединениях указано не так уж много всего. Это потому, что если tables / db-model уже настроены с соответствующими внешними ключами между этими таблицами — SQLAlchemy позаботится о соединении В соответствующих столбцах для вас.

2. Это наиболее правильный ответ и правильное использование sqlalchemy. Однако мне пришлось использовать решение с фильтром, потому что SQLAlchemy не принимает инструкции delete с помощью join()

Ответ №3:

Хорошим стилем было бы настроить некоторые отношения и первичный ключ для разрешений (на самом деле, обычно хорошим стилем является настройка целых первичных ключей для всего, но неважно):

 class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author_email = Column(String, ForeignKey("users.email"))
    author = relation(User, backref='documents')

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    id = Column(Integer, primary_key=True)
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)
    document_name = Column(String, ForeignKey("documents.name"))
    document = relation(Document, backref = 'permissions')

Затем выполните простой запрос с объединениями:

 query = session.query(User, Document, DocumentsPermissions).join(Document).join(DocumentsPermissions)

1. Что query установлено в последней строке и как получить доступ к объединенным записям в ней?

2. @pate Я не уверен, что вы подразумеваете под «на что задан запрос», но он будет объединяться в соответствии с отношениями и даст 3 кортежа. Аргументы вызова query() по сути являются списком выбора в sqlalchemy.

3. Как мне получить доступ к Document (значению 2-го кортежа) в наборе результатов запроса?

4. @PetrusTheron Как я уже сказал, запрос выдаст 3 кортежа. Вы можете индексировать элементы или просто распаковать: for (user, doc, perm) in query: print "Document: %s" % doc

5. Вау, взрыв из прошлого. ‘permissions’ — это атрибут объекта Document, который предоставляет вам набор объектов DocumentPermission. Отсюда обратная ссылка.

Ответ №4:

Развивая ответ Абдула, вы можете получить KeyedTuple вместо дискретной коллекции строк, объединив столбцы:

 q = Session.query(*User.__table__.columns   Document.__table__.columns).
        select_from(User).
        join(Document, User.email == Document.author).
        filter(User.email == 'someemail').all()

Ответ №5:

Эта функция создаст требуемую таблицу в виде списка кортежей.

 def get_documents_by_user_email(email):
    query = session.query(
       User.email, 
       User.name, 
       Document.name, 
       DocumentsPermissions.readAllowed, 
       DocumentsPermissions.writeAllowed,
    )
    join_query = query.join(Document).join(DocumentsPermissions)

    return join_query.filter(User.email == email).all()

user_docs = get_documents_by_user_email(email)