Last active
October 26, 2015 21:23
-
-
Save giwa/16077fd19763592bc433 to your computer and use it in GitHub Desktop.
SQLAlchemy + DB migration by Alembic ref: http://qiita.com/giwa/items/87789d7ac22e633024d2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from sqlalchemy import create_engine |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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') | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column('id', Integer, primary_key=True), | |
Column('version_id', Integer, primary_key=True), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column('col1', Integer, unique=True) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from sqlalchemy import UniqueConstraint | |
UniqueConstraint('col2', 'col3', name='uix_1') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column('col1', Integer, index=True), | |
Column('col2', Integer, index=True, unique=True), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column('pref_name', String(40), nullable=False), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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')) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# sqlite://<nohostname>/<path> | |
# where <path> is relative: | |
engine = create_engine('sqlite:///foo.db') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
pip install alembic |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ alembic init alembic |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
app | |
├── alembic | |
│ ├── README | |
│ ├── env.py | |
│ ├── script.py.mako | |
│ └── versions | |
├── model.py |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ alembic revision --autogenerate -m "initial" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ alembic upgrade head |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ alembic history --verbose |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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)) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
class User(DBmixin, Base): | |
name = Column(String(50)) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column('id', Integer, primary_key=True) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
PrimaryKeyConstraint('id', 'version_id', name='mytable_pk') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment