How do you connect to specific schema in Postgres? (Python, Sqlalchemy)

Question

How do you connect to specific Postgres schema using Sqlalchemy?

So I've created a new schema called "rezerwacja_domkow". I can see it in Pgadmin4. But the whole schema system confuses me (so far I've played only with sqlite which was much more straightforward). I have no clue how to connect to it and work only in this schema.

When I connect to the main db I see:

            List of schemas
       Name        |       Owner       
-------------------+-------------------
 public            | pg_database_owner
 rezerwacja_domkow | postgres
 tiger             | postgres
 tiger_data        | postgres
 topology          | postgres
(5 rows)

But when I try to list table names using Sqlalchemy inspector I don't see any tables from "rezerwacja_domkow" schema. I'd like to "connect" to this specific schema and modify / add data only to this one. Your help is greatly appreaciated.

Answer 1

To connect to the specific schema you first need to create database URL. This can be done as:

database_url = "postgresql://username:password@host:port/database_name?currentSchema=rezerwacja_domkow"

The "currentSchema" property is referring to the schema selected.

Next, create engine:

engine = create_engine(database_url)

Now, to execute query:

query = "SELECT * FROM table_name"
result = engine.execute(query)

In the end close connection:

result.close()
engine.dispose()

*Replace table names & credentials accordingly.

Reference-1: https://analyzingalpha.com/connect-postgresql-sqlalchemy

Reference-2: https://docs.sqlalchemy.org/en/20/core/engines.html

Answer 2

You may want to take half an hour out to at least skim the official manuals. Schemas are just like directories or any other namespace. Just like directories let you have two files with the same name "dir1/file1.txt" and "dir2/file2.txt" so schemas let you have two tables with the same name.

And, just like with directories you either need to give the full path to a file ("dir1/file1.txt") or specify some other way to find it. With unix/linux systems there is the concept of a "search path" which is held in a variable and for example saves you having type "/usr/bin/date". Instead you can just type "date", hit enter and your computer will check all the directories in the $PATH variable in order.

Exactly the same situation applies in PostgreSQL. You can either refer to a table by its full schema:

SELECT * FROM rezerwacja_domkow.table1

Or let your search-path handle it

SET search_path=rezerwacja_domkow,some_other_schema,public;
SELECT * FROM table1;

Like most other PostgreSQL settings you can provide a default value for the search_path for a specific database or user (ALTER DATABASE ... or ALTER USER ...) or just set it on connection or in a session like I showed above.

Plenty of examples of doing this online.