{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将文件中的ocv数据更新到数据库中\n",
    "import pandas as pd\n",
    "import os\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "from sqlalchemy.pool import NullPool\n",
    "from urllib import parse\n",
    "import json\n",
    "\n",
    "host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'\n",
    "port = '3306'\n",
    "db = 'hz_dev'\n",
    "user = 'hz_dev'\n",
    "password = parse.quote_plus('Qx@123456')\n",
    "\n",
    "db_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, password, host, port, db\n",
    "    ), poolclass=NullPool)\n",
    "\n",
    "\n",
    "\n",
    "df_cell = pd.read_sql(\"select * from sys_battery_cell_model\", db_engine)\n",
    "df_model_lk = pd.read_sql(\"select * from sys_battery_model_lk where model_type='M'\", db_engine)\n",
    "df_module_model = pd.read_sql(\"select * from sys_battery_module_model\", db_engine)\n",
    "# df_entity = pd.read_sql(\"select * from sys_battery_pack_entity_storage\", db_engine)\n",
    "df_pack_model = pd.read_sql(\"select * from sys_battery_pack_model\", db_engine)\n",
    "df_algo_pack_param = pd.read_sql(\"select * from algo_pack_param\", db_engine)\n",
    "\n",
    "# 读取ocv曲线\n",
    "files = os.listdir('./OCV-SOC曲线')\n",
    "for f in files:\n",
    "    # 根据cellcode 查找packcode\n",
    "    module_codes =  list(df_module_model[df_module_model['cell_model']==f[0:-5].replace('_','/')]['model_code'].values)\n",
    "    for module_code in module_codes:\n",
    "        pack_codes = df_model_lk[df_model_lk['child_code']==module_code]['code']\n",
    "        for pack_code in pack_codes:\n",
    "            # 读取ocv曲线， 并更新到pack参数中\n",
    "            df = pd.read_excel('./OCV-SOC曲线/'+f)\n",
    "            param = df_algo_pack_param[df_algo_pack_param['pack_code']==pack_code]['param']\n",
    "            if len(param) == 0:\n",
    "                param = {'charge_ocv_v':list(df['充电OCV']),'charge_ocv_soc':list(df['充电SOC']),'discharge_ocv_v':list(df['放电OCV']),'discharge_ocv_soc':list(df['放电SOC'])}\n",
    "            else:\n",
    "                param = json.loads(param.values[0])\n",
    "                param.update({'charge_ocv_v':list(df['充电OCV']),'charge_ocv_soc':list(df['充电SOC']),'discharge_ocv_v':list(df['放电OCV']),'discharge_ocv_soc':list(df['放电SOC'])})\n",
    "            param = json.dumps(param)\n",
    "            Session = sessionmaker(bind=db_engine)\n",
    "            session = Session()\n",
    "            cursor = session.execute('insert into algo_pack_param (pack_code, param) values (:pack_code, :param) on duplicate key update param = :param',\n",
    "                                        params={\"pack_code\":pack_code,\"param\": param})\n",
    "            session.commit()\n",
    "            session.close()\n",
    "db_engine.dispose()\n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 更新pack的容量到参数中\n",
    "import pandas as pd\n",
    "import os\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.pool import NullPool\n",
    "from urllib import parse\n",
    "import json\n",
    "\n",
    "host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'\n",
    "port = '3306'\n",
    "db = 'hz_dev'\n",
    "user = 'hz_dev'\n",
    "password = parse.quote_plus('Qx@123456')\n",
    "\n",
    "db_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, password, host, port, db\n",
    "    ), poolclass=NullPool)\n",
    "\n",
    "Session = sessionmaker(bind=db_engine)\n",
    "session = Session()\n",
    "\n",
    "df_cell = pd.read_sql(\"select * from sys_battery_cell_model\", db_engine)\n",
    "df_model_lk = pd.read_sql(\"select * from sys_battery_model_lk where model_type='M'\", db_engine)\n",
    "df_module_model = pd.read_sql(\"select * from sys_battery_module_model\", db_engine)\n",
    "# df_entity = pd.read_sql(\"select * from sys_battery_pack_entity_storage\", db_engine)\n",
    "df_pack_model = pd.read_sql(\"select * from sys_battery_pack_model\", db_engine)\n",
    "df_algo_pack_param = pd.read_sql(\"select * from algo_pack_param\", db_engine)\n",
    "\n",
    "# 读取ocv曲线\n",
    "files = os.listdir('./OCV-SOC曲线')\n",
    "for f in files:\n",
    "    module_codes =  list(df_module_model[df_module_model['cell_model']==f[0:-5].replace('_','/')]['model_code'].values)\n",
    "    for module_code in module_codes:\n",
    "        pack_codes = df_model_lk[df_model_lk['child_code']==module_code]['code']\n",
    "        for pack_code in pack_codes:\n",
    "            # 根据cellcode 查找packcode及容量\n",
    "            capacity = df_pack_model[df_pack_model['model_code']==pack_code]['capacity'].values[0]\n",
    "            # 更新参数json\n",
    "            param = df_algo_pack_param[df_algo_pack_param['pack_code']==pack_code]['param']\n",
    "            param = json.loads(param.values[0])\n",
    "            param.update({'capacity':str(capacity)})\n",
    "            param = json.dumps(param)\n",
    "            Session = sessionmaker(bind=db_engine)\n",
    "            session = Session()\n",
    "            cursor = session.execute('insert into algo_pack_param (pack_code, param) values (:pack_code, :param) on duplicate key update param = :param',\n",
    "                                        params={\"pack_code\":pack_code,\"param\": param})\n",
    "            session.commit()\n",
    "db_engine.dispose()\n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ac7555030399793291671316948dfc1cf0035da70681b2458d9e2645bf33bfe4"
  },
  "kernelspec": {
   "display_name": "Python 3.8.12 ('algo_dev_env')",
   "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.8.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}