def object_exists(auth, path):
bucket, filename = path.split(':', 1)
service = get_service('storage', 'v1', auth)
try:
service.objects().get(bucket=bucket, object=filename).execute()
return True
except:
return False
def bucket_get(auth, name):
service = get_service('storage', 'v1', auth)
try:
return service.buckets().get(bucket=name).execute()
except HttpError as e:
if e.resp.status == 404: return None
elif e.resp.status in [403, 500, 503]: sleep(5)
else: raise
def __init__(self, auth, profile_id):
"""Initializes the object with a specific CM profile ID and an authorization scheme.
"""
self.service = get_service('dfareporting', self.API_VERSION, auth)
self.profile_id = profile_id
self._entity = 'UNDEFINED'
self._metrics = {}
def test_dictionary_credentials_service(self):
with open(self.service_file, 'r') as json_file:
project.initialize(_service=json.load(json_file))
service = get_service('cloudresourcemanager', 'v1', 'service')
response = service.projects().list().execute()
self.assertIn('projects', response)
def test_file_credentials_user(self):
project.initialize(_user=self.user_file)
service = get_service('oauth2', 'v2', 'user')
response = service.userinfo().get().execute()
self.assertIn('email', response)
self.helper_refresh()
def file_find(auth, name, parent = None):
drive = get_service('drive', 'v3', auth)
query = "trashed = false and name = '%s'" % name
if parent: query = "%s and '%s' in parents" % (query, parent)
try: return (API_Retry(drive.files().list(q=query))['files'] or [None])[0]
except HttpError: return None
def test_dictionary_credentials_user(self):
with open(self.user_file, 'r') as json_file:
project.initialize(_user=json.load(json_file))
service = get_service('oauth2', 'v2', 'user')
response = service.userinfo().get().execute()
self.assertIn('email', response)
self.helper_refresh()
def create_folder(auth, parent, name):
drive = get_service('drive', 'v3', auth)
file_metadata = {
'name' : name,
'parents' : [parent],
'mimeType' : 'application/vnd.google-apps.folder'
}
return drive.files().create(body=file_metadata, fields='id').execute()
def test_string_credentials_user(self):
with open(self.user_file, 'r') as json_file:
config = Configuration(user=json_file.read())
service = get_service(config, 'oauth2', 'v2', 'user')
response = service.userinfo().get().execute()
self.assertIn('email', response)
self.helper_refresh()
def sheets_clear(auth, sheet_url_or_name, sheet_tab, sheet_range):
if project.verbose:
print('SHEETS CLEAR', sheet_url_or_name, sheet_tab, sheet_range)
service = get_service('sheets', 'v4', auth)
sheet_id = sheets_id(auth, sheet_url_or_name)
API_Retry(service.spreadsheets().values().clear(
spreadsheetId=sheet_id,
range=sheets_tab_range(sheet_tab, sheet_range),
body={}))
def file_delete(auth, name, parent = None):
drive_file = file_find(auth, name, parent)
if drive_file:
drive = get_service('drive', 'v3', auth)
API_Retry(drive.files().delete(fileId=drive_file['id']))
return True
else:
return False
def get_email_messages(auth, email_from, email_to, subject_regexp=None, link_regexp=None, attachment_regexp=None, download=False, date_min=None, date_max=None):
if project.verbose: print('GETTING EMAILS')
service = get_service('gmail', 'v1', auth)
messages = _email_find(service, email_from, email_to, date_min, date_max)
subject_filter = re.compile(r'%s' % subject_regexp) if subject_regexp else None
for message in messages:
message = API_Retry(service.users().messages().get(userId='me', id=message['id']))
if subject_filter is None or subject_filter.match(_get_subject(message)):
yield _email_message(service, message, link_regexp, attachment_regexp, download)
def sheets_write(auth, sheet_url, sheet_tab, sheet_range, data, valueInputOption='RAW'):
if project.verbose: print 'SHEETS WRITE', sheet_url, sheet_tab, sheet_range
service = get_service('sheets', 'v4', auth)
sheet_id = sheets_id(sheet_url)
range = sheets_tab_range(sheet_tab, sheet_range)
body = {
"values": list(data)
}
API_Retry(service.spreadsheets().values().update(spreadsheetId=sheet_id, range=range, body=body, valueInputOption=valueInputOption))
def report_file(auth,
account,
report_id=None,
name=None,
timeout=60,
chunksize=DCM_CHUNK_SIZE):
""" Retrieves most recent DCM file by name or ID, if in progress, waits for it to complete.
Bulletproofing: https://developers.google.com/doubleclick-advertisers/v3.2/files/get
Timeout is in minutes ( retries will happen at 1 minute interval, default total time is 60 minutes )
If chunksize is set to 0 then the whole file is downloaded at once.
Args:
* auth: (string) Either user or service.
* account: (string) [account:advertiser@profile] token.
* report_id: (int) ID of DCm report to fetch ( either or name ).
* name: (string) Name of report to fetch ( either or report_id ).
* timeout: (int) Minutes to wait for in progress report before giving up.
* chunksize: (int) number of bytes to download at a time, for memory constrained systems.
Returns:
* (filename, iterator) if file exists and is ready to download in chunks.
* (filename, file) if file exists and chunking is off.
* ('report_running.csv', None) if report is in progress.
* (None, None) if file does not exist.
"""
account_id, advertiser_id, profile_id = parse_account(auth, account)
file_json = report_fetch(auth, account, report_id, name, timeout)
if file_json == False:
return None, None
elif file_json == True:
return 'report_running.csv', None
else:
service = get_service('dfareporting',
API_VERSION,
auth,
uri_file=API_URI)
filename = '%s_%s.csv' % (file_json['fileName'],
file_json['dateRange']['endDate'].replace(
'-', ''))
# streaming
if chunksize:
return filename, media_download(
service.files().get_media(reportId=file_json['reportId'],
fileId=file_json['id']), chunksize)
# single object
else:
return filename, StringIO(
API_Retry(service.files().get_media(
reportId=file_json['reportId'], fileId=file_json['id'])))
def sheets_read(auth, sheet_url_or_name, sheet_tab, sheet_range, retries=10):
if project.verbose:
print('SHEETS READ', sheet_url_or_name, sheet_tab, sheet_range)
service = get_service('sheets', 'v4', auth)
sheet_id = sheets_id(auth, sheet_url_or_name)
return API_Retry(service.spreadsheets().values().get(
spreadsheetId=sheet_id, range=sheets_tab_range(sheet_tab,
sheet_range)),
'values',
retries=retries)
def object_list(auth, path, raw=False, files_only=False):
bucket, prefix = path.split(':', 1)
service = get_service('storage', 'v1', auth)
next_page = None
while next_page != '':
response = service.objects().list(bucket=bucket,
prefix=prefix).execute()
next_page = response.get('nextPageToken', '')
for item in response.get('items', []):
if files_only and item['name'].endswith('/'): continue
yield item if raw else '%s:%s' % (bucket, item['name'])
def report_build(auth, body):
""" Creates a DBM report given a JSON definition.
Bulletproofing: https://developers.google.com/bid-manager/v1/queries/createquery
The report will be automatically run the first time.
The body JSON provided will have the following fields added if not present:
* schedule - set to run daily and expire in one year.
Args:
* auth: (string) Either user or service.
* body: (json) As defined in: https://developers.google.com/doubleclick-advertisers/v3.2/reports#resource
Returns:
* JSON definition of report created or existing.
"""
report = report_get(auth, name=body['metadata']['title'])
if not report:
service = get_service('doubleclickbidmanager', API_VERSION, auth)
# add default daily schedule if it does not exist ( convenience )
if "schedule" not in body:
body['schedule'] = {
"endTimeMs": long((time.time() + (365 * 24 * 60 * 60)) *
1000), # 1 year in future
"frequency": "DAILY",
"nextRunMinuteOfDay": 4 * 60,
"nextRunTimezoneCode": body['timezoneCode']
}
#pprint.PrettyPrinter().pprint(body)
# build report
job = service.queries().createquery(body=body)
report = API_Retry(job)
# run report first time
body = {
"dataRange": report['metadata']['dataRange'],
"timezoneCode": report['schedule']['nextRunTimezoneCode']
}
run = service.queries().runquery(queryId=report['queryId'], body=body)
API_Retry(run)
else:
if project.verbose:
print 'DBM Report Exists:', body['metadata']['title']
return report
def create_table_if_not_exist(auth,
project_id,
dataset_id,
table_id,
is_time_partition=False):
service = get_service('bigquery', 'v2', auth)
if not check_table_exists(auth, project_id, dataset_id, table_id,
is_time_partition):
create_table(auth, project_id, dataset_id, table_id, is_time_partition)
def query_to_rows(auth,
project_id,
dataset_id,
query,
row_max=None,
legacy=True):
service = get_service('bigquery', 'v2', auth)
# Create the query
body = {
"kind": "bigquery#queryRequest",
"query": query,
"timeoutMs": 10000,
"dryRun": False,
"useQueryCache": True,
"useLegacySql": legacy
}
if row_max: body['maxResults'] = row_max
if dataset_id:
body['defaultDataset'] = {
"projectId": project_id,
"datasetId": dataset_id
}
response = API_Retry(service.jobs().query(projectId=project_id, body=body))
while not response['jobComplete']:
sleep(5)
response = API_Retry(service.jobs().getQueryResults(
projectId=project_id, jobId=response['jobReference']['jobId']))
# Fetch all results
row_count = 0
while 'rows' in response:
converters = _build_converter_array(response.get('schema', None), None,
len(response['rows'][0].get('f')))
for row in response['rows']:
yield [
converters[i](r.values()[0]) for i, r in enumerate(row['f'])
] # may break if we attempt nested reads
row_count += 1
if 'PageToken' in response:
response = API_Retry(service.jobs().getQueryResults(
projectId=project_id,
jobId=response['jobReference']['jobId'],
pageToken=response['PageToken']))
elif row_count < response['totalRows']:
response = API_Retry(service.jobs().getQueryResults(
projectId=project_id,
jobId=response['jobReference']['jobId'],
startIndex=row_count))
def report_fetch(auth, report_id):
service = get_service('doubleclicksearch', 'v2', auth)
if project.verbose: print 'Fetching Report', report_id
files = report_ready(service, report_id)
reports = []
i = 0
for file in files:
reports.append({'name': '%s_%d_%s.csv' % (report_id, i, str(date.today())), 'report_id': report_id, 'report_fragment': i})
return reports
def lineitem_read(auth, advertisers=[], insertion_orders=[], lineitems=[]):
""" Reads line item configurations from DBM.
Bulletproofing: https://developers.google.com/bid-manager/v1/lineitems/downloadlineitems
Args:
* auth: (string) Either user or service.
* advertisers (list) List of advertiser ids ( exclusive with insertion_orders and lineitems ).
* insertion_orders (list) List of insertion_order ids ( exclusive with advertisers and lineitems ).
* lineitems (list) List of ilineitem ids ( exclusive with insertion_orders and advertisers ).
Returns:
* Iterator of lists: https://developers.google.com/bid-manager/guides/entity-write/format
"""
service = get_service('doubleclickbidmanager', API_VERSION, auth)
body = {
'format':'CSV',
'fileSpec':'EWF'
}
if advertisers:
body['filterType'] = 'ADVERTISER_ID'
body['filterIds'] = list(advertisers) # in case its a generator
elif insertion_orders:
body['filterType'] = 'INSERTION_ORDER_ID'
body['filterIds'] = list(insertion_orders) # in case its a generator
elif lineitems:
body['filterType'] = 'LINE_ITEM_ID'
body['filterIds'] = list(lineitems) # in case its a generator
#print(body)
result = API_Retry(service.lineitems().downloadlineitems(body=body))
for count, row in enumerate(csv_to_rows(result.get('lineItems', ''))):
if count == 0: continue # skip header
row[0] = int(row[0] or 0) # LineItem ID
row[2] = int(row[2] or 0) # Partner ID
row[11] = float(row[11] or 0) # IO Budget Amount
row[18] = float(row[18] or 0) # LineItem Budget Amount
row[21] = float(row[21] or 0) # LineItem Pacing Amount
row[23] = int(row[23] or 0) # LineItem Frequency Exposures
row[25] = int(row[25] or 0) # LineItem Frequency Amount
row[26] = float(row[26] or 0) # Bid Price (CPM)
row[28] = float(row[28] or 0) # Partner Revenue Amount
yield row
def sheets_read(auth, sheet_url_or_name, sheet_tab, sheet_range='', retries=10):
if project.verbose:
print('SHEETS READ', sheet_url_or_name, sheet_tab, sheet_range)
service = get_service('sheets', 'v4', auth)
sheet_id = sheets_id(auth, sheet_url_or_name)
if sheet_id is None:
raise (OSError('Sheet does not exist: %s' % sheet_url_or_name))
else:
return API_Retry(
service.spreadsheets().values().get(
spreadsheetId=sheet_id,
range=sheets_tab_range(sheet_tab, sheet_range)),
'values',
retries=retries)
def create_table(auth, project_id, dataset_id, table_id):
service = get_service('bigquery', 'v2', auth)
body = {
"tableReference": {
"projectId": project_id,
"tableId": table_id,
"datasetId": dataset_id,
}
}
service.tables().insert(projectId=project_id,
datasetId=dataset_id,
body=body).execute()
def storage_to_table(auth,
project_id,
dataset_id,
table_id,
path,
schema=[],
skip_rows=1,
structure='CSV',
disposition='WRITE_TRUNCATE'):
if project.verbose:
print 'BIGQUERY STORAGE TO TABLE: ', project_id, dataset_id, table_id
service = get_service('bigquery', 'v2', auth)
body = {
'configuration': {
'load': {
'destinationTable': {
'projectId': project_id,
'datasetId': dataset_id,
'tableId': table_id,
},
'sourceFormat': 'NEWLINE_DELIMITED_JSON',
'writeDisposition': disposition,
'autodetect': True,
'allowJaggedRows': True,
'allowQuotedNewlines': True,
'ignoreUnknownValues': True,
'sourceUris': [
'gs://%s' % path.replace(':', '/'),
],
}
}
}
if schema:
body['configuration']['load']['schema'] = {'fields': schema}
body['configuration']['load']['autodetect'] = False
if structure == 'CSV':
body['configuration']['load']['sourceFormat'] = 'CSV'
body['configuration']['load']['skipLeadingRows'] = skip_rows
job = service.jobs().insert(
projectId=project_id, body=body).execute(num_retries=BIGQUERY_RETRIES)
try:
job_wait(service, job)
except Exception, e:
print 'BIGQUERY SKIPPING: %s, %s' % (path, str(e))
def query_to_view(auth,
project_id,
dataset_id,
view_id,
query,
legacy=True,
replace=False):
service = get_service('bigquery', 'v2', auth)
body = {
'tableReference': {
'projectId': project_id,
'datasetId': dataset_id,
'tableId': view_id,
},
'view': {
'query': query,
'useLegacySql': legacy
}
}
try:
job = service.tables().list(
projectId=project_id,
datasetId=dataset_id).execute(num_retries=BIGQUERY_RETRIES)
table_id = ('%s:%s.%s' % (project_id, dataset_id, view_id))
table_exist = False
for table in job['tables']:
if table_id == table['id']:
table_exist = True
if replace and table_exist:
job = service.tables().update(
projectId=project_id,
datasetId=dataset_id,
tableId=view_id,
body=body).execute(num_retries=BIGQUERY_RETRIES)
else:
job = service.tables().insert(
projectId=project_id, datasetId=dataset_id,
body=body).execute(num_retries=BIGQUERY_RETRIES)
except HttpError, e:
#if e.resp.status in [403, 500, 503]: sleep(5)
if json.loads(e.content)['error']['code'] == 409:
pass # already exists ( ignore )
else:
raise
def get_profile_for_api(auth, account_id):
"""Return a DCM profile ID for the currently supplied credentials.
Bulletproofing: https://developers.google.com/doubleclick-advertisers/v3.2/userProfiles/get
Handles cases of superuser, otherwise chooses the first matched profile.
Allows DCM jobs to only specify account ID, which makes jobs more portable
between accounts.
Args:
* auth: (string) Either user or service.
* account_id: (int) Account number for which report is retrieved.
Returns:
* Is Superuser ( bool ): True if superuser account
* Profile ID ( int ): profile id to be used to make API call
Raises:
* If current credentials do not have a profile for this account.
"""
service = get_service('dfareporting', API_VERSION, auth, uri_file=API_URI)
profile_admin = None
profile_network = None
for p in API_Retry(service.userProfiles().list())['items']:
p_id = int(p['profileId'])
a_id = int(p['accountId'])
# take the first profile for admin
if '@dcm' in p['userName']:
profile_admin = p_id
#elif '@dfa' in p['userName']: profile_admin = p_id
elif a_id == 2515:
profile_admin = p_id
# try to find a network profile if exists
if a_id == account_id:
profile_network = p_id
break
if profile_admin: return True, profile_admin
elif profile_network: return False, profile_network
else:
raise Exception('Add your user profile to DCM account %s.' %
account_id)
def report_get(auth, account, report_id=None, name=None):
""" Returns the DCM JSON definition of a report based on name or ID.
Bulletproofing: https://developers.google.com/doubleclick-advertisers/v3.2/reports/get
Args:
* auth: (string) Either user or service.
* account: (string) [account:advertiser@profile] token.
* account: (string) [account:advertiser@profile] token.
* report_id: (int) ID of DCm report to fetch ( either or name ).
* name: (string) Name of report to fetch ( either or report_id ).
Returns:
* JSON definition of report.
"""
account_id, advertiser_ids, profile_id = parse_account(auth, account)
service = get_service('dfareporting', API_VERSION, auth, uri_file=API_URI)
report = None
if name:
next_page = None
while next_page != '' and report is None:
if INTERNAL_MODE:
response = API_Retry(service.reports().list(
accountId=account_id,
profileId=profile_id,
pageToken=next_page))
else:
response = API_Retry(service.reports().list(
profileId=profile_id, pageToken=next_page))
next_page = response['nextPageToken']
for r in response['items']:
if r['name'] == name:
report = r
break
elif report_id:
if INTERNAL_MODE:
response = API_Retry(service.reports().get(accountId=account_id,
profileId=profile_id,
reportId=report_id))
else:
response = API_Retry(service.reports().get(profileId=profile_id,
reportId=report_id))
#pprint.PrettyPrinter().pprint(response)
return report
def test_remote_credentials_user(self):
project.initialize(_user=self.user_file)
credentials = get_credentials('user')
account = Account.objects.get_or_create_user(credentials, 'password')
clear_credentials_cache()
project.initialize(_user=account.get_credentials_path())
self.assertEqual(project.recipe['setup']['auth']['user'],
account.get_credentials_path())
service = get_service('oauth2', 'v2', 'user')
response = service.userinfo().get().execute()
self.assertIn('email', response)
self.helper_refresh()
def init_daos():
global video_format_dao
global landing_page_dao
global placement_group_dao
global campaign_dao
global creative_association_dao
global creative_dao
global placement_dao
global creative_asset_dao
global ad_dao
global event_tag_dao
global dynamic_targeting_key_dao
global spreadsheet
service = get_service('sheets', 'v4', project.task['auth'])
spreadsheet = service.spreadsheets().get(
spreadsheetId=project.task['sheet_id']).execute()
store.auth = project.task['auth']
store.trix_id = project.task.get('store', {}).get('sheet_id',
project.task['sheet_id'])
store.load_id_map()
video_format_dao = VideoFormatDAO(project.task['auth'],
project.task['dcm_profile_id'])
landing_page_dao = LandingPageDAO(project.task['auth'],
project.task['dcm_profile_id'])
placement_group_dao = PlacementGroupDAO(project.task['auth'],
project.task['dcm_profile_id'])
campaign_dao = CampaignDAO(project.task['auth'],
project.task['dcm_profile_id'])
creative_association_dao = CreativeAssociationDAO(
project.task['auth'], project.task['dcm_profile_id'])
creative_dao = CreativeDAO(project.task['auth'],
project.task['dcm_profile_id'])
placement_dao = PlacementDAO(project.task['auth'],
project.task['dcm_profile_id'])
creative_asset_dao = CreativeAssetDAO(project.task['auth'],
project.task['dcm_profile_id'],
project.id)
ad_dao = AdDAO(project.task['auth'], project.task['dcm_profile_id'])
event_tag_dao = EventTagDAO(project.task['auth'],
project.task['dcm_profile_id'])
dynamic_targeting_key_dao = DynamicTargetingKeyDAO(
project.task['auth'], project.task['dcm_profile_id'])
def sheets_tab_copy(auth,
from_sheet_url_or_name,
from_sheet_tab,
to_sheet_url_or_name,
to_sheet_tab,
overwrite=False):
if project.verbose:
print('SHEETS COPY', from_sheet_url_or_name, from_sheet_tab,
to_sheet_url_or_name, to_sheet_tab)
service = get_service('sheets', 'v4', auth)
# convert human readable to ids
from_sheet_id, from_tab_id = sheets_tab_id(auth, from_sheet_url_or_name,
from_sheet_tab)
to_sheet_id, to_tab_id = sheets_tab_id(auth, to_sheet_url_or_name,
to_sheet_tab)
# overwrite only if does not exist
if overwrite or to_tab_id is None:
# copy tab between sheets, the name changes to be "Copy of [from_sheet_tab]"
copy_sheet = API_Retry(service.spreadsheets().sheets().copyTo(
spreadsheetId=from_sheet_id,
sheetId=from_tab_id,
body={
"destinationSpreadsheetId": to_sheet_id,
}))
body = {"requests": []}
# if destination tab exists, delete it
if to_tab_id:
body['requests'].append({"deleteSheet": {"sheetId": to_tab_id}})
# change the copy name to the designated name, remove "Copy of "
body['requests'].append({
"updateSheetProperties": {
"properties": {
"sheetId": copy_sheet['sheetId'],
"title": to_sheet_tab
},
"fields": "title"
}
})
API_Retry(service.spreadsheets().batchUpdate(spreadsheetId=to_sheet_id,
body=body))
