How to use SQL View with Pony ORM
I'm trying to get the data returned by a View in MySQL using the Pony ORM, but the documentation doesn't provide any information on how to achieve this (well, I couldn't find a solution up to this point). Can Pony ORM do this? If so, what should I do to make it work?
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)
source to share
You can try one of the following:
1) Define a new object and provide the view name as the table name for that object:
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 this object 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, the column name is equal to the attribute name. I have explicitly specified the column for each attribute because Python cannot contain spaces in attribute names and is usually written in lowercase.
You can explicitly specify the table name if it is not equal to the entity name:
class ResidueCount(db.Entity):
_table_ = 'ResidueCountByDate'
...
2) You can write the original 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 the column name can be used as a Python identifier (i.e. it does not contain spaces or special characters), you can access the column value using dot notation, as in the last line
source to share