Altering UUID column types

models.py

from __future__ import print_function
from sqlalchemy import (
    Column,
    create_engine,
    MetaData,
)
from sqlalchemy.types import String, Integer
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


DATABASE_NAME = 'column_rename'
UUID_1 = '00000000-0000-0000-0000-111111111111'
UUID_2 = '00000000-0000-0000-0000-222222222222'
UUID_3 = '00000000-0000-0000-0000-333333333333'
UUID_4 = '00000000-0000-0000-0000-444444444444'
metadata = MetaData()
BaseModel = declarative_base()
Session = None


def create_database(engine):
    conn = engine.connect()
    conn.execute('ROLLBACK')
    conn.execute('DROP DATABASE IF EXISTS {}'.format(DATABASE_NAME))
    conn.execute('ROLLBACK')
    conn.execute('CREATE DATABASE {}'.format(DATABASE_NAME))


def create_tables(engine):
    BaseModel.metadata.drop_all(engine)
    BaseModel.metadata.create_all(engine)


def add_fixtures(session):
    try:
        for uid in [UUID_1, UUID_2, UUID_3, UUID_4]:
            session.add(Thing(**{'uid': uid, 'uid_new': uid}))
        session.commit()
    except Exception:
        session.rollback()
        raise


class Thing(BaseModel):
    __tablename__ = 'things'

    id = Column(Integer, autoincrement=True, primary_key=True)
    uid = Column(String(36), unique=True)
    uid_new = Column(UUID, unique=True)


def print_thing(thing):
    print("""Thing
    id      = {id}
    uid     = {uid} {uid_type}
    uid_new = {uid_new} {uid_new_type}
    """.format(
        id=thing.id, uid=thing.uid, uid_new=thing.uid_new,
        uid_type=type(thing.uid), uid_new_type=type(thing.uid_new)
    ))
    #""".format(**thing.__dict__))


def get_thing(uid):
    session = Session()
    try:
        thing = session.query(Thing).filter(Thing.uid==uid).one()
        session.expunge(thing)
        return thing
    finally:
        session.rollback()


if __name__ == '__main__':
    engine = create_engine('postgresql+psycopg2://localhost', echo=True)
    create_database(engine)
    engine = create_engine('postgresql+psycopg2://localhost/{}'.format(DATABASE_NAME), echo=True)
    create_tables(engine)
    Session = sessionmaker(bind=engine)
    add_fixtures(Session())

    thing = get_thing(UUID_1)
    print_thing(thing)
    
    print('\nWhile at this break point, run swap-columns.sh and then continue\n')
    import ipdb; ipdb.set_trace()
    
    new_thing = get_thing(UUID_1)
    print_thing(new_thing)
    
    print('\nnotice that nothing blew up in get_thing!')
    print('compare the types of uid and uid_new to those in the previous call to get_thing and print_thing')
    import ipdb; ipdb.set_trace()
    pass

requirements.txt

ipdb
psycopg2==2.6.1
sqlalchemy==1.1.9

swap-columns.sh

#!/bin/bash -xe

psql -h localhost -e -f swap_columns.sql column_rename

swap_columns.sql

BEGIN;
ALTER TABLE things RENAME COLUMN uid TO uid_temp;
ALTER TABLE things RENAME COLUMN uid_new TO uid;
ALTER TABLE things RENAME COLUMN uid_temp TO uid_new;
COMMIT;
\d+ things