example between_ helper and building SQL queries.

[dibsonmuad]

Hi Toby,

If I were to implement a between_ helper, like and_, or_ etc. in expressions.py, would it look something like this?

def between_(column, low, high):
    """
    Helper to create an instance of the BETWEEN Operator.

    Example:
        >>> between_(x, 1, 10)
        'x BETWEEN 1 AND 10'

    Args:
        column: an instance of Column.
        low: the low end of the range.
        high: the high end of the range.

    Returns:
        Between: an instance of Between
    """
    return Between(this=column,
                   low=maybe_parse(low),
                   high=maybe_parse(high))

Are there examples that show creating various AST objects? How do I indicate that I need to parse a Literal instead of an identifier? I'm interested in generating SQL/HIVE queries programmatically.

Thanks
Sriram

[tobymao]

you can probably just call it between without the underscore

can you do x between y and z? they don't necesasrily have to be literals right?

you also need to do maybe_parse the column

[dibsonmuad]

you can probably just call it between without the underscore

Thanks. This was copy paste edited from expressions.py :). Would you consider a PR or add to a future release?

can you do x between y and z? they don't necesasrily have to be literals right?

Yeah BETWEEN can take text. When I call maybe_parse("blah"), it returns:

(COLUMN this:
  (IDENTIFIER this: blah, quoted: False))

However, maybe_parse(1) evaluates to:

(LITERAL this: 1, is_string: False)

If I explicitly instantiate like this: Literal(this="blah", is_string=True), I'm seeing the expected

(LITERAL this: blah, is_string: True)

Which is what I want when I'm comparing strings. Right? So how do I tell maybe_parse to parse a string literal? Hope I'm asking the right question.

you also need to do maybe_parse the column
Ok.

[tobymao]

what did you copy paste? there is no def between?

what if you want between 1 + 2 and x

then you don't want it to be a literal

[dibsonmuad]

I copied another function from expressions.py. Just as a starting point. Copied the convention (or_, and_ etc.).

Oh yeah duh (on 1+ 2). Thanks!

[tobymao]

and_ and or_ are reserved keywords

[tobymao]

yes, if you make a PR and add tests i will gladly accept it, thanks

[dibsonmuad]

Along the lines of between, I am testing

def in_(column, expressions):
    """
    Helper for sql IN operator.

    Example:
        >>> in_("x", (10, 20, 30)).sql()
        'x IN (10, 20, 30)'

        >>> parse_one("SELECT x FROM t").where(in_(x, (10, 20, 30))).sql()
        'SELECT x FROM t WHERE x IN (10, 20, 30)'

    Args:
        column: an instance of Column or a column identifier.
        expressions: a sequence of expression instances or literals.

    Returns:
        In: an instance of In
    """
    return In(this=maybe_parse(column),
              expressions=tuple(maybe_parse(e) for e in expressions)
              )

However, in_("x", ["a", "b", "c"]).sql() returns x in (a, b, c) instead of x in ("a", "b", "c"). What am I doing wrong?
Thanks.

[tobymao]

you'll need to call exp.convert() instead of maybe_parse

parsing "a" parses into a column. exp.convert('a') -> exp.Literal.string('a')