{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 溯源系统的表单合并到vin-pack关系表中\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "from urllib import parse\n",
    "\n",
    "# 数据库配置\n",
    "host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'\n",
    "port = 3306\n",
    "user = 'hz_dev'\n",
    "password = parse.quote_plus('Qx@123456')\n",
    "database = 'hz_dev'\n",
    "\n",
    "db_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, password, host, port, database\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\", 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_res = pd.DataFrame(columns=['id', 'vin','sn','pack_model_code', 'pack_capacity', 'pack_series_parallerl', 'module_model_code','module_manufactory', 'cell_model_code',\n",
    "                               'cell_dict_shape','cell_battery_type','cell_manufactory'])\n",
    "for index in df_entity.index:\n",
    "    df_t = df_entity.loc[index]\n",
    "    vin = df_t['vin']\n",
    "    sn = df_t['code']\n",
    "    pack_model_code = df_t['model_id']\n",
    "    df_pack_model_t = df_pack_model[df_pack_model['model_code']==pack_model_code]\n",
    "    pack_capacity =  df_pack_model_t['capacity'].values[0]\n",
    "    pack_series_parallerl =  df_pack_model_t['series_parallerl'].values[0]\n",
    "    \n",
    "    module_model_code = df_model_lk[(df_model_lk['model_type']=='M')&(df_model_lk['code']==pack_model_code)]['child_code'].values[0]\n",
    "    df_module_model_t = df_module_model[df_module_model['model_code']==module_model_code]\n",
    "    module_manufactory = df_module_model_t['manufactory_name'].values[0]\n",
    "    \n",
    "    cell_model_code = df_module_model_t['cell_model'].values[0]\n",
    "    \n",
    "    df_cell_t = df_cell[df_cell['model_code']==cell_model_code]\n",
    "\n",
    "    cell_dict_shape = df_cell_t['dict_shape'].values[0]\n",
    "    cell_battery_type = df_cell_t['battery_type'].values[0]\n",
    "    cell_manufactory = df_cell_t['manufactory_name'].values[0]\n",
    "    \n",
    "    df_res = df_res.append({\n",
    "        'vin':vin,'sn':sn,'pack_model_code':pack_model_code, 'pack_capacity':pack_capacity, 'pack_series_parallerl':pack_series_parallerl, \n",
    "        'module_model_code':module_model_code,'module_manufactory':module_manufactory, 'cell_model_code':cell_model_code,\n",
    "                               'cell_dict_shape':cell_dict_shape,'cell_battery_type':cell_battery_type,'cell_manufactory':cell_manufactory\n",
    "    },ignore_index=True)\n",
    "df_res.to_csv('result.csv')\n",
    "\n",
    "    \n",
    "\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}