def handle(self, *args, **options):
# read users list
users = read_csv(f'{DATA_ROOT}/{USERS_FILE}')
for i, user in users.iterrows():
try:
# add user record to db
new_user = User(id=int(user['id']),
area=user['area'],
tariff=user['tariff'])
new_user.save()
print(f'Importing consumptions for user {user["id"]}')
except IntegrityError:
print(f'Skipping already existing user {user["id"]}')
new_user = User.objects.get(id=int(user['id']))
# read user's consumption records
consumptions = read_csv(
f'{DATA_ROOT}/{CONSUMPTIONS_DIR}/{user["id"]}.csv',
parse_dates=['datetime'])
for j, consumption in consumptions.iterrows():
try:
# add user's single consumption to db
Consumption(user=new_user,
datetime=consumption['datetime'],
consumption=float(
consumption['consumption'])).save()
except IntegrityError:
print(
f'Skipping already existing consumption record for user {user["id"]} at'
f' {consumption["datetime"]}')
def setup(self):
new_user = User(id=100, area='area1', tariff='t1')
new_user.save()
Consumption(user_id=100,
consumption=30.0,
datetime='2016-07-15 09:30:00').save()
Consumption(user_id=100,
consumption=20.0,
datetime='2016-07-19 12:30:00').save()
Consumption(user_id=100,
consumption=10.0,
datetime='2016-07-21 11:30:00').save()
new_user = User(id=101, area='area1', tariff='t1')
new_user.save()
Consumption(user_id=101,
consumption=45.0,
datetime='2016-07-15 09:30:00').save()
Consumption(user_id=101,
consumption=27.0,
datetime='2016-07-21 10:30:00').save()
Consumption(user_id=101,
consumption=21.0,
datetime='2016-07-24 11:30:00').save()
Consumption(user_id=101,
consumption=43.0,
datetime='2016-09-02 06:30:00').save()
def store_expected_user(self, user_id):
area = self.get_random_string()
tariff = self.get_random_string()
try:
caused_error = False
user = User(user_id=user_id, area=area, tariff=tariff)
user.save()
user = list(User.objects.filter(user_id=user_id))[0]
except Exception:
caused_error = True
self.assertFalse(caused_error)
self.assertEqual(user.area, area)
self.assertEqual(user.tariff, tariff)
def test_average(self):
new_user = User(id=100, area='area1', tariff='t1')
new_user.save()
Consumption(user_id=100,
consumption=30.0,
datetime='2016-07-15 09:30:00').save()
Consumption(user_id=100,
consumption=20.0,
datetime='2016-07-19 12:30:00').save()
# average consumptions during July must be (20 + 30) / 2 = 50
self.assertEqual(new_user.calculate_monthly_consumptions()[1][6],
50.00)
def handle(self, *args, **options):
"""
command line method for ETL of user and consumption data from csv to default database
(see dashboard/settings.py to configure the engine)
:param args: args
:param options: kwargs
:return: None
"""
stdlogger = logging.getLogger(__name__)
data_dir = os.path.join(os.path.dirname(settings.BASE_DIR), 'data')
user_path = os.path.join(data_dir, 'user_data.csv')
stdlogger.info("Reading file: {}".format(user_path))
with open(user_path, 'r') as user_data:
reader = csv.DictReader(user_data, delimiter=',')
u_counter = 0
for u_counter, record in enumerate(reader):
user = User()
user.id = int(record['id'])
user.area = record['area']
user.tariff = record['tariff']
stdlogger.debug(
"Saving User object: Id: {}, Area: {}, Tariff: {}".format(
user.id, user.area, user.tariff))
user.save()
stdlogger.info("{} User records saved to database".format(u_counter))
consumption_path = os.path.join(data_dir, 'consumption', '*.csv')
consumption_files = glob.glob(consumption_path)
for con_file in consumption_files:
stdlogger.info("Reading file: {}".format(con_file))
with open(con_file, 'r') as con_data:
reader = csv.DictReader(con_data, delimiter=',')
c_counter = 0
for c_counter, record in enumerate(reader):
usage = Consumption()
usage.user_id = User.objects.get(
id=int(os.path.split(con_file)[-1].rstrip('.csv')))
usage.timestamp = datetime.strptime(
record['datetime'], '%Y-%m-%d %H:%M:%S')
usage.consumption = float(record['consumption'])
stdlogger.debug("Saving Consumption object: "
"Id: {}, Area: {}, Tariff: {}".format(
usage.user_id, usage.timestamp,
usage.consumption))
usage.save()
stdlogger.info(
"{} Consumption records saved to database".format(c_counter))