1

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.

cristi.calugaru
  • 571
  • 10
  • 22
  • 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 Oct 03 '19 at 14:58

1 Answers1

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'))
)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 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. – cristi.calugaru Oct 03 '19 at 14:29
  • 1
    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. – Laurenz Albe Oct 03 '19 at 14:53
  • 2
    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. – Parfait Oct 03 '19 at 14:53