title: How can i keep the same session using subprocess in Pytest script
body: <p>I am writing a Pytest where I am creating temp tables in memory in db using a fixture and scoping the fixture to sesssion.</p>
  
  <pre><code>@fixture(scope='session')
  def tmp_dim_sponsor(get_redshift_connection):
      sql = """DROP TABLE IF EXISTS mock_tmp_dim_sponsor;
          CREATE TEMP TABLE mock_tmp_dim_sponsor (LIKE 
  tmp.tmp_dim_sponsor);"""
      execute_sql_no_return(sql, get_redshift_connection, False)
      print("mock_tmp_dim_sponsor Created")
  </code></pre>
  
  <p>I then have another fixture which calls a bash script using subprocess. This bash scripts needs to reference these temp tables that were created earlier using the fixture. The bash scripts makes a new connection to db and then executes the sql which has reference to temp tables created earlier.</p>
  
  <pre><code>@fixture(scope='session')
  def dim_sponsor_transformation_1(get_redshift_connection):
      copy2('/dim_sponsor_transformation_test.sh', 
  '/tmp_dim_sponsor_transformation.sh')
      with open("tmp_dim_sponsor_transformation.sh", "r+") as file1:
          with open("mock_dim_sponsor_transformation.sh", "w") as file2:
              for line in file1:
                  new = re.sub(r'\$\{\w*SCHEMA\}\.', "mock_", line)
                  new1 = re.sub(r'check_status \$\?.*', "", new)
                  new2 = re.sub(r'&gt;&gt; \$LOGDIR.*', "", new1)
                  new3 = re.sub(r'log .*"', "", new2)
                  new4 = re.sub(r'SCRIPTDIR="\$HOME/scripts"', 
  'SCRIPTDIR="/Users/abc/src/main/scripts"', new3)
                  file2.write(new4)
              file.close(file1)
              file.close(file2)
              print(file2)
      os.chmod('mock_dim_sponsor_transformation.sh', 0o777)
      print("Changed the permission")
      print("Starting to Run the Transformation")
      t = 
  subprocess.check_output(["./mock_dim_sponsor_transformation.sh", 
  "qa"])
      print(t)
      print("Completed Transformation")
  </code></pre>
  
  <p>The bash script (mock_dim_sponsor_transformation.sh) makes a new connection to db and then executes the sql. </p>
  
  <pre><code>SCRIPTDIR="/User/xxx/xxx/src/main/scripts"
  source $SCRIPTDIR/utils/get_environment.sh $1
  export PGPASSWORD=$RSDBPASSWORD
  
  PAST_DAY=$(date -d "yesterday" +"%Y-%m-%d")
  PAST_DAY_VALUE="'"${PAST_DAY}"'"
  
  psql -h $RSDBHOST -p $RSDBHOSTPORT -U $RSDBOWNER -d $RSDBNAME -c \
  "
  TRUNCATE mock_tmp_dim_sponsor;
  " 
  
  psql -h $RSDBHOST -p $RSDBHOSTPORT -U $RSDBOWNER -d $RSDBNAME -c \
  "
  INSERT INTO     mock_tmp_dim_sponsor
              (
              sponsor_legacy_id,
              sponsor_id,
              name,
              plan_type,
              default_segment_id,
              created_date,
              updated_date,
              is_genesis,
              sponsor_display_name,
               ...
  </code></pre>
  
  <p>However it is not able to find those temp tables which i guess makes sense b/c 
  1. Since the bash script is making a new connection to db, it will not find temp tables since temp tables are in memory of that session.
  2. Does subprocess creates a new session when calling another script?</p>
  
  <p>Any suggestions on how can i be able to reference the the temp tables created in fixture in the bash script? 
  Also, is there a way i can pass the db connection object in subprocess call so that mock_dim_sponsor_transformation.sh does not make a new connection?</p>
  
  <p>I am fairly new to Pytest and still learning. Any suggestions or guidance would be much appreciated. Thanks!</p>