{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n",
"# importlib.reload(IotpAlgoService)\n",
"host = \"algo-dev-internal.li-ai.com.cn\"#\"192.168.0.43\"\n",
"# host = \"hb-bp182rj5nf626041g-proxy-phoenix.hbase.rds.aliyuncs.com\"\n",
"# ip = \"192.168.0.43\"\n",
"# port = 9765\n",
"port = 8765\n",
"hbase_param = {'host':host, 'port':port}\n",
"iotp_service = IotpAlgoService(hbase_params=hbase_param)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from ZlwlAlgosCommon.utils.ProUtils import *\n",
"from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n",
"from ZlwlAlgosCommon.service.iotp.Beans import DataField\n",
"# from COMMON.service.iotp.IotpAlgoService import IotpAlgoService\n",
"# from COMMON.service.iotp.Beans import DataField\n",
"from ZlwlAlgosCommon.orm.models import *\n",
"import pandas as pd\n",
"# from COMMON.service.iotp.IotpAlgoService import IotpAlgoService\n",
"cur_env = 'prd' # 设置运行环境\n",
"app_path = \"/home/wangliming/project/zlwl-algos/\" # 设置app绝对路径\n",
"\n",
"sysUtils = SysUtils(cur_env, app_path)\n",
"hbase_params = sysUtils.get_cf_param('hbase')\n",
"print(hbase_params)\n",
"iotp_service = IotpAlgoService(hbase_params=hbase_params)\n",
"columns = [ DataField.time, DataField.sn, DataField.pack_crnt, DataField.pack_volt, DataField.pack_soc, \n",
" DataField.cell_voltage_count, DataField.cell_temp_count, DataField.cell_voltage, DataField.cell_temp, \n",
" DataField.other_temp_value, DataField.bms_sta]\n",
"sn_list = ['PK50001A100000162']#PK50001A100000162,LY9F49BC5MALBZ882\n",
"start_time = \"2023-06-29 15:00:00\"\n",
"end_time = \"2023-06-30 15:23:00\"\n",
"df_data = iotp_service.get_data(sn_list=sn_list, columns=columns, start_time=start_time, end_time=end_time)\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from ZlwlAlgos.PERIODIC.task_min_5.group_1.safetyalarm.V_1_0_0.CBMSSafetyAlarm import SafetyAlarm\n",
"base_dir = \".\"\n",
"df_t_devices = pd.read_csv(f\"{base_dir}/param/t_device.csv\")\n",
"df_algo_adjustable_param = pd.read_csv(f\"{base_dir}/param/algo_adjustable_param.csv\")\n",
"df_algo_pack_param = pd.read_csv(f\"{base_dir}/param/algo_pack_param.csv\")\n",
"df_algo_list= pd.read_csv(f\"{base_dir}/param/algo_list.csv\")\n",
"df_r_risk_model= pd.read_csv(f\"{base_dir}/param/r_risk_model.csv\")\n",
"df_r_risk_list= pd.read_csv(f\"{base_dir}/param/sys_dict_data.csv\")\n",
"df_dept= pd.read_csv(f\"{base_dir}/param/sys_dept.csv\")\n",
"df_dept['organ_code'] = df_dept['organ_code'].astype(str)\n",
"\n",
"sn_list = sn_list\n",
"imei_list = [\"\"]\n",
"df_snlist = pd.DataFrame({'sn':sn_list, 'imei':imei_list})\n",
"pack_code = \"KY01710\"\n",
"algo_pack_param = json.loads(df_algo_pack_param[df_algo_pack_param['pack_code'] == pack_code]['param'].values[0])\n",
"df_data, df_table, cellvolt_name, celltemp_name = iotp_service.datacleaning(algo_pack_param,df_data)#进行数据清洗\n",
"df_bms_ram = pd.DataFrame(columns=['time', 'sn', 'packvolt', 'cellvolt', 'celltemp', 'packsoc','packcrnt'])\n",
"df_alarm_ram = pd.DataFrame(columns=['time','sn','safetywarning1','safetywarning2'])\n",
"df_diag_ram = pd.DataFrame(columns=['start_time','end_time','sn','imei','model','fault_level','fault_code','fault_info','fault_reason','fault_advice','fault_location','device_status','odo'])\n",
"safety_alarm = SafetyAlarm(df_data, df_diag_ram, df_bms_ram, df_alarm_ram, df_algo_list, df_table, cellvolt_name, celltemp_name, pack_code, df_snlist)\n",
"df_res_new, df_res_end, df_bms_ram, df_alarm_ram = safety_alarm.safety_alarm_diag()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"ename": "ModuleNotFoundError",
"evalue": "No module named 'COMMON'",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mModuleNotFoundError\u001b[0m Traceback (most recent call last)",
"\u001b[1;32m/home/wangliming/project/zlwl-algos/USER/wangliming/test.ipynb 单元格 6\u001b[0m line \u001b[0;36m2\n\u001b[1;32m 1\u001b[0m \u001b[39mfrom\u001b[39;00m \u001b[39mZlwlAlgosCommon\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mutils\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mProUtils\u001b[39;00m \u001b[39mimport\u001b[39;00m \u001b[39m*\u001b[39m\n\u001b[0;32m----> 2\u001b[0m \u001b[39mfrom\u001b[39;00m \u001b[39mCOMMON\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mservice\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mhz\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mHzAlgoService\u001b[39;00m \u001b[39mimport\u001b[39;00m HzAlgoService\n\u001b[1;32m 3\u001b[0m \u001b[39m# from COMMON.service.iotp.Beans import DataField\u001b[39;00m\n\u001b[1;32m 4\u001b[0m \u001b[39mfrom\u001b[39;00m \u001b[39mZlwlAlgosCommon\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39morm\u001b[39;00m\u001b[39m.\u001b[39;00m\u001b[39mmodels\u001b[39;00m \u001b[39mimport\u001b[39;00m \u001b[39m*\u001b[39m\n",
"\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'COMMON'"
]
}
],
"source": [
"from ZlwlAlgosCommon.utils.ProUtils import *\n",
"from COMMON.service.hz.HzAlgoService import HzAlgoService\n",
"# from COMMON.service.iotp.Beans import DataField\n",
"from ZlwlAlgosCommon.orm.models import *\n",
"import pandas as pd\n",
"\n",
"hbase_params = {\n",
" \"host\":\"ld-uf69n0762fjx616l6-proxy-hbaseue.hbaseue.rds.aliyuncs.com\",\n",
" \"port\":30060,\n",
" \"username\":\"root\",\n",
" \"password\":\"root\",\n",
" \"db\":\"algo_result\"\n",
"}\n",
"hz_service = HzAlgoService(hbase_params=hbase_params)\n",
"sn = '09SPE005FA0111C180000214'#PK50001A100000162,LY9F49BC5MALBZ882\n",
"start_time = \"2023-01-01 00:00:00\"\n",
"end_time = \"2023-01-02 00:00:00\"\n",
"c,r = hz_service.get_original_hz_algo_data(\"algo_health_score\",sn, start_time=start_time, end_time=end_time)\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"cellVoltNums=20\n",
"\n",
"cellvolt_name=[f'cellVoltage{x}' for x in range(1, cellVoltNums+1)]\n",
"df_data[['cellVoltage']][0:10]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df_data['otherTempValue'].loc[0]\n",
"# d = [x[0] for x in np.array(df_data[['cellVoltage']][0:10])]\n",
"\n",
" \n",
" "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.DataFrame().astype()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.DataFrame(d, columns=cellvolt_name)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"type(d[0])"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from typing import List\n",
"a = {\"cellVoltage\": List[float]}\n",
"str(a['cellVoltage'])\n",
"# d[0] = list(d[0])"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import time, datetime\n",
"\n",
"from apscheduler.schedulers.blocking import BlockingScheduler\n",
"import pandas as pd\n",
"from ZlwlAlgosCommon.orm.models import *\n",
"from ZlwlAlgosCommon.utils.ProUtils import *\n",
"from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n",
"cur_env = 'dev' # 设置运行环境\n",
"app_path = \"/home/wangliming/project/zlwl-algos/\" # 设置app绝对路径\n",
"app_name = \"schedule\" # 应用名, 建议与topic的后缀相同\n",
"\n",
"sysUtils = SysUtils(cur_env, app_path)\n",
"\n",
"mysqlUtils = MysqlUtils()\n",
"mysql_params = sysUtils.get_cf_param('mysql-algo')\n",
"mysql_engine, mysql_Session= mysqlUtils.get_mysql_engine(mysql_params)\n",
"mysql_conn = mysql_engine.connect()\n",
"\n",
"kafka_params = sysUtils.get_cf_param('kafka')\n",
"kafkaUtils = KafkaUtils()\n",
"kafka_consumer = kafkaUtils.get_kafka_consumer(kafka_params, 'topic_test_wlm', 'group_id_test_wlm')\n",
"for c in kafka_consumer:\n",
" print(json.loads(c.value))"
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from sqlalchemy import text, delete, and_, or_, update\n",
"with mysql_Session() as session:\n",
" session.execute(delete(Test2).where(and_((Test2.field2== \"测试2\"))))\n",
" session.flush()\n",
" session.add(Test2(field2=\"测试3\"))\n",
" session.flush()\n",
" session.commit()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# mysql_conn.close()\n",
"# mysql_conn = mysql_engine.connect()\n",
"pd.read_sql(\"select * from test2 \", mysql_engine)\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"mysql_engine.pool.status()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"mysql_engine.connect()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# 增加\n",
"from ZlwlAlgosCommon.orm.models import *\n",
"from sqlalchemy import delete, and_, or_, update\n",
"# from sqlalchemy.dialects.mssql import insert\n",
"with mysql_Session() as session:\n",
" session.add(Test2(field1=\"算法\"))\n",
" session.commit()\n",
"# 删除\n",
"# with mysql_Session() as session:\n",
"# session.execute(delete(Test2).where(or_((Test2.id == 10), (Test2.id == 12))))\n",
"# session.commit()\n",
"# 修改\n",
"# with mysql_Session() as session:\n",
"# session.execute(update(Test2).where(and_((Test2.id == 12), (Test2.field1 == 10))).values(id=15))\n",
"# session.commit()\n",
"# # upsert\n",
"# insert_data = insert(Test2).valuess()\n",
"# with mysql_Session() as session:\n",
"# session.execute(update(Test2).where(and_((Test2.id == 12), (Test2.field1 == 10))).values(id=15))\n",
"# session.commit()\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "py3916",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.16"
},
"orig_nbformat": 4,
"vscode": {
"interpreter": {
"hash": "8f4b0a32e8aea4331366ae2a004af1cd179962452cce6e31b3bd8ca311f93913"
}
}
},
"nbformat": 4,
"nbformat_minor": 2
}