Unable to create schema for Postgres database using sqlalchemy

Question

I'm able to create a schema via the command line using CREATE SCHEMA test_schema;.

However, running the following code doesn't create a schema:

from sqlalchemy import create_engine from sqlalchemy.schema import CreateSchema def main(): conn_str = "postgresql+psycopg2://<myusername>:<mypassword>@localhost/belgarath_test" engine = create_engine(conn_str, echo=True) connection = engine.connect() connection.execute(CreateSchema("test_schema")) if __name__ == "__main__": main()

Weirdly sqlalchemy does emit the correct sql. Here's the full output:

2024-12-29 17:54:22,128 INFO sqlalchemy.engine.Engine select pg_catalog.version() 2024-12-29 17:54:22,129 INFO sqlalchemy.engine.Engine [raw sql] {} 2024-12-29 17:54:22,129 INFO sqlalchemy.engine.Engine select current_schema() 2024-12-29 17:54:22,129 INFO sqlalchemy.engine.Engine [raw sql] {} 2024-12-29 17:54:22,130 INFO sqlalchemy.engine.Engine show standard_conforming_strings 2024-12-29 17:54:22,130 INFO sqlalchemy.engine.Engine [raw sql] {} 2024-12-29 17:54:22,130 INFO sqlalchemy.engine.Engine BEGIN (implicit) 2024-12-29 17:54:22,130 INFO sqlalchemy.engine.Engine CREATE SCHEMA test_schema # <------------------ 2024-12-29 17:54:22,130 INFO sqlalchemy.engine.Engine [no key 0.00008s] {}

Any ideas on why the schema isn't created?

1) I am not seeing any COMMIT in that sequence, in which case the schema creation would not survive the session or be seen by another session. Read Commit as you go. 2) If by ...via the command line you mean using psql, then psql has autocommit by default, so the CREATE SCHEMA test_schema; would be committed. — Adrian Klaver
– Adrian Klaver, Commented Dec 29, 2024 at 18:16
@AdrianKlaver - thanks. Added an answer for full clarity. The command line reference was really to confirm that it wasn't any kind of permission issue... — Jossy
– Jossy, Commented Dec 29, 2024 at 18:36

Jossy · Accepted Answer · 2024-12-29 18:33:34Z

Thanks to @AdrianKlaver in the comments - got me on the right track.

Here's the solution I went with to ensure a COMMIT was issued:

from sqlalchemy import create_engine from sqlalchemy.schema import CreateSchema def main(): conn_str = "postgresql+psycopg2://philipjoss:Mombassa11@localhost/belgarath_test" engine = create_engine(conn_str, echo=True) with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection: with connection.begin(): connection.execute(CreateSchema("test_schema")) if __name__ == "__main__": main()

Collectives™ on Stack Overflow

Unable to create schema for Postgres database using sqlalchemy

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related