unicode literals cause migrations to fail

[sqlalchemy-bot]

Migrated issue, originally created by Bruk Habtu (@brukhabtu)

Columns

from __future__ import unicode_literals
class Foo(BaseModel):
    __tablename__ = 'foo'
    __table_args__ = {'schema': 'bar'}
    mimetype = Column(String, nullable=False, server_default='application/pdf'){code}

The server_default unicode literal causes migrations to fail with the following traceback

[localhost] local: alembic -x name=boss_stage -x host=localhost revision --autogenerate
INFO  [env_py] *** Running migrations ***
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/bruk/.virtualenvs/bean/bin/alembic", line 9, in <module>
    load_entry_point('alembic==0.7.6', 'console_scripts', 'alembic')()
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/config.py", line 439, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/config.py", line 433, in main
    self.run_cmd(cfg, options)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/config.py", line 416, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/command.py", line 113, in revision
    script.run_env()
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/script.py", line 390, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/util.py", line 243, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 387, in <module>
    Migrator(_initialize(context.config)).migrate()
  File "alembic/env.py", line 130, in migrate
    self._migrate_online()
  File "alembic/env.py", line 187, in _migrate_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/environment.py", line 738, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/migration.py", line 300, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/command.py", line 95, in retrieve_migrations
    autogen._produce_migration_diffs(context, template_args, imports)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/autogenerate/api.py", line 154, in _produce_migration_diffs
    autogen_context, object_filters, include_schemas)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/autogenerate/api.py", line 256, in _produce_net_changes
    inspector, metadata, diffs, autogen_context)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/autogenerate/compare.py", line 111, in _compare_tables
    diffs, autogen_context, inspector):
  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/autogenerate/compare.py", line 200, in _compare_columns
    col_diff, autogen_context
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/autogenerate/compare.py", line 588, in _compare_server_default
    rendered_conn_default
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/migration.py", line 414, in _compare_server_default
    rendered_column_default)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/alembic/ddl/postgresql.py", line 54, in compare_server_default
    rendered_metadata_default
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 844, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 906, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
    statement, parameters
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/bruk/.virtualenvs/bean/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "/"
LINE 1: ... 'application/pdf'::character varying = 'u'application/pdf''
                                                                 ^
 [SQL: "SELECT 'application/pdf'::character varying = 'u'application/pdf''"]

Fatal error: local() encountered an error (return code 1) while executing 'alembic -x name=boss_stage -x host=localhost revision --autogenerate'

Removing the unicode_literals import fixes the issue above. Does alembic using repr() on the arguments passed to the the Column.init method? Notice how the string is quoted

'u'application/pdf''

Thanks!

[sqlalchemy-bot]

Changes by Bruk Habtu (@brukhabtu):

• edited description

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: autogenerate - detection
• added labels: autogenerate - rendering

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "fasttrack"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

normally you'd show me the actual rendered script here, I hope you arent running autogenerations into scripts and then straight to your database (docs warn against this)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this is in detection. wow OK I totally thought this was something else.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: autogenerate - rendering
• added labels: autogenerate - server defaults

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed milestone (was: "fasttrack")

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

that string is from repr() because 99% of the time it is used to generate Python code. PG's server default comparison is just an optional hackish feature where you'll note in most cases I can't really fix the issues people have with it. but you're in luck.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Fixed issue in PG server default comparison where model-side defaults
  configured with Python unicode literals would leak the "u" character
  from a repr() into the SQL used for comparison, creating an invalid
  SQL expression, as the server-side comparison feature in PG currently
  repurposes the autogenerate Python rendering feature to get a quoted
  version of a plain string default.
  fixes unicode literals cause migrations to fail #324

→ 1c67b1a

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

still has many more failure cases. thanks for reporting.