The code that makes up the query builder I've been working on is pretty
extensive, so I'll go through the high-level basics.
I've set up a `_base` method that augments the SQLALchemy Base model. Note
the column details retrived in `get_column_and_json_key_from_sql_name` class
method:
class _base:
"""
This class augments the default SQLAlchemy Base model
"""
@classmethod
def get_column_and_json_key_from_sql_name(cls, name):
"""
Returns the column and, if applicable, the JSON top-level key from the
JSON dict.
:param name: Name of field.
:return:
"""
assert type(name) == str
json_key = None
col_names = name.split(COLUMN_NAME_DELIMITER)
if len(col_names) == 2:
name, json_key = col_names
try:
col = cls.__table__.c[name]
except KeyError:
log.error("Invalid column name: %s", name)
return None
return (getattr(cls, col.name), json_key)
We then have a `_build_column` method that essentially is responsible for
retrieving the details necessary to construct the column for the select
statement necessary for constructing the SQLAlchemy ORM query:
def _build_column(self):
field_name = self.db.get("column")
model = self._object.get("model")
column_type, column_key = self.db.get("type"), self.db.get("key")
select_column, json_key = model.get_column_and_json_key_from_sql_name(
field_name
)
select_column = self._construct_json_select_field(
column_type, select_column, json_key, column_key
)
return select_column
What I'm trying to figure out is how to dynamically generate SQLAlchemy ORM
statements based on the formula. The formulas can be any math equation
using +, -, /, *, and parentheses:
formula1 = '"metric:1" + "metric:2" + "metric:3"'
formula2 = '"metric:1" + "metric:2" - "metric:3"'
formula3 = '"metric:1" + ("metric:2" * "metric:3")'
formula4 = '"metric:1" / "metric:2"'
formula5 = '"metric:1 / ("metric:2" * "metric:3")'
The InstrumentedAttribute objects I mentioned earlier are the select fields
for each individual metric - what I need to figure out how to do is to be
able to build a SQLAlchemy ORM query by parsing the formula string, and
evaluating each operation in the context of the query. `func.sum` would
work for formula1, but I'd need to iteratively build the formula with
SQLAlchemy ORM helper methods for formula2 - formula5.
Per +Jonathan Vanasco's last comment, I've already figured out Phase 1. I'm
stuck on Phase 2.
On Thursday, March 28, 2019 at 4:43:56 PM UTC-4, Ian Miller wrote:
>
> Hello all,
>
> I am in the process of trying to create a dynamic expression query engine
> in an application I'm working on.
>
> So there is a formula that gets defined like so:
>
> formula = '"metric:123" + "metric:456" + "metric:789"'
>
> Each metric maps to a column in the database tables - long story short,
> I'm able to retrieve the metric by ID, and instantiate an
> InstrumentedAttribute object that has the SQLAlchemy metadata for the
> associated column. What I'm trying to achieve is to be able to iterate
> through the formula, and dynamically build a SQLALchemy query that maps to
> the formula.
>
> For example, the formula defined above would look something like this in
> SQL:
>
> SELECT post.id + campaign.id + asset.id
> FROM post, campaign, asset
> WHERE ......;
>
> The idea is to translate the above to something like:
>
> session.query(<sqlalchemy.orm.attributes.InstrumentedAttribute object at
> 0x7ff9269f92b0> + <sqlalchemy.orm.attributes.InstrumentedAttribute object
> at 0x7ff9269c5990> + <sqlalchemy.orm.attributes.InstrumentedAttribute
> object at 0x7ff926896048>).all()
>
> I've tried a couple of approaches of dynamically generating the SQLAlchemy
> ORM query, but I haven't been able to find anything that works. Would
> anyone have any idea or tips on how to accomplish this?
>
> Thank you!
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
