How to create a new database using SQLAlchemy?
python, sqlalchemy
Solution
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()
Problem
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?