如何使用 SQLalchemy 连接三个表并将所有列保留在其中一个表中? [英] How do I join three tables with SQLalchemy and keeping all of the columns in one of the tables?

问题描述

所以，我有三个表:

类定义:

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 用双引号创建一些行名.

NOTE: I know user.username should be unique, need to fix that, and I'm not sure why SQLalchemy creates some row names with the double-quotes.

而且我正在尝试想出一种方法来检索所有频道，以及指示某个特定用户(由 user.sessionId 和 user.id 标识)订阅了哪些频道.

And I'm trying to come up with a way to retrieve all of the channels, as well as an indication on what channels one particular user (identified by user.sessionId together with user.id) has a subscription on.

例如，假设我们有四个频道:频道1、频道2、频道3、频道4；一个用户:user1；谁在频道 1 和频道 4 上订阅了.对 user1 的查询将返回如下内容:

For example, say we have four channels: channel1, channel2, channel3, channel4; a user: user1; who has a subscription on channel1 and channel4. The query for user1 would return something like:

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

这是最好的结果，但由于我完全不知道如何完成订阅的列，我一直在尝试获取用户订阅的行中的特定用户 ID，以及缺少订阅，只需将其留空.

This is a best-case result, but since I have absolutely no clue as how to accomplish the subscribed column, I've been instead trying to get the particular users id in the rows where the user has a subscription and where a subscription is missing, just leave it blank.

我与 SQLalchemy atm 一起使用的数据库引擎.是 sqlite3

The database engine that I'm using together with SQLalchemy atm. is sqlite3

我已经为此苦恼了两天，通过订阅表将所有三个连接在一起没有问题，但是用户没有订阅的所有频道都被省略了.

I've been scratching my head over this for two days now, I've no problem joining together all three by way of the subscription table but then all of the channels where the user does not have a subscription gets omitted.

我希望我已经设法充分描述了我的问题，提前致谢.

I hope I've managed to describe my problem sufficiently, thanks in advance.

EDIT:设法以涉及子查询的稍微笨拙的方式解决此问题:

EDIT: Managed to solve this in a slightly clunky way involving a sub-query:

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

不过，我会继续寻找更优雅的解决方案，所以答案仍然非常受欢迎.

However, I'll be continuing to search for a more elegant solution, so answers are still very much welcomed.

推荐答案

Option-1:

Subscription 只是一个多对多关系对象，我建议您将其建模为这样，而不是作为一个单独的类.请参阅配置多对多关系 SQLAlchemy/declarative 的文档.

Subscription is just a many-to-many relation object, and I would suggest that you model it as such rather then as a separate class. See Configuring Many-to-Many Relationships documentation of SQLAlchemy/declarative.

您使用测试代码的模型变为:

You model with the test code becomes:

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 的使用，当要求 channels 时，它只会为每个 User 发出 1 个 SELECT 语句而不是 1 个.

Please note the use of eagerload, which will issue only 1 SELECT statement instead of 1 for each User when channels are asked for.

选项 2:

但是，如果您想保留模型并仅创建一个 SA 查询来按照您的要求提供列，则以下查询应该可以完成这项工作:

But if you want to keep you model and just create an SA query that would give you the columns as you ask, following query should do the job:

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 完全相同.

The output is absolutely the same as in the option-1 above.

这篇关于如何使用 SQLalchemy 连接三个表并将所有列保留在其中一个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！