I'm trying to add a column to a PostgreSQL table with SQLAlchemy. I read on a couple threads that the simplest way to do this is to send plain SQL rather than modifying the Table object.
My query is really basic:
import sqlalchemy
engine = create_engine("postgresql://user@host:port/db")
query = sqlalchemy.text("alter table schema.table add column if not exists column int")
with engine.begin() as conn:
resultAddColumn = conn.execute(query)
# I also tried
# with engine.connect() as conn:
# resultAddColumn = conn.execute(query)
# conn.commit()
# and also
# conn = engine.connect()
# resultAddColumn = conn.execute(query)
# conn.commit()
# conn.close()
The weird thing is that if I send this command directly in the interactive terminal ("manually"), it works fine.
But I'm using this in a script which I'm calling with (windows) python script.py and it's when running the script that SQLAlchemy hangs at the conn.execute(query) command (in the three methods described above). The only way to stop it is kill the terminal.
I tried other SQL queries such as SELECT and it works fine also.
What am I doing wrong? Thanks.
SQLAlchemy 2.0.34 and Python 3.11.10.
Your ALTER TABLE statement is syntactically incorrect (you cannot name a column columns), but if that's similar to what the statement actually looks like, it will be very fast, no matter how big the table is.
The only reason for a statement like that to hang for any length of time is if there is a concurrent query or transaction using the table. The reason is that such an ALTER TABLE requires an ACCESS EXCLUSIVE lock, which will conflict even with the ACCESS SHARE lock taken by SELECT.
You will have to make sure that all concurrent transactions that are using the table are closed before you run that ALTER TABLE.