def obtain_region_data():
database = Database()
query, column_names = database.coronavirus_region_list(region='madrid')
df = pd.DataFrame(query)
df.columns = column_names
return df
class Gsheet:
services = {}
database = Database()
filename = 'coronavirus_data'
file_id = ''
increment = 10000
def gsheet_service(self):
"""
Esta función devuelve los 2 servicios necesarios de google en un diccionario:
- sheets service
- drive service
"""
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive.file',
'https://www.googleapis.com/auth/drive'
]
creds = None
if os.path.exists('token.pickle'):
with open('token.pickle', 'rb') as token:
creds = pickle.load(token)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file('credentials.json', scopes)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.pickle', 'wb') as token:
pickle.dump(creds, token)
service_sheet = build('sheets', 'v4', credentials=creds)
service_drive = build('drive', 'v3', credentials=creds)
self.services = {
'sheet': service_sheet,
'drive': service_drive
}
return
def get_gdrive_file_id(self):
"""
Esta función devuelve el id de un fichero de drive desde su nombre
"""
files = self.services['drive'].files()
results = files.list(q="trashed=false and name='{}'".format(self.filename), fields="files(id)").execute()
items = results.get('files', [])
try:
self.file_id = items[0]['id']
except:
file_metadata = {
'name': self.filename,
'mimeType': 'application/vnd.google-apps.spreadsheet'
}
file = files.create(body=file_metadata, fields='id').execute()
self.file_id = file.get('id')
return
def upload_gsheet_data(self, gsheet_list, sheet):
"""
Esta función carga los datos de una lista dentro de un gsheet.
Lo hago en bloques, porque de 1 en uno es muy lento y todo completo da un error de timeout
"""
index = 1
end_index = index + self.increment - 1
last_line = []
rows = gsheet_list[0]
for r in rows:
last_line.append('')
letter = chr(ord('A') - 1 + len(rows))
while True:
update_range = '{sheet}!A{line}:{column}'.format(sheet=sheet, line=index, column=letter)
sub_list = gsheet_list[index - 1:end_index]
if not sub_list:
break
sub_list.append(last_line)
body = {
'values': sub_list
}
self.services['sheet'].spreadsheets().values().update(
spreadsheetId=self.file_id,
range=update_range,
valueInputOption='RAW',
body=body
).execute()
print("Added rows to the sheet {} from {} to {}".format(sheet, index, len(sub_list) - 1),)
index = end_index + 1
end_index = index + self.increment - 1
# time.sleep(0.5)
def create_data_sheet(self):
gsheet_list = self.database.coronavirus_data_list()
self.upload_gsheet_data(
gsheet_list=gsheet_list,
sheet='coronavirus'
)
return
def create_countries_sheet(self):
gsheet_countries = self.database.coronavirus_geography_list()
self.upload_gsheet_data(
gsheet_list=gsheet_countries,
sheet='geography'
)
return
def set_filename(self, filename):
self.filename = filename
self.get_gdrive_file_id()
def __init__(self):
self.gsheet_service()
self.get_gdrive_file_id()
class Json:
mydb = Database()
def read_row(self, data, date, country_id, region_id, subregion_id):
"""
Esta función, genera una lista desde un diccionario con los elementos
que tiene que tener y en el orden que tienen que tener
"""
if region_id == '':
region_id = country_id
if subregion_id == '':
subregion_id = region_id
row = []
row.append(country_id)
row.append(region_id)
row.append(subregion_id)
row.append(date)
try:
row.append(data['today_confirmed'])
except:
row.append('0')
try:
row.append(data['today_deaths'])
except:
row.append('0')
try:
row.append(data['today_recovered'])
except:
row.append('0')
try:
row.append(data['today_open_case'])
except:
row.append('0')
try:
row.append(data['today_new_confirmed'])
except:
row.append('0')
try:
row.append(data['today_new_deaths'])
except:
row.append('0')
try:
row.append(data['today_new_recovered'])
except:
row.append('0')
try:
row.append(data['today_new_open_case'])
except:
row.append('0')
try:
row.append(data['yesterday_confirmed'])
except:
row.append('0')
try:
row.append(data['yesterday_deaths'])
except:
row.append('0')
try:
row.append(data['yesterday_recovered'])
except:
row.append('0')
try:
row.append(data['yesterday_open_case'])
except:
row.append('0')
return row
def get_data(self, country_id, country, region_id, subregion_id, from_date,
to_date):
url_subregion = 'https://api.covid19tracking.narrativa.com/api' \
'/country/{country_id}'.format(country_id=country_id)
if region_id != '':
url_subregion = url_subregion + '/region/{region_id}'.format(
region_id=region_id)
if subregion_id != '':
url_subregion = url_subregion + '/sub_region/{subregion_id}'.format(
subregion_id=subregion_id)
url_subregion = url_subregion + \
'?date_from={from_date}&date_to={to_date}'.format(from_date=from_date, to_date=to_date)
rows = []
try:
json_url = urlopen(url_subregion)
json_data = json.loads(json_url.read())['dates']
for j in json_data:
date = '' + j
if subregion_id != '':
data = json_data[date]['countries'][country]['regions'][0][
'sub_regions'][0]
elif region_id != '':
data = json_data[date]['countries'][country]['regions'][0]
else:
data = json_data[date]['countries'][country]
row = self.read_row(data, date, country_id, region_id,
subregion_id)
rows.append(row)
except:
print('Error al obtener: {url}'.format(url=url_subregion))
return rows
def load_subregion_by_region(self, url, country_id, country, region_id,
from_date, to_date):
url = 'https://api.covid19tracking.narrativa.com{url}'.format(url=url)
json_url = urlopen(url)
sub_regions = json.loads(json_url.read())['countries']
rows = []
for sr in sub_regions[0][country_id][region_id]:
# print(' Sub Region: {}'.format(sr['name']))
row_aux = self.get_data(country_id=country_id,
country=country,
region_id=region_id,
subregion_id=sr['id'],
from_date=from_date,
to_date=to_date)
rows = rows + row_aux
return rows
def load_region_by_country(self, url, country_id, country, from_date,
to_date):
url = 'https://api.covid19tracking.narrativa.com{url}'.format(url=url)
json_url = urlopen(url)
regions = json.loads(json_url.read())['countries']
rows = []
for r in regions[0][country_id]:
print(' Region: {}'.format(r['name']))
rows_aux = self.get_data(country_id=country_id,
country=country,
region_id=r['id'],
subregion_id='',
from_date=from_date,
to_date=to_date)
rows = rows + rows_aux
return rows
def load_country(self, from_date, to_date, country):
"""Esta función obtiene un json de una api web y lo devuelve"""
url = 'https://api.covid19tracking.narrativa.com/api/countries'
json_url = urlopen(url)
countries = json.loads(json_url.read())['countries']
rows = []
for c in countries:
if c['name'] == country or country == '':
rows_aux = []
if c['name'] == 'Spain':
rows_aux = self.load_region_by_country(
url=c['links'][0]['href'],
country_id=c['id'],
country=c['name'],
from_date=from_date,
to_date=to_date)
if not rows_aux:
rows_aux = self.get_data(country_id=c['id'],
country=c['name'],
region_id='',
subregion_id='',
from_date=from_date,
to_date=to_date)
rows = rows + rows_aux
if rows_aux:
self.mydb.insert_coronavirus(rows_aux)
return
def load_countries(self):
"""Esta función obtiene un json de una api web y lo devuelve"""
url = 'https://api.covid19tracking.narrativa.com/api/countries'
json_url = urlopen(url)
countries = json.loads(json_url.read())['countries']
self.mydb.insert_country(countries=countries)
print('Countries have been loaded')
return
def load_regions(self):
url = 'https://api.covid19tracking.narrativa.com/api/countries/spain/regions'
json_url = urlopen(url)
regions_aux = json.loads(json_url.read())['countries'][0]
country_id = list(regions_aux.keys())[0]
regions = regions_aux['spain']
for c in regions:
self.mydb.insert_region(country_id, regions)
# self.load_subregions(
# country_id=country_id,
# region_id=c['id'],
# url=c['links'][0]['href']
# )
self.mydb.create_null_geography()
print('Regions and subregions have been loaded')
return
def load_subregions(self, country_id, region_id, url):
url = 'https://api.covid19tracking.narrativa.com{url}'.format(url=url)
json_url = urlopen(url)
sub_regions = json.loads(json_url.read())['countries']
self.mydb.insert_subregion(country_id, region_id,
sub_regions[0][country_id][region_id])
return
return
def load_subregions(self, country_id, region_id, url):
url = 'https://api.covid19tracking.narrativa.com{url}'.format(url=url)
json_url = urlopen(url)
sub_regions = json.loads(json_url.read())['countries']
self.mydb.insert_subregion(country_id, region_id,
sub_regions[0][country_id][region_id])
return
# Este main es para hacer las cargas iniciales
if __name__ == '__main__':
json_var = Json()
database = Database()
days_per_block = 7
from_dt = datetime.datetime.strptime('2020-02-01', '%Y-%m-%d')
to_dt = from_dt + datetime.timedelta(days=days_per_block)
until_dt = datetime.datetime.strptime('2020-05-05', '%Y-%m-%d')
while from_dt < until_dt:
print("Principio de vuelta {}".format(datetime.datetime.now()))
from_date = datetime.date.strftime(from_dt, "%Y-%m-%d")
to_date = datetime.date.strftime(to_dt, "%Y-%m-%d")
json_var.load_country(from_date=from_date,
to_date=to_date,
country='Spain')
from_dt = to_dt + datetime.timedelta(days=1)
to_dt = from_dt + datetime.timedelta(days=days_per_block)
from coronavirus_database import Database
from coronavirus_json import Json
from coronavirus_gsheet import Gsheet
if __name__ == '__main__':
today = datetime.datetime.now()
to_dt = today - datetime.timedelta(days=1)
from_dt = to_dt - datetime.timedelta(days=2)
to_date = datetime.date.strftime(to_dt, "%Y-%m-%d")
from_date = datetime.date.strftime(from_dt, "%Y-%m-%d")
print(f'{to_dt}, {from_dt}')
# from_date = '2020-05-21'
# to_date = '2020-05-22'
database = Database()
json = Json()
gsheet = Gsheet()
database.delete_last_days(from_date)
print('Database deleted')
json.load_countries()
json.load_regions()
json.load_country(from_date=from_date, to_date=to_date, country='')
print('Database loaded')
database.update_rank()