Как присоединиться к трем таблицам с SQLalchemy и сохранить все столбцы в одной из таблиц?

Итак, у меня есть три таблицы:

Классовые срывы:

engine = create_engine('sqlite://test.db', echo=False) SQLSession = sessionmaker(bind=engine) Base = declarative_base() class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = Column(String) pubDate = Column(DateTime) class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key = True) username = Column(String) password = Column(String) sessionId = Column(String) class Subscription(Base): __tablename__ = 'subscription' userId = Column(Integer, ForeignKey('user.id'), primary_key=True) channelId = Column(Integer, ForeignKey('channel.id'), primary_key=True) 

ПРИМЕЧАНИЕ. Я знаю, что user.username должно быть уникальным, нужно исправить это, и я не уверен, почему SQLalchemy создает некоторые имена строк с двойными кавычками.

И я пытаюсь найти способ получить все каналы, а также указать, по какому каналу подключен один конкретный пользователь (идентифицированный user.sessionId вместе с user.id).

Например, скажем, у нас есть четыре канала: channel1, channel2, channel3, channel4; пользователь: user1; который имеет подписку на каналы 1 и канал4. Запрос для user1 вернет что-то вроде:

 channel.id | channel.title | subscribed --------------------------------------- 1 channel1 True 2 channel2 False 3 channel3 False 4 channel4 True 

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

Ядро базы данных, которое я использую вместе с SQLalchemy atm. sqlite3

Я уже два дня царапаю голову над этим, у меня нет проблем с объединением всех трех таблиц подписки, но тогда все каналы, в которых пользователь не имеет подписки, опускаются.

Надеюсь, мне удалось достаточно подробно описать мою проблему, спасибо заранее.

РЕДАКТИРОВАТЬ : Умело решить эту проблему немного неуклюжим способом, связанным с подзапросом:

 # What a messy SQL query! stmt = query(Subscription).filter_by(userId = uid()).join((User, Subscription.userId == User.id)).filter_by(sessionId = id()).subquery() subs = aliased(Subscription, stmt) results = query(Channel.id, Channel.title, subs.userId).outerjoin((subs, subs.channelId == Channel.id)) 

Тем не менее, я продолжу поиск более элегантного решения, поэтому ответы по-прежнему очень приветствуются.

3 Solutions collect form web for “Как присоединиться к трем таблицам с SQLalchemy и сохранить все столбцы в одной из таблиц?”

Опция 1:

Subscription – это просто объект отношения «ко многим ко многим», и я бы предложил, чтобы вы моделировали его как такового, а не как отдельный класс. См. Раздел « Конфигурирование документации« Многие-ко-многим отношениям » SQLAlchemy/declarative .

Модель с тестовым кодом становится:

 from sqlalchemy import create_engine, Column, Integer, DateTime, String, ForeignKey, Table from sqlalchemy.orm import relation, scoped_session, sessionmaker, eagerload from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///:memory:', echo=True) session = scoped_session(sessionmaker(bind=engine, autoflush=True)) Base = declarative_base() t_subscription = Table('subscription', Base.metadata, Column('userId', Integer, ForeignKey('user.id')), Column('channelId', Integer, ForeignKey('channel.id')), ) class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = Column(String) pubDate = Column(DateTime) class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key = True) username = Column(String) password = Column(String) sessionId = Column(String) channels = relation("Channel", secondary=t_subscription) # NOTE: no need for this class # class Subscription(Base): # ... Base.metadata.create_all(engine) # ###################### # Add test data c1 = Channel() c1.title = 'channel-1' c2 = Channel() c2.title = 'channel-2' c3 = Channel() c3.title = 'channel-3' c4 = Channel() c4.title = 'channel-4' session.add(c1) session.add(c2) session.add(c3) session.add(c4) u1 = User() u1.username ='user1' session.add(u1) u1.channels.append(c1) u1.channels.append(c3) u2 = User() u2.username ='user2' session.add(u2) u2.channels.append(c2) session.commit() # ###################### # clean the session and test the code session.expunge_all() # retrieve all (I assume those are not that many) channels = session.query(Channel).all() # get subscription info for the user #q = session.query(User) # use eagerload(...) so that all 'subscription' table data is loaded with the user itself, and not as a separate query q = session.query(User).options(eagerload(User.channels)) for u in q.all(): for c in channels: print (c.id, c.title, (c in u.channels)) 

