Convert SQL string to pypika.Query

[ranamihir]

Hi,

I'm wondering if there's any function for converting a given SQL string to a pypika.Query object? If not, I think it'd be a very helpful feature. People who are more comfortable (or prefer) writing SQL, but also need programmatic flexibility in certain cases would really benefit a lot.

E.g. something like:

sql = "select * from student # ... potentially some very big query"
query = pypika.convert_string_to_Query(sql)
new_query = query.where(student.name == "John")
new_query.get_sql()

returns:

select * from students # ... potentially some very big query
where student.name == "John"

[sabrikaragonen]

Hi @ranamihir,
Have you worked on it? I need the same feature. I found sqlparse package, but still, it would be perfect to have it in pypika instead of using another one.

[twheys]

For another project that uses pypika, I've written a grammar using sly that parses SQL into PyPika objects. It's not exhaustive and only handles expressions, not the entire query string, however it might serve as a good example for anyone who is interested in extending it. I've shard it as a gist: https://gist.github.com/twheys/5635a932ca6cfce0d114a86fb55f6c80

[twheys]

Here's the code that runs the eval

class ExpressionEvaluator(object):
    def __init__(self, tables):
        self.lexer = PyPikaLexer()
        self.parser = PyPikaParser({table._table_name: table
                                    for table in tables})

    def eval(self, expression):
        assert expression is not None
        tokens = self.lexer.tokenize(expression)
        result = self.parser.parse(tokens)
        if result is None:
            raise ExpressionParseException(f'Unable to parse. Either expression is invalid or unsupported.')
        return result

[zmunro]

@twheys I am trying to use your code, but int your ExpressionEvaluator class you need to pass in the tables, but dont say what the table objects are supposed to be. I tried using the PyPika table object where I defined my tables like this:

db_tables = [
    pypika.Table("table_name", schema="schema").as_("abreviated_name"),
]

but that didn't work. I get the following error:

Original exception was:
Traceback (most recent call last):
  File "sql_converter.py", line 694, in <module>
    print(expression_evaluator.eval(sqlfile))
  File "sql_converter.py", line 670, in eval
    result = self.parser.parse(tokens)
  File "/home/zach/.local/lib/python3.7/site-packages/sly/yacc.py", line 2119, in parse
    tok = self.error(errtoken)
  File "sql_converter.py", line 656, in error
    raise ExpressionSyntaxError(f'Syntax error on line:column {lineno}:{index}, '
__main__.ExpressionSyntaxError: Syntax error on line:column 1:8, unexpected value 'abreviated_name'

and the beginning of the sql file looks something like:

select abreviated_name.column_name
from schema_name.table_name abreviated_name;

[pahwaranger]

For another project that uses pypika, I've written a grammar using sly that parses SQL into PyPika objects. It's not exhaustive and only handles expressions, not the entire query string, however it might serve as a good example for anyone who is interested in extending it. I've shard it as a gist: https://gist.github.com/twheys/5635a932ca6cfce0d114a86fb55f6c80

Spent some time searching for something like this but couldn't find anything. The gist was very helpful. First time with Sly so took some time to get familiar with it.. in doing so I ended up getting 99% of the way to a package so I ended up publishing it here: https://github.com/pahwaranger/sql_to_pypika