Gino: Math operations in select

[filantus]

• GINO version: 0.8.2
• Python version: 3.7.2
• asyncpg version: 0.18.3
• aiocontextvars version: 0.2.1
• PostgreSQL version: 11.1

Description

Hi!

Can you please tell how possible to use math operations in models querying?

I want reach with GINO orm same result like in raw sql, for example:

SELECT t1.col_a * t2.col_b * 1.5 as value
FROM t1
JOIN t2 ON t2.some_key = t1.some_key;

[fantix]

Use something like this:

            await db.select([T1.col_a * T2.col_b * 1.5])
            .select_from(T1.join(T2))
            .gino.all()

Please see full example below:

import asyncio

import gino

db = gino.Gino()


class T1(db.Model):
    __tablename__ = "t1"

    id = db.Column(db.Integer, primary_key=True)
    col_a = db.Column(db.Float)


class T2(db.Model):
    __tablename__ = "t2"

    col_b = db.Column(db.Float)
    t1_id = db.Column(db.ForeignKey("t1.id"))


async def main():
    async with db.with_bind("postgresql://localhost/dbname", echo=True):
        await db.gino.create_all()
        t1 = await T1.create(col_a=2.3)
        await T2.create(col_b=4.5, t1_id=t1.id)
        result = (
            await db.select([T1.col_a * T2.col_b * 1.5])
            .select_from(T1.join(T2))
            .gino.all()
        )
        print(result)
        await db.gino.drop_all()


asyncio.run(main())

Yields:

2019-03-12 09:34:05,879 INFO gino.engine._SAEngine SELECT t1.col_a * t2.col_b * $1 AS anon_1
FROM t1 JOIN t2 ON t1.id = t2.t1_id
2019-03-12 09:34:05,880 INFO gino.engine._SAEngine (1.5,)

[(15.524999999999999,)]

[filantus]

Cool, that works. Thank you a lot for quick and comprehensive answer. Also thank you for great work like GINO in general.

[fantix]

@filantus Anytime! Glad it works for you 😃