SQLAlchemy Parameterize an Unquoted Value

Question

I'm trying to dynamically create users in a database like so:

from sqlalchemy.sql import text

def create_user(user: str, password: str):
    stmt = text("CREATE USER :user WITH PASSWORD :password")
    engine.execute(stmt, user=user, password=password)

create_user("bob", "password123")

But the problem with this is that SQLAlchemy will parameterize this query as:

CREATE USER 'bob' WITH PASSWORD 'password123'"

Which throws an error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "'bob'"
LINE 1: CREATE USER 'bob' WITH PASSWORD 'password123'

This error is thrown because bob is supposed to be unquoted. The correct SQL output should in fact be:

CREATE USER bob WITH PASSWORD 'password123'

How can I prevent SQLAlchemy from wrapping the parameterized value in quotation marks?

Answer 1

In cases where standard query parameterization is not applicable, such as when trying to incorporate database object names into a SQL statement, we must resort to dynamic SQL:

with engine.begin() as conn:
    sql = "CREATE USER {} WITH PASSWORD {}".format("bob", "password123")
    conn.exec_driver_sql(sql)

(Obviously, care must be taken to avoid a visit from Little Bobby Tables.)