df = df[df.price.notna()].copy()
slope, intercept, r_value, _, _ = stats.linregress(
np.log(df.netdemand.values), df.price.values)
df['predictprice'] = np.log(df.netdemand.values) * slope + intercept
df['error'] = df.price - df.predictprice
#print('slope: {}, intercept: {}, r: {}'.format(slope, intercept, r_value))
s = """
INSERT INTO price_function (date, slope, intercept, r, created_on)
VALUES
('{date}', {slope}, {intercept}, {r}, CURRENT_TIMESTAMP);
"""
#('2020-07-04', 2., 2.9, 0.7, CURRENT_TIMESTAMP);
s = s.format(date=t.strftime('%Y-%m-%d'),
slope=slope,
intercept=intercept,
r=r_value)
conn, cur = getConnection()
cur.execute(s)
conn.commit()
conn.close()
except Exception as err:
errstr += str(err)
errstr += traceback.format_exc() + '\n'
email_script(errstr, 'pricefit.py', 0)
if len(errstr):
print(errstr)
if len(dfs):
s = """
INSERT INTO sm_hh_variable_vals (var_id, period_id, value) values
"""
for a, b in dfs.iterrows():
s+= " ({}, {}, {}),".format(j.var_id, a, b.value_exc_vat)
s = s[:-1] + ';'
loadDataFromDb(s)
except Exception as err:
errstr += 'Problem with Agile price import \n'
errstr += str(err)
errstr += traceback.format_exc() + '\n'
try:
cutoff = (datetime.datetime.now()-pd.offsets.Day(2)).isoformat()
s = f'''
update sm_accounts set active='0' where last_updated<'{cutoff}' and session_id != 'e4280c7d-9d06-4bbe-87b4-f9e106ede788'
'''
loadDataFromDb(s)
s = '''
delete from sm_quantity where account_id not in (select account_id from sm_accounts where active='1')
'''
loadDataFromDb(s)
except Exception as err:
errstr += 'Problem with deleting historical data \n'
errstr += str(err)
errstr += traceback.format_exc() + '\n'
email_script(errstr, 'smloads.py', 1)
if len(errstr):
print(errstr)
data['netdemand'] = data.demand - data.wind
conn, cur = getConnection()
s = 'select slope, intercept from price_function order by date desc, created_on desc limit 1;'
cur.execute(s)
slope, intercept = cur.fetchone()
data['price'] = np.log(data.netdemand.values) * slope + intercept
if True:
timestamp = datetime.datetime.now().isoformat()[:16]
s = """
INSERT INTO price_forecast (datetime, demand, solar, wind, price, created_on)
VALUES """
for i, j in data.iterrows():
s += "('{}', {}, {}, {}, {}, '{}'),".format(
i, j.grossdemand, j.solar, j.wind, j.price, timestamp)
s = s[:-1] + ';'
#print(s)
cur.execute(s)
conn.commit()
conn.close()
except Exception as err:
errstr += str(err)
errstr += traceback.format_exc() + '\n'
email_script(errstr, 'priceforecast.py', 1)
if len(errstr):
print(errstr)
import traceback
errstr = ''
try:
s = '''
with sessions as (
select sm_accounts.account_id, sm_accounts.session_id, sm_accounts.type_id, last_updated, max(datetime) as last_called
from sm_accounts
left outer join sm_log on sm_accounts.session_id=sm_log.session_id
where sm_accounts.session_id != 'e4280c7d-9d06-4bbe-87b4-f9e106ede788' and sm_accounts.active='1'
group by account_id, last_updated)
update sm_accounts set active='0' where account_id in
(
select account_id from sessions where last_updated<CURRENT_TIMESTAMP-Interval '6 hours' or last_called<CURRENT_TIMESTAMP-Interval '3 hours' )
'''
loadDataFromDb(s, returndf=True)
#print(loadDataFromDb(s, returndf=True))
s = "delete from sm_quantity where account_id not in (select account_id from sm_accounts where active='1') "
loadDataFromDb(s, returndf=True)
#print(loadDataFromDb(s, returndf=True))
except Exception as err:
errstr += str(err)
errstr += traceback.format_exc()
email_script(errstr, 'smdelete', 0)
if len(errstr):
print(errstr)