I just saw prefix_with. It works correctly! Thanks. Still curious if there's a "direct text edit" approach that works with the query API.
On Wednesday, May 8, 2019 at 1:22:32 PM UTC-4, Jacob Jensen wrote: > > My attempted solution in the meantime: Render the query, edit, execute. I > have run into several different errors. > > > > query = ... # my complex query, includes params that do not > render with literal_bind. > # Directly edit query text and rebuild to add MAX_EXECUTION_TIME > hint > query_statement_compiled = query.statement.compile() > query_binds = [bindparam(key=name, value=bp.value, type_=bp.type) > for bp, name in query_statement_compiled.bind_names.iteritems()] > query_str = str(query_statement_compiled).replace('"', '`') > sel_ind = query_str.index('SELECT') > # Insert the hint directly after '...SELECT' > hint_str = ' /*+ MAX_EXECUTION_TIME(1000) */' > # hint_str = '' > query_str_with_hint = query_str[:sel_ind + 6] + hint_str + > query_str[sel_ind+6:] > query_stmt_with_hint = text(query_str_with_hint, bindparams= > query_binds) > query = db.session.query(ProvenancePrototype).from_statement( > query_stmt_with_hint) > > > query.all() # Fails. > > > > > > > > On Wednesday, May 8, 2019 at 10:11:58 AM UTC-4, Mike Bayer wrote: >> >> 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 <2tim...@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 sqlal...@googlegroups.com. >> > To post to this group, send email to sqlal...@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/37d7c086-7b0d-4f94-8137-49916827b2df%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.