Creating a PostgreSQL database using psycopg

Are you getting a postgres syntax error about an extra or missing quote around your database, table, or index you’re trying to create with Python?

Are you getting that error creating, dropping, or altering things in PostgreSQL via psycopg2 or psycopg3?

By things I mean all of the database objects that aren’t rows. Yeah, I’ve been there friend. Just now in fact!

This one is frustrating to debug and hard to Google for a solution. I’ve run into this AT LEAST THREE TIMES in recent years and I always have to re-discover the answer the hard way.

The Issue

Here is how you normally use psycopg 2 or 3. I’ve omitted the connection info and other boilerplate to focus on the important bits of code.

import psycopg2

conn = psycopg2.connect(...)
cursor = conn.cursor()

cursor.execute("INSERT INTO my_table (name) VALUES (%s);", ["my-value"])

So it is reasonable to assume that something like this would work:

cursor.execute("DROP TABLE IF EXISTS %s WITH(FORCE);", ["my-table"])

and confusing when you instead get this error:

syntax error at or near "'my-table'"
LINE 1: DROP DATABASE IF EXISTS 'my-table' WITH (FORCE);

If you haven’t poked around in a psql shell in awhile PostgreSQL is rightfully picky about how things are quoted in the SQL you write. The first INSERT query works because we’re having psycopg2 represent our string … as a string …

The second DOES NOT work because while the table name feels like it should be yet another string, it’s actually an identifier. And should be either bare, without quotes, or with double-quotes. Strings on the other hand use single quotes.

So this manifests in the seemingly odd "'<value>'" situation. psycopg is wrapping your string with single quotes and then PostgreSQL is assuming it is an identifier, and you can’t use single quotes in an identifier.

Once you know this fact, and on closer inspection, it does make sense. It just is not terribly useful in leading you to the solution.

The Solution

Luckily the solution is easy to implement, but for whatever reason hard for me to remember I need it so infrequently.

We need instead to do:

cursor.execute(
    sql.SQL(
        "DROP TABLE IF EXISTS {} (FORCE);").format(sql.Identifier("my-table")
    )
)

psycopg’s sql.SQL is another way to format SQL statements. And this one allows us to denote that what we’re passing is an identifier and not a string, number, or other data type.

The Next Problem

The next problem you may run into, is if you’re trying to drop or create specifically a database.

DROP DATABASE cannot run inside a transaction block

This one is easier to find a solution online, but it’s just a matter of turning on auto commit mode to your connection like this:

import psycopg2

conn = psycopg2.connect(...)
conn.autocommit = True

While I wrote this blog post for future me that runs into this particular problem again, I do hope you have found it useful.

Hopefully, this saved you the frustrating hour I had to spend on it. Again.

Frank Wiles

Founder of REVSYS and former President of the Django Software Foundation . Expert in building, scaling and maintaining complex web applications. Want to reach out? Contact me here or use the social links below.