iss_data_cratedb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'timestamp': 1620852103,\n",
       " 'iss_position': {'longitude': '-13.6377', 'latitude': '12.5858'},\n",
       " 'message': 'success'}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = requests.get(\"http://api.open-notify.org/iss-now.json\")\n",
    "response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def position():\n",
    "    response = requests.get(\"http://api.open-notify.org/iss-now.json\")\n",
    "    position = response.json()[\"iss_position\"]\n",
    "    return f'POINT ({position[\"longitude\"]} {position[\"latitude\"]})'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'POINT (-12.2397 14.4059)'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "position()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from crate import client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = client.connect(\"localhost:4200\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\n",
    "    \"\"\"CREATE TABLE iss (\n",
    "           timestamp TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP,\n",
    "           position GEO_POINT)\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"INSERT INTO iss (position) VALUES (?)\", [position()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('SELECT * FROM iss ORDER BY timestamp DESC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[1620852238781, [-8.3287, 19.2625]]]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n",
      "CONNECT OK\n",
      "INSERT OK\n",
      "Sleeping for 10 seconds...\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "import requests\n",
    "from crate import client\n",
    "\n",
    "\n",
    "def position():\n",
    "    response = requests.get(\"http://api.open-notify.org/iss-now.json\")\n",
    "    position = response.json()[\"iss_position\"]\n",
    "    return f'POINT ({position[\"longitude\"]} {position[\"latitude\"]})'\n",
    "\n",
    "\n",
    "def insert():\n",
    "    # New connection each time\n",
    "    try:\n",
    "        connection = client.connect(\"localhost:4200\")\n",
    "        print(\"CONNECT OK\")\n",
    "    except Exception as err:\n",
    "        print(\"CONNECT ERROR: %s\" % err)\n",
    "        return\n",
    "    cursor = connection.cursor()\n",
    "    try:\n",
    "        cursor.execute(\n",
    "            \"INSERT INTO iss (position) VALUES (?)\", [position()],\n",
    "        )\n",
    "        print(\"INSERT OK\")\n",
    "    except Exception as err:\n",
    "        print(\"INSERT ERROR: %s\" % err)\n",
    "        return\n",
    "\n",
    "\n",
    "# Loop indefinitely\n",
    "while True:\n",
    "    insert()\n",
    "    print(\"Sleeping for 10 seconds...\")\n",
    "    time.sleep(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}