How to do following sql operation using Table.select() method in python sqlobject?

Question

Sql Operation:

    SELECT *,
        CASE
            WHEN quota_unit = 'MB' THEN quota*1024
            ELSE quota
        END AS data_usage
    FROM mapping
    ORDER BY data_usage;

I tried like this,

query = mapping.select('CASE WHEN mapping.quota_unit = 'MB' THEN 1024 * mapping.quota ELSE mapping.quota END AS data_usage)
query.orderBy(data_usage)
res = list(query)

but select method taking that value in braces for Where clause and resultant query becomes

SELECT * FROM mapping WHERE 'CASE WHEN mapping.quota_unit = MB THEN 1024 * mapping.quota ELSE mapping.quota END AS data_usage'

which is not expected. Can you please me here ?

score 0 · Answer 1 · answered Dec 31 '20 at 08:09

0

Not every possible SQL query could be mapping back to SQLObject expressions. Sometimes you need to go down to SQLBuilder or even raw queries:

results = connection.queryAll(string)

answered Dec 31 '20 at 08:09

phd

82,685
13
120
165

How to do following sql operation using Table.select() method in python sqlobject?

1 Answers1