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.
