How to solve psycopg SyntaxError while executing "SHOW DATABASES LIKE" query?-CodePudding

I use Windows 11, Python 3.10.4 64-bit, PostgreSQL 14.2 database and psycopg2 2.9.3.

I connect to a database:

import psycopg2

SCHEMA_NAME = "myschema"

connx = psycopg2.connect(database="mydatabase", user="myadmin", password="123456", host="127.0.0.1", port="5432", options="-c search_path=myschema")

cur = connx.cursor()

Then I want to check if the schema exists:

sql_str = "SHOW DATABASES LIKE %s ESCAPE ''"
cur.execute(sql_str, [SCHEMA_NAME])

but I get the following error:

Traceback (most recent call last):

    cur.execute("SHOW `DATABASES` LIKE %s ESCAPE ''", [SCHEMA_NAME])
psycopg2.errors.SyntaxError: syntax error at or near "LIKE"
LINE 1: SHOW DATABASES LIKE 'myschema' ESCAPE ''

What is wrong here?

CodePudding user response：

In PostgreSQL a schema and a database are 2 different things.

The following query will tell you whether the schema exists.

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'myschema';

And your code becomes

sql_str = "SELECT schema_name FROM information_schema.schemata WHERE schema_name =  %s ;"
cur.execute(sql_str, [SCHEMA_NAME])

If you are checking that a database exists it will be

SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname';

And your code will become

sql_str = "SELECT datname FROM pg_catalog.pg_database WHERE datname =  %s ;"
cur.execute(sql_str, [DATABASE_NAME])