Best way to access schema in postgres with sqlalchemy

Question

I am working with a Postgres database. This database has three schemas (schema_1, schema_2, public). If I run a simple query, the public schema will be queried by default:

from sqlalchemy import create_engine
con  = create_engine('postgresql+pg8000://usr:pwd@server/db')
con.execute('select count(*) from the_table')

I cannot access the tables in schema_1 or schema_2, unless I specify the path in the query:

con.execute('select count(*) from schema_1.the_table')

Is there any way to specify the default path of the query to schema_1 without the need of specifying the full path in the query itself?

I tried with:

con.execute('SET search_path TO "schema_1";')

but this does not seem to work:

insp = inspect(con)
print(insp.default_schema_name)
# 'public'

I believe I am not executing the SET search_path TO "schema_1" correctly because the same command does work in other Postgres clients (like pgAdmin)

I don't think [`default_schema_name`](http://docs.sqlalchemy.org/en/latest/core/reflection.html#sqlalchemy.engine.reflection.Inspector.default_schema_name) is what you think it is ("Return the default schema name presented by the dialect for the current engine’s database user."). Have you actually tried executing some statements that'd use the tables from schema_1 after setting the search path? — Ilja Everilä, Oct 03 '17 at 08:51
I did and I am getting an error `42P01` saying the table does not exist — VinceP, Oct 03 '17 at 08:56
Fair enough, but the inspection attribute would to my reading reflect the setting set by `ALTER ROLE SET search_path TO ...` instead of the session's current setting. But that's just my view. — Ilja Everilä, Oct 03 '17 at 08:57
the sequential commands should be run on the same *connection*, not just the same engine that makes one-off connections. — Antti Haapala -- Слава Україні, Oct 03 '17 at 09:19
As @AnttiHaapala pointed out, you're actually using the engine, not a connection. So you execute each query with a (possibly) new connection. And when the engine's done with the connection, it is returned to the pool and rollbacked. That resets the search path. — Ilja Everilä, Oct 03 '17 at 09:19
As for the best way, not using any search path would be preferred. — Antti Haapala -- Слава Україні, Oct 03 '17 at 09:25

Best way to access schema in postgres with sqlalchemy

0 Answers0