How to create a database using psycopg3?

Question

This does not work:

conn = psycopg.connect(dsn)
conn.execute("CREATE DATABASE test")

Here is the documentation about transactions in psycopg3: https://www.psycopg.org/psycopg3/docs/basic/transactions.html

The most important statement for this problem:

Psycopg has a behaviour that may seem surprising compared to psql: by default, any database operation will start a new transaction.

It is quite a long page, but it does not tell anywhere how to execute a statement without starting a new transaction. There is an autocommit=True argument for connect(), but it doesn't work either.

No matter what I do, I always get this error:

psycopg.errors.ActiveSqlTransaction: CREATE DATABASE cannot run inside a transaction block

How can I create a database with psycopg3?

Does this answer your question? [Create a Postgres database using python](https://stackoverflow.com/questions/34484066/create-a-postgres-database-using-python) — Maurice Meyer, Jan 07 '22 at 12:42
It does not. That answer is for psycopg2. In psycopg3, there is no isolation level called "auto commit". It is not an isolation level anyway. In psycopg3, you can set autocommit=True in connect(), but I have already tried that. You can also call set_isolation_level on it but it has different values ( https://www.psycopg.org/psycopg3/docs/api/connections.html#psycopg.IsolationLevel ). The problem is not the isolation level. The problem is that a transaction is automatically started for any statement, including DDL statements. — nagylzs, Jan 07 '22 at 15:35
The page you mention has an example precisely about CREATE DATABASE: > The manual commit requirement can be suspended using autocommit, either as connection attribute or as connect() parameter. This may be required to run operations that cannot be executed inside a transaction, such as CREATE DATABASE, VACUUM, CALL on stored procedures using transaction control. — piro, Jan 07 '22 at 16:58
The "manual commit requirement" precisely means that you do not need to commit manually. It does not mean that no transaction is started. The error message says that CREATE DATABASE cannot be run inside a transaction. The problem is starting the transaction in the first place. The commit (auto or not) can only come after the CREATE DATABASE statement was executed. — nagylzs, Jan 08 '22 at 06:58
I was wrong after all, using autocommit=True DOES work. At some point in my application, the autocommit=True parameter was not passed to a method call. It was a simple mistake. — nagylzs, Jan 08 '22 at 12:02

score 5 · Accepted Answer · answered Jan 07 '22 at 16:25

Using an autocommit connection works for me:

>>> conn = psycopg.connect(dbname='postgres', autocommit=True)
>>> cur = conn.cursor()
>>> cur.execute('drop database if exists test3')
<psycopg.Cursor [COMMAND_OK] [IDLE] (user=xxx database=postgres) at 0x7f438ef92f00>
>>> cur.execute('create database test3')
<psycopg.Cursor [COMMAND_OK] [IDLE] (user=xxx database=postgres) at 0x7f438ef92f00>
>>>

xxx@host psql postgres -tl | grep test3
 test3             │ xxx      │ UTF8     │ en_GB.UTF-8 │ en_GB.UTF-8 │

How to create a database using psycopg3?

1 Answers1