I'm not at a computer right now but what you need to do is write a simple
recursive descent parser, which makes use of the objects in
sqlalchemy.sql.operators for the operators as it parses the string into an
expression tree. This is actually a fun classic computer problem I'm sure
someone can show here or I can demo one later.
On Fri, Mar 29, 2019, 2:31 PM Ian Miller <irmille...@gmail.com> wrote:
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
