4

I'm not sure why comment on schema is not working via sqlalchemy, but works perfectly on psql console,

Have tried various settings still no luck, schema gets created but cannot add description.

import sqlalchemy

engine = sqlalchemy.create_engine('postgresql://postgres@localhost/posgres')
engine.execute("CREATE SCHEMA IF NOT EXISTS myschema")
engine.execute("COMMENT ON SCHEMA myschema IS 'Seemly Random Description'")

checking with with psql

postgres=# \dn+
                           List of schemas
   Name   │  Owner   │  Access privileges   │      Description       
──────────┼──────────┼──────────────────────┼────────────────────────
 myschema │ postgres │                      │ 
 public   │ postgres │ postgres=UC/postgres↵│ standard public schema
          │          │ =UC/postgres         │ 
(2 rows)
rho
  • 771
  • 9
  • 24

1 Answers1

6

Not entirely sure why, but it appears to work once you create a connection and run COMMIT:

In [8]: conn = engine.connect()                                                                                                                                                                              

In [9]: conn.execute("COMMENT ON SCHEMA myschema IS 'Seemly Random Description'")                                                                                                                            
Out[9]: <sqlalchemy.engine.result.ResultProxy at 0x7fd109991cd0>

In [10]: conn.execute("COMMIT;")                                                                                                                                                                             
Out[10]: <sqlalchemy.engine.result.ResultProxy at 0x7fd0ff3b7d10>

postgres=# \dn+
                            List of schemas
   Name   |  Owner   |  Access privileges   |        Description        
----------+----------+----------------------+---------------------------
 myschema | postgres |                      | Seemly Random Description
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
Stephen Fuhry
  • 12,624
  • 6
  • 56
  • 55
  • i was surprise to to find it works without connection, which let me to think commit is not necessary as in psql console. and engine.commit() or connection.commit() were not the valid methods. – rho Jan 18 '20 at 03:53
  • Given SQLAlchemy version 1.3 or less, this seems to rely on undocumented behaviour. Without an explicitly started transaction an `Engine` or `Connection` operates in autocommit mode, which is documented for example [here](https://docs.sqlalchemy.org/en/13/core/connections.html#understanding-autocommit). Due to how DB-API works (a connection is (almost) always in a transaction), and SQLAlchemy not explicitly issuing rollbacks in autocommit mode, the executed `COMMIT` does work, but the proper solution would be to use SQLA's transaction handling: https://stackoverflow.com/a/59702609/2681632 – Ilja Everilä Jan 18 '20 at 08:24
  • 1
    ...Executing `COMMIT` would not work using an `Engine`, because in "connectionless" execution the connection is returned to the pool after using it and in that case it *is* rollbacked. – Ilja Everilä Jan 18 '20 at 08:27