Add a column to existing class of sqlalchemy

Question

I have a parent class that generates a unique id and a child class having a username.

class Ids(object): 
    id = Column(Integer, Sequence('res_id_seq'), primary_key=True) 


class Names(Ids, base): 
    username = Column(String(50))

# I have created an sqlite db and persisting using sqlalchemy

engine = create_engine('sqlite:///emp.db', echo=True)
base.metadata.create_all(engine)

I want to add a column to Names (lastname). How can i do that with sqlalchemy-migrate? Any examples would be helpful?

score 0 · Answer 1 · answered Jul 07 '20 at 15:31

See this section of the SQLAlchemy documentation from here

While SQLAlchemy directly supports emitting CREATE and DROP statements for schema constructs, the ability to alter those constructs, usually via the ALTER statement as well as other database-specific constructs, is outside of the scope of SQLAlchemy itself. The SQLAlchemy project offers the Alembic migration tool for this purpose.

You can follow the explanation here on how you can add a column of your choice to the class Names - Alembic-Create a Migration Script

score 0 · Answer 2 · answered Jul 08 '20 at 06:17

According to sqlalchemy-migrate documentation use can use create and drop methods on columns in your migrations. Your migrate script may loop like this:

from sqlalchemy import Column, Integer, MetaData, String, Table

metadata = MetaData()

# Note that here you use Table instance
# not the class derived from sqlalchemy.ext.declarative.declarative_base
names = Table(
    'names',
    metadata,
    # You need to specify only primary key column
    # if you just adding a new column in this script
    Column('id', Integer, primary_key=True),
)

lastname = Column('lastname', String(50))


def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    lastname.create(names)


def downgrade(migrate_engine):
    metadata.bind = migrate_engine
    names.append_column(lastname)
    names.c.lastname.drop()

Add a column to existing class of sqlalchemy

2 Answers2