Incompatibility of create_database() from sqlalchemy-utils 0.39.0 with SQLAlchemy 2.0.0

[introvenk]

Package / Application versions:

Python version: 3.9.0
sqlalchemy: 2.0.0
sqlalchemy-utils: 0.39.0
postgres 14

Description

In SQLAlchemy 2.0, Direct engine.execute() calls are no longer supported. The "connectionless" execution pattern, where engine.execute() implicitly procures a Connection object and runs the execute method, has several issues and was not intended for frequent use.

Due to this change that require the use of Connection objects instead of Engine objects, the create_database() function from the sqlalchemy-utils library has become incompatible with SQLAlchemy 2.0

The failure is caused by the get_bind() function not returning a bind for the Engine object and the subsequent failure of the quote() function to determine the dialect and ultimately leading to the failure of the create_database() function. A check in the quote() function to see if the input is an Engine object and using the dialect attribute directly will resolve this issue.

Steps to Reproduce

1. Install the latest version of SQLAlchemy and SQLAlchemy-utils
2. Try to use the create_database() function from sqlalchemy-utils on an SQLAlchemy 2.0 Engine object.
3. Observe the failure of the quote() function due to the failure of get_bind() to return a bind for the Engine object.

Expected Result

The create_database() function should successfully execute the create database statement.

Actual Result

When using the create_database() function from the sqlalchemy-utils library, the underlying quote() function that create_database() depends upon fails and raises an error because it cannot determine the dialect from the Engine object provided to it, this is because the get_bind() function expects the Engine object to have the execute attribute but in SQLAlchemy 2.0 it has been changed. The error message reads as follows:

TypeError: This method accepts only Session, Engine, Connection and declarative model objects.

Solution

To resolve this issue, a check can be added in the quote() function to see if the input is an Engine object and using the dialect attribute on it directly. I am raising a pull request with these changes and would welcome any guidance or suggestions to address the underlying issues with the functions.

[kvesteri]

Fixed in #690