After I create my tables using SQLAlchemy, how can I add additional columns to it?

Question

This is my file so far:

from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy import Column, Integer, String
from sqlalchemy import Table, Text

engine = create_engine('mysql://root:ababab@localhost/alctest',
            echo=False)

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key = True)
    name = Column(String(100))
    fullname = Column(String(100))
    password = Column(String(100))
    addresses = relationship("Address", order_by="Address.id", backref="user")

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)


class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key = True)
    email_address = Column(String(100), nullable=False)

    #foreign key, must define relationship
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", backref = backref('addresses',order_by=id))

Base.metadata.create_all(engine)

This file is pretty simple. It creates a User and Address tables. After I run this file, the tables are created.

But now I want to add a column to "User". How can I do that? What do I have to do?

Answer 1

You can add column with Table.append_column method.

test = Column('test', Integer)
User.__table__.append_column(test)

But this will not fire the ALTER TABLE command to add that column in database. As per doc given for append_column that command you have to run manually after adding that column in model.

Answer 2

Short answer: You cannot: AFAIK, currently there is no way to do it from sqlalchemy directly.

Howerever, you can use sqlalchemy-migrate for this if you change your model frequently and have different versions rolled out to production. Else it might be an overkill and you may be better off generating the ALTER TABLE ... scripts manually.