How to create a new database using SQLAlchemy?

Using SQLAlchemy, an Engine object is created like this:

from sqlalchemy import create_engine
engine = create_engine("postgresql://localhost/mydb")

Accessing engine fails if the database specified in the argument to create_engine (in this case, mydb) does not exist. Is it possible to tell SQLAlchemy to create a new database if the specified database doesn’t exist?

Answer

On postgres, three databases are normally present by default. If you are able to connect as a superuser (eg, the postgres role), then you can connect to the postgres or template1 databases. The default pg_hba.conf permits only the unix user named postgres to use the postgres role, so the simplest thing is to just become that user. At any rate, create an engine as usual with a user that has the permissions to create a database:

>>> engine = sqlalchemy.create_engine("postgres://postgres@/postgres")

You cannot use engine.execute() however, because postgres does not allow you to create databases inside transactions, and sqlalchemy always tries to run queries in a transaction. To get around this, get the underlying connection from the engine:

>>> conn = engine.connect()

But the connection will still be inside a transaction, so you have to end the open transaction with a commit:

>>> conn.execute("commit")

And you can then proceed to create the database using the proper PostgreSQL command for it.

>>> conn.execute("create database test")
>>> conn.close()