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?

Answer 1

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 │