def deleteExcessRows(table, max_rows, time_field):
'''
Delete rows to bring count down to max_rows
INPUT table: name of table in Carto from which we will delete excess rows (string)
max_rows: maximum rows that can be stored in the Carto table (integer)
time_field: column that stores datetime information (string)
RETURN num_dropped: number of rows that have been dropped from the table (integer)
'''
# initialize number of rows that will be dropped as 0
num_dropped = 0
# get cartodb_ids from carto table sorted by date (new->old)
r = cartosql.getFields('cartodb_id',
table,
order='{} desc'.format(time_field),
f='csv',
user=CARTO_USER,
key=CARTO_KEY)
# turn response into a list of strings of the ids
ids = r.text.split('\r\n')[1:-1]
# if number of rows is greater than max_rows, delete excess rows
if len(ids) > max_rows:
r = cartosql.deleteRowsByIDs(table, ids[max_rows:], CARTO_USER,
CARTO_KEY)
# get the number of rows that have been dropped from the table
num_dropped += r.json()['total_rows']
if num_dropped:
logging.info('Dropped {} old rows from {}'.format(num_dropped, table))
return (num_dropped)
def deleteExcessRows(table, max_rows, time_field, max_age=''):
'''Delete excess rows by age or count'''
num_dropped = 0
if isinstance(max_age, datetime.datetime):
max_age = max_age.isoformat()
# 1. delete by age
if max_age:
r = cartosql.deleteRows(table, "{} < '{}'".format(time_field, max_age))
num_dropped = r.json()['total_rows']
# 2. get sorted ids (old->new)
r = cartosql.getFields('cartodb_id',
table,
order='{}'.format(time_field),
f='csv')
ids = r.text.split('\r\n')[1:-1]
# 3. delete excess
if len(ids) > max_rows:
r = cartosql.deleteRowsByIDs(table, ids[:-max_rows])
num_dropped += r.json()['total_rows']
if num_dropped:
logging.info('Dropped {} old rows from {}'.format(num_dropped, table))
return num_dropped
def deleteExcessRows(table, max_rows, time_field):
'''Delete rows to bring count down to max_rows'''
num_dropped=0
# 1. get sorted ids (old->new)
r = cartosql.getFields('cartodb_id', table, order='{} desc'.format(time_field),
f='csv')
ids = r.text.split('\r\n')[1:-1]
# 2. delete excess
if len(ids) > max_rows:
r = cartosql.deleteRowsByIDs(table, ids[max_rows:])
num_dropped += r.json()['total_rows']
if num_dropped:
logging.info('Dropped {} old rows from {}'.format(num_dropped, table))
return(num_dropped)
def deleteExcessRows(table, max_rows, age_field):
'''Delete excess rows by age or count'''
num_dropped = 0
# get sorted ids (old->new)
r = cartosql.getFields('cartodb_id',
table,
order='{}'.format(age_field.lower()),
f='csv')
ids = r.text.split('\r\n')[1:-1]
# delete excess
if len(ids) > max_rows:
r = cartosql.deleteRowsByIDs(table, ids[:-max_rows])
num_dropped += r.json()['total_rows']
if num_dropped:
logging.info('Dropped {} old rows from {}'.format(num_dropped, table))
return num_dropped
def deleteExcessRows(table, max_rows, time_field, max_age=''):
'''Delete excess rows by age or count'''
num_dropped = 0
if isinstance(max_age, datetime.datetime):
max_age = max_age.isoformat()
# 1. delete by age
if max_age:
r = cartosql.deleteRows(table, "{} < '{}'".format(time_field, max_age))
num_dropped = r.json()['total_rows']
# 2. get sorted ids (old->new)
ids = getFieldAsList(CARTO_TABLE, 'cartodb_id', orderBy=''.format(TIME_FIELD))
# 3. delete excess
if len(ids) > max_rows:
r = cartosql.deleteRowsByIDs(table, ids[:-max_rows])
num_dropped += r.json()['total_rows']
if num_dropped:
logging.info('Dropped {} old rows from {}'.format(num_dropped, table))
def insertIfNew(response,
existing_ids,
explore=True,
input_date=INPUT_DATE_FORMAT,
output_date=OUTPUT_DATE_FORMAT):
'''Loop over months in the data, add to new rows if new'''
m = map(methodcaller('split', '_'), (existing_ids))
existing_dates = list(zip(*m))
seen_dates = [] if not len(existing_dates) else existing_dates[0]
all_dates = set(sorted(seen_dates))
logging.debug('Seen dates: {}'.format(all_dates))
today = datetime.today().replace(hour=23, minute=30,
second=0).strftime(OUTPUT_DATE_FORMAT)
new_rows = []
for item in response['items']:
if explore:
dt_info = item['displayName'].split('_')[-1]
dt = datetime.strptime(dt_info + "2330", INPUT_DATE_FORMAT)
dt = dt.strftime(OUTPUT_DATE_FORMAT)
else:
dt_info = item['displayName'].split('_')[-2:]
dt = datetime.strptime(dt_info[0] + dt_info[1][:4],
INPUT_DATE_FORMAT)
dt = dt.strftime(OUTPUT_DATE_FORMAT)
if dt == today:
# Delete existing data for this date
logging.info('deleting ids related to today, {}'.format(dt))
delete_ids = [
existing_ids[ix] for ix, date in enumerate(seen_dates)
if date == dt
]
if len(delete_ids):
logging.debug('deleting ids: ' + str(delete_ids))
cartosql.deleteRowsByIDs(CARTO_TABLE_EXPLORE,
delete_ids,
id_field=UID_FIELD,
dtype='text')
else:
logging.debug('Not yet any ids for today')
if (dt not in seen_dates) or (dt == today):
logging.info('adding data for datetime ' + dt)
for act in item['action']:
if act['displayName'] == 'export':
for use in act['using']:
if use['displayName'] == 'geojson':
geojson = req.get(use['url']).json()
for ix, feature in enumerate(geojson['features']):
UID = genUID(dt, ix)
the_geom = feature['geometry']
nowcast = feature['properties']['nowcast']
values = []
for field in CARTO_SCHEMA:
if field == UID_FIELD:
values.append(UID)
if field == TIME_FIELD:
values.append(dt)
if field == 'nowcast':
values.append(nowcast)
if field == 'the_geom':
values.append(the_geom)
new_rows.append(values)
logging.debug('running count of num new rows: ' + str(len(new_rows)))
return new_rows