Using OData with raw SQL

Using a raw SQL interface is slightly more involved and less powerful, but offers a lot of flexibility in return.

Parsing the OData Query

To get from a string representing an OData query to a usable representation, we need to tokenize and parse it as follows:

from odata_query.grammar import ODataParser, ODataLexer

lexer = ODataLexer()
parser = ODataParser()
ast = parser.parse(lexer.tokenize(my_odata_query))

This process is described in more detail in Parsing OData.

Optional: Modifying the AST

There are cases where you’ll want to modify the query before executing it. That’s what NodeTransformer’s are for!

One example might be that certain fields are exposed to end users under a different name than the one in the database. In this case, the odata_query.rewrite.AliasRewriter will come in handy. Just pass it a mapping of aliases to their full name and let it do its job:

from odata_query.rewrite import AliasRewriter

rewriter = AliasRewriter({
    "name": "author/name",
})
new_ast = rewriter.visit(ast)

Building a Query Filter

To get from an AST to a SQL clause, you’ll need to use odata_query.sql.base.AstToSqlVisitor (standard SQL) or one of its dialect-specific subclasses, such as odata_query.sql.sqlite.AstToSqliteSqlVisitor (SQLite).

from odata_query.sql import AstToSqlVisitor

visitor = AstToSqlVisitor()
where_clause = visitor.visit(ast)

Running the query

Finally, we’re ready to run the query:

query = "SELECT * FROM my_table WHERE " + where_clause
results = conn.execute(query).fetchall()

Supported dialects

class odata_query.sql.base.AstToSqlVisitor(table_alias: Optional[str] = None)

NodeVisitor that transforms an AST into a SQL WHERE clause. Based on SQL-99 as described here: https://crate.io/docs/sql-99/en/latest/

Parameters

table_alias – Optional alias for the root table.

class odata_query.sql.sqlite.AstToSqliteSqlVisitor(table_alias: Optional[str] = None)

NodeVisitor that transforms an AST into a SQLite SQL WHERE clause.

Parameters

table_alias – Optional alias for the root table.

class odata_query.sql.athena.AstToAthenaSqlVisitor(table_alias: Optional[str] = None)

NodeVisitor that transforms an AST into an Athena SQL WHERE clause.

Parameters

table_alias – Optional alias for the root table.