On Fri, Mar 29, 2019 at 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.
im still finding it strange when you say InstrumentedAttribute, as the
table.c[name] objects are Column objects, but I don't see what
_construct_json_select_field is doing but I would again hope that you
are not progammatically creating InstrumentedAttribute objects as they
are not intended to be publicly constructable nor is there any reason
to do so.
Here's a simplified version of what you describe that uses a short
series of functions to parse the strings into expression fragments
directly from a given model class and to run an ORM query, while the
parsing is not robust against syntactical problems and may likely have
other bugs outside of the four test expressions, it will hopefully
illustrate the basic parts of the full front-to-back for this kind of
thing:
import operator
import re
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
def parse_formula(model, formula):
return _parse_tokens(model, _tokenize(formula))
def _tokenize(strval):
return [tok for tok in re.split(r"\s*([\(\)\+\*\/\-])\s*", strval) if tok]
_operator_lookup = {
"+": operator.add,
"-": operator.sub,
"*": operator.mul,
"/": operator.truediv,
}
def _parse_tokens(model, tokens):
if not tokens:
return None
expr = None
while tokens:
if expr is None:
tok = tokens.pop(0)
if tok == "(":
left_expr = _parse_tokens(model, tokens)
assert tokens.pop(0) == ")"
else:
left_expr = _resolve_model(model, tok)
else:
left_expr = expr
tok = tokens.pop(0)
if tok == ")":
tokens.insert(0, tok)
return expr
operator = _operator_lookup[tok]
tok = tokens.pop(0)
if tok == "(":
right_expr = _parse_tokens(model, tokens)
assert tokens.pop(0) == ")"
else:
right_expr = _resolve_model(model, tok)
expr = operator(left_expr, right_expr)
return expr
def _resolve_model(model, expr):
m = re.match(r'^"(\w+)\:(\d)+"$', expr)
if not m:
raise ValueError("Can't resolve symbol: %s" % expr)
attrname = "col%s" % (m.group(2))
return getattr(model, attrname)
Base = declarative_base()
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
col1 = Column(Integer)
col2 = Column(Integer)
col3 = Column(Integer)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(col1=14.231, col2=7.913, col3=20.115))
s.commit()
for formula in [
'"metric:1" + "metric:2" + "metric:3"',
'"metric:1" + "metric:2" - "metric:3"',
'"metric:1" + ("metric:2" * "metric:3")',
'"metric:1" / "metric:2"',
'"metric:1" / ("metric:2" * "metric:3")',
]:
print(parse_formula(A, formula))
print(s.query(parse_formula(A, formula)).first())
>
> 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.
