问题:如何用sqlalchemy创建postgres用户

我需要允许通过在后端使用 Python 和 SQLAlchemy 的 Web 应用程序创建数据库用户/角色。

为此,我正在使用类似的东西:

sql = 'CREATE USER :username WITH PASSWORD :passwd'
sql_data = {
    "username": "the_user_name",
    "passwd": "the_password",
}
request.db.execute(sql, sql_data)

但是这个查询创建的 SQL 是:

CREATE USER 'the_user_name' WITH PASSWORD 'the_password'

我需要的是:

CREATE USER "the_user_name" WITH PASSWORD 'the_password'

用户名正确转义。有没有一种方法可以让 SQLAlchemy 处理这个问题,或者我应该手动创建字符串?如果是这样,我该如何转义用户输入名称?

解答

使用引用_name:

表示结合引用首选项的 SQL 标识符。

quoted_name 是一个 Python unicode/str 子类,它代表一个特定的标识符名称和一个引号标志。 此引用标志设置为 True 或 False 时,会覆盖此标识符的自动引用行为,以便无条件引用或不引用名称。如果保留其默认值“无”,则引用行为将基于对令牌本身的检查在每个后端的基础上应用于标识符。

以下是我如何让它工作:

from sqlalchemy.sql import text, quoted_name

DATABASE_USER = "your_user_here"
DATABASE_USER_PASSWORD = "your_password"

create_user_sql = text(f"CREATE USER {quoted_name(DATABASE_USER, False)} WITH PASSWORD :database_password")\
    .bindparams(                                     # You can remove this line if you don't want to test
        database_password=DATABASE_USER_PASSWORD.    # You can remove this line if you don't want to test
    )\                                               # You can remove this line if you don't want to test
    .compile(compile_kwargs={"literal_binds": True}) # You can remove this line if you don't want to test
print(str(create_user_sql))                          # You can remove this line if you don't want to test

导致执行以下操作:

CREATE USER your_user_here WITH PASSWORD 'your_password'

您可以删除.bindparams()和.compile()并将参数传递给connection.execute(create_user_sql, database_password=DATABASE_USER_PASSWORD)看起来更干净,上面的代码只是一个概念证明。