Different results of code with local account and technical account

[dreyz64]

Hi everyone,

I am running a forecasting code and am having a very weird issue.
My code is stored on a server and we have created a service account which takes the code and executes it.
This allows us to have the code launched automatically based on a scheduler.

My issue is now the following.
My code is failing when I am executing it using the service account and job scheduler.
I get an arithmetic overflow:
pyodbc.DataError: ('22003', '[22003] [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting float to data type numeric. (8115) (SQLExecDirectW); [22003] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)')

Now this is normal given that apparently the number I am trying to upload is: 1.0753157419192108e+264

The thing is, if I take this exact code and run it but with my user account (throught the command terminal). I get no error message and this number:2.812633513

Does anyone know what could be the source of these two behaviors?
Again, the code is EXACTLY the same. I literally copy and pasted the same version of it.
I have no clue what is wrong :(

Would anyone be able to help?
Thanks!
Cheers

[micseydel]

This sounds like an MS SQL question, probably not a Python question.

[dreyz64]

This sounds like an MS SQL question, probably not a Python question.

Hi Micseydel
Thank you for your answer.
The SQL error seems to have pop up as a result of the issue when we try to upload our results into the database.

What I did is uncomment the part and re-ran the code.
I basically plotted the numbers that the algorithm spits out when ran using my normal account and using the technical account on our server.
I still get different numbers even though the code is the same...

Running it on my computer i get these numbers:
0
2019-02-01 2.818704599
2019-03-01 2.91823362
2019-04-01 3.04171862
2019-05-01 1.400378323

When i ran it using the job server and technical account:
2019-02-01 NaN
2019-03-01 NaN
2019-04-01 NaN
2019-05-01 NaN

Anyone knows what could be the issue?

[micseydel]

You really need to provide more details. If your question is about Python code, we need to see the code that causes the problem. (If that code more than about ten lines, I highly recommend you come up with example code which is minimized to reproduce your problem, hard-code as much as possible.) As for the most recent results you posted, I'm not especially familiar with MS SQL but you'll need to provide your queries if you want people to comment on the results.

[dreyz64]

You really need to provide more details. If your question is about Python code, we need to see the code that causes the problem. (If that code more than about ten lines, I highly recommend you come up with example code which is minimized to reproduce your problem, hard-code as much as possible.) As for the most recent results you posted, I'm not especially familiar with MS SQL but you'll need to provide your queries if you want people to comment on the results.

hi Micseydel,

here is the code:

from datetime import datetime, timedelta
import pandas as pd
import numpy as np
import ast
from warnings import catch_warnings
from warnings import filterwarnings
from statsmodels.tsa.holtwinters import ExponentialSmoothing
import time
import sqlite3
import pyodbc as msql
from dateutil.relativedelta import relativedelta

def lit_eval(strg):
    if type(strg) == str:
        return ast.literal_eval(strg)
    else:
        return strg

def pd_dict2list(row):
    return [value for key, value in row.items()][0:-1]

def frcst_cot(y, model, setup, fcst_hrz):
    result = None
    try:
        # never show warnings when grid searching, too noisy
        with catch_warnings():
            filterwarnings("ignore")

            t, d, s, p, b, r = setup
            # fit model
            model = ExponentialSmoothing(y, seasonal_periods=p, trend=t, seasonal=s, damped=d).fit(use_boxcox=b,
                                                                                                   remove_bias=r)
            yhat_v = model.predict(len(y) - fcst_hrz)
            yhat = model.forecast(fcst_hrz)
            result = pd.concat([yhat_v, yhat], axis=0)
    except:
        error = None
        result = None

    # if no forecasting was done applying generic model    
    if result is None:
        print('result is none, trying generic model...')
        try:
            # never show warnings when grid searching, too noisy
            with catch_warnings():
                filterwarnings("ignore")

                t, d, s, p, b, r = setup
                # fit model
                model = ExponentialSmoothing(y).fit()
                yhat_v = model.predict(len(y) - fcst_hrz)
                yhat = model.forecast(fcst_hrz)
                result = pd.concat([yhat_v, yhat], axis=0)
        except:
            error = None
            result = None

    # if results are still None using Naive model prediction 
    if result is None:
        print('result is still none, applying Naive model...')
        try:
            # never show warnings when grid searching, too noisy
            with catch_warnings():
                filterwarnings("ignore")

                forecst = y.copy()
                idx = pd.date_range(start=y.tail(1).index.min(), periods=fcst_hrz, freq='MS')
                forecst = forecst.reindex(idx, fill_value=0)
                forecst['Volume'] = y.tail(1).values[0][0]
                forecst = forecst.squeeze()
        except:
            error = None
    return result

# TODO: Add path to pickle file BEST_MODELS FILE
best_models = pd.read_pickle(r'C:\Users\XXX\Desktop\..\best_models.pkl')

starttime = time.time()

for itm in range(len(best_models)):
    row = best_models[itm:itm + 1]

    fcst_horizon = 12

    cols = (row.T != '(All)')
    cols.columns = ['chek']
    cols = cols[cols.chek == True].T

    needed_cols = list(set(cols.columns) - {'DATE', 'MODEL', 'SETUP', 'VERTEX'})

    qry_str = '\tand '.join([(c + ' == "' + row[c].values[0] + '" ') for c in needed_cols])

    # TODO: ADD PICKLE PATH COT_VERTEX
    CoT_vertex = pd.read_pickle(r'C:\Users\XXX\Desktop\..CoT_vertex.pkl')
    CoT_vertex = CoT_vertex.copy()

    CoT_vertex = CoT_vertex.pivot_table(index='Date', values=['Volume'], aggfunc=np.sum).reset_index()

    if CoT_vertex.Date.min() != CoT_vertex.Date.min():
        print('no COT data for:' + qry_str)
    else:
        if datetime.strptime(CoT_vertex.Date.max(), '%Y-%m-%d').date() >= (
                datetime.now().replace(day=1) + relativedelta(months=-2)).date():  # Added
            idx = pd.date_range(start=CoT_vertex.Date.min(), end=CoT_vertex.Date.max(), freq='MS')
            CoT_vertex.set_index('Date', inplace=True)
            CoT_vertex = CoT_vertex.reindex(idx, fill_value=0)
            CoT_vertex = CoT_vertex.squeeze()

            frcst = frcst_cot(CoT_vertex, row['MODEL'].values[0], row['SETUP'].values[0], fcst_horizon)

            print(frcst)

        else:
            print('Last COT older than 3m')

I tried to hardcode as much as I can but I am very new at Python so still struggling.
I am now looking to see how to send you the 2 pickle files so that you can run the code...

[dreyz64]

You really need to provide more details. If your question is about Python code, we need to see the code that causes the problem. (If that code more than about ten lines, I highly recommend you come up with example code which is minimized to reproduce your problem, hard-code as much as possible.) As for the most recent results you posted, I'm not especially familiar with MS SQL but you'll need to provide your queries if you want people to comment on the results.

Hi again,
Would you know how I can get to you the 2 pickle files? I don't see that I can attach files on the forum...
BEst

[buran]

Next post will be your fifth, so you will get the privilege to attach files

https://python-forum.io/misc.php?action=help&hid=31

[dreyz64]

Next post will be your fifth, so you will get the privilege to attach files

https://python-forum.io/misc.php?action=help&hid=31

Omg yay :) Thanks!

---

You really need to provide more details. If your question is about Python code, we need to see the code that causes the problem. (If that code more than about ten lines, I highly recommend you come up with example code which is minimized to reproduce your problem, hard-code as much as possible.) As for the most recent results you posted, I'm not especially familiar with MS SQL but you'll need to provide your queries if you want people to comment on the results.

Ok i don't see the attachment section.
But i have managed to put the 2 pickle files into a download link:
https://we.tl/t-Rr9O4XDs0N

Let me know if this is ok :)