[sqlalchemy] Re: Calculate rank of single row using subquery
> Is this the most efficient way to do this, or am I over-complicating it?
That roughly looks like code that I've implemented in the past.
If it works and you don't have issues, I wouldn't worry about efficiency.
Stuff like this will often vary based on the underlying table data - the
structure, size, etc. Adding indexes on columns can often improve
performance a lot.
If you're really concerned on optimizing this, the typical approach is to
focus on generating the target SQL query that works within the performance
constraints you want, and then porting it to sqlalchemy by writing python
code that will generate that same output.
On Thursday, September 9, 2021 at 7:57:57 AM UTC-4 ursc...@gmail.com wrote:
> I'm trying to calculate the rank of a particular (unique) row id by using
> a subquery:
>
> I first calculate the total ranking for a table, Game (using 1.4.23):
>
> sq = (
> session.query(
> Game.id,
> Game.score,
> func.rank().over(order_by=Game.score.desc()).label("rank"),
> )
> .filter(Game.complete == True)
> .subquery()
> )
>
> Then filter by the row ID I want (gameid):
>
> gamerank = (
> session.query(
> sq.c.id, sq.c.score, sq.c.rank
> )
> .filter(sq.c.id == gameid)
> .limit(1)
> .one()
> )
>
> Game.score is a Float column. Is this the most efficient way to do this,
> or am I over-complicating it?
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e6f92f26-4afb-44d5-a194-f04ace66be2cn%40googlegroups.com.
[sqlalchemy] Re: AsyncEngine always returns UTC time for datetime column
Can you share the database drivers / dialects you use? The discrepancy
could be there.
On Tuesday, September 14, 2021 at 7:03:27 AM UTC-4
ivan.ran...@themeanalytics.com wrote:
> Hi all,
>
> I am trying to figure it out why AsyncEngine always returns UTC time for
> datetime column, any help is appreciated?
>
> I am working with sqlalchemy core and async engine. Column definition:
> *Column('test', DateTime(timezone=True), nullable=False)*
>
> Also tried with this, but no luck:
>
> *_connect_args = {'server_settings': {'timezone':
> ''America/New_York''}}async_db: AsyncEngine =
> create_async_engine(async_url_from_config(), connect_args=_connect_args)*
>
> When I tried with regular create_engine, everything worked as expected
> with the same database data.
> Data in the database (configured for New York) contains timezone info:
>
> *test | 2021-08-26 16:02:46.057288-04*
>
> BR,
>
> Ivan
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e7f24870-80d0-49f7-99a0-de4d141f33b9n%40googlegroups.com.
[sqlalchemy] AsyncEngine always returns UTC time for datetime column
Hi all,
I am trying to figure it out why AsyncEngine always returns UTC time for
datetime column, any help is appreciated?
I am working with sqlalchemy core and async engine. Column definition:
*Column('test', DateTime(timezone=True), nullable=False)*
Also tried with this, but no luck:
*_connect_args = {'server_settings': {'timezone':
''America/New_York''}}async_db: AsyncEngine =
create_async_engine(async_url_from_config(), connect_args=_connect_args)*
When I tried with regular create_engine, everything worked as expected with
the same database data.
Data in the database (configured for New York) contains timezone info:
*test | 2021-08-26 16:02:46.057288-04*
BR,
Ivan
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/0696b293-89f1-4527-82de-6a1fd5e2393bn%40googlegroups.com.
