Using the psycopg2 module to connect to the PostgreSQL database using python. Able to execute all queries using the below connection method. Now I want to specify a different schema than public to execute my SQL statements. Is there any way to specify the schema name in the connection method?

conn = psycopg2.connect(host="localhost",
                            port="5432",
                            user="postgres",
                            password="password",
                            database="database",
                            )

I tried to specify schema directly inside the method. schema="schema2" But I am getting the following programming error.

ProgrammingError: invalid dsn: invalid connection option "schema"

When we were working on ThreadConnectionPool which is in psycopg2 and creating connection pool, this is how we did it.

from psycopg2.pool import ThreadedConnectionPool

db_conn = ThreadedConnectionPool(
    minconn=1, maxconn=5,
    user="postgres", password="password", database="dbname", host="localhost", port=5432,
    options="-c search_path=dbo,public"
)

You see that options key there in params. That's how we did it.

When you execute a query using the cursor from that connection, it will search across those schemas mentioned in options i.e., dbo,public in sequence from left to right.

You may try something like this:

psycopg2.connect(host="localhost", 
                 port="5432", 
                 user="postgres", 
                 password="password", 
                 database="database", 
                 options="-c search_path=dbo,public")

Hope this might help you.