migrate

[robneville73]

Does anybody have decent examples of using migrate?? The documentation
is OK, but pretty sparse...

For example, how do I alter a column on an existing table? I'm not
following the example provided in
http://packages.python.org/sqlalchemy-migrate/changeset.html#changeset-system

Help?

[Maciej Kaniewski]

Hi here : http://code.google.com/p/pydive/source/browse/trunk/pydive/pydive/migration/versions/

are some migrations i did for my project i am still new with all of that but it is working. 

Here is easy example of altering tables : http://code.google.com/p/pydive/source/browse/trunk/pydive/pydive/migration/versions/009_News_frontPage.py there is a table "news" i wanted to add new column to decide if column should go to frontpage or not so :

from sqlalchemy import *

from migrate import *

from datetime import datetime

from migrate.changeset import *

# ^^^^^this is very important line without it it won't work

meta = MetaData(migrate_engine)

news = Table('news', meta,

Column('id', Integer, autoincrement=True, primary_key=True),

Column('date', DateTime,nullable=False,default=datetime.now),

Column('title', Unicode(255)),

Column('short_text', UnicodeText),

Column('text', UnicodeText),

Column('author_id', Integer, ForeignKey('tg_user.user_id')),

) # ^^^^ existing table in database

def upgrade():

frontPage = Column('frontPage',Boolean, default=False)

frontPage.create(news)

def downgrade():

frontPage = Column('frontPage',Boolean, default=False)

frontPage.drop(news)

2009/8/29 robneville73 <robertn...@gmail.com>

[Christopher Arndt]

robneville73 schrieb:

Since many database systems do not support altering column types, but
only adding / dropping /renaming them, here's what I did to change the
name and type of a column (but be aware that this will destroy all the
old data in that column):

HTH, Chris

"""Migration script to change the 'old_name' column in table 'foo'."""

# Version 6 database schema

from migrate import migrate_engine
# need to import this to add .alter method to Column objects
# don't remove!
from migrate.changeset import drop_column

from sqlalchemy import MetaData, Table, Column, String, Boolean

metadata = MetaData(migrate_engine)
foo_table = Table('foo', metadata, autoload=True)

def upgrade():
"""Change 'old_name' column."""
# we can not use ALTER to change a String type column into Boolean
# so we drop the column and add a new one
drop_column(af_table.columns['old_name'])
Column('new_name', String(10), default="none").create(foo_table)

def downgrade():
"""Change 'new_name' column back."""
# we can not use ALTER to change a String type column into Boolean
# so we drop the column and add a new one
drop_column(af_table.columns['new_name'])
Column('old_name', Boolean, default=True).create(foo_table)

[Nick Barendt]

On Sun, Aug 30, 2009 at 4:05 PM, Christopher Arndt <chris...@web.de> wrote:

robneville73 schrieb:

> Does anybody have decent examples of using migrate?? The documentation
> is OK, but pretty sparse...
>
> For example, how do I alter a column on an existing table? I'm not
> following the example provided in
> http://packages.python.org/sqlalchemy-migrate/changeset.html#changeset-system

Since many database systems do not support altering column types, but
only adding / dropping /renaming them, here's what I did to change the
name and type of a column (but be aware that this will destroy all the
old data in that column):


HTH, Chris

 

For some of my projects, I'm using sqlite, which has very limited support for alterations, so I'm also doing destroying and recreating, but I am preserving data (small tables, so I can hold it all in RAM; persisting to disk temporarily shouldn't be much harder, though, if the table is too big).   I struggled to find examples, too, so this might not be as elegant as it could be, but it has been working very well for me.

-Nick

# change the width of the address field of the FooInstance model

from sqlalchemy import *

from migrate import *

from sqlalchemy.orm import sessionmaker, scoped_session

from zope.sqlalchemy import ZopeTransactionExtension

maker = sessionmaker(autoflush=True, autocommit=False,

                     extension=ZopeTransactionExtension())

DBSession = scoped_session(maker)

def upgrade():

    # Upgrade operations go here. Don't create your own engine; use the engine

    # named 'migrate_engine' imported from migrate.

    # sqlite doesn't properly support table alteration, so we'll dump

    # and restore

    meta = MetaData(migrate_engine)

    table = Table('foo_instance',

                  meta,

                  autoload=True)

    foo_model = Table('foo_model',

                             meta,

                             autoload=True)

    instances = [inst for inst in DBSession.query(table).all()]

    table.drop()

    meta = MetaData(migrate_engine)

    table = Table('foo_instance',

                  meta,

                  Column('id', Integer, autoincrement=True,

                         nullable=False, primary_key=True,

                         unique=True),

                  Column('address', String(length=255),

                         nullable=False, unique=True),

                  Column('model_id', Unicode(255),

                         ForeignKey('foo_model.id'),

                         nullable=False)

                  )

    foo_model = Table('foo_model',

                             meta,

                             autoload=True)

    table.create()

    i = table.insert()

    for instance in instances:

        i.execute({'id': instance.id,

                   'address': instance.address,

                   'model_id': instance.model_id})

def downgrade():

    meta = MetaData(migrate_engine)

    table = Table('foo_instance',

                  meta,

                  autoload=True)

    foo_model = Table('foo_model',

                             meta,

                             autoload=True)

    instances = [inst for inst in DBSession.query(table).all()]

    table.drop()

    meta = MetaData(migrate_engine)

    table = Table('foo_instance',

                  meta,

                  Column('id', Integer, autoincrement=True,

                         nullable=False, primary_key=True,

                         unique=True),

                  Column('address', String(length=17),

                         nullable=False, unique=True),

                  Column('model_id', Unicode(255),

                         ForeignKey('foo_model.id'),

                         nullable=False)

                  )

    foo_model = Table('foo_model',

                             meta,

                             autoload=True)

    table.create()

    i = table.insert()

    for instance in instances:

        i.execute({'id': instance.id,

                   'address': instance.address,

                   'model_id': instance.model_id})

 

[robneville73]

OK, that seems to make sense. Thanks for all the examples, that really
helps. I suppose if you're changing columns types you necessarily have
a lot of data manipulation in front of you anyway. I'll give it
another go....

On Aug 30, 4:05 pm, Christopher Arndt <chris.ar...@web.de> wrote:
> robneville73 schrieb:
>
> > Does anybody have decent examples of using migrate?? The documentation
> > is OK, but pretty sparse...
>
> > For example, how do I alter a column on an existing table? I'm not
> > following the example provided in

> >http://packages.python.org/sqlalchemy-migrate/changeset.html#changese...