Updating a Table after changing class based on Declarative in PostgreSQL

[mclovin]

I am new to SQL and SQLalchemy, but say I have a class like this:

class User(Base):
__tablename__ = "users"

id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
join = Column(DateTime)
infractions =Column(Integer)
posts = Column(Integer)

def __init__(self, name):
self.name = name
self.join = datetime.datetime.now()
self.infractions = 0
self.posts = 0


but I wanted to change it to this:
class User(Base):
__tablename__ = "users"

id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
join = Column(DateTime)
infractions =Column(Integer)
#posts = Column(Integer) REMOVE POSTS
bannedTill = Column(DateTime) #ADD BANNEDTILL

def __init__(self, name):
self.name = name
self.join = datetime.datetime.now()
self.infractions = 0
self.bannedTill = datetime.datetime.now()


Where I remove the column posts and add a column "bannedTill". What
are the steps to update my table "users" to reflect these changes
without losing the data that is already in the table (I will populate
my new field manually).

[Conor]

What you want is called schema migration. It's a big topic, but the simplest way is to manually execute raw SQL commands to add/remove columns. For example, in PostgreSQL:

BEGIN;
ALTER TABLE users DROP COLUMN posts;
ALTER TABLE users ADD COLUMN bannedTill TIMESTAMP;
COMMIT;

Alternatively, there is a package called sqlalchemy-migrate that offers a more sophisticated way to migrate schemas.

-Conor