который производит следующий результат:

 (1, u'channel-1', True) (2, u'channel-2', False) (3, u'channel-3', True) (4, u'channel-4', False) (1, u'channel-1', False) (2, u'channel-2', True) (3, u'channel-3', False) (4, u'channel-4', False) 

Обратите внимание на использование eagerload , которое выдает только 1 инструкцию SELECT вместо 1 для каждого User когда запрашиваются channels .

Вариант-2:

Но если вы хотите сохранить модель и просто создать запрос SA, который даст вам столбцы по вашему запросу, следующий запрос должен выполнить задание:

 from sqlalchemy import and_ from sqlalchemy.sql.expression import case #... q = (session.query(#User.username, Channel.id, Channel.title, case([(Subscription.channelId == None, False)], else_=True) ).outerjoin((Subscription, and_(Subscription.userId==User.id, Subscription.channelId==Channel.id)) ) ) # optionally filter by user q = q.filter(User.id == uid()) # assuming uid() is the function that provides user.id q = q.filter(User.sessionId == id()) # assuming uid() is the function that provides user.sessionId res = q.all() for r in res: print r 

Выходной сигнал абсолютно такой же, как и в варианте-1 выше.

Чтобы сделать это немного легче, я добавил отношения к вашей модели, таким образом вы можете просто сделать user.subscriptions, чтобы получить все подписки.

 engine = create_engine('sqlite://test.db', echo=False) SQLSession = sessionmaker(bind=engine) Base = declarative_base() class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = Column(String) pubDate = Column(DateTime) class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key = True) username = Column(String) password = Column(String) sessionId = Column(String) class Subscription(Base): __tablename__ = 'subscription' userId = Column(Integer, ForeignKey('user.id'), primary_key=True) user = relationship(User, primaryjoin=userId == User.id, backref='subscriptions') channelId = Column(Integer, ForeignKey('channel.id'), primary_key=True) channel = relationship(channel, primaryjoin=channelId == channel.id, backref='subscriptions') results = session.query( Channel.id, Channel.title, Channel.subscriptions.any().label('subscribed'), ) for channel in results: print channel.id, channel.title, channel.subscribed 

Не запрашивайте у пользователя. Запрос из канала.

 user = query(User).filter_by(id=1).one() for channel in query(Channel).all(): print channel.id, channel.title, user in channel.subscriptions.user 

Таким образом, вы получаете все каналы, а не только те, которые связаны с пользователем.

  • Обновление с Django 1.6 до 1.9: сбой python manage.py
  • У кого-нибудь есть пример кода для конвейера sqlite в Scrapy?
  • Использование инструкции WHERE ___ IN ___
  • Sqlite / SQLAlchemy: как применять внешние ключи?
  • Отсутствует таблица при запуске Django Unittest с Sqlite3
  • SQLAlchemy filter in_ operator
  • Обработать новый символ строки «\ n» в базе данных Sqlite с помощью Python?
  • нужна проверка и добавление базы данных sqlite для ведения домашнего хозяйства и управления кодом
  •  
    Interesting Posts for Van-Lav

    pandas: проверить, содержит ли строка одну из подстрок в списке

    Облицовка проблемы с помощью панели действий в Python kivy Application

    Как удалить столбец из структурированного массива numpy * без его копирования *?

    как я могу удалить уведомления и предупреждения из браузера? selenium python 2.7.7

    Инкрементный PCA на большие данные

    Как вы можете определить, совпадают ли два регулярных выражения в строках, которые они могут сопоставить?

    Побитовые операции в классе, наследующем int

    Почему `datetime.strptime` получает неверную дату во вторник на неделе 0 2015 года?

    Как динамически изменять базовый класс экземпляров во время выполнения?

    Элегантный способ хранения словаря на Python?

    Numpy Array в base64 и обратно в массив Numpy – Python

    Как удалить элемент в списке, если он существует?

    как загружать зависимости пиков локально?

    Javascript – заголовок No-Access-Control-Allow-Origin присутствует на запрошенном ресурсе

    Установите pyserial Mac OS 10.10?

    Python - лучший язык программирования в мире.