How to do it

We proceed with the recipe as follows:

1. The following code will read the planets data and write it to the database (code in 03/save_in_postgres.py):

import psycopg2
from get_planet_data import get_planet_data

try:
  # connect to PostgreSQL
  conn = psycopg2.connect("dbname='scraping' host='localhost' user='postgres' password='mypassword'")

  # the SQL INSERT statement we will use
  insert_sql = ('INSERT INTO public."Planets"(name, mass, radius, description, moreinfo) ' +
          'VALUES (%(Name)s, %(Mass)s, %(Radius)s, %(Description)s, %(MoreInfo)s);')

  # open a cursor to access data
  cur = conn.cursor()

  # get the planets data and loop through each
  planet_data = get_planet_data()
  for planet in planet_data:
    # write each record
    cur.execute(insert_sql, planet)

  # commit the new records to the database
  conn.commit()
  cur.close()
  conn.close()

  print("Successfully wrote data to the database")

except Exception as ex:
  print(ex)

1. If successful you will see the following:

Successfully wrote data to the database

1. Using GUI tools such as pgAdmin you can examine the data within the database:

1. The data can be queried with the following Python code (found in 03/read_from_postgresql.py):

import psycopg2

try:
  conn = psycopg2.connect("dbname='scraping' host='localhost' user='postgres' password='mypassword'")

  cur = conn.cursor()
  cur.execute('SELECT * from public."Planets"')
  rows = cur.fetchall()
  print(rows)

  cur.close()
  conn.close()

except Exception as ex:
  print(ex)

1. And results in the following output (truncated a little bit:

[(1, 'Mercury', 0.33, 4879.0, 'Named Mercurius by the Romans because it appears to move so swiftly.', 'https://en.wikipedia.org/wiki/Mercury_(planet)'), (2, 'Venus', 4.87, 12104.0, 'Roman name for the goddess of love. This planet was considered to be the brightest and most beautiful planet or star in the heavens. Other civilizations have named it for their god or goddess of love/war.', 'https://en.wikipedia.org/wiki/Venus'), (3, 'Earth', 5.97, 12756.0, "The name Earth comes from the Indo-European base 'er,'which produced the Germanic noun 'ertho,' and ultimately German 'erde,' Dutch 'aarde,' Scandinavian 'jord,' and English 'earth.' Related forms include Greek 'eraze,' meaning 'on the ground,' and Welsh 'erw,' meaning 'a piece of land.'", 'https://en.wikipedia.org/wiki/Earth'), (4, 'Mars', 0.642, 6792.0, 'Named by the Romans for their god of war because of its red, bloodlike color. Other civilizations also named this planet from this attribute; for example, the Egyptians named it