title: Assign/Substitute a list of values in a SQL statement for PostgreSQL using Python
body: <p>I have a huge table with lot of partition tables underneath, but i only want to query/update few(18) of the partitions. So i have to prepare and run 18 python scripts (because i have to create and close the connection after inserting into every partition table as per our DBA instructions, because each <strong>period</strong> consists of millions of records) to run my Insert statements.
  So i tried two ways to solve this:
  One is to run a for loop to substitute the list of periods in my SQL statement which involves writing two python scripts:</p>
  <pre class="lang-py prettyprint-override"><code>import os
  import logging
  #import psycopg2
  import socket
  logging.basicConfig(level=logging.INFO)
  cpc_name = socket.gethostname()
  
  list_of_periods = [
      {'period': '1940'},
      {'period': '1941_1945'},
      {'period': '1946_1950'},
      {'period': '1951_1955'},
      {'period': '1956_1960'},
      {'period': '1961_1965'},
      {'period': '1966_1970'},
      {'period': '1971_1975'},
      {'period': '1976_1980'},
      {'period': '1981_1985'},
      {'period': '1986_1990'},
      {'period': '1991_1995'},
      {'period': '1996_2000'},
      {'period': '2001_2005'},
      {'period': '2006_2010'},
      {'period': '2011_2015'},
      {'period': '2016_2020'},
      {'period': '2021_2025'}
  ]
  
  if __name__ == &quot;__main__&quot;:
      logging.info(&quot;Starting test process&quot;)
      logging.info(&quot;  cpc = {}&quot;.format(cpc_name) + '\n')
      for period in list_of_periods:
          os.system('python sample_segment.py')
  
  
  ########
  sample_segment.py
  
  import os
  import logging
  import psycopg2
  import socket
  logging.basicConfig(level=logging.INFO)
  cpc_name = socket.gethostname()
  
  if __name__ == &quot;__main__&quot;:
      logging.info(&quot;Starting test process&quot;)
      logging.info(&quot;  cpc = {}&quot;.format(cpc_name) + '\n')
      connection = psycopg2.connect(user        = os.environ.get(&quot;DATABASE_USER&quot;, &quot;SVTDATAVANT&quot;),
                                    password    = os.environ.get(&quot;DATABASE_PASS&quot;, &quot;pass&quot;),
                                    host        = os.environ.get(&quot;DATABASE_HOST&quot;, &quot;psql.silver.com&quot;),
                                    port        = 5432,
                                    dbname      = os.environ.get(&quot;DATABASE_NAME&quot;, &quot;psql_db&quot;),
                                    options     = &quot;-c search_path=DATAVANT_O&quot;)                    
      with connection.cursor() as cursor:
          logging.info(str(connection.get_dsn_parameters()) + '\n')
          cursor.execute(&quot;SELECT version();&quot;)
          connection.commit()
          conn = cursor.fetchone()
          logging.info(&quot;You are connected to - &quot; + str(conn))
          tempb = '''
          INSERT INTO DATAVANT_STG_O.mortality_index_{period}           
          SELECT * FROM DATAVANT_STG_O.tmp_mortality_{period}; '''
          logging.info(&quot;Performing Insert Operation&quot;)
          cursor.execute(tempb)
          connection.commit()
          count = cursor.rowcount
          logging.info(str(count) + &quot; - count for the period:  {period}&quot;)
          logging.info(&quot; - count for the period:  {period}&quot;)
          connection.close()
          print(&quot;PostgreSQL connection is closed&quot;)
  
  </code></pre>
  <p>Obviously it did not work and i suspect my python programming skills of-course or maybe i am looking for something like <code> os.sytem.sql_with_parameters()</code>
  But anyway my intention is to turn the below statement</p>
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO DATAVANT_STG_O.mortality_index_{period}           
  SELECT * FROM DATAVANT_STG_O.tmp_mortality_{period};
  </code></pre>
  <p><strong>INTO</strong></p>
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO DATAVANT_STG_O.mortality_index_1940           
  SELECT * FROM DATAVANT_STG_O.tmp_mortality_1940;
  
  INSERT INTO DATAVANT_STG_O.mortality_index_1941_1945           
  SELECT * FROM DATAVANT_STG_O.tmp_mortality_1941_1945;
  
  etc...for 18 periods
  </code></pre>
  <p>And the second method i tried is by reading <strong>YAML</strong> file in my python script.</p>
  <pre class="lang-py prettyprint-override"><code>import os
  import logging
  import psycopg2
  import socket
  import yaml
  logging.basicConfig(level=logging.INFO)
  cpc_name = socket.gethostname()
  
  with open(r'/home/SILVER/user/test/periods.yaml') as file:
      YEARS = yaml.load(file, Loader=yaml.FullLoader)
      print(&quot;load yaml file&quot; + '\n')
      print(YEARS)
  
  
  if __name__ == &quot;__main__&quot;:
      logging.info(&quot;Starting test process&quot;)
      logging.info(&quot;  cpc = {}&quot;.format(cpc_name) + '\n')
      connection = psycopg2.connect(user        = os.environ.get(&quot;DATABASE_USER&quot;, &quot;SVTDATAVANT&quot;),
                                    password    = os.environ.get(&quot;DATABASE_PASS&quot;, &quot;pass&quot;),
                                    host        = os.environ.get(&quot;DATABASE_HOST&quot;, &quot;psql.host.com&quot;),
                                    port        = 5432,
                                    dbname      = os.environ.get(&quot;DATABASE_NAME&quot;, &quot;psql_db&quot;),
                                    options     = &quot;-c search_path=DATAVANT_O&quot;)                    
      with connection.cursor() as cursor:
          logging.info(str(connection.get_dsn_parameters()) + '\n')
          cursor.execute(&quot;SELECT version();&quot;)
          connection.commit()
          conn = cursor.fetchone()
          logging.info(&quot;You are connected to - &quot; + str(conn))
          tempb = '''
          INSERT INTO DATAVANT_STG_O.mortality_index_YEARS[periods]
          SELECT * FROM DATAVANT_STG_O.tmp_mortality_YEARS[periods]; '''
          cursor.execute(tempb)
          logging.info(&quot;Performing Insert Operation&quot;)
          connection.commit()
          count = cursor.rowcount
          logging.info(str(count) + &quot; - count for the period:  YEARS[periods]&quot;)
          logging.info(&quot; - count for the period:  YEARS[periods]&quot;)
          connection.close()
          print(&quot;PostgreSQL connection is closed&quot;)
  
  </code></pre>
  <p>##YAML file</p>
  <pre><code>---
  periods:
  
    - 1940
    - 1941_1945
    - 1946_1950
    - 1951_1955
    - 1956_1960
    - 1961_1965
    - 1966_1970
    - 1971_1975
    - 1976_1980
    - 1981_1985
    - 1986_1990
    - 1991_1995
    - 1996_2000
    - 2001_2005
    - 2006_2010
    - 2011_2015
    - 2016_2020
    - 2021_2025
  </code></pre>
  <p>This is the part where i learnt that values from YAML file cannot be assigned to a SQL statement in a python file.</p>
  <p>My apologies to the Large question but i just want to be clear and provide as much information as i could.</p>
  <p>I would be very grateful for any suggestions to help me solve this scenario using either of the methods mentioned above or feel free to recommend me a new one, Thank you!</p>
  <p>##Below is output, when i tried Mike's approach:</p>
  <pre class="lang-py prettyprint-override"><code>import os
  import logging
  import psycopg2
  import socket
  logging.basicConfig(level=logging.INFO)
  cpc_name = socket.gethostname()
  
  periods = ['1940']
  periods.extend(['{}_{}'.format(i, i + 4) for i in range(1941, 2026, 5)])
  
  for period in periods:
      # Do your psycopg2 connection here and get your cursor
      connection = psycopg2.connect(user        = os.environ.get(&quot;DATABASE_USER&quot;, &quot;SVTDATAVANT&quot;),
                                    password    = os.environ.get(&quot;DATABASE_PASS&quot;, &quot;pass&quot;),
                                    host        = os.environ.get(&quot;DATABASE_HOST&quot;, &quot;psql.silver.com&quot;),
                                    port        = 5432,
                                    dbname      = os.environ.get(&quot;DATABASE_NAME&quot;, &quot;psql_db&quot;),
                                    options     = &quot;-c search_path=DATAVANT_O&quot;)                    
      with connection.cursor() as cursor:
          logging.info(str(connection.get_dsn_parameters()) + '\n')
          cursor.execute(&quot;SELECT version();&quot;)
          connection.commit()
          conn = cursor.fetchone()
          logging.info(&quot;You are connected to - &quot; + str(conn))
          cursor.execute(&quot;&quot;&quot;
          SELECT COUNT(*) FROM datavant_stg_o.mortality_index_{};&quot;&quot;&quot;. format(period, period)
          )
          # Commit and close your connection here
          connection.commit()
          count = cursor.rowcount
          logging.info(&quot;Count for the period {} is: &quot; . format(period, period) + str(count) +  '\n')
          connection.close()
          print(&quot;PostgreSQL connection is closed&quot; + &quot;\n&quot;)
  </code></pre>
  <p>(Currently i am only trying to execute the count(*)s to check the functionality)
  So i am expecting the counts for the partitions as:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(*) FROM datavant_stg_o.mortality_index_1940 - 1001066
  SELECT COUNT(*) FROM datavant_stg_o.mortality_index_1941_1945 - 1713850
  etc which are the original counts when i query from pgAdmin
  </code></pre>
  <p>but instead i am getting the output as</p>
  <pre><code>Count for the period 1940 is: 1, 
  Count for the period 1941_1945 is: 1 etc,
  </code></pre>
  <p>Does it have anything to do with the quotes?</p>