proegssilb proegssilb - 4 years ago 81
Python Question

Create an entity by summarizing data in SqlAlchemy

I'm working with an existing schema about combat data in a video game, because I have to work with data generated by an external program. This schema isn't technically compatible with how I want to organize the data, but it mostly works.

One problem that comes up 3 times (so far) is providing the user with a big table of data to browse/search through to start sifting through the data that's available. So, I decided to have a single generic way of generating a data table, and handling AJAX requests for more data to that table.

The problem is that one of these cases is basically retrieving data straight from a table, while another is actually retrieving summarized data from a table. For example, the schema defines a 'Combatant', or a participant in a combat, but I want the system to expose a concept of a 'Character', or a thing that exists and participates in many combats. So, a

would be a
in multiple, different

There's basically two ways I can create the idea of a Character from the database. I can either use a view, or I can have my ORM (SQLAlchemy) create the required query from scratch every time. View support is possible in SQLAlchemy via some possible extensions, but because I'm summarizing, I lose the primary key, and therefore I can't create an ORM class representing the view. Supposedly, you can force a column to be a primary key, but that didn't work for me (seemed to do nothing). If I try to use the query from scratch, my current way of generating a generic table doesn't work for want of being able to inspect(...) a query result.

So, I have two questions, and a MCVE for the query-from-scratch approach:

  1. Am I taking an approach to this problem known to be bad?

  2. Is there a way to avoid using inspect(...) that lets me use arbitrary strings to get values from a query result object?

Some notes about the MCVE:

  • I've stripped down the number of ORM objects, the columns in each, and the config information in the column lists. The column lists are ugly in their original form.

  • Where the double-nested
    loops are in the MCVE, there's a templating system in my actual code. Web server, generating HTML, all the usual fun has been stripped out to focus on SQLAlchemy.

Answer Source

I wound up solving this by using this code for Materialized Views. So, all the aggregation is done by the DB, and SQLAlchemy just treats the view as another selectable.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download