SqlAlchemy auto increment composite key for every new combination

Question

I have a scenario to iterate up session_number column for related user_name. If a user created a session before I'll iterate up the last session_number but if a user created session for the first time session_number should start from 1. I tried to illustrate on below. Right now I handle this by using logic but try to find more elegant way to do that in SqlAlchemy.

id - user_name - session_number
1      user_1             1
2      user_1             2
3      user_2             1
4      user_1             3
5      user_2             2

Here is my python code of the table. My database is PostgreSQL and I'm using alembic to upgrade tables. Right now it continues to iterate up the session_number regardless user_name.

class UserSessions(db.Model):
    __tablename__ = 'user_sessions'

    id = db.Column(db.Integer, primary_key=True, unique=True)
    username = db.Column(db.String, nullable=False)
    session_number = db.Column(db.Integer, Sequence('session_number_seq', start=0, increment=1))
    created_at = db.Column(db.DateTime)
    last_edit = db.Column(db.DateTime)

    __table_args__ = (
        db.UniqueConstraint('username', 'session_number', name='_username_session_number_idx_'),
    )

I've searched on the internet for this situation but those were not like my problem. Is it possible to achieve this with SqlAlchemy/PostgreSQL actions?

You could possibly use a hybrid property, where the return value is the `count` of the user_name? https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html — match, May 11 '21 at 15:49
Do I need to count of user_name? Did you mean session_number? — Süleyman Sümertaş, May 11 '21 at 16:01
Do you actually need a value of `session_number` anywhere during the execution? Or would you just like to have/use it at some point later? — van, May 11 '21 at 16:47
Do you need to store this value or can it be generated on retrieval? — rfkortekaas, May 11 '21 at 18:04
@rfkortekaas yes I do actually. This is an illustration of the real problem but the scenario is the same. — Süleyman Sümertaş, May 11 '21 at 19:35
I was meaning that if you count rows all rows for a given user_name, then the count+1 is the current session number for that user. — match, May 12 '21 at 20:55

score 0 · Answer 1 · answered May 12 '21 at 06:23

First, I do not know of any "pure" solution for this situation by using either SqlAlchemy or Postgresql or a combination of the two.

Although it might not be exactly the solution you are looking for, I hope it will give you some ideas.

If you wanted to calculate the session_number for the whole table without it being stored, i would use the following query or a variation of thereof:

def get_user_sessions_with_rank():
    expr = (
        db.func.rank()
        .over(partition_by=UserSessions.username, order_by=[UserSessions.id])
        .label("session_number")
    )
    subq = db.session.query(UserSessions.id, expr).subquery("subq")
    q = (
        db.session.query(UserSessions, subq.c.session_number)
        .join(subq, UserSessions.id == subq.c.id)
        .order_by(UserSessions.id)
    )
    return q.all()

Alternatively, I would actually add a column_property to the model compute it on the fly for each instance of UserSessions. it is not as efficient in calculation, but for queries filtering by specific user it should be good enough:

class UserSessions(db.Model):
    __tablename__ = "user_sessions"

    id = db.Column(db.Integer, primary_key=True, unique=True)
    username = db.Column(db.String, nullable=False)
    created_at = db.Column(db.DateTime)
    last_edit = db.Column(db.DateTime)

# must define this outside of the model definition because of need for aliased
US2 = db.aliased(UserSessions)
UserSessions.session_number = db.column_property(
    db.select(db.func.count(US2.id))
    .where(US2.username == UserSessions.username)
    .where(US2.id <= UserSessions.id)
    .scalar_subquery()
)

In this case, when you query for UserSessions, the session_number will be fetched from the database, while being None for newly created instances.

SqlAlchemy auto increment composite key for every new combination

1 Answers1