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.

Answer 1

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