About alembic alter column problem
limodou
column, it'll automatically create add and drop statment, just like
this:
op.add_column('bas_grp_user', sa.Column('username', sa.Integer(),
nullable=False))
op.drop_column('bas_grp_user', u'USERNAME')
But when I ran the upgrade I got:
sqlalchemy.exc.OperationalError: (OperationalError) (1060, "Duplicate
column name 'username'") 'ALTER TABLE bas_grp_user ADD COLUMN username
INTEGER NOT NULL' ()
so the problem is that the table is already exists in my database, so
when it excute the first statement it'll be fail. So I think drop
should be put in front of add.
--
I like python!
UliPad <<The Python Editor>>: http://code.google.com/p/ulipad/
UliWeb <<simple web framework>>: http://code.google.com/p/uliweb/
My Blog: http://hi.baidu.com/limodou
limodou
1. if there is no changes should it can create nothing, not like this:
def upgrade():
### commands auto generated by Alembic - please adjust! ###
pass
### end Alembic commands ###
def downgrade():
### commands auto generated by Alembic - please adjust! ###
pass
### end Alembic commands ###
2. If I can merge the current changes into head revision, before I
execute upgrade, so that I can combine several changes into one
change. For me, I think just remove the head revision, and recreate
new one that's ok. And only problem I think is that : if the user has
manually changed the revision file, the changes will be lost.
Michael Bayer
On Apr 12, 2012, at 4:07 AM, limodou wrote:
> I'm using alembic today, and I found a problem, if I changed the
> column, it'll automatically create add and drop statment, just like
> this:
>
> op.add_column('bas_grp_user', sa.Column('username', sa.Integer(),
> nullable=False))
> op.drop_column('bas_grp_user', u'USERNAME')
>
> But when I ran the upgrade I got:
>
> sqlalchemy.exc.OperationalError: (OperationalError) (1060, "Duplicate
> column name 'username'") 'ALTER TABLE bas_grp_user ADD COLUMN username
> INTEGER NOT NULL' ()
this is a column name change - per the documentation, Alembic can't detect these and you need to manually change it to an alter_column(). In this case, there seems to be an odd casing change where the DB is reporting the name as USERNAME in all caps (usually this is oracle or firebird, though alembic should be normalizing these to lowercase....) - but then the database can't distinguish between USERNAME and "username". If this is happening for all your columns then there might be some dialect-related issue at play. Otherwise, if you just changed the name to "USERNAME" in the DB with quotes then this is what you'd get.
Michael Bayer
On Apr 12, 2012, at 4:18 AM, limodou wrote:
> I also have other requirements:
>
> 1. if there is no changes should it can create nothing, not like this:
>
> def upgrade():
> ### commands auto generated by Alembic - please adjust! ###
> pass
> ### end Alembic commands ###
>
>
> def downgrade():
> ### commands auto generated by Alembic - please adjust! ###
> pass
> ### end Alembic commands ###
that's what it should be doing what are you getting ?
>
> 2. If I can merge the current changes into head revision, before I
> execute upgrade, so that I can combine several changes into one
> change. For me, I think just remove the head revision, and recreate
> new one that's ok. And only problem I think is that : if the user has
> manually changed the revision file, the changes will be lost.
right, that's a problem, so easiest here is to just delete the head file yourself and re-run autogenerate. Alembic isn't going to delete files nor is it going to merge into an existing file - the first is too much of a surprise, the second is far too complicated for this kind of tool. "KISS" applies....
limodou
I defined the column name as "USERNAME“ at first, then I changed it to
"username", so it is the thing I want to change. I know the doc says
it can't detect the column rename, but I think if alembic can put drop
statement before add statement, it'll ok for this situation. What do
you think?
limodou
<mik...@zzzcomputing.com> wrote:
>
> On Apr 12, 2012, at 4:18 AM, limodou wrote:
>
>> I also have other requirements:
>>
>> 1. if there is no changes should it can create nothing, not like this:
>>
>> def upgrade():
>> ### commands auto generated by Alembic - please adjust! ###
>> pass
>> ### end Alembic commands ###
>>
>>
>> def downgrade():
>> ### commands auto generated by Alembic - please adjust! ###
>> pass
>> ### end Alembic commands ###
>
> that's what it should be doing what are you getting ?
But if I changed the database later, and I want to run revision
command, and it'll tell me the datebase is not up to date. So I must
run upgrade even though that will no effect at all. So I think it's
tedious. So I think if there is no change at all, why not just say "
The database is up to date, and don't create any revision file at all.
If is this appoach better?
>
>
>>
>> 2. If I can merge the current changes into head revision, before I
>> execute upgrade, so that I can combine several changes into one
>> change. For me, I think just remove the head revision, and recreate
>> new one that's ok. And only problem I think is that : if the user has
>> manually changed the revision file, the changes will be lost.
>
> right, that's a problem, so easiest here is to just delete the head file yourself and re-run autogenerate. Alembic isn't going to delete files nor is it going to merge into an existing file - the first is too much of a surprise, the second is far too complicated for this kind of tool. "KISS" applies....
>
So if it can be an option to let user to decide if he want to remove
the head revision will be handful. User knows what they want to do. I
just don't want to delete the file manually.
limodou
>>> execute upgrade, so that I can combine several changes into one
>>> change. For me, I think just remove the head revision, and recreate
>>> new one that's ok. And only problem I think is that : if the user has
>>> manually changed the revision file, the changes will be lost.
>>
>> right, that's a problem, so easiest here is to just delete the head file yourself and re-run autogenerate. Alembic isn't going to delete files nor is it going to merge into an existing file - the first is too much of a surprise, the second is far too complicated for this kind of tool. "KISS" applies....
>>
>
> So if it can be an option to let user to decide if he want to remove
> the head revision will be handful. User knows what they want to do. I
> just don't want to delete the file manually.
>
BTW, the head I said just suit for those which have not be upgraded
yet. So if the head revision is already upgraded, then it'll not be
deleted.
And If you like I can try to make some patches for these two demands.
Michael Bayer
On Apr 12, 2012, at 10:42 AM, limodou wrote:
> On Thu, Apr 12, 2012 at 10:37 PM, Michael Bayer
> <mik...@zzzcomputing.com> wrote:
>>
>> On Apr 12, 2012, at 4:07 AM, limodou wrote:
>>
>>> I'm using alembic today, and I found a problem, if I changed the
>>> column, it'll automatically create add and drop statment, just like
>>> this:
>>>
>>> op.add_column('bas_grp_user', sa.Column('username', sa.Integer(),
>>> nullable=False))
>>> op.drop_column('bas_grp_user', u'USERNAME')
>>>
>>> But when I ran the upgrade I got:
>>>
>>> sqlalchemy.exc.OperationalError: (OperationalError) (1060, "Duplicate
>>> column name 'username'") 'ALTER TABLE bas_grp_user ADD COLUMN username
>>> INTEGER NOT NULL' ()
>>
>> this is a column name change - per the documentation, Alembic can't detect these and you need to manually change it to an alter_column(). In this case, there seems to be an odd casing change where the DB is reporting the name as USERNAME in all caps (usually this is oracle or firebird, though alembic should be normalizing these to lowercase....) - but then the database can't distinguish between USERNAME and "username". If this is happening for all your columns then there might be some dialect-related issue at play. Otherwise, if you just changed the name to "USERNAME" in the DB with quotes then this is what you'd get.
>>
>
> I defined the column name as "USERNAME“ at first, then I changed it to
> "username", so it is the thing I want to change. I know the doc says
> it can't detect the column rename, but I think if alembic can put drop
> statement before add statement, it'll ok for this situation. What do
> you think?
dropping the column then recreating it with a new name IMHO is just wrong, whether or not it works - it needs to be changed to an alter_column regardless. The two Column objects have totally different names in any case - this is a rare edge case where the "two different names" happen to be the same in case insensitive - so there's quirky behavior on the part of the database at play (what database is this ? I'd love to know how you got this outcome).
Michael Bayer
On Thu, Apr 12, 2012 at 10:39 PM, Michael Bayer
<mik...@zzzcomputing.com> wrote:On Apr 12, 2012, at 4:18 AM, limodou wrote:I also have other requirements:1. if there is no changes should it can create nothing, not like this:def upgrade():### commands auto generated by Alembic - please adjust! ###pass### end Alembic commands ###def downgrade():### commands auto generated by Alembic - please adjust! ###pass### end Alembic commands ###that's what it should be doing what are you getting ?
But if I changed the database later,
and I want to run revision
command, and it'll tell me the datebase is not up to date.
So I must
run upgrade even though that will no effect at all.
So I think it's
tedious. So I think if there is no change at all, why not just say "
The database is up to date, and don't create any revision file at all.
If is this appoach better?
So if it can be an option to let user to decide if he want to remove
the head revision will be handful. User knows what they want to do. I
just don't want to delete the file manually.
Michael Bayer
On Apr 12, 2012, at 10:54 AM, limodou wrote:
>>>> 2. If I can merge the current changes into head revision, before I
>>>> execute upgrade, so that I can combine several changes into one
>>>> change. For me, I think just remove the head revision, and recreate
>>>> new one that's ok. And only problem I think is that : if the user has
>>>> manually changed the revision file, the changes will be lost.
>>>
>>> right, that's a problem, so easiest here is to just delete the head file yourself and re-run autogenerate. Alembic isn't going to delete files nor is it going to merge into an existing file - the first is too much of a surprise, the second is far too complicated for this kind of tool. "KISS" applies....
>>>
>>
>> So if it can be an option to let user to decide if he want to remove
>> the head revision will be handful. User knows what they want to do. I
>> just don't want to delete the file manually.
>>
>
> BTW, the head I said just suit for those which have not be upgraded
> yet. So if the head revision is already upgraded, then it'll not be
> deleted.
>
> And If you like I can try to make some patches for these two demands.
here's what I can do, since I will grant that "rm" is inconvenient in that you have to type the path to the file:
alembic rm <revision>
will delete revision files, including a range like "alembic rm 5ea:head"
it must include an "are you sure?" dialog after it emits the pathnames of the files it will delete.
limodou
I'm using mysql. I also thought about it, and I think you are right.
But I'm in developing stage, so I can drop the whole table at all, I
just want to do is that I can run the upgrade script completely, but
because the order of add and drop, I can't run the script directly,
and I should modify the script so that it can run successfully. I just
don't want to change them by hand. Maybe there is no a good solution.
limodou
<mik...@zzzcomputing.com> wrote:
>
> On Apr 12, 2012, at 10:50 AM, limodou wrote:
>
> On Thu, Apr 12, 2012 at 10:39 PM, Michael Bayer
> <mik...@zzzcomputing.com> wrote:
>
>
> On Apr 12, 2012, at 4:18 AM, limodou wrote:
>
>
> I also have other requirements:
>
>
> 1. if there is no changes should it can create nothing, not like this:
>
>
> def upgrade():
>
> ### commands auto generated by Alembic - please adjust! ###
>
> pass
>
> ### end Alembic commands ###
>
>
>
> def downgrade():
>
> ### commands auto generated by Alembic - please adjust! ###
>
> pass
>
> ### end Alembic commands ###
>
>
> that's what it should be doing what are you getting ?
>
>
> But if I changed the database later,
>
>
> i dont understand, "changed the database" - do you mean, you changed your
> SQLAlchemy models? or you went into the database directly and manually
> added things ?
I just changed sqlalchemy models, but not directly change the database.
>
> and I want to run revision
> command, and it'll tell me the datebase is not up to date.
>
>
> revision with --autogenerate is comparing the current state of the database
> to the current SQLAlchemy metadata, so the DB has to be on the latest
> revision for this to work, otherwise it will repeat other migrations that
> are already in previous version files that haven't been run. why not say
> "alembic upgrade head" each time before you say "revision --autogenerate" ?
>
>
Because I think the script will do nothing at all (because the created
script upgrade section is empty), why it'll always keep no use
scripts? Why not just keep the last upgraded script and don't create
new script if there is no changes?
> So I must
> run upgrade even though that will no effect at all.
>
>
> I'm really not following. How is the version number in alembic_version not
> the same number in your version files, except that you just need to run
> "alembic upgrade head" ?
But if I don't run alembic upgrade head, I can't do other things. And
for a revison control system, if there are no changes at all, it'll
not create any patches at all, and I think maybe this is a good way.
>
> So I think it's
> tedious. So I think if there is no change at all, why not just say "
> The database is up to date, and don't create any revision file at all.
> If is this appoach better?
>
>
> --autogenerate can't detect all changes, though. if you're using the
> "revision" command, it implies you want it to create a new revision file.
> It would be quite annoying if I wanted to use --autogenerate to catch things
> in general before I add my manual migration changes, which could include
> changes in column type or changes in server default that Alembic by default
> does not detect.
>
I know, but sometimes user want to create new revision even there is
no changes, but sometime they won't I think. And if there is an option
to toggle the behavior is better.
>
>
> So if it can be an option to let user to decide if he want to remove
> the head revision will be handful. User knows what they want to do. I
> just don't want to delete the file manually.
>
>
> That's asking Alembic to guess, in a dangerous way, what revision files
> should be deleted. Suppose I check out the latest source tree. Revision
> files load in, say new files with (fake) numbers 7, 8, and 9 load in. My
> database is at version 6. For some reason, I'm not running "alembic upgrade
> head" (why??).
For example, in my case, when I made some changes and I want to test
them, so I can run "revision --autogenerate" and "upgrade" the
database. And then I made other changes, and do the cycle again I can
do this way. But sometimes I just change the model and run "revision
--autogenerate" and don't upgrade the database, and I also made other
changes, and I want to merge two changes into one piece, so I just
want to delete the lastest revision, and re autogenerate the revision.
So if the alembic can delete the head revision script for me, I don't
need to delete it manually. That's what I think.
> I forget to do all that, then I change some of my app, and
> say "alembic revision -m 'my changes' --autogenerate
> --delete-everything-not-there" - according to this command, alembic would
> need to delete *three* revision files, for 7, 8 and 9, which I haven't even
> looked at. Since the request is really, "delete all revision files down to
> the most recent revision that's in the database". I can't see people
> really wanting that behavior without them having to check things manually
> first. It's a manual operation, and there's no way to automate this -
> therefore might as well have the user just "rm <the file>" - that's what rm
> is for !
>
But in my case, I can't create 8, 9 before I run "upgrade" on 7, I'm
using autogenerate option, so how to create 8, 9 without upgrade on
previous rerison? I can only keep on un upgraded revision in my test.
limodou
great, This way maybe better.
Michael Bayer
On Apr 12, 2012, at 8:07 PM, limodou wrote:
>
> For example, in my case, when I made some changes and I want to test
> them, so I can run "revision --autogenerate" and "upgrade" the
> database. And then I made other changes, and do the cycle again I can
> do this way. But sometimes I just change the model and run "revision
> --autogenerate" and don't upgrade the database, and I also made other
> changes, and I want to merge two changes into one piece, so I just
> want to delete the lastest revision, and re autogenerate the revision.
> So if the alembic can delete the head revision script for me, I don't
> need to delete it manually. That's what I think.
i think if people wanted anything, they'd want it to append to the file that's already there....or just add another new migration file. I'd never want it to delete....
i know what you mean, you generate the rev, then keep doing other things before running upgrade. but for that to really do "what I want", autogenerate would need to keep a list of what it already autogenerated, and is still pending, then add new migrations onto that. which implies it's keeping a datafile somewhere, or in the last migration file. which starts getting too complicated, and kind of redundant.
I think really, just running upgrade is the answer - you can always downgrade again to test !
if more people start complaining about this issue maybe we'll come up with something else...
limodou
>
> On Apr 12, 2012, at 8:07 PM, limodou wrote:
>
>>
>> For example, in my case, when I made some changes and I want to test
>> them, so I can run "revision --autogenerate" and "upgrade" the
>> database. And then I made other changes, and do the cycle again I can
>> do this way. But sometimes I just change the model and run "revision
>> --autogenerate" and don't upgrade the database, and I also made other
>> changes, and I want to merge two changes into one piece, so I just
>> want to delete the lastest revision, and re autogenerate the revision.
>> So if the alembic can delete the head revision script for me, I don't
>> need to delete it manually. That's what I think.
>
> i think if people wanted anything, they'd want it to append to the file that's already there....or just add another new migration file. I'd never want it to delete....
So this thing I think is migrate, but I thought it's harder than just
delete the un upgraded scripts and re autogenerate the revision. So I
think deletion is just a simple way. If there is better appoach will
be better.
>
> i know what you mean, you generate the rev, then keep doing other things before running upgrade. but for that to really do "what I want", autogenerate would need to keep a list of what it already autogenerated, and is still pending, then add new migrations onto that. which implies it's keeping a datafile somewhere, or in the last migration file. which starts getting too complicated, and kind of redundant.
>
> I think really, just running upgrade is the answer - you can always downgrade again to test !
>
> if more people start complaining about this issue maybe we'll come up with something else...
>
So I think "rm" command will satisfy me now. And I think autogenerate
is more handful, and for simple situation, I really know what I've
made and don't need autogenerate to worry about for me. So that's what
I need. And so I think "rm" can do the work. And if the rm command can
remove all un upgraded revision is better, so I don't need to give
"rev:head" parameter, because I need to check which is the first rev
that un upgraded yet.