Re: [sqlalchemy] Proper way to handle new 128 character identifier limit in Oracle >= 12.2
I think you would actually want to query v$parameter since "compatible" can be set to a lower version than server which disables 128 lengths. SQL> select value from v$parameter where name = 'compatible'; VALUE -- 12.2.0 On Thursday, November 8, 2018 at 10:30:19 AM UTC-6, Mike Bayer wrote: > > On Thu, Nov 8, 2018 at 11:23 AM 'Van Klaveren, Brian N.' via > sqlalchemy > wrote: > > > > Hi, > > > > Oracle 12.2 now allows 128 character length identifiers: > > > > > https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-64283AD6-0939-47B0-856E-5E9255D7246B > > > > > It'd be great if sqlalchemy knew about this, but what's the proper way > of handling this? Just use the existing dialect and monkey patch > max_identifier_length or create a new dialect? > > for now monkeypatch, and for the fix, we would need to look at the > server version info and modify the variable accordingly, here: > > > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/oracle/base.py#L1087 > > > Pull requests welcome on this or at least we can create an issue in > bitbucket to track this. > > > > > > Thanks, > > Brian > > > > > > -- > > 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. > > 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/41e601e2-d077-4d5c-93ea-4302d02df591%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] MySQL optimizer hints not working with Query API
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 <2times...@gmail.com> 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 <2tim...@gmail.com> wrote:
>>> >
>>> > I'm trying to use the query API to create a MySQL query with the the
>>> > MAX_EXECUTION_TIME(3) 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(3)")
>>> > base_query_with_hint.execute() # THIS DOES NOT WORK.
>>> >
>>> > str(base_query_with_hint)
>>> > # Output
>>> > # Select a, b, c from MyTable MAX_EXECUTION_TIME(3)
>>> >
>>> > The hint is just rendered at the end of the query, apparently!
>>> >
>>> > It should render as
>>> >
>>> > Select /*+ MAX_EXECUTION_TIME(3) */ 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
Re: [sqlalchemy] MySQL optimizer hints not working with Query API
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(3) 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(3)")
>> > base_query_with_hint.execute() # THIS DOES NOT WORK.
>> >
>> > str(base_query_with_hint)
>> > # Output
>> > # Select a, b, c from MyTable MAX_EXECUTION_TIME(3)
>> >
>> > The hint is just rendered at the end of the query, apparently!
>> >
>> > It should render as
>> >
>> > Select /*+ MAX_EXECUTION_TIME(3) */ 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
Re: [sqlalchemy] MySQL optimizer hints not working with Query API
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(3) 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(3)")
> > base_query_with_hint.execute() # THIS DOES NOT WORK.
> >
> > str(base_query_with_hint)
> > # Output
> > # Select a, b, c from MyTable MAX_EXECUTION_TIME(3)
> >
> > The hint is just rendered at the end of the query, apparently!
> >
> > It should render as
> >
> > Select /*+ MAX_EXECUTION_TIME(3) */ 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/f571a904-acee-4318-b8f8-2b0bb4ff55da%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] MySQL optimizer hints not working with Query API
Update, please use prefix_with(), sorry I forgot this goes into the right place:
query.prefix_with("/*+ MAX_EXECUTION_TIME(3) */")
will add a note to the docs
On Wed, May 8, 2019 at 10:11 AM 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 <2times...@gmail.com> wrote:
> >
> > I'm trying to use the query API to create a MySQL query with the the
> > MAX_EXECUTION_TIME(3) 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(3)")
> > base_query_with_hint.execute() # THIS DOES NOT WORK.
> >
> > str(base_query_with_hint)
> > # Output
> > # Select a, b, c from MyTable MAX_EXECUTION_TIME(3)
> >
> > The hint is just rendered at the end of the query, apparently!
> >
> > It should render as
> >
> > Select /*+ MAX_EXECUTION_TIME(3) */ 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%2BRjkXHjfyXW6K3aV4HG%2BZXFezPBYoq_rTG3_qDyaQxQaLmGsQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] MySQL optimizer hints not working with Query API
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(3) 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(3)") > base_query_with_hint.execute() # THIS DOES NOT WORK. > > str(base_query_with_hint) > # Output > # Select a, b, c from MyTable MAX_EXECUTION_TIME(3) > > The hint is just rendered at the end of the query, apparently! > > It should render as > > Select /*+ MAX_EXECUTION_TIME(3) */ 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.
Re: [sqlalchemy] MySQL optimizer hints not working with Query API
Hi there- I see you are using str() to get the string representation, which will not give you a MySQL representation. DId you read https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined ? 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(3) 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(3)") > base_query_with_hint.execute() # THIS DOES NOT WORK. > > str(base_query_with_hint) > # Output > # Select a, b, c from MyTable MAX_EXECUTION_TIME(3) > > The hint is just rendered at the end of the query, apparently! > > It should render as > > Select /*+ MAX_EXECUTION_TIME(3) */ 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%2BRjkXFL9n0g1t9FTPv7z%3DzbkvBWkjuPmqcg8S989CoczQy0fg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] MySQL optimizer hints not working with Query API
I'm trying to use the query API to create a MySQL query with the the MAX_EXECUTION_TIME(3) 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(3)") base_query_with_hint.execute() # THIS DOES NOT WORK. str(base_query_with_hint) # Output # Select a, b, c from MyTable MAX_EXECUTION_TIME(3) The hint is just rendered at the end of the query, apparently! It should render as Select /*+ MAX_EXECUTION_TIME(3) */ 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.
