{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "unsupported format character 'Y' (0x59) at index 35",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-42-def20a744eb0>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     44\u001b[0m \u001b[0msql\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"select sn, DATE_FORMAT(add_time, '%Y-%%m-%%d %%H:%%i:%%s') as time, level, last_time from offline_info where add_time >= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d 00:00:00')\"\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     45\u001b[0m     \u001b[1;34m\" and add_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d 23:59:59')\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 46\u001b[1;33m \u001b[0mdf_offlinealarm_lastday\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdb_qxcas_engine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     47\u001b[0m \u001b[0msn_offline\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_offlinealarm_lastday\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'sn'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtolist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     48\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[1;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[0;32m    519\u001b[0m         )\n\u001b[0;32m    520\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 521\u001b[1;33m         return pandas_sql.read_query(\n\u001b[0m\u001b[0;32m    522\u001b[0m             \u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    523\u001b[0m             \u001b[0mindex_col\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mindex_col\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mread_query\u001b[1;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize)\u001b[0m\n\u001b[0;32m   1306\u001b[0m         \u001b[0margs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1307\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1308\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1309\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1310\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1174\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1175\u001b[0m         \u001b[1;34m\"\"\"Simple passthrough to SQLAlchemy connectable\"\"\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1176\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnectable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecution_options\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1177\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1178\u001b[0m     def read_table(\n",
      "\u001b[1;32m<string>\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, statement, *multiparams, **params)\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\util\\deprecations.py\u001b[0m in \u001b[0;36mwarned\u001b[1;34m(fn, *args, **kwargs)\u001b[0m\n\u001b[0;32m    388\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mskip_warning\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    389\u001b[0m             \u001b[0m_warn_with_version\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mversion\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m3\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 390\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mfn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    391\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    392\u001b[0m     \u001b[0mdoc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[1;32mor\u001b[0m \u001b[1;34m\"\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[0;32m   3035\u001b[0m         \"\"\"\n\u001b[0;32m   3036\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3037\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3038\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3039\u001b[0m     @util.deprecated_20(\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[0;32m   1183\u001b[0m             )\n\u001b[0;32m   1184\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1185\u001b[1;33m             return self._exec_driver_sql(\n\u001b[0m\u001b[0;32m   1186\u001b[0m                 \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1187\u001b[0m                 \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_exec_driver_sql\u001b[1;34m(self, statement, multiparams, params, execution_options, future)\u001b[0m\n\u001b[0;32m   1482\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1483\u001b[0m         \u001b[0mdialect\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1484\u001b[1;33m         ret = self._execute_context(\n\u001b[0m\u001b[0;32m   1485\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1486\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_init_statement\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1746\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1747\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1748\u001b[1;33m             self._handle_dbapi_exception(\n\u001b[0m\u001b[0;32m   1749\u001b[0m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1750\u001b[0m             )\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[0;32m   1931\u001b[0m                 )\n\u001b[0;32m   1932\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1933\u001b[1;33m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1934\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1935\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    209\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    210\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 211\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    212\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    213\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1703\u001b[0m                             \u001b[1;32mbreak\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1704\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1705\u001b[1;33m                     self.dialect.do_execute(\n\u001b[0m\u001b[0;32m   1706\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1707\u001b[0m                     )\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    690\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    691\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 692\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    693\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    694\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    144\u001b[0m             \u001b[1;32mpass\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    145\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 146\u001b[1;33m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    147\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    148\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mmogrify\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    123\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    124\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0margs\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 125\u001b[1;33m             \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_escape_args\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    126\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    127\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mquery\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: unsupported format character 'Y' (0x59) at index 35"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime,time\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from urllib import parse\n",
    "import pymysql\n",
    "\n",
    "host='172.16.121.236'\n",
    "port=3306\n",
    "db='fastfun'\n",
    "user='readonly'\n",
    "password='Fast1234'\n",
    "\n",
    "db_iotp_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, parse.quote_plus(password), host, port, db\n",
    "    ))\n",
    "\n",
    "host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'\n",
    "port=3306\n",
    "db='qx_cas'\n",
    "user='qx_algo_rw'\n",
    "password='qx@123456'\n",
    "\n",
    "db_qxcas_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, parse.quote_plus(password), host, port, db\n",
    "    ))\n",
    "\n",
    "db = 'safety_platform'\n",
    "conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)\n",
    "cursor = conn.cursor()\n",
    "db_sp_engine = create_engine(\n",
    "    \"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(\n",
    "        user, parse.quote_plus(password), host, port, db\n",
    "    ))\n",
    "\n",
    "# sql = \"select sn, DATE_FORMAT(time, '%%Y-%%m-%%d %%H:%%i:%%s') as time, level from lowsoc_info where time >DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%%Y-%%m-%%d 00:00:00')\"+ \\\n",
    "#     \" and  time <DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%%Y-%%m-%%d 23:59:59')\"           \n",
    "# df_lowsocalarm_lastday = pd.read_sql(sql, db_qxcas_engine)\n",
    "# sn_lowsoc = df_lowsocalarm_lastday['sn'].tolist()\n",
    "\n",
    "sql = \"select sn, DATE_FORMAT(add_time, '%Y-%m-%d %H:%i:%s') as time, level, last_time from offline_info where add_time >= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 00:00:00')\"+ \\\n",
    "    \" and add_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 23:59:59')\"           \n",
    "df_offlinealarm_lastday = pd.read_sql(sql, db_qxcas_engine)\n",
    "sn_offline = df_offlinealarm_lastday['sn'].tolist()\n",
    "\n",
    "sql = \"select * from all_fault_info_copy1 where end_time='{}' and code={}\".format('0000-00-00 00:00:00', 59)\n",
    "df_all_alarm_info = pd.read_sql(sql, db_sp_engine)\n",
    "sn_allalarm = df_all_alarm_info['product_id'].tolist()\n",
    "\n",
    "# 遍历昨日报警和报警列表，如果 1：sn存在于昨日报警，但不存在与报警allinfo里，则添加一条新的报警记录到allinfo里；\n",
    "#                           2：如果sn存在于allinfo但不存在于昨日报警，则记录故障为结束；\n",
    "#                           3： 如果sn在两个表中都存在，但级别不同，则更新级别（离线暂时不考虑级别）。\n",
    "sn_new = list(set(sn_offline)-set(sn_allalarm))\n",
    "sn_close = list(set(sn_allalarm)-set(sn_offline))\n",
    "# sn_cross = list(set(sn_offline) & set(sn_allalarm))\n",
    "\n",
    "# 新增\n",
    "df_new = pd.DataFrame(columns=['add_time', 'product_id', 'start_time', 'end_time', 'code'])\n",
    "now = datetime.datetime.now()\n",
    "for sn in sn_new:\n",
    "    df = df_offlinealarm_lastday[df_offlinealarm_lastday['sn']==sn]\n",
    "    df_new = df_new.append({'add_time':now, 'product_id':sn, 'start_time':df['time'].values[0], 'end_time':'0000-00-00 00:00:00', 'code':59}, ignore_index=True)\n",
    "if not df_new.empty:\n",
    "    df_new.to_sql('all_fault_info_copy1', db_sp_engine, if_exists='append', index=False)\n",
    "\n",
    "# 修改\n",
    "now = datetime.datetime.now()\n",
    "for sn in sn_close:\n",
    "    df = df_offlinealarm_lastday[df_offlinealarm_lastday['sn']==sn]\n",
    "    df1 = df_all_alarm_info[df_all_alarm_info['product_id']==sn]\n",
    "    sql = \"select * from ff_battery_status where devcode = '{}'\".format(sn)\n",
    "    df_on = pd.read_sql(sql, db_iotp_engine)\n",
    "    sql = ''' update all_fault_info_copy1 set update_time='{}', end_time='{}' where product_id = '{}' and start_time = '{}' and code = {}'''.format \\\n",
    "                (now, pd.to_datetime(df_on['status_time'].values[0]), sn, pd.to_datetime(df1['start_time'].values[0]), df1['code'].values[0])\n",
    "    cursor.execute(sql)\n",
    "    conn.commit()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime,time\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from urllib import parse\n",
    "import pymysql\n",
    "host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'\n",
    "port=3306\n",
    "db='qixiang_manage'\n",
    "user='qx_query'\n",
    "password='@Qx_query'\n",
    "\n",
    "conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"select qrcode from py_battery where status=3\")\n",
    "res = cursor.fetchall()\n",
    "ignore_sns = pd.DataFrame(res, columns=['sn'])['sn'].tolist()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "303"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alarm_lastday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b3ba2566441a7c06988d0923437866b63cedc61552a5af99d1f4fb67d367b25f"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "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.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}