Let’s create a simple Python script that uses psycopg2 to query your database. Create a file called inventory.py on your own machine and open it in your editor.

First, import psycopg2:

# In a new file, inventory.py
import psycopg2

Then create a connection to your database:

# Connect to PostgreSQL database
conn = psycopg2.connect(
  host="35.177.174.19",
  user="your_database_id",
  password="your_database_password")

# Automatically commit transactions
conn.set_session(autocommit=True)

The final line instructs Psycopg2 to automatically commit any transactions - in other words, queries you make take permanent effect in the database by default.

Then we need to get a cursor. This is a Python object with methods to run queries and iterate over the results:

# Create a cursor object
cur = conn.cursor()

Now we can write a function to get all the items from the inventory table:

def get_items():
  # Run a query
  cur.execute('SELECT * FROM inventory;')
  
  # Get all the rows for that query
  items = cur.fetchall()
  
  # Convert the result into a list of dictionaries (useful later)
  return [
    {'id': item[0], 'name': item[1], 'quantity': item[2]}
    for item in items
  ]

Now add a print statement at the end of the script so you can inspect the result:

print(get_items())

Run this from your terminal using python inventory.py - you’ll see your database table printed as a list of dictionaries!

[
  {'id': 1, 'name': 'Vase', 'quantity': 10},
  {'id': 2, 'name': 'Bookcase', 'quantity': 5},
  {'id': 3, 'name': 'Bathtowel', 'quantity': 20},
  {'id': 4, 'name': 'Frying Pan', 'quantity': 20},
  {'id': 5, 'name': 'Large Saucepan', 'quantity': 25},
  {'id': 6, 'name': 'Small Saucepan', 'quantity': 25},
  {'id': 7, 'name': 'Dinner Plate', 'quantity': 15},
  {'id': 8, 'name': 'Dining Chair', 'quantity': 10}
]

The full script looks like this:

import psycopg2

conn = psycopg2.connect(
  host="35.177.174.19",
  user="your_database_id",
  password="your_database_password")
conn.set_session(autocommit=True)

cur = conn.cursor()

def get_items():
  cur.execute('SELECT (id, item_name, quantity) FROM inventory;')
  items = cur.fetchall()
  return [
    {'id': item[0], 'name': item[1], 'quantity': item[2]}
    for item in items
  ]

print(get_items())

We’re now successfully querying our external database using SQL from Python.

We’ve set up the database and made SQL queries from Python.

We’ve set up the database and made SQL queries from Python.