SQLAlchemy + DB migration by Alembic


This article is a brief memo for how to use SQLAlchemy and DB migration using Alembic. The best way to learn them is to read their document directly though they are nice volume. I, therefore, pick up some useful and basic snipets from document. 


SQLAlchemy is ORM(Object-relational mapping). You can map your Python code to DB. It means you do not need to write SQL directly for handling data. But ORM library does not provide what you expected. You might write SQL.

DB migration is a way of changing your database schema from one version into another. When you add, delete schema and so on, these action can be managed by DB migration tools.

I will write abour how to qury using SQLAlchemy later.


# SQLAlchemy

## How to connect to DBs
http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#database-urls

```
from sqlalchemy import create_engine
```


### MySQL



```
# default
engine = create_engine('mysql://scott:tiger@localhost/foo')

# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

# OurSQL
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

```

### SQLite
```
# sqlite://<nohostname>/<path>
# where <path> is relative:
engine = create_engine('sqlite:///foo.db')
```

### Postgres
```
# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
```

## Declare a Mapping

Declaretive is mapping python class to DB schema. This is done by inheriting declaretive base class

http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/index.html


```
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))

```



Mixin class allows to  declare primary key easy.

```
from sqlalchemy.ext.declarative import declared_attr

class DBMixin(object):

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    __table_args__ = {'mysql_engine': 'InnoDB'}
    __mapper_args__= {'always_refresh': True}

    id =  Column(Integer, primary_key=True)

```

If `DBMixin` is inherited by a declarative class for Table definition, the above `User` example becomes simpler.

```

class User(DBmixin, Base):
    name =  Column(String(50))

```


### created_at, update_at

http://docs.sqlalchemy.org/en/rel_1_0/core/defaults.html

`default` is invoked when INSERT query is executed if no other value is supplied.


`onupadte` is invovoked when UPDATE query is executed and no values is supplied.

These are call by sqlalchemy; therefore it should be callable object(function etc..).



```python
import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now, onpudate=datetime.datetime.now)
```

### Constrants and Indexes
http://docs.sqlalchemy.org/en/rel_1_0/core/constraints.html

Basically, these are defined by argument in `Column`

#### Primary Key


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


##### Composite Primary Key

```
PrimaryKeyConstraint('id', 'version_id', name='mytable_pk')
```

```
    Column('id', Integer, primary_key=True),
    Column('version_id', Integer, primary_key=True),
```


#### Foregin Key

```
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
```

##### Composite Foregin Key

Add primary_key in different row. SQLAlchemy atuomatically handles it.



#### Unique
```
Column('col1', Integer, unique=True)
```

##### Composite Unique 

This is more explicit way.

```
from sqlalchemy import UniqueConstraint

UniqueConstraint('col2', 'col3', name='uix_1')
```


#### Index

```
Column('col1', Integer, index=True),

Column('col2', Integer, index=True, unique=True),
```


##### Composite Index

```
	# place an index on col1, col2
    Index('idx_col12', 'col1', 'col2'),

    # place a unique index on col3, col4
    Index('idx_col34', 'col3', 'col4', unique=True)
```


#### Nullable

```
    Column('pref_name', String(40), nullable=False),
```


## data type

http://docs.sqlalchemy.org/en/rel_0_9/core/type_basics.html#generic-types

- Integer
- Float
- String
- Date
- Datetime
- Text
- etc...

There are some each DB specific data types.


## Relation

### one to one

An important thing,here is to specify `uselist=False` . 

```python
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, backref="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

```

### one to many
```python
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # employees is added in Department as an attribute for birateral relationship 
    departments = relationship(
        Department,
        backref='employees'
    )
```

### many to many

```python
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # employees can be hanlded as python list.
    # the string of name of class is okay for first arguemnt of relationship
    employees = relationship(
        'Employee',
        secondary='department_employee'
    )
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # employees can be hanlded as python list.
    departments = relationship(
        Department,
        secondary='department_employee'
    )
 
 
class DepartmentEmployee(Base):
    __tablename__ = 'department_employee'
    department_id = Column(Integer, ForeignKey('department.id'))
    employee_id = Column(Integer, ForeignKey('employee.id'))
    Index('deparment_employee_idx, 'deparment_id', 'employee_id', unique=True)
```

# Migration

Use alembic

SQLAlchemy-migrate is not under active development. It seems that it stopped to adopt new SQLAlchemy(0.8 of SQLAlchemy is the latest support). While, alembic is maintained by SQLAlemy author. Bug fix is done really quick. Alembic is develped in Bitbucket. Github is a mirror of BitBucket.

ref:
https://alembic.readthedocs.org/en/latest/

## Install Alembic

```
pip install alembic
```

## Initiation

### Directory hierarchy

```
$ alembic init alembic
```


```
app
├── alembic
│   ├── README
│   ├── env.py
│   ├── script.py.mako
│   └── versions
├── model.py
```

## Auto migrate template detection
Cool feature of alembic is detect changes automatically compared database and revison files. NOTE: auto migration does no detect all changes.

### Set up

Modify env.py

```
# imprt model.py
import os
import sys
MODEL_PATH = os.path.join(os.path.abspath(os.path.dirname(__file__)), "..")
sys.path.append(MODEL_PATH)
import model


# edit this line and pass metadata
target_metadata = model.Base.metadata

```

### Can

- Table additions, removals.
- Column additions, removals.
- Change of nullable status on columns.
- Basic changes in indexes and explcitly-named unique constraints
- Basic changes in foreign key constraints


### Cannot

- Changes clumn name
- Changes table name
- Special SQLALchemy types such as Enum


## migrate 

https://alembic.readthedocs.org/en/latest/tutorial.html#creating-an-environment

Create auto migrate template

```
$ alembic revision --autogenerate -m "initial"
```

### Execute migration

head means the most recent change. It will migrate untill the most recent one.

```
$ alembic upgrade head
```

### Show migration

```
$ alembic history --verbose
```

output


```
Rev: 340434aac9a (head)
Parent: 14db12dc041
Path: /Users/ken/proto/python/sqlalchemy/test/alembic/versions/340434aac9a_add_address.py

    add address

    Revision ID: 340434aac9a
    Revises: 14db12dc041
    Create Date: 2015-10-25 23:40:55.398984

Rev: 14db12dc041
Parent: <base>
Path: /Users/ken/proto/python/sqlalchemy/test/alembic/versions/14db12dc041_initial.py

    initial

    Revision ID: 14db12dc041
    Revises:
    Create Date: 2015-10-25 23:27:39.622146
```


# License 

SQLAlchemy

- MIT License

Alembic

- MIT License


# Ref
http://docs.sqlalchemy.org/en/rel_1_0/contents.html

http://pythoncentral.io/series/python-sqlalchemy-database-tutorial/