How would I create a PostgreSQL database using "CREATE DATABASE" over an ODBC connection using PyODBC?

Question

I am attempting to create a PostgreSQL 12.1 database (not a schema) using the CREATE DATABASE statement from a Python 3.7.5 program connecting to a PostgreSQL 12.1 instance as user postgres over UnixODBC and using the PyODBC module, all running on Ubuntu 19.10. My program runs a loop that reads lines from a SQL DDL script, splitting SQL statements on the semicolon character. It logs the following errors:

16:15:20 ERROR    Error executing statement 'CREATE DATABASE "TestV1" WITH OWNER = postgres': ('25001', '[25001] ERROR: CREATE DATABASE cannot run inside a transaction block;\nError while executing the query (1) (SQLExecDirectW)')
16:15:20 CRITICAL Unable to create objects: ('25001', '[25001] ERROR: CREATE DATABASE cannot run inside a transaction block;\nError while executing the query (1) (SQLExecDirectW)')

My SQL DDL script begins with the following line: CREATE DATABASE "TestV1" WITH OWNER = postgres;

I've had success with CREATE SCHEMA in the same context, but not with CREATE DATABASE. As well, I cannot seem to find any answers here related to CREATE DATABASE on PostgreSQL specifically. I can create a database from the shell command line using createdb. I have tried setting AUTOCOMMIT on within the SQL DDL script, but that does not work.

Can you try setting autocommit=True in pyodbc.connect()? — mechanical_meat
– mechanical_meat, Commented Feb 14, 2020 at 0:45
I've tried setting AutoCommit=True in the connection string and it turns out to be the default, so the result is the same as if I left it out. — Michael G. Morey
– Michael G. Morey, Commented Feb 14, 2020 at 23:14
"[autocommit=True] turns out to be the default" - No. The Python DBAPI clearly specifies that the default must be autocommit=False. — Gord Thompson
– Gord Thompson, Commented Feb 14, 2020 at 23:33
@GordThompson Thanks for that. I was able to verify that autocommit is false by default when I tried removing my calls to the commit() and rollback() methods and was subsequently surprised to find my data missing. — Michael G. Morey
– Michael G. Morey, Commented Feb 15, 2020 at 0:37

Michael G. Morey · Accepted Answer · 2020-02-14 23:15:47Z

1

It seems it is best to issue the createdb command from the Bash shell and be done with it. I can then connect to the newly created database from ODBC. Rinse and repeat for each database to be created and populated.

answered Feb 14, 2020 at 23:15

Michael G. Morey

3811 gold badge5 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mechanical_meat Over a year ago

Good on you for the workaround. Shame this doesn't work through the interface.

Collectives™ on Stack Overflow

How would I create a PostgreSQL database using "CREATE DATABASE" over an ODBC connection using PyODBC?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related