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
– Raulillo, Commented Sep 3, 2019 at 17:15
Related: Passing table name as a parameter in psycopg2. Although the discussion is about tables, the answer is essentially the same for database names. See in particular, this answer which uses pyscopg2.sql.SQL and Identifiers to build the SQL dynamically. — unutbu
– unutbu, Commented Sep 3, 2019 at 17:22
Possible duplicate of Create a Postgres database using python — Life is complex
– Life is complex, Commented Sep 3, 2019 at 17:36

Raulillo · Accepted Answer · 2019-09-03 18:19:51Z

6

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

edited Sep 3, 2019 at 18:19

answered Sep 3, 2019 at 17:44

Raulillo

3183 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related