explore.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read from sql database\n",
    "path=\"/home/jan/code_projects/core/config/home-assistant_v2.db\"\n",
    "import pandas\n",
    "import sqlite3\n",
    "\n",
    "# Read sqlite query results into a pandas DataFrame\n",
    "con = sqlite3.connect(path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>tbl_name</th>\n",
       "      <th>rootpage</th>\n",
       "      <th>sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>table</td>\n",
       "      <td>event_data</td>\n",
       "      <td>event_data</td>\n",
       "      <td>2</td>\n",
       "      <td>CREATE TABLE event_data (\\n\\tdata_id INTEGER N...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_event_data_hash</td>\n",
       "      <td>event_data</td>\n",
       "      <td>3</td>\n",
       "      <td>CREATE INDEX ix_event_data_hash ON event_data ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>table</td>\n",
       "      <td>state_attributes</td>\n",
       "      <td>state_attributes</td>\n",
       "      <td>4</td>\n",
       "      <td>CREATE TABLE state_attributes (\\n\\tattributes_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_state_attributes_hash</td>\n",
       "      <td>state_attributes</td>\n",
       "      <td>5</td>\n",
       "      <td>CREATE INDEX ix_state_attributes_hash ON state...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>table</td>\n",
       "      <td>statistics_meta</td>\n",
       "      <td>statistics_meta</td>\n",
       "      <td>6</td>\n",
       "      <td>CREATE TABLE statistics_meta (\\n\\tid INTEGER N...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_meta_statistic_id</td>\n",
       "      <td>statistics_meta</td>\n",
       "      <td>7</td>\n",
       "      <td>CREATE UNIQUE INDEX ix_statistics_meta_statist...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>table</td>\n",
       "      <td>recorder_runs</td>\n",
       "      <td>recorder_runs</td>\n",
       "      <td>8</td>\n",
       "      <td>CREATE TABLE recorder_runs (\\n\\trun_id INTEGER...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_recorder_runs_start_end</td>\n",
       "      <td>recorder_runs</td>\n",
       "      <td>9</td>\n",
       "      <td>CREATE INDEX ix_recorder_runs_start_end ON rec...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>table</td>\n",
       "      <td>schema_changes</td>\n",
       "      <td>schema_changes</td>\n",
       "      <td>10</td>\n",
       "      <td>CREATE TABLE schema_changes (\\n\\tchange_id INT...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>table</td>\n",
       "      <td>statistics_runs</td>\n",
       "      <td>statistics_runs</td>\n",
       "      <td>11</td>\n",
       "      <td>CREATE TABLE statistics_runs (\\n\\trun_id INTEG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_runs_start</td>\n",
       "      <td>statistics_runs</td>\n",
       "      <td>12</td>\n",
       "      <td>CREATE INDEX ix_statistics_runs_start ON stati...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>table</td>\n",
       "      <td>events</td>\n",
       "      <td>events</td>\n",
       "      <td>13</td>\n",
       "      <td>CREATE TABLE events (\\n\\tevent_id INTEGER NOT ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_events_context_id</td>\n",
       "      <td>events</td>\n",
       "      <td>14</td>\n",
       "      <td>CREATE INDEX ix_events_context_id ON events (c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_events_event_type_time_fired</td>\n",
       "      <td>events</td>\n",
       "      <td>15</td>\n",
       "      <td>CREATE INDEX ix_events_event_type_time_fired O...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_events_data_id</td>\n",
       "      <td>events</td>\n",
       "      <td>16</td>\n",
       "      <td>CREATE INDEX ix_events_data_id ON events (data...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_events_time_fired</td>\n",
       "      <td>events</td>\n",
       "      <td>17</td>\n",
       "      <td>CREATE INDEX ix_events_time_fired ON events (t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>table</td>\n",
       "      <td>statistics</td>\n",
       "      <td>statistics</td>\n",
       "      <td>18</td>\n",
       "      <td>CREATE TABLE statistics (\\n\\tid INTEGER NOT NU...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_metadata_id</td>\n",
       "      <td>statistics</td>\n",
       "      <td>19</td>\n",
       "      <td>CREATE INDEX ix_statistics_metadata_id ON stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_start</td>\n",
       "      <td>statistics</td>\n",
       "      <td>20</td>\n",
       "      <td>CREATE INDEX ix_statistics_start ON statistics...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_statistic_id_start</td>\n",
       "      <td>statistics</td>\n",
       "      <td>21</td>\n",
       "      <td>CREATE UNIQUE INDEX ix_statistics_statistic_id...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>table</td>\n",
       "      <td>statistics_short_term</td>\n",
       "      <td>statistics_short_term</td>\n",
       "      <td>22</td>\n",
       "      <td>CREATE TABLE statistics_short_term (\\n\\tid INT...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_short_term_statistic_id_start</td>\n",
       "      <td>statistics_short_term</td>\n",
       "      <td>23</td>\n",
       "      <td>CREATE UNIQUE INDEX ix_statistics_short_term_s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_short_term_metadata_id</td>\n",
       "      <td>statistics_short_term</td>\n",
       "      <td>24</td>\n",
       "      <td>CREATE INDEX ix_statistics_short_term_metadata...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_statistics_short_term_start</td>\n",
       "      <td>statistics_short_term</td>\n",
       "      <td>25</td>\n",
       "      <td>CREATE INDEX ix_statistics_short_term_start ON...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>table</td>\n",
       "      <td>states</td>\n",
       "      <td>states</td>\n",
       "      <td>28</td>\n",
       "      <td>CREATE TABLE states (\\n\\tstate_id INTEGER NOT ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_states_old_state_id</td>\n",
       "      <td>states</td>\n",
       "      <td>29</td>\n",
       "      <td>CREATE INDEX ix_states_old_state_id ON states ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_states_attributes_id</td>\n",
       "      <td>states</td>\n",
       "      <td>30</td>\n",
       "      <td>CREATE INDEX ix_states_attributes_id ON states...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_states_event_id</td>\n",
       "      <td>states</td>\n",
       "      <td>31</td>\n",
       "      <td>CREATE INDEX ix_states_event_id ON states (eve...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_states_last_updated</td>\n",
       "      <td>states</td>\n",
       "      <td>32</td>\n",
       "      <td>CREATE INDEX ix_states_last_updated ON states ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_states_entity_id_last_updated</td>\n",
       "      <td>states</td>\n",
       "      <td>33</td>\n",
       "      <td>CREATE INDEX ix_states_entity_id_last_updated ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_states_context_id</td>\n",
       "      <td>states</td>\n",
       "      <td>34</td>\n",
       "      <td>CREATE INDEX ix_states_context_id ON states (c...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     type                                         name               tbl_name  \\\n",
       "0   table                                   event_data             event_data   \n",
       "1   index                           ix_event_data_hash             event_data   \n",
       "2   table                             state_attributes       state_attributes   \n",
       "3   index                     ix_state_attributes_hash       state_attributes   \n",
       "4   table                              statistics_meta        statistics_meta   \n",
       "5   index              ix_statistics_meta_statistic_id        statistics_meta   \n",
       "6   table                                recorder_runs          recorder_runs   \n",
       "7   index                   ix_recorder_runs_start_end          recorder_runs   \n",
       "8   table                               schema_changes         schema_changes   \n",
       "9   table                              statistics_runs        statistics_runs   \n",
       "10  index                     ix_statistics_runs_start        statistics_runs   \n",
       "11  table                                       events                 events   \n",
       "12  index                         ix_events_context_id                 events   \n",
       "13  index              ix_events_event_type_time_fired                 events   \n",
       "14  index                            ix_events_data_id                 events   \n",
       "15  index                         ix_events_time_fired                 events   \n",
       "16  table                                   statistics             statistics   \n",
       "17  index                    ix_statistics_metadata_id             statistics   \n",
       "18  index                          ix_statistics_start             statistics   \n",
       "19  index             ix_statistics_statistic_id_start             statistics   \n",
       "20  table                        statistics_short_term  statistics_short_term   \n",
       "21  index  ix_statistics_short_term_statistic_id_start  statistics_short_term   \n",
       "22  index         ix_statistics_short_term_metadata_id  statistics_short_term   \n",
       "23  index               ix_statistics_short_term_start  statistics_short_term   \n",
       "24  table                                       states                 states   \n",
       "25  index                       ix_states_old_state_id                 states   \n",
       "26  index                      ix_states_attributes_id                 states   \n",
       "27  index                           ix_states_event_id                 states   \n",
       "28  index                       ix_states_last_updated                 states   \n",
       "29  index             ix_states_entity_id_last_updated                 states   \n",
       "30  index                         ix_states_context_id                 states   \n",
       "\n",
       "    rootpage                                                sql  \n",
       "0          2  CREATE TABLE event_data (\\n\\tdata_id INTEGER N...  \n",
       "1          3  CREATE INDEX ix_event_data_hash ON event_data ...  \n",
       "2          4  CREATE TABLE state_attributes (\\n\\tattributes_...  \n",
       "3          5  CREATE INDEX ix_state_attributes_hash ON state...  \n",
       "4          6  CREATE TABLE statistics_meta (\\n\\tid INTEGER N...  \n",
       "5          7  CREATE UNIQUE INDEX ix_statistics_meta_statist...  \n",
       "6          8  CREATE TABLE recorder_runs (\\n\\trun_id INTEGER...  \n",
       "7          9  CREATE INDEX ix_recorder_runs_start_end ON rec...  \n",
       "8         10  CREATE TABLE schema_changes (\\n\\tchange_id INT...  \n",
       "9         11  CREATE TABLE statistics_runs (\\n\\trun_id INTEG...  \n",
       "10        12  CREATE INDEX ix_statistics_runs_start ON stati...  \n",
       "11        13  CREATE TABLE events (\\n\\tevent_id INTEGER NOT ...  \n",
       "12        14  CREATE INDEX ix_events_context_id ON events (c...  \n",
       "13        15  CREATE INDEX ix_events_event_type_time_fired O...  \n",
       "14        16  CREATE INDEX ix_events_data_id ON events (data...  \n",
       "15        17  CREATE INDEX ix_events_time_fired ON events (t...  \n",
       "16        18  CREATE TABLE statistics (\\n\\tid INTEGER NOT NU...  \n",
       "17        19  CREATE INDEX ix_statistics_metadata_id ON stat...  \n",
       "18        20  CREATE INDEX ix_statistics_start ON statistics...  \n",
       "19        21  CREATE UNIQUE INDEX ix_statistics_statistic_id...  \n",
       "20        22  CREATE TABLE statistics_short_term (\\n\\tid INT...  \n",
       "21        23  CREATE UNIQUE INDEX ix_statistics_short_term_s...  \n",
       "22        24  CREATE INDEX ix_statistics_short_term_metadata...  \n",
       "23        25  CREATE INDEX ix_statistics_short_term_start ON...  \n",
       "24        28  CREATE TABLE states (\\n\\tstate_id INTEGER NOT ...  \n",
       "25        29  CREATE INDEX ix_states_old_state_id ON states ...  \n",
       "26        30  CREATE INDEX ix_states_attributes_id ON states...  \n",
       "27        31  CREATE INDEX ix_states_event_id ON states (eve...  \n",
       "28        32  CREATE INDEX ix_states_last_updated ON states ...  \n",
       "29        33  CREATE INDEX ix_states_entity_id_last_updated ...  \n",
       "30        34  CREATE INDEX ix_states_context_id ON states (c...  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pandas.read_sql_query(\"select * FROM sqlite_master;\", con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    count(*)\n",
       "0         24\n",
       "1         24\n",
       "2         24\n",
       "3         24\n",
       "4         24\n",
       "5         24\n",
       "6         24\n",
       "7          1\n",
       "8          1\n",
       "9          1\n",
       "10         1\n",
       "11         1\n",
       "12         1\n",
       "13         1"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pandas.read_sql_query(\"select count(*) FROM statistics group by metadata_id;\", con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>created</th>\n",
       "      <th>start</th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>last_reset</th>\n",
       "      <th>state</th>\n",
       "      <th>sum</th>\n",
       "      <th>metadata_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-09-10 20:53:45.950718</td>\n",
       "      <td>2022-09-08 22:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-09-10 20:53:45.951331</td>\n",
       "      <td>2022-09-08 23:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2022-09-10 20:53:45.951775</td>\n",
       "      <td>2022-09-09 00:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-09-10 20:53:45.952244</td>\n",
       "      <td>2022-09-09 01:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2022-09-10 20:53:45.952750</td>\n",
       "      <td>2022-09-09 02:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>171</td>\n",
       "      <td>2022-09-10 21:00:10.023726</td>\n",
       "      <td>2022-09-10 20:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>172</td>\n",
       "      <td>2022-09-10 21:00:10.023762</td>\n",
       "      <td>2022-09-10 20:00:00.000000</td>\n",
       "      <td>1.059498</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>173</td>\n",
       "      <td>2022-09-10 21:00:10.023794</td>\n",
       "      <td>2022-09-10 20:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>174</td>\n",
       "      <td>2022-09-10 21:00:10.023826</td>\n",
       "      <td>2022-09-10 20:00:00.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>175</td>\n",
       "      <td>2022-09-10 21:00:10.023857</td>\n",
       "      <td>2022-09-10 20:00:00.000000</td>\n",
       "      <td>156.881003</td>\n",
       "      <td>155.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>175 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                     created                       start        mean  \\\n",
       "0      1  2022-09-10 20:53:45.950718  2022-09-08 22:00:00.000000    0.000000   \n",
       "1      2  2022-09-10 20:53:45.951331  2022-09-08 23:00:00.000000    0.000000   \n",
       "2      3  2022-09-10 20:53:45.951775  2022-09-09 00:00:00.000000    0.000000   \n",
       "3      4  2022-09-10 20:53:45.952244  2022-09-09 01:00:00.000000    0.000000   \n",
       "4      5  2022-09-10 20:53:45.952750  2022-09-09 02:00:00.000000    0.000000   \n",
       "..   ...                         ...                         ...         ...   \n",
       "170  171  2022-09-10 21:00:10.023726  2022-09-10 20:00:00.000000    0.000000   \n",
       "171  172  2022-09-10 21:00:10.023762  2022-09-10 20:00:00.000000    1.059498   \n",
       "172  173  2022-09-10 21:00:10.023794  2022-09-10 20:00:00.000000    0.000000   \n",
       "173  174  2022-09-10 21:00:10.023826  2022-09-10 20:00:00.000000    0.000000   \n",
       "174  175  2022-09-10 21:00:10.023857  2022-09-10 20:00:00.000000  156.881003   \n",
       "\n",
       "       min    max last_reset state   sum  metadata_id  \n",
       "0      0.0    0.0       None  None  None            1  \n",
       "1      0.0    0.0       None  None  None            1  \n",
       "2      0.0    0.0       None  None  None            1  \n",
       "3      0.0    0.0       None  None  None            1  \n",
       "4      0.0    0.0       None  None  None            1  \n",
       "..     ...    ...        ...   ...   ...          ...  \n",
       "170    0.0    0.0       None  None  None           10  \n",
       "171    0.0    2.0       None  None  None           11  \n",
       "172    0.0    0.0       None  None  None           12  \n",
       "173    0.0    0.0       None  None  None           13  \n",
       "174  155.0  159.0       None  None  None           14  \n",
       "\n",
       "[175 rows x 10 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pandas.read_sql_query(\"select * FROM statistics;\", con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>statistic_id</th>\n",
       "      <th>source</th>\n",
       "      <th>unit_of_measurement</th>\n",
       "      <th>has_mean</th>\n",
       "      <th>has_sum</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>huawei_fusionsolar:selfusepower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>huawei_fusionsolar:dischargepower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>huawei_fusionsolar:chargepower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>huawei_fusionsolar:ongridpower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>huawei_fusionsolar:disgridpower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>huawei_fusionsolar:productpower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>huawei_fusionsolar:usepower</td>\n",
       "      <td>huawei_fusionsolar</td>\n",
       "      <td>kW</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>sensor.selfusepower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>sensor.dischargepower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>sensor.chargepower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>sensor.ongridpower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>sensor.disgridpower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>sensor.productpower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>sensor.usepower</td>\n",
       "      <td>recorder</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                       statistic_id              source  \\\n",
       "0    1    huawei_fusionsolar:selfusepower  huawei_fusionsolar   \n",
       "1    2  huawei_fusionsolar:dischargepower  huawei_fusionsolar   \n",
       "2    3     huawei_fusionsolar:chargepower  huawei_fusionsolar   \n",
       "3    4     huawei_fusionsolar:ongridpower  huawei_fusionsolar   \n",
       "4    5    huawei_fusionsolar:disgridpower  huawei_fusionsolar   \n",
       "5    6    huawei_fusionsolar:productpower  huawei_fusionsolar   \n",
       "6    7        huawei_fusionsolar:usepower  huawei_fusionsolar   \n",
       "7    8                sensor.selfusepower            recorder   \n",
       "8    9              sensor.dischargepower            recorder   \n",
       "9   10                 sensor.chargepower            recorder   \n",
       "10  11                 sensor.ongridpower            recorder   \n",
       "11  12                sensor.disgridpower            recorder   \n",
       "12  13                sensor.productpower            recorder   \n",
       "13  14                    sensor.usepower            recorder   \n",
       "\n",
       "   unit_of_measurement  has_mean  has_sum  name  \n",
       "0                   kW         1        0  None  \n",
       "1                   kW         1        0  None  \n",
       "2                   kW         1        0  None  \n",
       "3                   kW         1        0  None  \n",
       "4                   kW         1        0  None  \n",
       "5                   kW         1        0  None  \n",
       "6                   kW         1        0  None  \n",
       "7                    W         1        0  None  \n",
       "8                    W         1        0  None  \n",
       "9                    W         1        0  None  \n",
       "10                   W         1        0  None  \n",
       "11                   W         1        0  None  \n",
       "12                   W         1        0  None  \n",
       "13                   W         1        0  None  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pandas.read_sql_query(\"select * FROM statistics_meta;\", con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.4 ('test')",
   "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.10.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "34dd195c82d98ea1fd0f576f69cba24ac4319de302e7f18a25a9aeac4bb35a8a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}