{ "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 }