0

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
Lemon Reddy
  • 553
  • 3
  • 5
  • *Does SQLAlchemy have support for user-defined variables?* User-defined MySQL variables are not accessible by external script. But you may use them in raw SQL query. *Is this possible to do in SQLAlchemy without writing raw SQL query?* Yes, you may build a query which uses 3 copies of source table. – Akina Nov 02 '21 at 04:51
  • @Akina _User-defined MySQL variables are not accessible by external script._ Why is that the case? Can you explain or link to a page where it explains this? – Lemon Reddy Nov 03 '21 at 02:59
  • If external script wants to know the value of UDV it must query it (like `SELECT @variable'`) and receive the value. No another way. No the possibility to access the variable other than quering it. – Akina Nov 03 '21 at 04:27

1 Answers1

0

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.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • I wanted to use this as a subquery and get the overall time spent for all the users. I am trying to avoid raw SQL query to mitigate the risks associated. But if it comes at the cost of time, I am willing to take the extra risk. My question is, does using a LEFT JOIN increase/decrease the time taken to run the query compared to UDV? Or Is it better to use UDV and substitute the input after proper validation? – Lemon Reddy Nov 02 '21 at 15:22
  • @LemonReddy *I am trying to avoid raw SQL query to mitigate the risks associated.* I understand this. My query CAN be converted from raw text to framework's syntax, and you won't use raw query. *does using a LEFT JOIN increase/decrease the time taken to run the query compared to UDV?* The query which uses UDV is less expensive anycase - it uses one table copy and single-pass fullscan which must be more fast than joining 3 table copies, even when suitable indexes exists. – Akina Nov 02 '21 at 19:38