title: Nest dictionary insert to psycopg2
body: <p>I am trying to input data from a nested dictionary structure into a PostgreSQL DB using psycopg2 as shown <a href="https://wiki.postgresql.org/wiki/Psycopg2_Tutorial" rel="nofollow">here</a>. What I'm unsure of is how to access the nested parts of the dictionary or if this is even possible to do in one insert query. The data structure looks like this:</p>
  
  <pre><code>dictionary = ({
              "article_id":94527, "article_title":"An article title",
              "country_info": [{"country_id":281133,
                                "country_code":"GB",
                                "country_name":"United Kingdom",
                                "lon":32.0000,
                                "lat":49.0000},
                               {"country_id":281133,
                                "country_code":"FR",
                                "country_name":"France",
                                "lon":22.0000,
                                "lat":39.0000}]
           })
  </code></pre>
  
  <p>And the query so far looks like this:</p>
  
  <pre><code>cur.execute("""
  WITH article_s AS (
      SELECT id FROM article
      WHERE id = %(article_id)s
  ),
  article_i AS (
      INSERT INTO article (article_id, article_title)
          SELECT %(article_id)s, %(article_title)s
          WHERE NOT EXISTS (SELECT id FROM article where id = %(article_id)s)
          RETURNING id
  ),
  country_s AS (
      SELECT id FROM country
      WHERE id = %(country_id)s
  ),
  country_i AS (
      INSERT INTO country (id, country_code, country, geom)
          SELECT %(country_id)s, %(country_code)s, %(country_name)s, ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)
          WHERE NOT EXISTS (SELECT 1 FROM country_s)
          RETURNING id
  )
  INSERT INTO article_countries (article_id, country_id)
      SELECT COALESCE (article_i.id, country_i.id),
      COALESCE (article_i.id, country_i.id)
      FROM article_i, country_i,
      article_s, country_s
  ;
  """, dictionary)
  </code></pre>
  
  <p>Anyone have any suggestions on how to achieve this? Thanks in advance!</p>