Search code examples
pythonpython-3.xpostgresqlpsycopg3

Python: using psycopg3 to connect to specific schema in database?

this is my first time asking a question, cause usually i'm able to find an answer in older threads. ANYWAY, i'm developing a software (in Python) to analyze certain kind of errors inside specific tables in a database (in PostgreSQL). The structure is like this:

DATABASE
├── schema1
│   └── some_table
│   └── another_talbe
│   └── just_table
│   └── supreme_table
├── schema2
    └── (guess what, other tables)

So, using psycopg, i was able to connect to DATABASE, but i have to work only on the first schema, so it's pretty annoying to write every query with something like "schema1.some_table". Is there something i missed to connect directly to a specific schema?

First thing i tried was using a dotted dbname for psycopg (like dbname="DATABASE.schema1") but it didnt work. Then i tried looking at the documentation, in search of a parameter that could help me, but didnt find an answer.

Solution

  • Likely not in psycopg3 itself, but see the documentation on the schema search path; you can probably just call

    con.execute("SET SEARCH_PATH to schema1")

    as the first thing you do with your connection.