SQLAlchemy + DB migration by Alembic ref: http://qiita.com/giwa/items/87789d7ac22e633024d2

file0.txt

from sqlalchemy import create_engine

file1.txt

# 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')

file10.txt

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

file11.txt

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

file12.txt

Column('col1', Integer, unique=True)

file13.txt

from sqlalchemy import UniqueConstraint

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

file14.txt

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

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

file15.txt

    # 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)

file16.txt

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

file17.txt

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'))

file18.txt

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'
    )

file19.txt

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)

file2.txt

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

file20.txt

pip install alembic

file21.txt

$ alembic init alembic

file22.txt

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

file23.txt

# 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

file24.txt

$ alembic revision --autogenerate -m "initial"

file25.txt

$ alembic upgrade head

file26.txt

$ alembic history --verbose

file27.txt

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

file3.txt

# 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')

file4.txt

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))

file5.txt

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)

file6.txt

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

file7.txt

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)

file8.txt

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

file9.txt

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