Sql alchemy quote database bind parameter on postgres

Question

I am new to sql alchemy.

I have a postgres local server, and I want to use sql alchemy to create a database.

I have the following code:

connection = engine.connect()
connection.execute(
    text("CREATE DATABASE :database_name").bindparams(bindparam('database_name', quote=False)),
    database_name="test_db"
)

But this unfortunately single quotes the database name parameter, which does not work in postgres. The logs from sql alchemy:

[SQL: CREATE DATABASE %(database_name)s]
[parameters: {'database_name': 'test_db'}]
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "'test_db'" LINE 1: CREATE DATABASE 'test_db`'

In postgres logs, it executes the following statement, which is invalid because of the single quotes. A valid one would have double quotes:

CREATE DATABASE 'test_db'

Is there a way for the bind parameter to not be quoted in the resulting statement? I do not want to do the parameter quoting and string creation myself, as I think this abstraction should be handled by sql alchemy - in case I change my underlying database engine for eg, and this looks to be the mechanism sql alchemy promotes to avoid sql injections too.

The same question would apply to other postgres statements like creating an user with a password, or granting privileges to an existing user, which all need quoting which is postgres specific.

Reconsider your database design and/or project needs. CREATE DATABASE is a powerful statement as it sets up entire internal structures. While you aim for best practices re SQL injections, you should also heed proper design and not build databases in application layer for security and durability reasons. — Parfait
– Parfait, Commented Oct 3, 2019 at 14:58

Laurenz Albe · Accepted Answer · 2019-10-03 14:47:58Z

3

You cannot have parameters in statements other than SELECT, INSERT, UPDATE or DELETE.

You'll have to construct the CREATE DATABASE statement as a string containing the database name. Something like

from psycopg2 import sql

cursor.execute(
    sql.SQL("CREATE DATABASE {}").format(sql.Identifier('test_db'))
)

edited Oct 3, 2019 at 14:47

answered Oct 3, 2019 at 14:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cristi.calugaru Over a year ago

I get a "sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: Composed([SQL('CREATE DATABASE '), Identifier('test_db')])" for that. I am also looking for something that would work across other databases too, so that if I change to Mysql or SqlServer, I don't have to change my imports and use something else.

Laurenz Albe Over a year ago

I fixed the example. cursor should be a psycopg2 cursor. CREATE DATABASE is definitely not a statement that will work in all RDBMS. It is not even in the standard. And if it works, it will mean quite different things in different RDBMS.

Parfait Over a year ago

This is not an SQLAlchemy solution but the DB-API, pyscopg2, solution. Also, no two RDBMS's are alike for easy interchangeability. And I would refrain from ever creating databases on the fly in application layer scripts like Python. Databases, schemas, users, etc. should be planned, manual, one-time events not run in code.

Collectives™ on Stack Overflow

Sql alchemy quote database bind parameter on postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related