How to get the ID of newly created backup using "Cloud SQL Admin API" with Python?

Question

I am writing a script using Cloud SQL Admin API's backupRuns instance which has the following methods:

1. delete(project=*, instance=*, id=*)
2. get(project=*, instance=*, id=*)
3. insert(project=*, instance=*, body=*)
4. list(project=*, instance=*, maxResults=None, pageToken=None) 
5. list_next(previous_request=*, previous_response=*)

Here's the link to the API: https://developers.google.com/resources/api-libraries/documentation/sqladmin/v1beta4/python/latest/sqladmin_v1beta4.backupRuns.html

What I find strange in the API is the insert() method, when we insert/create a new backup calling this method of the API, it returns the following response :

{'insertTime': '2018-12-26T06:48:35.675Z',
 'kind': 'sql#operation',
 'name': 'some-random-string,
 'operationType': 'BACKUP_VOLUME',
 'selfLink': 'https://www.googleapis.com/sql/v1beta4/projects/project-name/operations/some-random-string',
 'status': 'PENDING',
 'targetId': 'cloud-instance-name',
 'targetLink': 'https://www.googleapis.com/sql/v1beta4/projects/project-name/instances/cloud-instance-name',
 'targetProject': 'project-name',
 'user': 'some-user@project.iam.gserviceaccount.com'}

This seems like an asynchronous call in which status changes from PENDING to SUCCESSFUL after a few seconds. Now if I want to keep on checking the instance until its status is SUCCESSFUL, I'll need the _id_ of the newly created instance (to call the get() method).

The only way I could figure out is calling the list() method and checking the enqueuedTime of the list items with the insertTime of the response above and get the id, then call the get() method for the status. It feels like a hack to me, is there a better way to watch for the status until it is SUCCESSFUL?

Answer 1

Google API treats every request as an operation which can be retrieved from the operations() of service built from googleapiclient.discovery. For example :

from googleapiclient import discovery

service = discovery.build('sqladmin', 'v1beta4')

#Just insert a backup for an SQL instance or any other operation
insert_response = service.backupRuns().insert(project=<project-id>,instance=<instance-id>, body={}).execute()

#Get the opepration to check the status
insert_operation = service.operations().get(project=<project-id>,operation=insert_response['name']).execute()

This insert_opertation can be used to check the current status of the operation.

Here how insert_response and insert_operation look:

print(insert_response)
{'insertTime': '2019-01-08T13:04:31.941Z',                                                                                                                              
 'kind': 'sql#operation',                                                                                                                                               
 'name': '<unique-name-of-the-operation>',                                                                                                                        
 'operationType': 'BACKUP_VOLUME',                                                                                                                                      
 'selfLink': 'https://www.googleapis.com/sql/v1beta4/projects/<project-id>/operations/<unique-name-of-the-operation>',                                                
 'startTime': '2019-01-08T13:04:32.052Z',                                                                                                                               
 'status': 'RUNNING',                                                                                                                                                   
 'targetId': '<instance-name>',                                                                                                                                   
 'targetLink': 'https://www.googleapis.com/sql/v1beta4/projects/<project-id>/instances/<instance-name>',                                                              
 'targetProject': '<project-id>',                                                                                                                                           
 'user': '<user>'}

print(insert_operation)
{'endTime': '2018-12-26T13:07:08.746Z',
'enqueuedTime': '2018-12-26T13:06:33.563Z',
'id': '<operation-id>',
'instance': '<instance-name>',
'kind': 'sql#backupRun',
'selfLink': 'https://www.googleapis.com/sql/v1beta4/projects/<project-id>/instances/<instance-name>/backupRuns/<operation-id>',
'startTime': '2018-12-26T13:06:33.563Z',
'status': 'SUCCESSFUL',
'type': 'ON_DEMAND',
'windowStartTime': '2018-12-26T13:06:33.563Z'}

service.operations().get() can be used to get any operation performed using the APIs which return a name in the response.

Refer to this link for more information.

Answer 2

I found this API also confusing. The insert method returns a sql#operation object and it has a GUID-type id-field. The list and get methods use sql#backupRun object and they have id with datatype long. I have not found any way to map these.

You could use the description field to identify your backupRun. It is not optimal, but could work for you. Specify a unique value in description field in your insert method body parameter. You can then filter the list method result with the description and possibly also filter type='ON_DEMAND' in addition to the enqueuedTime. The list result is in reverse chronological order so you should find the right item in the beginning of the list.

There is an id parameter in documentation for insert methods body parameter but setting it will raise an error. The API is still in beta. Hope this API matures and changes so that we can have linking from inserts to get.