2

I need SQLAlchemy to return a default value if the Column in the Database Table does not exist. This can be easily done if the value of the column is NULL. But my case needs to cover a default value if the database table does not have the column.

Is this possible in SQLAlchemy? If not possible, is there anyway around that?

Basil Musa
  • 8,198
  • 6
  • 64
  • 63
  • Which SQLAlchemy function do you want to return Null when a column doesn't exist? – JeffUK Nov 24 '20 at 13:50
  • 1
    On postgres at least, and probably any other db, the db will raise an error for a non-existent column, so you'd need to wrap the query in a try / except, send an acceptable query in the except clause and then somehow combine it with the default – snakecharmerb Nov 24 '20 at 13:51
  • 1
    @snakecharmerb is right, it's also worth point out this gets exponentially more complicated if your query uses said column for filtering, joining, and in subqueries etc. – JeffUK Nov 24 '20 at 13:53
  • 1
    Of course you could reflect the tables in advance, to work out what columns are available. But in general I think I'd wonder if there was a better architecture / design available. E.g No-SQL or use JSON columns. – snakecharmerb Nov 24 '20 at 13:56
  • I meant the some_column = Column("some_column", default="some_value"). This will return some_value if a NULL value exists. However, I need to return some_value even if the whole column does not exist in the table. – Basil Musa Nov 24 '20 at 13:56
  • I'm having difficulty imagining a sensible use-case where a column may or may not exist in a particular table, at least for a table that you would be querying on a regular basis. – Gord Thompson Nov 24 '20 at 14:19
  • @GordThompson The PostgreSQL table is being synced from a DynamoDB table using live streaming. Columns might get created or deleted realtime. – Basil Musa Nov 24 '20 at 14:48
  • 1
    Similar https://stackoverflow.com/questions/18951071/postgres-return-a-default-value-when-a-column-doesnt-exist (db level, and there is a deleted answer that says it no longer works, but that isn't confirmed) – snakecharmerb Nov 24 '20 at 15:52
  • 1
    I just tried Erwin's [fastest solution](https://stackoverflow.com/a/18964090/2144390) under PostgreSQL 12.3 and it still works fine. – Gord Thompson Nov 24 '20 at 16:31

0 Answers0