I am working with SQLAlchemy in Python to perform column's CRUD operations on my database. I have successfully retrieved all the columns of a table using the following code:
def getItems(self):
items = self.table.columns.keys()
return items
However, I am facing difficulties while trying to add, update, and delete columns.
I have tried several methods, including executing raw SQL queries and using methods from SQLAlchemy's documentation, but none seem to work as expected.
Here's an example of a raw SQL query I tried:
query = f'ALTER TABLE {self.table} ADD {column_name} integer;'
connection.execute(query)
I also tried using SQLAlchemy's append_column method and migrate library as follows:
self.table.append_column(column: ColumnClause[Any], replace_existing: bool = False)
col = Column('new_column_name', String(20), default='foo')
col.create(self.table)
Although I don't receive any errors, the changes don't seem to persist. When I check the table in pgAdmin, the added columns are not there, even though they appear when I retrieve the columns using my getItems function.
I checked this link but I didn't got my answer.
Could anyone point out what I might be doing wrong and how to properly add a column to an existing table in SQLAlchemy?