render func.XYZ constructs correctly in defaults

[sqlalchemy-bot]

Migrated issue, originally created by ElGans (@delegans)

(description modified from the original)

from alembic import autogenerate
from sqlalchemy import func
from sqlalchemy.schema import DefaultClause
from sqlalchemy.engine import default

print autogenerate._render_server_default(
                DefaultClause(func.now()),
                {"dialect": default.DefaultDialect()})

renders:

'now()'

instead of

func.now()

[sqlalchemy-bot]

ElGans (@delegans) wrote:

It works when I write alembic migration script like this:

#!python

sa.Column('created', sa.DateTime(), server_default=sa.sql.expression.text('current_timestamp'), nullable=False),

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: invalid

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

This is the correct behavior.

http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=column#sqlalchemy.schema.Column

server_default –

A FetchedValue instance, str, Unicode or text() construct representing the DDL DEFAULT value for the column.

String types will be emitted as-is, surrounded by single quotes:

Column('x', Text, server_default="val")

x TEXT DEFAULT 'val'

A text() expression will be rendered as-is, without quotes:

Column('y', DateTime, server_default=text('NOW()'))0

y DATETIME DEFAULT NOW()

Strings and text() will be converted into a DefaultClause object upon initialization.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

so you want to use func.now() consistently each time you use Column.

[sqlalchemy-bot]

ElGans (@delegans) wrote:

Forgot to specify this issue is only for 'revision -auto'.

[sqlalchemy-bot]

ElGans (@delegans) wrote:

--autogenerate does not generate migration script correctly when I set column server_default like this db.func.now()

[sqlalchemy-bot]

ElGans (@delegans) wrote:

but sqlalchemy does ddl correctly for this one

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

DB backend matters quite a bit here. However, it's documented that server_default can't work fully - we don't get back consistent enough information from the database to know what should be quoted and not, and how. Here's the docs for that: http://alembic.readthedocs.org/en/latest/tutorial.html#auto-generating-migrations

Autogenerate can optionally detect:

Change of server default. This will occur if you set compare_server_default=True on EnvironmentContext.configure(). This feature works well for simple cases but cannot always produce accurate results. The Postgresql backend will actually invoke the “detected” and “metadata” values against the database to determine equivalence. The feature is off by default so that it can be tested on the target schema first. Like type comparison, it can also be customized by passing a callable; see the function’s documentation for details.

This possibly can be worked around to some degree by using a column_reflect event that changes the type to have text() surrounding it, for specific strings.

[sqlalchemy-bot]

Carl Meyer (@carljm) wrote:

It seems to me that this bug report was misunderstood and closed incorrectly.

I don't care about detection of changes to a column's server_default, I understand that that is documented to not be reliable. But I don't see why, when initially creating a column for the first time, --autogenerate is unable to handle server_default=sa.func.now() correctly.

Specifically, it generates server_default='now()' in the migration, resulting in a fixed datetime (the datetime the migration was run) being set as the column default.

What you actually want is server_default=sa.func.now() in the migration as well.

Is it really not possible for alembic to generate the right thing here, for a newly-created column?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

yes the original user did not describe this clearly.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: invalid

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• edited description

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "server_default does not work correcly" to "render func.XYZ constructs correctly in defaults"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

see also #111

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: autogenerate - rendering

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Duplicate of #197.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: duplicate
• changed status to closed