使用SQLAlchemy执行CREATE DATABASE IF NOT EXISTS报错，如何修复？

我参考示例编写了实现CREATE DATABASE IF NOT EXISTS功能的代码，代码如下：

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
db_user = 'postgres'
db_pass = 'xxxxxx'
db_ip = '127.0.0.1'
db_name = 'mydb'
engine = create_engine('postgresql://{}:{}@{}/{}/'.format(db_user,db_pass,db_ip,db_name))
if not database_exists(engine.url): create_database(engine.url)

运行时触发如下错误：

Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy_utils/functions/database.py", line 569, in create_database
    quote(engine, database),
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy_utils/functions/orm.py", line 528, in quote
    dialect = get_bind(mixed).dialect
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy_utils/functions/orm.py", line 339, in get_bind
    raise TypeError(
TypeError: This method accepts only Session, Engine, Connection and declarative model objects.

问题原因 #

1. 数据库URL格式错误：URL末尾多了一个斜杠/，导致create_database解析数据库名称时出现异常。
2. 参数类型不兼容：部分版本的sqlalchemy_utils中，create_database对传入的URL对象处理存在问题，更适配Engine对象作为参数。

修正代码 #

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

db_user = 'postgres'
db_pass = 'xxxxxx'
db_ip = '127.0.0.1'
db_name = 'mydb'

# 移除URL末尾的多余斜杠
db_url = f'postgresql://{db_user}:{db_pass}@{db_ip}/{db_name}'
engine = create_engine(db_url)

# 传入Engine对象而非URL对象
if not database_exists(engine):
    create_database(engine)

备选方案（针对特殊场景） #

如果上述方法仍报错，可以先连接到PostgreSQL默认的postgres数据库（避免直接连接不存在的目标库），再执行创建操作：

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

db_user = 'postgres'
db_pass = 'xxxxxx'
db_ip = '127.0.0.1'
db_name = 'mydb'

db_url = f'postgresql://{db_user}:{db_pass}@{db_ip}/{db_name}'
# 先连接默认的postgres库
temp_engine = create_engine(f'postgresql://{db_user}:{db_pass}@{db_ip}/postgres')

if not database_exists(db_url):
    create_database(db_url)

# 再创建目标库的Engine
engine = create_engine(db_url)

内容的提问来源于stack exchange，提问作者showkey