you can alter SQL text at execution time using the before_cursor_execute() event, see the example at https://github.com/sqlalchemy/sqlalchemy/wiki/SessionModifiedSQL . I just updated some out of date things a bit so if there's still problems let me know.
On Wed, May 8, 2019 at 1:26 PM Jacob Jensen <[email protected]> wrote: > > 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 <[email protected]> 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 [email protected]. >>> > To post to this group, send email to [email protected]. >>> > 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 [email protected]. > To post to this group, send email to [email protected]. > 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. -- 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 [email protected]. To post to this group, send email to [email protected]. 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%2BRjkXE9Cqk2nVMMGu%3Db2f%2B10RhDy8CHVeF0V%3DhUASHeS2B%2BCw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
