How to specify Schema in psycopg2 connection method?

Question

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"

Answer 1

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.

Answer 2

If you are using the string form you need to URL escape the options argument:

postgresql://localhost/airflow?options=-csearch_path%3Ddbo,public

(%3D = URL encoding of =)

This helps if you are using SQLAlchemy for example.