[Sqlalchemy-tickets] Issue #4144: <sqlalchemy.dialects.postgresql.dml.Insert>.__str__() fails when printing dialect-specific columns. (zzzeek/sqlalchemy)

[Michael O. <iss...@bi...>]

New issue 4144: <sqlalchemy.dialects.postgresql.dml.Insert>.__str__() fails when printing dialect-specific columns.
https://bitbucket.org/zzzeek/sqlalchemy/issues/4144/__str__-fails-when-printing-dialect

Michael Overmeyer:

I was trying to debug an issue I was having with my PostgreSQL query and wanted to print it out in order to see the SQL.

But I ran into an issue where the `print stmt` was failing.

Running this example code:

```
#!python

from sqlalchemy import Column, Integer, Sequence, String
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class FileMetadata(Base):
    __tablename__ = 'filenames'

    iid = Column(Integer, Sequence('{}_iid_seq'.format(__tablename__)))
    filename = Column(String, primary_key=True)

    @staticmethod
    def print_test_insert():
        table = FileMetadata.__table__
        stmt = postgresql.insert(table).values({'filename': "hello.txt"})
        print postgresql.dialect, type(stmt)
        print stmt


FileMetadata.print_test_insert()

```

You get an unexpected exception:

```
Traceback (most recent call last):
  File "test_bug.py", line 22, in <module>
    FileMetadata.print_test_insert()
  File "test_bug.py", line 19, in print_test_insert
    print stmt
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 448, in __str__
    return unicode(self.compile()).encode('ascii', 'backslashreplace')
  File "<string>", line 1, in <lambda>
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 436, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 442, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 435, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 216, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1968, in visit_insert
    self, insert_stmt, crud.ISINSERT, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 57, in _setup_crud_params
    return _get_crud_params(compiler, stmt, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 137, in _get_crud_params
    _col_bind_name, check_columns, values, kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 290, in _scan_cols
    values, kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 477, in _append_param_insert_hasdefault
    proc = compiler.process(c.default, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 909, in visit_sequence
    self.dialect.name
NotImplementedError: Dialect 'default' does not support sequence increments.

```

I would have expected it to work and printed something like:

```
INSERT INTO filenames (iid, filename) VALUES (nextval('filenames_iid_seq'), :filename)
```

As the Insert statement was created using the postgresql dialect.

It seems to be using the SQLCompiler.visit_sequence() instead of the expected PGCompiler.visit_sequence().


Tested with both SQLAlchemy 1.1 and master branch, on both CentOS 7 and Windows with Python 2.7.