{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "845dd7c8-cad8-4920-8e80-4ccb45cc7e3d",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Demo sources plugin"
   ]
  },
  {
   "cell_type": "raw",
   "id": "945e7f45-f523-4f54-983c-14faeb220f5e",
   "metadata": {
    "editable": true,
    "raw_mimetype": "text/restructuredtext",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "This example demonstrates the usage of :ref:`sources plugins <sources_plugin>` in earthkit-data.\n",
    "\n",
    "We will use the **earthkit-data-demo-source** plugin, which allows accessing data from a SQL database. It has to be installed to make the exercise work. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "648d19f6-5777-4530-b562-d480c7c2f51f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# !pip install --quiet earthkit-data-demo-source"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cce14277-f60a-4fe7-9afa-771b53b19ad8",
   "metadata": {},
   "source": [
    "First, we create a test database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "029d95f3-ef20-48fc-92a4-907b1ab0b475",
   "metadata": {},
   "outputs": [],
   "source": [
    "import earthkit.data as ekd\n",
    "\n",
    "import os\n",
    "import sqlite3\n",
    "\n",
    "DATA = [\n",
    "    (50, 3.3, \"2001-01-01 00:00:00\", 4.9),\n",
    "    (51, -3, \"2001-01-02 00:00:00\", 7.3),\n",
    "    (50.5, -1.8, \"2001-01-03 00:00:00\", 5.5),\n",
    "]\n",
    "\n",
    "\n",
    "def make_db():\n",
    "    if os.path.exists(\"_test.db\"):\n",
    "        os.unlink(\"_test.db\")\n",
    "\n",
    "    conn = sqlite3.connect(\"_test.db\")\n",
    "    c = conn.cursor()\n",
    "    c.execute(\n",
    "        \"\"\"CREATE TABLE data(\n",
    "                    lat NUMBER,\n",
    "                    lon NUMBER,\n",
    "                    time TEXT,\n",
    "                    value NUMBER)\"\"\"\n",
    "    )\n",
    "    c.executemany(\"INSERT INTO data VALUES(?,?,?,?);\", DATA)\n",
    "    conn.commit()\n",
    "\n",
    "make_db()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "a4654539-dc5e-4d0f-a5c6-d74a9408edb3",
   "metadata": {
    "editable": true,
    "raw_mimetype": "text/restructuredtext",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "The plugin implements a new earthkit-data source called \"demo-source\". We can simply use :func:`from_source` to read our database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3e6b039f-26d9-4430-b3cb-3ddb0023b2eb",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>time</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>2001-01-02</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50.5</td>\n",
       "      <td>-1.8</td>\n",
       "      <td>2001-01-03</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    lat  lon       time  value\n",
       "0  50.0  3.3 2001-01-01    4.9\n",
       "1  51.0 -3.0 2001-01-02    7.3\n",
       "2  50.5 -1.8 2001-01-03    5.5"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds = ekd.from_source(\n",
    "        \"demo-source\",\n",
    "        \"sqlite:///_test.db\",\n",
    "        \"select * from data;\",\n",
    "        parse_dates=[\"time\"],\n",
    "    )\n",
    "df = ds.to_pandas()\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dev_ecc",
   "language": "python",
   "name": "dev_ecc"
  },
  "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}