add_column does not modify table.columns?

[Felix Schwarz]

Hey,

I built my migration using

t = Table('...', metadata, ...)

Now I can use alembic's "add_column()" like
add_column(t.name, foo_column)

My problem is that the next statement produces invalid sql:
execute(
t.update().\
values({foo_column.name: ...})
)
because the update statement is issued without any fields to update.

I guess this is because table.columns does not contain the new column. However
something like 'table.append_column' does not work because SQLAlchemy
complains that the column already has a parent.

Am I'm doing something stupid here? How can I force a column to appear in
table.columns?

fs

PS: I'm using an older version of alembic here but I think I encountered the
problem also with alembic 0.5. Maybe it's a known bug which was already fixed
since then?

[Michael Bayer]

On Aug 16, 2013, at 11:16 AM, Felix Schwarz <felix....@oss.schwarz.eu> wrote:

> Hey,
>
> I built my migration using
>
> t = Table('...', metadata, ...)
>
> Now I can use alembic's "add_column()" like
> add_column(t.name, foo_column)
>
> My problem is that the next statement produces invalid sql:
> execute(
> t.update().\
> values({foo_column.name: ...})
> )
> because the update statement is issued without any fields to update.
>
> I guess this is because table.columns does not contain the new column.

op.add_column() has nothing to do with the "Table" object you've created, there's no connection between these two elements.

Normally when I'm doing an add_column() and I also want to do an update(), I just create a lightweight table() upfront with the columns I need:

t = table("my_table", column("col1"), column("col2"))

op.execute(t.update().values(col1='widget'))

the docs have an example of this kind of thing: https://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.Operations.execute

> However
> something like 'table.append_column' does not work because SQLAlchemy
> complains that the column already has a parent.

right that Column you pass in is used by add_column() and it was not anticipated that this Column would have been re-used anywhere else. I suppose that's worth mentioning in the docs for now.

[Felix Schwarz]

Am 16.08.2013 20:14, schrieb Michael Bayer:
>> I guess this is because table.columns does not contain the new column.
>
> op.add_column() has nothing to do with the "Table" object you've created, there's no connection between these two elements.
>
> Normally when I'm doing an add_column() and I also want to do an update(), I just create a lightweight table() upfront with the columns I need:

To me there are couple of different points which confuse me:
1. I kind of expected that my column wouldn't be connected to my table (even
though it would be nice). However I didn't find out how to manually add the
column to my Table after add_column().
2. I'd like to avoid duplicating the table definition as I use
add_column/update quite often (e.g. to add a column with "NOT NULL" and a
non-server-side default)
3. Could alembic/SQLAlchemy please throw an exception if 'values' references
a column name which is unknown for the given table? Silently dropping the
value can lead to some longer debugging/makes a nasty surprise.

But talking more in terms of the big picture: Do you think the problem could
be solved in alembic/SQLAlchemy and if so, which parts should be adapted? Or
are you against changing the current behavior at all?

fs

[Michael Bayer]

On Aug 17, 2013, at 8:07 AM, Felix Schwarz <felix....@oss.schwarz.eu> wrote:

>
> Am 16.08.2013 20:14, schrieb Michael Bayer:
>>> I guess this is because table.columns does not contain the new column.
>>
>> op.add_column() has nothing to do with the "Table" object you've created, there's no connection between these two elements.
>>
>> Normally when I'm doing an add_column() and I also want to do an update(), I just create a lightweight table() upfront with the columns I need:
>
> To me there are couple of different points which confuse me:
> 1. I kind of expected that my column wouldn't be connected to my table (even
> though it would be nice). However I didn't find out how to manually add the
> column to my Table after add_column().

if you have a Table you can use append_column() on it, it's just this Column can't be associated with any other table. The alembic op.add_column() associates it with a table internally. I can try having it detach but I'd need to add a lot of tests to ensure it is fully detached, as there's a lot of state stuff that goes on when that happens.

> 2. I'd like to avoid duplicating the table definition as I use
> add_column/update quite often (e.g. to add a column with "NOT NULL" and a
> non-server-side default)

there's no duplication of table definition here. We're talking about a Column, but if you need that Column to take place in an UPDATE statement separate from the op.add_column(), no "NOT NULL", type information, or anything else is needed. Just name your column by name, as I illustrated:

t = table("tablename", column("columnname"))

no MetaData needed, no table options, no types, no constraints, no foreign keys, no other columns besides that which you want in your UPDATE statement. It is an extremely minimal form of table metadata.

> 3. Could alembic/SQLAlchemy please throw an exception if 'values' references
> a column name which is unknown for the given table? Silently dropping the
> value can lead to some longer debugging/makes a nasty surprise.

it does. in 0.7 it's a warning and in 0.8 becomes an exception, see http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html#unconsumed-column-names-warning-becomes-an-exception

demo:

from sqlalchemy import *
from sqlalchemy.sql import table, column

from sqlalchemy import __version__
if __version__ < '0.8.0':
import warnings
warnings.filterwarnings("error")

