How to use a SQL View with Pony ORM

I'm trying to fetch the data returned by a View in MySQL using Pony ORM but the documentation does not provide any information on how to achieve this (well, I couldn't find any solution until this moment). Can Pony ORM do this? If so, what should I do to get it working?

Here is my MySQL View:

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `admin`@`%` 
SQL SECURITY DEFINER
VIEW `ResidueCountByDate` AS
SELECT 
    CAST(`ph`.`DATE` AS DATE) AS `Date`,
    COUNT(`art`.`RESIDUE_TYPE_ID`) AS `Aluminum Count`,
    COUNT(`prt`.`RESIDUE_TYPE_ID`) AS `PET Count`
FROM
    ((((`TBL_PROCESS_HISTORY` `ph`
    JOIN `TBL_RESIDUE` `pr` ON ((`ph`.`RESIDUE_ID` = `pr`.`RESIDUE_ID`)))
    LEFT JOIN `TBL_RESIDUE_TYPE` `prt` ON (((`pr`.`RESIDUE_TYPE_ID` = `prt`.`RESIDUE_TYPE_ID`)
        AND (`prt`.`DESCRIPTION` = 'PET'))))
    JOIN `TBL_RESIDUE` `ar` ON ((`ph`.`RESIDUE_ID` = `ar`.`RESIDUE_ID`)))
    LEFT JOIN `TBL_RESIDUE_TYPE` `art` ON (((`ar`.`RESIDUE_TYPE_ID` = `art`.`RESIDUE_TYPE_ID`)
        AND (`art`.`DESCRIPTION` = 'ALUMINUM'))))
GROUP BY CAST(`ph`.`DATE` AS DATE)
ORDER BY CAST(`ph`.`DATE` AS DATE)

Solution

You can try one of the following:

1) Define a new entity and specify the view name as a table name for that entity:

class ResidueCountByDate(db.Entity):
    dt = PrimaryKey(date, column='Date')
    aluminum_count = Required(int, column='Aluminum Count')
    pet_count = Required(int, column='PET Count')

After that you can use that entity to select data from the view:

with db_session:
    start_date = date(2017, 1, 1)
    query = select(rc for rc in ResidueCountByDate if rc.date >= start_date)
    for rc in query:
        print(rc.date, rc.aluminum_count, rc.pet_count)

By default, a column name is equal to an attribute name. I explicitly specified column for each attribute, because in Python attribute names cannot contain spaces, and usually written in lowercase.

It is possible to explicitly specify table name if it is not equal to entity name:

class ResidueCount(db.Entity):
    _table_ = 'ResidueCountByDate'
    ...

2) You can write raw SQL query without defining any entity:

with db_session:
    start_date = date(2017, 1, 1)
    rows = db.select('''
        SELECT `Date` AS dt, `Aluminum Count` AS ac, `PET Count` AS pc
        FROM `ResidueCountByDate`
        WHERE `Date` >= $start_date
    ''')
    for row in rows:
        print(row[0], row[1], row[2])
        print(row.dt, row.ac, row.pc)  # the same as previous row

If column name can be used as a Python identifier (i.e. it does not contain spaces or special characters) you can access column value using dot notation as in the last line