How to create a database with psycopg2 using execute() second argument?

Question

I'm trying to create a database with the name a user will provide. As far as I know the correct way is to use the second argument of execute().

So I did as follows:

import psycopg2

conn = psycopg2.connect(host="...", dbname="...",
                        user="...", password="...", port='...')
cursor = conn.cursor()
query = ''' CREATE DATABASE %s ;'''
name = 'stackoverflow_example_db'

conn.autocommit = True
cursor.execute(query, (name,))

cursor.close()
conn.close()

And I got this error:

psycopg2.errors.SyntaxError: syntax error at or near "'stackoverflow_example_db'"
LINE 1:  CREATE DATABASE 'stackoverflow_example_db' ;

I need to do this statement avoiding SQL injection, so using the second argument is a must.

@dfundako it's for users with administration privileges, who do not necessary have to know SQL for the app purpose. — Raulillo, Sep 03 '19 at 17:15
Related: [Passing table name as a parameter in psycopg2](https://stackoverflow.com/q/13793399/190597). Although the discussion is about tables, the answer is essentially the same for database names. See in particular, [this answer](https://stackoverflow.com/a/42947632/190597) which uses [pyscopg2.sql.SQL](http://initd.org/psycopg/docs/sql.html#module-psycopg2.sql) and [Identifier](http://initd.org/psycopg/docs/sql.html#psycopg2.sql.Identifier)s to build the SQL dynamically. — unutbu, Sep 03 '19 at 17:22
Possible duplicate of [Create a Postgres database using python](https://stackoverflow.com/questions/34484066/create-a-postgres-database-using-python) — Life is complex, Sep 03 '19 at 17:36

Raulillo · Accepted Answer · 2019-09-03T18:19:51.120

You can't pass values as second argument of execute(), if the statement is a CREATE DATABASE one.

As pointed out by unutbu one way to approach this is using the psycopg2.sql submodule and use identifiers to build the statement avoiding SQL injection.

The code:

import psycopg2
from psycopg2 import sql

conn = psycopg2.connect(host="...", dbname="...",
                        user="...", password="...", port='...')
cursor = conn.cursor()
query = ''' CREATE DATABASE {} ;'''
name = 'stackoverflow_example_db'

conn.autocommit = True
cursor.execute(sql.SQL(query).format(
    sql.Identifier(name)))

cursor.close()
conn.close()

Other aditional observations:

format() do not work with %s, use {} instead
Autocommit mode is a must for this statement to work
The specified connection user needs creation privileges

How to create a database with psycopg2 using execute() second argument?

1 Answers1