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?

Shubha Rajan Shubha Rajan · Accepted Answer · 2021-05-06T02:37:41

I believe I just answered your question here: Access GCP Cloud SQL from AI notebook?

Using the Cloud SQL Python Connector which was mentioned in the other post, you can run a script that looks something like this to connect to your database and run a query:

# Copyright 2021 Google LLC.
# SPDX-License-Identifier: Apache-2.0

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

# Connect to the database
conn = connector.connect(
    os.getenv("INSTANCE_CONNECTION_NAME"),
    "pg8000",
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    db=os.getenv("DB_NAME")
)

# Execute a query
cursor = conn.cursor()
cursor.execute("SELECT * from my_table")

# Fetch the results
result = cursor.fetchall()

# Do something with the results
for row in result:
    print(row)

The instance connection name should be in the format project:region:instance. If you don't want to hard code database credentials, you can read them in from environment variables instead.

Python script, query to GCP postgresql db from local machine?

1 Answers