Our system is running on Ubuntu, python 3.4, postgres 9.4.x and psycopg2.
We (will in the furture) split between
environments using schemas. I've create a convenience method for creating connections to our database. It uses json connection configuration files in order create the connection string. I want to configure the connection to use a particular schema for all following queries using the returned connection. I don't want my queries to have hardcoded schemas, because we should be able to easily switch between them depending on if we are in development, testing or production phase/environment.
Currently the convenience method looks like the following:
def connect(conn_config_file = 'Commons/config/conn_commons.json'):
with open(conn_config_file) as config_file:
conn_config = json.load(config_file)
conn = psycopg2.connect(
"dbname='" + conn_config['dbname'] + "' " +
"user='" + conn_config['user'] + "' " +
"host='" + conn_config['host'] + "' " +
"password='" + conn_config['password'] + "' " +
"port=" + conn_config['port'] + " "
cur = conn.cursor()
cur.execute("SET search_path TO " + conn_config['schema'])
It works fine as long as you give it time to execute the set
query. Unfortunately, if I'm too fast with executing a following query a race condition happens where the
isn't set. I've tried to force the execution with doing a
, however, this resets the
to the default schema
so that it doesn't use, say,
. Suggestions at the database or application layer is preferable, however, I know we probably could solve this at the OS level too, any suggestions in that direction are also welcomed.
An example json configuration file looks like the following:
Any suggestion is very appreciated.