25-1.1 SQL with a relational database

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"25-1.1 SQL with a relational database","provenance":[],"collapsed_sections":[],"authorship_tag":"ABX9TyPw+B8NBXxzSK2I5f8n4+sC"},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"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","executionInfo":{"status":"ok","timestamp":1605365491596,"user_tz":-60,"elapsed":4376,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}},"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","executionInfo":{"status":"ok","timestamp":1605365508101,"user_tz":-60,"elapsed":1027,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}},"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","executionInfo":{"status":"ok","timestamp":1605366458696,"user_tz":-60,"elapsed":1226,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}}},"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","executionInfo":{"status":"ok","timestamp":1605366460895,"user_tz":-60,"elapsed":632,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}}},"source":["cur = conn.cursor()"],"execution_count":19,"outputs":[]},{"cell_type":"code","metadata":{"id":"CX-6yq6EQZ23","executionInfo":{"status":"ok","timestamp":1605366921437,"user_tz":-60,"elapsed":965,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}}},"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","executionInfo":{"status":"ok","timestamp":1605366779817,"user_tz":-60,"elapsed":597,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}}},"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","executionInfo":{"status":"ok","timestamp":1605366884740,"user_tz":-60,"elapsed":573,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}}},"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","executionInfo":{"status":"ok","timestamp":1605366973355,"user_tz":-60,"elapsed":696,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}}},"source":["cur.execute(\"SELECT * from results where yield>80\")\n","rows = cur.fetchall()"],"execution_count":42,"outputs":[]},{"cell_type":"code","metadata":{"id":"qiwHaynnNVyz","executionInfo":{"status":"ok","timestamp":1605366981918,"user_tz":-60,"elapsed":602,"user":{"displayName":"Dr. Thorsten Gressling","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gjsy54zs1Pd2tw4fMBrz3a7FsYlzNHgwHWDqLywqA=s64","userId":"08619859847936335393"}},"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"}]}]}