Setting schema for all queries of a connection in psycopg2: Getting race condition when setting search_path

I think a more elegant solution would be to set the search_path in options parameter of connect(), like so:

def connect(conn_config_file = 'Commons/config/conn_commons.json'):    with open(conn_config_file) as config_file:            conn_config = json.load(config_file)    schema = conn_config['schema']    conn = psycopg2.connect(        dbname=conn_config['dbname'],        user=conn_config['user'],        host=conn_config['host'],        password=conn_config['password'],        port=conn_config['port'],        options=f'-c search_path={schema}',    )    return conn

Of course, you can use "options" as part of the connection string. But using keyword arguments prevents all the hassle with string concatenations.

I found this solution in this psycopg2 feature request. As for the "options" parameter itself, it's mentioned here.

I think a better idea is to have something like DatabaseCursor returning cursor you use to execute queries with "SET search_path..." instead of connection.Well I mean something like this:

class DatabaseCursor(object):    def __init__(self, conn_config_file):        with open(conn_config_file) as config_file:                 self.conn_config = json.load(config_file)    def __enter__(self):        self.conn = psycopg2.connect(            "dbname='" + self.conn_config['dbname'] + "' " +             "user='" + self.conn_config['user'] + "' " +             "host='" + self.conn_config['host'] + "' " +             "password='" + self.conn_config['password'] + "' " +             "port=" + self.conn_config['port'] + " "         )           self.cur = self.conn.cursor()        self.cur.execute("SET search_path TO " + self.conn_config['schema'])        return self.cur    def __exit__(self, exc_type, exc_val, exc_tb):        # some logic to commit/rollback        self.conn.close()

and

with DatabaseCursor('Commons/config/conn_commons.json') as cur:    cur.execute("...")