CODEWITHSUNDEEP
pythonflask-sqlalchemy
Bob
Bob

Reputation: 15

Is there a way to restore the old data for a table after modifing its definition in sqlalchemy?

Say, there is a movie.py, which contians the table definition for movie and base like 

# base.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgresql://usr:pass@localhost:5432/sqlalchemy')
Session = sessionmaker(bind=engine)
Base = declarative_base()

# move.py
from sqlalchemy import Column, String, Integer, Date
from base import Base
class Movie(Base):
    __tablename__ = 'movies'

    id = Column(Integer, primary_key=True)
    title = Column(String)
    release_date = Column(Date)

    def __init__(self, title, release_date):
        self.title = title
        self.release_date = release_date

And insert some queries like

# coding=utf-8
from datetime import date
from base import Session, engine, Base
from movie import Movie

# 2 - generate database schema
Base.metadata.create_all(engine)

# 3 - create a new session
session = Session()

# 4 - create movies
bourne_identity = Movie("The Bourne Identity", date(2002, 10, 11))
furious_7 = Movie("Furious 7", date(2015, 4, 2))
pain_and_gain = Movie("Pain & Gain", date(2013, 8, 23))

# 5 - persists data
session.add(bourne_identity)
session.add(furious_7)
session.add(pain_and_gain)


# 10 - commit and close session
session.commit()
session.close()

Is there a way I could restore the old data I have inserted, if I got a new definition for my movie table (add more columns to the move.py) ?

Upvotes: 0

Views: 143

Answers (1)

CryptoFool
CryptoFool

Reputation: 23119

Did you mean to ask "how do I update the database schema of an existing database?". That's called "schema migration". There are a number of ways of attacking that. The most basic way is to have SqlAlchemy work in the other direction...have it generate its schema metadata from an existing database, instead of creating metadata and then having SqlAlchemy build a database from that. This is called Reflection. You'd do this, then issue individual commands to update your database schema. In doing this, you'd have to allow for what is going to happen to the existing rows in your table as you make these changes. You would still use your domain object definition (the Movie object), but you wouldn't use create_all(). create_all() ignores any tables that already exist.

In reality, this gets complex quickly, and so you usually want to use a formal schema migration strategy, and probably a support package for doing so. SqlAlchemy's own documentation recommends two packages for doing so. See this page:

https://docs.sqlalchemy.org/en/latest/core/metadata.html

Scroll down a bit to the "Altering Schemas through Migrations" section.

Someone may have more to offer you in terms of how to do this manually, without a migration package. I've always used such a package for any task where I wasn't willing to blow away my data and start from scratch whenever my schema changed.

Another option I've seen used is to export all your data, have SqlAlchemy build a fresh, empty database, and then import your existing data back into that new database. You would set up appropriate defaults for the new fields that won't exist in the incoming data. You'll be doing this thing with setting defaults for missing columns no matter how you choose to attack this problem.

Upvotes: 1

Related Questions