{
  "cells": [
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 1\nimport sqlite3\nimport random\nimport time\nimport pandas as pd\nimport numpy as np",
      "execution_count": 1,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 2\nconn = sqlite3.connect('../output/example.db')\nc = conn.cursor()",
      "execution_count": 2,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 3\nc.execute('''CREATE TABLE IF NOT EXISTS readings (date text, PM25 real, PM10 real)''')",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 3,
          "data": {
            "text/plain": "<sqlite3.Cursor at 0x7f78f7ba1ab0>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 4\ncounter = 0\nwhile True:\n    readings = {}\n    readings[\"PM25\"] = round(random.uniform(0, 121),1)\n    readings[\"PM10\"] = round(random.uniform(0, 201),1)\n    readings[\"datetime\"] = time.strftime('%Y-%m-%d %H:%M:%S')\n    \n    c.execute(\"INSERT INTO readings VALUES (?,?,?)\", (readings[\"datetime\"], readings[\"PM25\"], readings[\"PM10\"]))\n    \n    conn.commit()\n    \n    counter += 1\n    if counter == 10:\n        break",
      "execution_count": 4,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 5\nfor row in c.execute('SELECT * FROM readings ORDER BY PM25 DESC LIMIT 10'):\n        print(row)",
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": "('2018-12-16 11:18:08', 120.2, 53.4)\n('2018-12-16 11:18:07', 117.2, 14.6)\n('2018-12-16 09:22:54', 116.6, 186.9)\n('2018-12-16 12:25:49', 109.8, 189.4)\n('2018-12-16 09:22:54', 108.8, 77.6)\n('2018-12-16 11:18:07', 100.5, 160.5)\n('2018-12-16 12:25:50', 99.5, 10.6)\n('2018-12-16 09:24:15', 92.9, 27.4)\n('2018-12-16 09:22:53', 87.1, 180.8)\n('2018-12-16 11:18:08', 85.6, 40.4)\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 6\ndf = pd.read_sql_query(\"select * from readings\", conn)",
      "execution_count": 6,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 7\ndf[\"additional\"] = np.where(df[\"PM10\"] > 100, \"Alert!\", None)\ndf.to_sql(\"readings_modified\", conn, if_exists=\"replace\", index=False)\nfor row in c.execute('SELECT * FROM readings_modified ORDER BY PM25 DESC LIMIT 10'):\n        print(row)",
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "text": "('2018-12-16 11:18:08', 120.2, 53.4, None)\n('2018-12-16 11:18:07', 117.2, 14.6, None)\n('2018-12-16 09:22:54', 116.6, 186.9, 'Alert!')\n('2018-12-16 12:25:49', 109.8, 189.4, 'Alert!')\n('2018-12-16 09:22:54', 108.8, 77.6, None)\n('2018-12-16 11:18:07', 100.5, 160.5, 'Alert!')\n('2018-12-16 12:25:50', 99.5, 10.6, None)\n('2018-12-16 09:24:15', 92.9, 27.4, None)\n('2018-12-16 09:22:53', 87.1, 180.8, 'Alert!')\n('2018-12-16 11:18:08', 85.6, 40.4, None)\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Kod 8\nconn.close()",
      "execution_count": 8,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "conda-env-jakbadacdane.pl-py",
      "display_name": "Python [conda env:jakbadacdane.pl]",
      "language": "python"
    },
    "language_info": {
      "name": "python",
      "version": "3.6.7",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "gist": {
      "id": "",
      "data": {
        "description": " SQLite i Python – czy warto?",
        "public": true
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}