SQLAlchemy:从连接多个表中获取单个对象 [英] SQLAlchemy: Getting a single object from joining multiple tables
问题描述
考虑两个表的经典设置 - user
和 api_key
,由 SQLAlchemy 对象表示为:
Consider a classic setting of two tables - user
and api_key
, represented by SQLAlchemy objects as:
class User(Base):
__tablename__ = 'user'
user_id = Column(String)
user_name = Column(String)
vioozer_api_key = Column(String, ForeignKey("api_key.api_key"))
class ApiKey(Base):
__tablename__ = 'api_key'
api_key = Column(String(37), primary_key=True)
为清楚起见,省略了其他字段.
Other fields omitted for clarity.
假设我想获取特定用户 ID 的用户名和 api 密钥:
Suppose that I want to get the user name and the api key for a specific user id:
user, api_key = database.db_session.query(User, ApiKey)\
.join(ApiKey, User.vioozer_api_key==ApiKey.api_key)\
.filter(User.user_id=='user_00000000000000000000000000000000').first()
我得到两个对象 - user
和 api_key
,我可以从中获取 user.name
和 api_key.api_key代码>.
I get two objects - user
and api_key
, from which I can fetch user.name
and api_key.api_key
.
我想用一个函数包装这个调用,该函数将返回一个对象,其字段将是 user
字段和 api_key
字段的联合 -SQL join
以同样的方式返回一个表,其中两个表的列都被连接起来.有没有办法自动获取一个对象,其字段是两个表的字段的并集?
I would like to wrap this call with a function, which would return a single objects whose fields would be the union of the user
fields and the api_key
fields - the same way a SQL join
returns a table with the columns of both tables being joined. Is there a wayo to automatically get a object whose fields are the union of the fields of both tables?
我可以为每个 Join 操作定义一个 mapper class,但是我想知道是否可以自动完成映射.
I can define a mapper class for each Join operation, but I wonder if the mapping could be done automatically.
推荐答案
不是查询对象,而是查询字段列表,在这种情况下 SQLAlchemy 返回 KeyedTuple
,提供 KeyedTuple._asdict()
方法可用于返回任意字典:
Instead of querying objects, query for list of fields instead, in which case SQLAlchemy returns instances of KeyedTuple
, which offers KeyedTuple._asdict()
method you can use to return arbitrary dictionary:
def my_function(user_id):
row = database.db_session.query(User.name, ApiKey.api_key)\
.join(ApiKey, User.vioozer_api_key==ApiKey.api_key)\
.filter(User.user_id==user_id).first()
return row._asdict()
my_data = my_function('user_00000000000000000000000000000000')
但是对于您的特定查询,您甚至不需要加入 ApiKey
,因为 api_key
字段存在于 User
表中:
But for your particular query, you do not need even to join on ApiKey
as the api_key
field is present on the User
table:
row = database.db_session.query(User.name, User.api_key)\
.filter(User.user_id==user_id).first()
这篇关于SQLAlchemy:从连接多个表中获取单个对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!