Sqlalchemy alembic

Alembic is a database migration tool for python based on Sqlalchemy.

This is a straight forward reference – tutorial on how to use it.

Installation

>>> pip install alembic

Init

$ cd yourproject
$ alembic init alembic

Set connection string inĀ alembic.ini in sqlalchemy.url

Create migration script

$ alembic revision -m "create account table"

Edit file “1975ea83b712_create_account_table.py”

def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('description', sa.Unicode(200)),
    )

def downgrade():
    op.drop_table('account')

 

Upgrade to a version

Migrate to latest version:

$ alembic upgrade head

Migrate to specific version:

$ alembic upgrade ae1

Downgrade 1 from current

$ alembic downgrade -1

Upgrade to a specific version from a tag

$ alembic upgrade ae10+2

Get Information

Current version information

$ alembic current

History information

$ alembic history --verbose

History range

$ alembic history -r1975ea:ae1027
$ alembic history -r-3:current
$ alembic history -r1975ea:

Downgrade

Downgrade to init

alembic downgrade base

Autogenerate migration

Alembic can automatically recognize changes and generate migration code for us.
First need to point to declarative base models file in ‘env.py’ file

from common.db.models import Base
...
target_metadata = Base.metadata

*If you get error in the import, add PYTHONPATH environment variable

$ export PYTHONPATH=/home/${USER}/projects/some_project

Create the automatic revision code

$ alembic revision --autogenerate -m "Added account table"

Now you can use upgrade to execute the migration script

$ alembic upgrade head

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s