SQLAlchemy Subquery List Object has no Attribute

Question

Hello? I am trying to write a query (which has 2 subqueries) in SQLAlchemy but I am getting an error AttributeError: 'list' object has no attribute 'l_date'

Here is my code:

import sqlalchemy as db
from sqlalchemy import func
from sqlalchemy import desc
from sqlalchemy.orm import sessionmaker
from sqlalchemy import or_
from sqlalchemy import and_
from sqlalchemy.sql.expression import  alias

try:

    engine = db.create_engine('postgresql://user:pass@localhost:5432/db_name')
    connection = engine.connect()

except:
    print('Error establishing DB connection')

# Import metadata
metadata = db.MetaData()

# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
session = Session()

# Import articles, authors and log tables
log = db.Table('log', metadata, autoload=True, autoload_with=engine)


def days_with_request_errors():
    query1 = db.select([func.DATE(log.columns.time).label('l_date'), func.count(log.columns.status).label('s_count')]) \
        .where(or_(log.columns.status.like('%5%'), log.columns.status.like('%4%'))) \
        .group_by(func.DATE(log.columns.time))

    ResultProxy = connection.execute(query1)
    q1 = ResultProxy.fetchall()

    query2 = db.select([func.DATE(log.columns.time).label('l_date'),
                          func.count(log.columns.status).label('s_count')]) \
        .group_by(func.DATE(log.columns.time))

    ResultProxy = connection.execute(query2)
    q2 = ResultProxy.fetchall()

    main_query = db.select([q1.l_date, (q1.s_count * 100) / q2.s_count]) \
        .where(q1.l_date == q2.l_date) \
        .where(and_((q1.c.s_count * 100) / q2.c.s_count) > 1)

    ResultProxy = connection.execute(main_query)
    return ResultProxy.fetchall()

Here is the raw SQL query which I am transfering to SQLAlchemy. (The raw query is working fine):

SELECT q1.date, ((q1.count * 100) / q2.count) as percentage
  FROM (SELECT date(time) as date, COUNT(status) as count
        FROM log
        WHERE status LIKE '%4%' or status LIKE '%5%'
        GROUP BY date(time)) as q1,
       (SELECT date(time) as date, COUNT(status) as count
        FROM log
        GROUP BY date(time)) as q2
  WHERE q1.date = q2.date and ((q1.count * 100) / q2.count) > 1

Any help will be greatly appreciated.

You've actually executed the queries and are trying to use the results as subqueries, is all. — Ilja Everilä, Nov 26 '18 at 12:08
So whats the cause of the error @IljaEverilä ? What should I do? — Benjamin Mwendwa Munyoki, Nov 26 '18 at 12:10
I do not have a way to test this right now, but I'm pretty sure if you replace `q1` with `query1` and `q2` with `query2` that would work. — themanatuf, Nov 26 '18 at 12:11
@themanatuf replacing gets the same error => AttributeError: 'Select' object has no attribute 'l_date' ... looks like my alias l_date and s_count are not in the query results. Again, executing the subqueries individually returns expected results. — Benjamin Mwendwa Munyoki, Nov 26 '18 at 12:20
Please read https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-aliases, and https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-subqueries and see if they help. — Ilja Everilä, Nov 26 '18 at 12:24
This should get you what you need: https://stackoverflow.com/questions/34803234/sqlalchemy-subquery-in-from-must-have-an-alias — themanatuf, Nov 26 '18 at 13:08
Thank you so much @themanatuf ... adding .alias('alias_name') at the end of each subquery solved my issue — Benjamin Mwendwa Munyoki, Nov 26 '18 at 13:21

score 2 · Accepted Answer · answered Nov 26 '18 at 13:23

I figured out what was wrong. The subqueries were missing an alias. To make it work, I modified the subqueries as follows:

query1 = db.select([func.DATE(log.columns.time).label('l_date'), func.count(log.columns.status).label('s_count')]) \
        .where(or_(log.columns.status.like('%5%'), log.columns.status.like('%4%'))) \
        .group_by(func.DATE(log.columns.time)).alias('query1')


    query2 = db.select([func.DATE(log.columns.time).label('l_date'),
                          func.count(log.columns.status).label('s_count')]) \
        .group_by(func.DATE(log.columns.time)).alias('query2')

SQLAlchemy Subquery List Object has no Attribute

1 Answers1