SQLAlchemy: get single object to join multiple tables
DB - SQLAlchemy setup
Consider the classic setup 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 have been omitted for clarity.
My request
Suppose I want to get the username and 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()
I get two objects - user
and api_key
, from which I can get user.name
and api_key.api_key
.
Problem
I would like to wrap this call with a function that will return single objects whose fields will be concatenated into fields user
and fields api_key
- just like SQL join
returns a table and the columns of both tables are concatenated. Is there a way to automatically get an object whose fields are the union of the fields of both tables?
What i tried
I can define a mapping class for each Join operation, but I'm wondering if the mapping can be done automatically.
source to share
Instead of querying objects, ask for a list of fields instead, in which case SQLAlchemy returns instances KeyedTuple
, which it suggests KeyedTuple._asdict()
, which you can use to return an 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')
But for your specific query, you don't even need to join ApiKey
as the field is api_key
present in the table User
:
row = database.db_session.query(User.name, User.api_key)\ .filter(User.user_id==user_id).first()
source to share
I would add the api_key relation to User
:
class User(Base):
__tablename__ = 'user'
user_id = Column(String)
user_name = Column(String)
vioozer_api_key = Column(String, ForeignKey("api_key.api_key"))
api_key = Relationship('ApiKey', uselist=False)
Then you can make a request like this:
>>> user = database.db_session.query(User)\
.filter(User.user_id=='user_00000000000000000000000000000000').first()
>>> user.api_key
<ApiKey>
source to share