Additionally, those hints seem to be a new feature (http://mysqlserverteam.com/whats-new-in-mysql-5-7-first-release-candidate/) for a hint format that was previously not present in MySQL and don't seem to be present in MariaDB either, and surprisingly enough they look just like Oracle optimizer hints. Unfortunately the MySQL dialect in SQLAlchemy does not support this format of MySQL optimizer hint at this time, and this would need to be new feature add. It also might be fairly complicated to pull off since these hints don't apply to all MySQL versions, there might need to be new API features added, not sure.
I've added https://github.com/sqlalchemy/sqlalchemy/issues/4667 to track this feature. On Wed, May 8, 2019 at 9:31 AM Jacob Jensen <2times...@gmail.com> wrote: > > I'm trying to use the query API to create a MySQL query with the the > MAX_EXECUTION_TIME(30000) hint. > > My code is roughly: > > from flask_sqlalchemy import SQLAlchemy > > class MyTable(SQLAlchemy().Model): > ... > > base_query = Sqlalchemy.session.query(MyTable) # This works when I execute it. > base_query_with_hint = base_query.with_hint(MyTable, > "MAX_EXECUTION_TIME(30000)") > base_query_with_hint.execute() # THIS DOES NOT WORK. > > str(base_query_with_hint) > # Output > # Select a, b, c from MyTable MAX_EXECUTION_TIME(30000) > > The hint is just rendered at the end of the query, apparently! > > It should render as > > Select /*+ MAX_EXECUTION_TIME(30000) */ a, b, c from MyTable > > I want it to behave exactly like > https://docs.sqlalchemy.org/en/13/core/selectable.html#sqlalchemy.sql.expression.Select.with_hint > but I'm using the Query API instead of the Select API. > > Another possibility: A commenter in stackoverflow suggested this is an issue > with the MySQL dialect not the query API. > > > See comments here (they suggested I come to this mailing list): > https://stackoverflow.com/questions/56030825/sqlalchemy-query-api-not-working-correctly-with-hints > > -- > 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/789cd456-ce14-4624-a363-446845d94a2d%40googlegroups.com. > 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXHowbxR-nBDqTp6xrzzD2FvDozm%3Dr9h-fMYLKGxLVsJng%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
