Hello guys,
I made an app that takes records from a database and show’s them.Now I have several machines that use the same querry from the databases but the new one has one more value in itemlog in her own database, that the others don’t have. So I made an if - else function and it’s not reading the dataframes okay,do you maybe know what’s wrong here?
Or have some other suggestions?
Some notes:
I can pick 7 machines from a dropdown menu that read the same query as below and they give data.just the eight one has one more column in her ItemLog db.
I wanted to make something that read the querry and if there is no “FloatID1” in it it will show the version without it
Is it maybe a problem in my querry or I should do my function in some other way?
I put my old version of the function at the end below.
In here I tried to check if the column ‘Float1D’ is present in the returned records, and then use the correct code block.
Instead of trying to access the column in the first record, I use the pd.DataFrame.from_records() method to create a DataFrame from the records, and then check if the column ‘FloatID1’ is present in the DataFrame. If it is present, then it should the first code block, otherwise it should the second code block.
This is my current app.py
def show_users(
n_clicks,
start_date_picker,
start_time_picker,
end_date_picker,
end_time_picker,
database,
result_selection,
language,
):
if n_clicks:
start_time = datetime.datetime.strptime(
start_date_picker, "%Y-%m-%d"
) + datetime.timedelta(hours=int(start_time_picker[0:2]))
end_time = datetime.datetime.strptime(
end_date_picker, "%Y-%m-%d"
) + datetime.timedelta(hours=int(end_time_picker[0:2]))
parameters = [start_time, end_time]
query_string = mssql_queries.build_db_query_string_app2_new(result_selection)
records = mssql_conn.execute_query(query_string, parameters, str(database))
print("result_selection")
dataframe = pd.DataFrame.from_records(records)
if 'FloatID1' in dataframe.columns:
dataframe.columns = ["ID", "Timestamp", "Type", "Result", "DefectCode", "FloatID1"]
mask = dataframe.applymap(type) != bool
d = {True: "1", False: "0"}
dataframe = dataframe.where(mask, dataframe.replace(d))
# Scrap Codes
query_string = mssql_queries.build_db_query_string_defect_descriptions(language)
records = mssql_conn.execute_without_params(query_string, str(database))
scrap_codes = pd.DataFrame.from_records(records)
scrap_codes.columns = ["DefectCode", "DefectDescription"]
scrap_codes["DefectCode"]=scrap_codes["DefectCode"].apply(numpy.int64)
dataframe = dataframe.merge(scrap_codes, how="left", on="DefectCode")
else:
dataframe.columns = ["ID", "Timestamp", "Type", "Result", "DefectCode"]
mask = dataframe.applymap(type) != bool
d = {True: "1", False: "0"}
dataframe = dataframe.where(mask, dataframe.replace(d))
# Scrap Codes
query_string = mssql_queries.build_db_query_string_defect_descriptions(language)
records = mssql_conn.execute_without_params(query_string, str(database))
scrap_codes = pd.DataFrame.from_records(records)
scrap_codes.columns = ["DefectCode", "DefectDescription"]
scrap_codes["DefectCode"]=scrap_codes["DefectCode"].apply(numpy.int64)
dataframe = dataframe.merge(scrap_codes, how="left", on="DefectCode")
print("----")
print(dataframe)
return (
dbc.Table.from_dataframe(dataframe),
dataframe.to_json(date_format="iso", orient="split"),
None,
)
if records is None or len(records) == 0:
return None, None, dbc.Alert("No entries within the specified timeframe.", color="warning", duration=5000)
else:
return dbc.Table(None), None, None
I just added ItemLog.FloatID1 in the query below,as I used this one without the ItemLog.FloatID1 before,maybe the placing is bad?
query.py
def build_db_query_string_app2_new(result_selection):
if not result_selection:
db_query_string = """SELECT ItemLog.ID, ItemLog.Timestamp, ItemLog.Type, ItemLog.Result, ItemLog.DefectCode, ItemLog.FloatID1
FROM ItemLog
WHERE ItemLog.Timestamp BETWEEN ? AND ?"""
else:
db_query_string = """SELECT ItemLog.ID, ItemLog.Timestamp, ItemLog.Type, ItemLog.Result, ItemLog.DefectCode
FROM ItemLog
WHERE ItemLog.Timestamp BETWEEN ? AND ? AND ItemLog.Result = {}""".format(result_selection)
return db_query_string
if try it like this and pick a machine that doesn’t have " FloatID1" in his database then I get:
Traceback (most recent call last):
File “C:\Users\Administrator\Documents\dashboard\apps\app2.py”, line 169, in show_users
records = mssql_conn.execute_query(query_string, parameters, str(database))
File “C:\Users\Administrator\Documents\dashboard\mssql_conn.py”, line 63, in execute_query
cursor.execute(query_string, parameters)
pyodbc.ProgrammingError: (‘42S22’, “[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ‘FloatID1’. (207) (SQLExecDirectW); [42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)”)
If I try the machine that does have the " FloatID1" then I get:
ValueError: Length mismatch: Expected axis has 6 elements, new values have 5 elements
“C:\Users\Administrator\Documents\dashboard\apps\app2.py”, line 187, in show_users
dataframe.columns = [“ID”, “Timestamp”, “Type”, “Result”, “DefectCode”]
This is how my function in the app looked before the new value for the new machine:
def show_users(
n_clicks,
start_date_picker,
start_time_picker,
end_date_picker,
end_time_picker,
database,
result_selection,
language,
):
if n_clicks:
start_time = datetime.datetime.strptime(
start_date_picker, "%Y-%m-%d"
) + datetime.timedelta(hours=int(start_time_picker[0:2]))
end_time = datetime.datetime.strptime(
end_date_picker, "%Y-%m-%d"
) + datetime.timedelta(hours=int(end_time_picker[0:2]))
parameters = [start_time, end_time]
query_string = mssql_queries.build_db_query_string_app2_new(result_selection)
records = mssql_conn.execute_query(query_string, parameters, str(database))
print("result_selection")
# query_string = mysql_queries.build_db_query_string_app2(str(database))
# records = connect.execute_query(query_string, parameters, str(database))
if records:
dataframe = pd.DataFrame.from_records(records)
dataframe.columns = ["ID", "Timestamp", "Type", "Result", "DefectCode"]
mask = dataframe.applymap(type) != bool
d = {True: "1", False: "0"}
dataframe = dataframe.where(mask, dataframe.replace(d))
# Scrap Codes
query_string = mssql_queries.build_db_query_string_defect_descriptions(
language
)
records = mssql_conn.execute_without_params(query_string, str(database))
scrap_codes = pd.DataFrame.from_records(records)
scrap_codes.columns = ["DefectCode", "DefectDescription"]
scrap_codes["DefectCode"] = scrap_codes["DefectCode"].apply(numpy.int64)
dataframe = dataframe.merge(scrap_codes, how="left", on="DefectCode")
print("----")
print(dataframe)
return (
dbc.Table.from_dataframe(dataframe),
dataframe.to_json(date_format="iso", orient="split"),
None,
)
else:
return (
None,
None,
dbc.Alert(
"No entries within the specified timeframe.",
color="warning",
duration=5000,
),
)
else:
return dbc.Table(None), None, None
