Adding external data to to SQLAlchemy object collection

Question

I am building Flask based API app with flask-sqlalchemy and Postgres DB.

users table in DB holds a record for each user. PK for this table is username (nothing special here)
performance table in DB holds user performance. PK for this table is a Date. There is a column for each user in this table named by username. (I am aware that some say this structure is not ideal - yet it is required by unrelated requirements).

Examples:

PK = "username", tablename = "users"

| username | firstname | lastname | 
-----------------------------------
| alice    | Alice     | Johns    |
| bob      | Bob       | Speed    |


PK = "timestamp", tablename = "performance"

| timestamp | alice | bob  | 
-----------------------------------
| 2017-11-2 | 1     | 5    |
| 2017-11-3 | 6     | 9    |

I use SQLAlchemy to access users table.

I am willing to create REST API which will receive a Date parameter and return a collection of all users with their performance on that Date.

What is the correct SQLAlchemy query, so the performance value won't be selected independently for each user.

Wait, you have a table with a column for each user? Or did you mean you have something like `(date, user_id, value)`? — Ilja Everilä, Dec 29 '17 at 13:55
A time series table with a column for each User (the number of Users is static and not changing) — Meir Tseitlin, Dec 29 '17 at 13:56
I rewritten the question from scratch - can you pls vote to reopen it? tnx — Meir Tseitlin, Dec 31 '17 at 09:38

score 0 · Answer 1 · answered Jan 02 '18 at 11:11

If you absolutely cannot alter your schema, then you could unpivot performance data and join with users. Sample mappings for relations, since none were provided:

In [2]: class User(Base):
   ...:     __tablename__ = "users"
   ...:     username = Column(Unicode(255), primary_key=True)
   ...:     firstname = Column(Unicode(255))
   ...:     lastname = Column(Unicode(255))
   ...:     

In [3]: class Performance(Base):
   ...:     __tablename__ = "performance"
   ...:     timestamp = Column(Date, primary_key=True)
   ...:     # NOTE: this works **only** in a class body context,
   ...:     # and your schema shouldn't be like this anyway.
   ...:     for name in ['alice', 'bob']:
   ...:         locals()[name] = Column(Integer, nullable=False)
   ...:

Get usernames that have performance data using inspection. You could also hold on to a static list of usernames:

In [11]: users = inspect(Performance).attrs.keys()[1:]

In [12]: users
Out[12]: ['alice', 'bob']

Form the unpivot query – just one way to do it:

In [15]: from sqlalchemy.dialects import postgresql
    ...: performance = session.query(
    ...:         func.unnest(postgresql.array(users)).label('username'),
    ...:         func.unnest(postgresql.array(
    ...:             [getattr(Performance, name)
    ...:              for name in users])).label('value')).\
    ...:     filter(Performance.timestamp == '2017-11-2').\
    ...:     subquery()
    ...:

Join users with their performance values:

In [24]: session.query(User, performance.c.value).\
    ...:     join(performance, performance.c.username == User.username).\
    ...:     all()
Out[24]: 
[(<__main__.User at 0x7f79eb5d2c88>, 1),
 (<__main__.User at 0x7f79eb5d2cf8>, 5)]

Were you to alter your schema such that performance data is stored as (timestamp, username, value) tuples, you could simply do this:

In [2]: class User(Base):
   ...:     __tablename__ = "users"
   ...:     ...
   ...:     performance = relationship("BetterPerformance")
   ...:

In [25]: class BetterPerformance(Base):
    ...:     __tablename__ = "better_performance"
    ...:     timestamp = Column(Date, primary_key=True)
    ...:     username = Column(ForeignKey('users.username'), primary_key=True)
    ...:     value = Column(Integer, nullable=False)
    ...: 

In [13]: session.query(User, BetterPerformance.value).\
    ...:     join(User.performance).\
    ...:     filter(BetterPerformance.timestamp == '2017-11-2').\
    ...:     all()
Out[13]: 
[(<__main__.User at 0x7f6ae3282c18>, 1),
 (<__main__.User at 0x7f6ae3282ba8>, 5)]

or even:

In [17]: session.query(User).\
    ...:     join(User.performance).\
    ...:     options(contains_eager(User.performance)).\
    ...:     filter(BetterPerformance.timestamp == '2017-11-2').\
    ...:     all()
Out[17]: [<__main__.User at 0x7f6ae3282c18>, <__main__.User at 0x7f6ae3282ba8>]

In [18]: [u.performance[0].value for u in _]
Out[18]: [1, 5]

Adding external data to to SQLAlchemy object collection

1 Answers1