25_1_1_SQL_with_a_relational_database.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "25-1.1 SQL with a relational database",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyPw+B8NBXxzSK2I5f8n4+sC",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Gressling/examples/blob/master/25_1_1_SQL_with_a_relational_database.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TLGCzrNPNLJ4"
      },
      "source": [
        "# SQL with a relational database\n",
        "# author: Gressling, T\n",
        "# license: MIT License # code: github.com/gressling/examples\n",
        "# activity: single example # index: 25-1 \n",
        "# https://api.elephantsql.com/console/f40ff351-afd8-4a64-893f-2af520215bd7/details"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "S9cL8m1nPQ9w",
        "outputId": "1da2a25a-bc07-4ce2-929c-a8978bd24426",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "!pip install psycopg2"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: psycopg2 in /usr/local/lib/python3.6/dist-packages (2.7.6.1)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gncL6qDGPZ42",
        "outputId": "07d40c94-7af0-4c28-ae5e-96bb165f0b7e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "import psycopg2"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
            "  \"\"\")\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ooTiqrigNQG7"
      },
      "source": [
        "conn = psycopg2.connect(\"dbname='fgyzziqp' user='fgyzziqp' host='dumbo.db.elephantsql.com' password='CMzjYfJxxxxxx'\n"
      ],
      "execution_count": 18,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WPSB5qA8Pecv"
      },
      "source": [
        "cur = conn.cursor()"
      ],
      "execution_count": 19,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CX-6yq6EQZ23"
      },
      "source": [
        "# https://www.postgresqltutorial.com\n",
        "# CREATE TABLE results (experimentID integer, yield float);\n",
        "# INSERT INTO results VALUES (155, '25.6'), (156, '81.7'), (158, '92');\n",
        "# commit;\n",
        "# https://www.psycopg.org/docs/usage.html\n",
        "conn.commit()"
      ],
      "execution_count": 37,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FW8M6tasTNKN"
      },
      "source": [
        "cur.execute(\"CREATE TABLE results (id serial PRIMARY KEY, experimentID integer, yield float);\")\n",
        "conn.commit()"
      ],
      "execution_count": 28,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O1IrwylJTUFT"
      },
      "source": [
        "cur.execute(\"INSERT INTO results (experimentID, yield) VALUES (170, 25.6), (171, 81.2), (172, 67.2);\")\n",
        "conn.commit()"
      ],
      "execution_count": 36,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IyUJDE2tNTrb"
      },
      "source": [
        "cur.execute(\"SELECT * from results where yield>80\")\n",
        "rows = cur.fetchall()"
      ],
      "execution_count": 42,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qiwHaynnNVyz",
        "outputId": "b0f32e17-6d34-4754-85d1-a254da12ba14",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "for row in rows:\n",
        " print(\" \", row[1])"
      ],
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "  171\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}