How to create a new database using SQLAlchemy?

Question

With SQLAlchemy, the engine is created as follows:

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

Access to the engine fails if there is no database. Can SQLAlchemy specify a new database if the specified database does not exist?

Answer 1

Postgres usually has three databases by default. If you can connect as a superuser (for example, the postgres role), you can connect to the postgres or template1 databases. By default, pg_hba.conf only allows a unix user named postgres use the postgres role, so it’s easiest to just become that user. In any case, create the engine, as usual, with the user who has permissions to create the database:

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

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

 >>> conn = engine.connect() 

But the connection will still be inside the transaction, so you need to complete the open transaction with commit :

 >>> conn.execute("commit") 

Then you can continue to create the database using the correct PostgreSQL command.

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

Answer 2

See http://sqlalchemy-utils.readthedocs.org/en/latest/database_helpers.html

 from sqlalchemy import create_engine from sqlalchemy_utils import database_exists, create_database engine = create_engine("postgres://localhost/mydb") if not database_exists(engine.url): create_database(engine.url) print(database_exists(engine.url)) 

Answer 3

You can avoid manual transaction management when creating the database by providing the isolation_level='AUTOCOMMIT' to create_engine :

 import sqlalchemy with sqlalchemy.create_engine( 'postgresql:///postgres', isolation_level='AUTOCOMMIT' ).connect() as connection: connection.execute('CREATE DATABASE my_database') 

Also, if you are not sure that the database does not exist, there is a way to ignore the database creation error due to existence by suppressing the sqlalchemy.exc.ProgrammingError exception:

 import contextlib import sqlalchemy.exc with contextlib.suppress(sqlalchemy.exc.ProgrammingError): # creating database as above