Using OData with SQLAlchemy
Basic Usage
The easiest way to add OData filtering to a SQLAlchemy query is with the shorthand:
SQLAlchemy ORM
from odata_query.sqlalchemy import apply_odata_query
orm_query = select(MyModel) # This is any form of Query or Selectable.
odata_query = "name eq 'test'" # This will usually come from a query string parameter.
query = apply_odata_query(orm_query, odata_query)
results = session.execute(query).scalars().all()
SQLAlchemy Core
Attention
Basic support for SQLAlchemy Core is new since version 0.7.0.
It currently does not support relationship traversal or any
/all
yet. Those operations will raise a NotImplementedException
.
from odata_query.sqlalchemy import apply_odata_core
core_query = select(MyTable) # This is any form of Query or Selectable.
odata_query = "name eq 'test'" # This will usually come from a query string parameter.
query = apply_odata_query(core_query, odata_query)
results = session.execute(query).scalars().all()
Advanced Usage
If you need some more flexibility or advanced features, the implementation of the
shorthand is a nice starting point: odata_query.sqlalchemy.shorthand
Let’s break it down real quick:
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 something SQLAlchemy can use, you’ll need to use the
odata_query.sqlalchemy.orm.AstToSqlAlchemyOrmVisitor
(ORM mode) or
the odata_query.sqlalchemy.core.AstToSqlAlchemyCoreVisitor
(Core
mode).
It needs to know about the ‘root model’ or table of your query in order to see
which fields exists and how objects are related.
SQLAlchemy ORM
from odata_query.sqlalchemy.orm import AstToSqlAlchemyOrmVisitor
visitor = AstToSqlAlchemyOrmVisitor(MyModel)
query_filter = visitor.visit(ast)
SQLAlchemy Core
from odata_query.sqlalchemy.core import AstToSqlAlchemyCoreVisitor
visitor = AstToSqlAlchemyCoreVisitor(MyTable)
query_filter = visitor.visit(ast)
Optional: Joins
Attention
Relationship traversal and automatic joins are not yet supported for SQLAlchemy Core mode.
If your query spans relationships, the AstToSqlAlchemyClauseVisitor
will
generate join statements. For the query to work, these will need to be
applied explicitly:
for j in visitor.join_relationships:
query = query.join(j)
Running the query
Finally, we’re ready to run the query:
query = query.where(query_filter)
results = s.execute(query).scalars().all()