def credentials(from_file=False, reauth=False):
if from_file:
return Base()._load_credentials(mode="prod")
if reauth:
return pydata_google_auth.get_user_credentials(
SCOPES, credentials_cache=pydata_google_auth.cache.REAUTH)
else:
return pydata_google_auth.get_user_credentials(SCOPES, )
def credentials(reauth=False):
SCOPES = [
"https://www.googleapis.com/auth/cloud-platform",
]
if reauth:
return pydata_google_auth.get_user_credentials(
SCOPES, credentials_cache=pydata_google_auth.cache.REAUTH)
else:
return pydata_google_auth.get_user_credentials(SCOPES, )
def __init__(self, type):
# client = bigquery.Client()
self.project_id = "valiant-striker-272613"
self.dataset_id = "Flights"
self.table_id = "Routes"
scopes = [
"https://www.googleapis.com/auth/bigquery",
]
self.credentials = pydata_google_auth.get_user_credentials(scopes, )
self.client = bigquery.Client(project=self.project_id,
credentials=self.credentials)
self.dataset_ref = self.client.dataset(self.dataset_id)
self.job_config = bigquery.LoadJobConfig()
self.table_ref = self.dataset_ref.table(self.table_id)
self.job_config.write_disposition = (
bigquery.WriteDisposition.WRITE_APPEND)
# WRITE_TRUNCATE WRITE_APPEND WRITE_EMPTY
if type == "csv" or type == "dat":
self.job_config.source_format = bigquery.SourceFormat.CSV
elif type == "json":
self.job_config.source_format = (
bigquery.SourceFormat.NEWLINE_DELIMITED_JSON)
else:
self.job_config.source_format = bigquery.SourceFormat.AVRO
self.job_config.use_avro_logical_types = True
def credentials(from_file=False, reauth=False):
SCOPES = [
"https://www.googleapis.com/auth/cloud-platform",
]
if from_file:
return Base()._load_credentials(mode="prod")
else:
if reauth:
return pydata_google_auth.get_user_credentials(
SCOPES, credentials_cache=pydata_google_auth.cache.REAUTH)
else:
return pydata_google_auth.get_user_credentials(SCOPES, )
def test_get_user_credentials_gets_valid_credentials():
import pydata_google_auth
credentials = pydata_google_auth.get_user_credentials(
TEST_SCOPES, use_local_webserver=True)
assert credentials.valid
def get_data():
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(
SCOPES,
# Set auth_local_webserver to True to have a slightly more convienient
# authorization flow. Note, this doesn't work if you're running from a
# notebook on a remote sever, such as over SSH or with Google Colab.
auth_local_webserver=True,
)
query = """
SELECT user_id, count(distinct order_id) as orders, CAST(avg(basket) as INT64) as total_basket,
max(case when cuisine_parent='Breakfast' then 1 else 0 end) as has_ordered_breakfast,
sum(case when cuisine_parent='Breakfast' then 1 else 0 end) as breakfast_orders
FROM `bi-2019-test.ad_hoc.orders_jan2021`
group by 1
"""
df = pandas_gbq.read_gbq(
query,
project_id='bi-2019-test',
credentials=credentials,
)
return df
def get_gcp_credentials():
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(SCOPES, )
return credentials
def get_credentials():
scopes = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/bigquery'
]
credentials = pydata_google_auth.get_user_credentials(scopes)
return credentials
def get_google_credentials():
scopes = [
"https://spreadsheets.google.com/feeds",
"https://www.googleapis.com/auth/drive"
]
credentials = pydata_google_auth.get_user_credentials(scopes, auth_local_webserver=True)
credentials.access_token = credentials.token
return credentials
def bq_connection(ds, **ags):
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(
SCOPES, auth_local_webserver=False)
print('the connection is available')
return credentials
def authenticate_gcloud(account: str, auth_local_webserver: bool = True):
cache = pydata_google_auth.cache.ReadWriteCredentialsCache(
dirname='gsc-sa-downloader/gcloud', filename=account)
scopes = ['https://www.googleapis.com/auth/cloud-platform']
credentials = pydata_google_auth.get_user_credentials(
scopes,
credentials_cache=cache,
auth_local_webserver=auth_local_webserver)
return credentials
def __init__(self):
self.project_id = "valiant-striker-272613"
scopes = [
"https://www.googleapis.com/auth/bigquery",
]
self.credentials = pydata_google_auth.get_user_credentials(scopes, )
self.client = bigquery.Client(project=self.project_id,
credentials=self.credentials)
print("*************** bq init ***************")
def __init__(self):
self.SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
self.credentials = pydata_google_auth.get_user_credentials(
self.SCOPES,
auth_local_webserver=False,
)
def credentials():
SCOPES = [
"https://www.googleapis.com/auth/cloud-platform",
]
return pydata_google_auth.get_user_credentials(
SCOPES,
# Use the NOOP cache to avoid writing credentials to disk.
# credentials_cache=pydata_google_auth.cache.NOOP,
)
def test_get_user_credentials_reauth_gets_valid_credentials():
import pydata_google_auth
import pydata_google_auth.cache
credentials = pydata_google_auth.get_user_credentials(
TEST_SCOPES,
credentials_cache=pydata_google_auth.cache.REAUTH,
use_local_webserver=True,
)
assert credentials.valid
assert credentials.has_scopes(TEST_SCOPES)
def bigquery_auth():
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(
SCOPES,
# Set auth_local_webserver to True to have a slightly more convienient
# authorization flow. Note, this doesn't work if you're running from a
# notebook on a remote sever, such as over SSH or with Google Colab.
auth_local_webserver=True,
)
def get_credentials(client_secrets=CLIENT_SECRETS,
scopes=None,
filename="credentials.json"):
scopes = scopes or SCOPES
with open(client_secrets, "r") as fp:
secrets = json.loads(fp.read())["installed"]
return pydata_google_auth.get_user_credentials(
scopes=scopes,
client_id=secrets.get("client_id"),
client_secret=secrets.get("client_secret"),
auth_local_webserver=True,
credentials_cache=pydata_google_auth.cache.ReadWriteCredentialsCache(
filename=filename),
)
def get_user_credentials():
try:
return service_account.Credentials.from_service_account_file(
os.environ.get('GOOGLE_APPLICATION_CREDENTIALS'))
except:
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
return pydata_google_auth.get_user_credentials(
SCOPES,
auth_local_webserver=False,
)
def authenticate_gsc(account: str,
client_id: str,
client_secret: str,
auth_local_webserver: bool = True):
cache = pydata_google_auth.cache.ReadWriteCredentialsCache(
dirname='gsc-sa-downloader/gsc', filename=account)
scopes = ['https://www.googleapis.com/auth/webmasters']
credentials = pydata_google_auth.get_user_credentials(
scopes,
client_id=client_id,
client_secret=client_secret,
credentials_cache=cache,
auth_local_webserver=auth_local_webserver)
return credentials
def get_credentials(
client_secrets=CLIENT_SECRETS, scopes=None, filename="credentials.json"
):
scopes = scopes or SCOPES
with open(client_secrets, "r") as fp:
secrets = json.loads(fp.read())["installed"]
return pydata_google_auth.get_user_credentials(
scopes=scopes,
client_id=secrets.get("client_id"),
client_secret=secrets.get("client_secret"),
auth_local_webserver=True,
credentials_cache=pydata_google_auth.cache.ReadWriteCredentialsCache(
filename=filename
),
)
def download_data(project_id,
auth=None,
sign=None,
time_constraint=None,
query=None):
"""Downlaod and authenticate the data"""
if project_id is None: raise ValueError('Argument cannot be none')
auth = pydata_google_auth.get_user_credentials(
scopes=["https://www.googleapis.com/auth/bigquery"])
query = query
if time_constraint is not None:
query += f"WHERE started_at {sign}{time_constraint}"
df = pd.read_gbq(query=query,
project_id=project_id,
credentials=auth,
progress_bar_type="tqdm_notebook",
use_bqstorage_api=True)
return df
def getting_result_from_gbq(project_id, sql):
# get spreadsheet on gbq
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(
SCOPES,
# Set auth_local_webserver to True to have a slightly more convienient
# authorization flow. Note, this doesn't work if you're running from a
# notebook on a remote sever, such as over SSH or with Google Colab.
auth_local_webserver=True,
)
df = pandas_gbq.read_gbq(
sql,
project_id=project_id,
credentials=credentials,
)
# filter out use with no email input
df = df[df.KlookEmail.notna()]
return df
from google.cloud import bigquery
from google.cloud.exceptions import NotFound
from datetime import datetime
from datetime import date
from datetime import timedelta
import pydata_google_auth
import pandas as pd
from bs4 import BeautifulSoup
import urllib.request
import urllib.parse
import re
#Get your GCP credential
credentials = pydata_google_auth.get_user_credentials(
['https://www.googleapis.com/auth/bigquery'], )
#BigQuery project_ID and table
project_id = 'xxxxx'
dataset_id = 'xxxxx'
#set BigQuery client
try:
client = bigquery.Client(project=project_id, credentials=credentials)
datasets = client.list_datasets()
if datasets:
for obj in datasets:
print('-------->')
print(vars(obj))
# Read data from the sheet at
# http://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/view
#
# Try changing this ID to read data from your own spreadsheet.
SHEET_ID = "1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E"
credentials = pydata_google_auth.get_user_credentials(
[
# userinfo.profile is a minimal scope to get basic account info.
# In this example, it is used for demonstration of the wrong scopes.
# This scope does not provide access to Google Sheets data.
"https://www.googleapis.com/auth/userinfo.profile",
# Uncomment the spreadsheets scopes to request the correct scopes
# to access the spreadsheet.
#"https://www.googleapis.com/auth/spreadsheets"
],
# Request credentials every time. Do not cache them to disk.
credentials_cache=pydata_google_auth.cache.NOOP,
# Use a local webserver so that the user doesn't have to copy-paste an
# authentication code.
auth_local_webserver=True,
)
# Exchange a refresh token for a temporary access token.
request = google.auth.transport.requests.Request()
credentials.refresh(request)
# Attach the access token to the request to the Google Sheets API via an HTTP
import PySimpleGUI as sg
import pydata_google_auth
from exhibit_b import generate_dt_data
from historical_platform_performance import write_performance_file
from platform_cnl_test import write_cnl_test
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(
SCOPES, auth_local_webserver=True)
# Layout the design of the GUI
layout = [[
sg.Button('Generate Vintage File'),
sg.Button('Generate Exhibit B'),
sg.Button('Generate CNL Test File')
], [sg.Quit()]]
sg.ChangeLookAndFeel('DarkTanBlue')
# Show the Window to the user
window = sg.Window('Platform Performance Reports', layout)
# Event loop. Read buttons, make callbacks
while True:
# Read the Window
event, values = window.Read()
if event in ('Quit', None):
break
from pathlib import Path
import observatory.reports.chart_utils as chart_utils
from precipy.analytics_function import AnalyticsFunction
from observatory.reports.tables import (GenericOpenAccessTable,
GenericPublishersTable)
from modules.report_tables import GenericDisciplinesTable
scopes = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(scopes, )
disciplines = ['Chemistry', 'Materials science', 'Biology']
disc = [d.split(' ')[0].lower() for d in disciplines]
FOCUS_YEAR = 2019
YEAR_RANGE = (2009, 2021)
PROJECT_ID = 'academic-observatory'
CACHE_FILENAME = 'cache.h5'
def get_country_data(af: AnalyticsFunction,
focus_year=FOCUS_YEAR,
year_range=YEAR_RANGE,
project_id=PROJECT_ID,
data: str = 'current'):
def get_data(af,
current_table,
project_id=project_id,
focus_year=2018,
year_range: tuple = (2000, 2019)):
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(SCOPES, )
template_sql = '''
SELECT
id,
name,
country,
country_code,
region,
subregion,
published_year,
combined.total as total,
combined.oa as total_oa,
combined.green as green,
combined.gold as gold,
combined.gold_just_doaj as gold_just_doaj,
combined.hybrid as hybrid,
combined.bronze as bronze,
combined.green_only as green_only,
combined.green_in_home_repo as green_in_home_repo,
combined.total_citations as total_citations,
combined.articles_with_citations as cited_articles,
combined.total_oa_citations as oa_citations,
combined.total_gold_citations as gold_citations,
combined.total_green_citations as green_citations,
combined.total_hybrid_citations as hybrid_citations
FROM
`{}` as institutions,
UNNEST(years) as years
WHERE
years.published_year > 2000 and
years.published_year < 2020
'''
institutions_sql = template_sql.format(current_table)
institutions = pd.io.gbq.read_gbq(institutions_sql,
project_id=project_id,
credentials=credentials,
dialect='standard',
verbose=False)
helpers.calculate_percentages(institutions, [
'total_oa', 'green', 'gold', 'gold_just_doaj', 'hybrid', 'bronze',
'green_only', 'green_in_home_repo'
], 'total')
helpers.clean_geo_names(institutions)
helpers.nice_column_names(institutions)
template_sql = '''
SELECT
id,
years.published_year as published_year,
funders.name as name,
funders.count as count,
funders.oa as oa,
funders.green as green,
funders.gold as gold
FROM `{}`,
UNNEST(years) as years,
UNNEST(years.combined.funders) as funders
WHERE
years.published_year = {}
ORDER BY published_year DESC, count DESC
'''
funders_sql = template_sql.format(current_table, focus_year)
funders = pd.io.gbq.read_gbq(funders_sql,
project_id=project_id,
credentials=credentials,
dialect='standard',
verbose=False)
helpers.calculate_percentages(funders, ['oa', 'green', 'gold'], 'count')
helpers.nice_column_names(funders)
template_sql = '''
SELECT
id,
years.published_year as published_year,
type.type as type,
type.total as total,
type.oa as oa,
type.green as green,
type.gold as gold
FROM `{}`,
UNNEST(years) as years,
UNNEST(years.combined.output_types) as type
WHERE
years.published_year > {} AND
years.published_year < {}
ORDER BY published_year DESC
'''
type_sql = template_sql.format(current_table, *year_range)
output_types = pd.io.gbq.read_gbq(type_sql,
project_id=project_id,
credentials=credentials,
dialect='standard',
verbose=False)
helpers.calculate_percentages(output_types, ['oa', 'green', 'gold'],
'total')
helpers.clean_output_type_names(output_types)
helpers.nice_column_names(output_types)
with pd.HDFStore(HDF5_CANONICAL_FILENAME
) as store: # write directly to the CACHE file location
store['institutions'] = institutions
store['funders'] = funders
store['outputs_types'] = output_types
af.add_existing_file(HDF5_CANONICAL_FILENAME, remove=True)
import regex as regex
import tqdm
import pydata_google_auth
import Helper
import re
import Config
SCOPES = [
'https://www.googleapis.com/auth/cloud-platform',
'https://www.googleapis.com/auth/drive',
]
credentials = pydata_google_auth.get_user_credentials(
SCOPES,
# Set auth_local_webserver to True to have a slightly more convienient
# authorization flow. Note, this doesn't work if you're running from a
# notebook on a remote sever, such as over SSH or with Google Colab.
auth_local_webserver=True,
)
#Query to get Shapley values for pages & Categories. The query loops through the event level GA4 data for every month (shapley values are calculated at the monthly level)
for m in Config.months:
df = pandas_gbq.read_gbq(
f"""SELECT
'{m}' as Month,
Path,
PathCategory,
sum(Transactions.Transactions) as Transactions
FROM
(
#Paths Table
def reauth():
pydata_google_auth.get_user_credentials(
SCOPES, credentials_cache=pydata_google_auth.cache.REAUTH)
def _connect_cache(self):
return pydata_google_auth.get_user_credentials(self.scopes)
