How can I get list of all cloud SQL ( GCP ) instances which are stopped in python, I am using google cloud api for this purpose

Question

from googleapiclient import discovery

PROJECT = gcp-test-1234
sql_client = discovery.build('sqladmin', 'v1beta4')
resp = sql_client.instances().list(project=PROJECT).execute()
print(resp)

But in response, I am getting a state as "RUNNABLE" for stopped instances, so how can I verify that the instance is running or stopped programmatically

I have also check gcloud sql instances describe gcp-test-1234-test-db, it is providing state as "STOPPED"

how can I achieve this programmatically using python

Answer 1

In the Rest API, the RUNNABLE for the state field means that the instance is running, or has been stopped by the owner, as stated here.

You need to read from the activationPolicy field, where ALWAYS means your instance is running and NEVER means it is stopped. Something like the following will work:

from pprint import pprint
  
from googleapiclient import discovery
service = discovery.build('sqladmin', 'v1beta4')


project = 'gcp-test-1234'


instance = 'gcp-test-1234-test-db' 
request = service.instances().get(project=project,instance=instance)
response = request.execute()

pprint(response['settings']['activationPolicy'])

Another option would be to use the Cloud SDK command directly from your python file:

import os
os.system("gcloud sql instances describe gcp-test-1234-test-db | grep state | awk {'print $2'}")

Or with subprocess:

import subprocess
subprocess.run("gcloud sql instances describe gcp-test-1234-test-db | grep state | awk {'print $2'}", shell=True)

---

Note that when you run gcloud sql instances describe you-instance --log-http on a stopped instance, in the response of the API, you'll see "state": "RUNNABLE", however, the gcloud command will show the status STOPPED. This is because the output of the command gets the status from the activationPolicy of the API response rather than the status, if the status is RUNNABLE.

If you want to check the piece of code that translates the activationPolicy to the status, you can see it in the SDK. The gcloud tool is written in python:

cat $(gcloud info --format "value(config.paths.sdk_root)")/lib/googlecloudsdk/api_lib/sql/instances.py|grep "class DatabaseInstancePresentation(object)" -A 17

You'll se the following:

class DatabaseInstancePresentation(object):
  """Represents a DatabaseInstance message that is modified for user visibility."""

  def __init__(self, orig):
    for field in orig.all_fields():
      if field.name == 'state':
        if orig.settings and orig.settings.activationPolicy == messages.Settings.ActivationPolicyValueValuesEnum.NEVER:
          self.state = 'STOPPED'
        else:
          self.state = orig.state
      else:
        value = getattr(orig, field.name)
        if value is not None and not (isinstance(value, list) and not value):
          if field.name in ['currentDiskSize', 'maxDiskSize']:
            setattr(self, field.name, six.text_type(value))
          else:
            setattr(self, field.name, value)