m = MetaData()
t1 = Table('t', m, Column('a', Integer), Column('b', Integer))

t2 = table('t', column('a'), column('b'))

try:
print(t1.update().values(c='asdf'))
except:
pass
else:
assert False

try:
print(t2.update().values(c='asdf'))
except:
pass
else:
assert False

[Felix Schwarz]

Am 17.08.2013 17:19, schrieb Michael Bayer:
> if you have a Table you can use append_column() on it, it's just this
> Column can't be associated with any other table. The alembic
> op.add_column() associates it with a table internally. I can try having it
> detach but I'd need to add a lot of tests to ensure it is fully detached,
> as there's a lot of state stuff that goes on when that happens.

I had a simpler idea (though I'm not sure if it makes sense obviously): Could
add_column() not accept a full Table instead of a plain string name? That way
it could work on my real table and there would be no need to detach any
internal state.

> it does. in 0.7 it's a warning and in 0.8 becomes an exception, see
> http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html#unconsumed-column-names-warning-becomes-an-exception

Ah, great. Sorry, I didn't try 0.6, my most up-to-date project still uses 0.5.

fs

[Michael Bayer]

On Aug 19, 2013, at 3:02 AM, Felix Schwarz <felix....@oss.schwarz.eu> wrote:

>
> Am 17.08.2013 17:19, schrieb Michael Bayer:
>> if you have a Table you can use append_column() on it, it's just this
>> Column can't be associated with any other table. The alembic
>> op.add_column() associates it with a table internally. I can try having it
>> detach but I'd need to add a lot of tests to ensure it is fully detached,
>> as there's a lot of state stuff that goes on when that happens.
>
> I had a simpler idea (though I'm not sure if it makes sense obviously): Could
> add_column() not accept a full Table instead of a plain string name? That way
> it could work on my real table and there would be no need to detach any
> internal state.

how would that look exactly ?

>
>> it does. in 0.7 it's a warning and in 0.8 becomes an exception, see
>> http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html#unconsumed-column-names-warning-becomes-an-exception
>
> Ah, great. Sorry, I didn't try 0.6, my most up-to-date project still uses 0.5.

alembic works with 0.5 ?!

[Felix Schwarz]

Am 19.08.2013 16:51, schrieb Michael Bayer:
> On Aug 19, 2013, at 3:02 AM, Felix Schwarz <felix....@oss.schwarz.eu> wrote:
>> I had a simpler idea (though I'm not sure if it makes sense obviously): Could
>> add_column() not accept a full Table instead of a plain string name? That way
>> it could work on my real table and there would be no need to detach any
>> internal state.
>
> how would that look exactly ?

foo_table = Table(...)
new_column = Column('....')
add_column(foo_table, new_column)
# afterwards I'd expect that new_column is present in foo_table.columns

Stupid idea?

>>> it does. in 0.7 it's a warning and in 0.8 becomes an exception, see
>>> http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html#unconsumed-column-names-warning-becomes-an-exception
>>
>> Ah, great. Sorry, I didn't try 0.6, my most up-to-date project still uses 0.5.
>
> alembic works with 0.5 ?!

Sorry I mixed up alembic and SQLAlchemy here. I referred to the alembic
versions but you were pointing to SQLAlchemy obviously.

fs

[Michael Bayer]

On Aug 19, 2013, at 11:00 AM, Felix Schwarz <felix....@oss.schwarz.eu> wrote:

>
>
> Am 19.08.2013 16:51, schrieb Michael Bayer:
>> On Aug 19, 2013, at 3:02 AM, Felix Schwarz <felix....@oss.schwarz.eu> wrote:
>>> I had a simpler idea (though I'm not sure if it makes sense obviously): Could
>>> add_column() not accept a full Table instead of a plain string name? That way
>>> it could work on my real table and there would be no need to detach any
>>> internal state.
>>
>> how would that look exactly ?
>
> foo_table = Table(...)
> new_column = Column('....')
> add_column(foo_table, new_column)
> # afterwards I'd expect that new_column is present in foo_table.columns
>
> Stupid idea?

im not getting the use case where you are making a Table and using append_column(), why can't you just create the Table with all the final Column objects at once? I understand the Table doesn't have that Column yet in the database, but it is unusual for a migration to work with a table before the new columns are added, and you can still emit UPDATE statements against that table, your new column won't be rendered if you don't refer to it.

>
>>>> it does. in 0.7 it's a warning and in 0.8 becomes an exception, see
>>>> http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html#unconsumed-column-names-warning-becomes-an-exception
>>>
>>> Ah, great. Sorry, I didn't try 0.6, my most up-to-date project still uses 0.5.
>>
>> alembic works with 0.5 ?!
>
> Sorry I mixed up alembic and SQLAlchemy here. I referred to the alembic
> versions but you were pointing to SQLAlchemy obviously.

oh, duh, my mistake, I don't pay attention to Alembic numbers the way I do SQLAlchemy ones.