Python Flask sqlalchemy Insert new column into table - heroku postgresql

Question

BACKGROUND INFOS:

I have an app which is hosted by heroku on a postgresql DB.

I have already some data in this DB and now I have to add a new row in one of my tables.

Usually I deleted the old DB and recreated it. But in future if the project is live I will have to update tables without losing the data.

I could create a DUMP and delete the old database and recreate it as always. Then I could use a script and upload all existing data into the new DB. But this feels wrong.

WHAT I NEED:

In my current situation there is blog data table=blog on the database and I need to insert a new column into my table=zimmer so blog isnt even affected.

class Zimmer(Base):
    __tablename__ = 'zimmer'
    id = Column(Integer, primary_key=True)
    infofeld = Column(Text, nullable=False)
    land = Column(Text, nullable=False)
    bundesland = Column(Text, nullable=False)
    stadt = Column(Text, nullable=False)
    plz = Column(Text, nullable=False)
    strasse = Column(Text, nullable=False)
    hausnr = Column(Text, nullable=True)
    eigener_link = Column(Text, nullable=True)
    zimmer_lat = Column(Float, nullable=False)
    zimmer_lng = Column(Float, nullable=False)
    reingestellt_am = Column(Date, nullable=False)

This is the new value: eigener_link = Column(Text, nullable=True)

I am currently experimenting on localhost but so far I am only getting ProgrammingError because everytime I try to load a site where zimmer is shown it says there is no column eigener_link (that is logical).

WHAT I TRIED:

I tryed to try except the ProgrammingError in the line where it occured, which gave me an InternalError. Here I tryed to update the zimmer table and add the new column eigener_link:

try:
    for page in paginator:
        pages_list.append(page.number)
except ProgrammingError:
    update(Zimmer).values(eigener_link='Ihr Link')
    db_session.commit()

It gave me an InternalError. I checked the DB via pgAdmin and the new value has not been added.

    try:
        for page in paginator:
            pages_list.append(page.number)
    except ProgrammingError:
        db_session.execute('ALTER TABLE zimmer ADD eigener_link TEXT')
        db_session.commit()

This gave me also InternalError the transaction has been canceled.

Roman · Accepted Answer · 2016-12-02 10:47:11Z

Okay I used alembic to solve this problem and it was really easy and took me like 10 min.

You install it for example via pip:

pip install alembic

And then follow the tutorial.

Basicly you go into your folder with your app and init alembic once, so it creates all necessary alembic files.

In alembic.ini you change the path to your database (you make the changes localy, no need to push something to heroku).

Then you use alembic revision to create a script which applies the changes to your DB. You have to open this created script with an editor to you can add changes. (more in the tutorial).

And finaly you run alembic upgrade head and that is it!

In my case this were the necessary changes in the script:

def upgrade():
    op.add_column('zimmer', sa.Column('eigener_link', sa.Text))


def downgrade():
    op.drop_column('zimmer', 'eigener_link')

Collectives™ on Stack Overflow

Python Flask sqlalchemy Insert new column into table - heroku postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related