CODEWITHSUNDEEP
pythonmysqlsqlalchemyuser-variables
Lemon Reddy
Lemon Reddy

Reputation: 637

User-defined variables support in Sqlalchemy

Does SQLAlchemy have support for user-defined variables?

https://dev.mysql.com/doc/refman/5.6/en/user-variables.html

Problem: I am trying to get the time difference between two consecutive rows. I can do this by writing raw SQL query using user-defined variables. Is this possible to do in SQLAlchemy without writing raw SQL query?

My table is something like this:

id user_id date
1. user_1  01-11-2021 00:00
2. user_1  01-11-2021 00:00
3. user_1  01-11-2021 00:01
4. user_2  01-11-2021 00:00

Output would be something like

id user_id time_diff
1. user_1  NULL
2. user_1  0
3. user_1  1
4. user_2  NULL

Upvotes: 0

Views: 207

Answers (1)

Akina
Akina

Reputation: 42834

The SQL query which performs needed task and does not use UDV and is applicable in MySQL 5.6 looks like:

SELECT t1.*, TIMESTAMPDIFF(SECOND, t1.date, t2.date) time_diff
FROM table t1
LEFT JOIN table t2 ON t1.user_id = t2.user_id 
                  AND t1.date > t2.date
LEFT JOIN table t3 ON t1.user_id = t3.user_id 
                  AND t1.date > t3.date
                  AND t3.date > t2.date
WHERE t3.id IS NULL

The logic: we select 2 rows (by date value) for a user from table copies t1 and t2 (and row in t2 have earlier date value), and the copy t3 checks that these rows are really adjacent (there is no third row between them).

This query does not use UDV and can be converted to SQLAlchemy syntax.

Upvotes: 0

Related Questions