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)