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)