add visit_sequence to StrSQLCompiler

Issue #4144 new
Michael Overmeyer
created an issue

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:

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.

Comments (2)

  1. Michael Bayer repo owner

    there are many constructs that do not print on the default dialect. In very recent versions of SQLAlchemy, we are beginning to provide non-SQL-compliant stringification in the specific case of "print" via the StrSQLCompiler. However, in general if you are printing statements you're going to want to compile to the PG dialect: print(stmt.compile(engine)) or print(stmt.compile(dialect=postgresql.dialect()). See http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined for background.

  2. Log in to comment