AttributeError using with_hint

[R. Grout]

With SQLAlchemy 1.1.9.

I've been trying to figure out how to get indexing hints into a MySQL query. I've been unsuccessful, but along the way, I ran across this AttributeError. When the dialect is set to 'mysql', the error isn't raised, but the hint does not appear in the query string. The default dialect of '*' does raise the AttributeError.

Running this function:

def hint():
    table = sql.table('runs')

    query = sql.select([table]).with_hint('runs', 'USE INDEX(my_index)').select_from(table)
    print(query)

When the dialect is "mysql", I'd expect the following query string

SELECT  
FROM runs USE INDEX(my_index)

but instead I get

SELECT 
FROM runs

When the dialect is '*' (the default), I get the following attribute error.

Traceback (most recent call last):
  File "sqltest.py", line 11, in <module>
    hint()
  File "sqltest.py", line 9, in hint
    print(query)
  File "lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 446, in __str__
    return str(self.compile())
  File "<string>", line 1, in <lambda>
  File "lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 436, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 435, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 216, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1696, in visit_select
    hint_text, byfrom = self._setup_select_hints(select)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1770, in _setup_select_hints
    select._hints.items()
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1771, in <listcomp>
    if dialect in ('*', self.dialect.name)
AttributeError: 'str' object has no attribute '_compiler_dispatch'

[Mike Bayer]

look carefully at the documentation for with_hint(). the first argument, "selectable", is a selectable object, not a string. There is a usage example in the docstring.

your example:

>>> from sqlalchemy import sql, types
>>> from sqlalchemy.dialects import mysql
>>> 
>>> table = sql.table('runs', sql.column('x', types.Integer))
>>> 
>>> query = sql.select([table]).with_hint(table, 'USE INDEX(my_index)').select_from(table)
>>> print(query.compile(dialect=mysql.dialect()))
SELECT runs.x 
FROM runs USE INDEX(my_index)

please post usage questions to the sqlalchemy mailing list as requested in the bug reporting guidelines. thanks!