Python script query to GCP postgresql db from local machine

Question

I have a GCP workspace, complete with a Postgresql database. On a frequent basis, I need to insert and/or select rows from the db. I've been searching for a python script that will (A) connect to GCP, then (B) connect to the db, then (C) query a specific table. I'd prefer not to hard code my credentials if possible, that way I could share this script with others on my team, and provided that they were authorized users, it would run without any hiccups.

Does anyone have such a script?

Answer 1

Using the Cloud SQL Python connector you can run a script that looks something like this 

import os
from google.cloud.sql.connector import connector

conn = connector.connect(
    os.getenv("INSTANCE_CONNECTION_NAME"),
    "pg8000",
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    db=os.getenv("DB_NAME")
)

cursor = conn.cursor()
cursor.execute("SELECT * from my_table")

result = cursor.fetchall()

for row in result:
    print(row)