def query_trafficdata(ip, user_id): """Query traffic input/output for IP :param ip: a valid ip :param user_id: an id of a mysql user tuple :return: a dict containing the traffic data in the form of {'history': [('weekday', in, out, credit), …], 'credit': credit} """ trafficdata = sql_query( "SELECT t.timetag - %(today)s AS day, input, output, amount " "FROM traffic.tuext AS t " "LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag " "WHERE ip = %(ip)s AND c.user_id = %(uid)s " "AND t.timetag BETWEEN %(weekago)s AND %(today)s " "ORDER BY 'day' DESC ", {'today': timetag_from_timestamp(), 'weekago': timetag_from_timestamp() - 6, 'ip': ip, 'uid': user_id} ).fetchall() if not trafficdata: raise DBQueryEmpty('No trafficdata retrieved for user {}@{}' .format(user_id, ip)) traffic = {'history': [], 'credit': 0} returned_days = [int(i['day']) for i in trafficdata] # loop through expected days ([-6..0]) for d in range(-6, 1): day = datetime.date.fromtimestamp( timestamp_from_timetag(timetag_from_timestamp() + d) ).strftime('%w') if d in returned_days: # pick the to `d` corresponding item of the mysql-result i = next((x for x in trafficdata if x['day'] == d), None) (input, output, credit) = ( round(i[param] / 1024.0, 2) for param in ['input', 'output', 'amount'] ) traffic['history'].append( (WEEKDAYS[day], input, output, credit)) else: traffic['history'].append( (WEEKDAYS[day], 0.0, 0.0, 0.0)) traffic['credit'] = (lambda x: x[3] - x[1] - x[2])(traffic['history'][-1]) return traffic
def query_current_credit(uid=None, ip=None): """Returns the current credit in MiB :param uid: The id of the user :param ip: The ip of the user :return: The current amount of credit or False if foreign IP """ if uid is None: if ip is None: raise AttributeError('Either ip or user_id must be specified!') user_id = user_id_from_ip(ip) if user_id is 0: return False # IP doesn't correspond to any user else: user_id = user_id_from_uid(uid) ip = ip_from_user_id(user_id) try: result = sql_query( "SELECT amount - input - output as current " "FROM traffic.tuext AS t " "LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag " "WHERE ip = %(ip)s AND c.user_id = %(user_id)s " "AND t.timetag = %(today)s", {'today': timetag_from_timestamp(), 'ip': ip, 'user_id': user_id} ).fetchone() except OperationalError as e: logger.critical('Unable to connect to MySQL server', extra={'data': {'exception_args': e.args}}) raise else: return round(result['current'] / 1024, 2)