{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "a925970f-8976-40d5-8691-0413e76a9b8d", "metadata": {}, "outputs": [], "source": [ "from data201 import db_connection\n", "from pandas import DataFrame" ] }, { "cell_type": "code", "execution_count": 2, "id": "7973017f-a31d-4d1f-bb83-3b02f243e25b", "metadata": {}, "outputs": [], "source": [ "conn = db_connection(config_file = 'school.ini')\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "89d36c7c-2570-4d46-a6d7-5c9a4601f7a1", "metadata": {}, "source": [ "#### A Python function that displays in a dataframe the results from a call to a stored procedure." ] }, { "cell_type": "code", "execution_count": 3, "id": "d60de5bf-7c16-4ff9-ac79-8005f7bf9225", "metadata": {}, "outputs": [], "source": [ "def display_results(cursor):\n", " \"\"\"\n", " If there are results in the cursor from a call to a\n", " stored procedure, display the results in a dataframe. \n", " \"\"\"\n", " columns = cursor.description\n", " \n", " if columns == None:\n", " return \n", " \n", " else:\n", " column_names = [column_info[0] \n", " for column_info in columns]\n", "\n", " rows = cursor.fetchall()\n", " \n", " if len(rows) > 0:\n", " df = DataFrame(rows)\n", " df.columns = column_names\n", " \n", " display(df)\n", " \n", " else:\n", " return" ] }, { "cell_type": "markdown", "id": "f51449b5-b4e7-4bbb-a63d-2c39cdc0d774", "metadata": { "tags": [] }, "source": [ "# Call stored procedure `teacher_names()`" ] }, { "cell_type": "code", "execution_count": 4, "id": "78a1a50e-8438-4099-b92a-a94b0e058b48", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CALL teachers_names\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
firstlast
0MabelFlynn
1JohnLane
2PatRogers
3SidneyThompson
\n", "
" ], "text/plain": [ " first last\n", "0 Mabel Flynn\n", "1 John Lane\n", "2 Pat Rogers\n", "3 Sidney Thompson" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sql = 'CALL teachers_names'\n", "print(sql)\n", "print()\n", "\n", "generator = cursor.execute(sql, multi=True)\n", "\n", "for result in generator:\n", " display_results(result)" ] }, { "cell_type": "markdown", "id": "2fc38e5d-22d2-46e2-8af0-8e7064cc4c44", "metadata": { "tags": [] }, "source": [ "# Call stored procedure `students_of()`" ] }, { "cell_type": "code", "execution_count": 5, "id": "769ebe0b-ff5b-4631-bcfb-e556342652d9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('Pat', 'Rogers'),\n", " ('Sidney', 'Thompson'),\n", " ('John', 'Lane'),\n", " ('Mabel', 'Flynn')]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cursor.execute('SELECT first, last FROM teacher')\n", "\n", "teachers = cursor.fetchall()\n", "teachers" ] }, { "cell_type": "code", "execution_count": 6, "id": "1dc58dd8-9a62-4964-9dc4-a0d0244a59d6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "CALL students_of('Pat', 'Rogers')\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idfirstlastsubject
0S1001JohnDoeDatabase Technologies
1S1005TimNovakDatabase Technologies
2S1021KimSmithDatabase Technologies
\n", "
" ], "text/plain": [ " id first last subject\n", "0 S1001 John Doe Database Technologies\n", "1 S1005 Tim Novak Database Technologies\n", "2 S1021 Kim Smith Database Technologies" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "CALL students_of('Sidney', 'Thompson')\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idfirstlastsubject
0S1001JohnDoeMathematical Models
1S1005TimNovakMathematical Models
\n", "
" ], "text/plain": [ " id first last subject\n", "0 S1001 John Doe Mathematical Models\n", "1 S1005 Tim Novak Mathematical Models" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "CALL students_of('John', 'Lane')\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idfirstlastsubject
0S1001JohnDoeData Mining
1S1021KimSmithData Mining
\n", "
" ], "text/plain": [ " id first last subject\n", "0 S1001 John Doe Data Mining\n", "1 S1021 Kim Smith Data Mining" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "CALL students_of('Mabel', 'Flynn')\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idfirstlastsubject
0S1014MaryJaneMachine Learning
1S1005TimNovakMachine Learning
2S1014MaryJanePython Programming
3S1005TimNovakPython Programming
4S1021KimSmithPython Programming
\n", "
" ], "text/plain": [ " id first last subject\n", "0 S1014 Mary Jane Machine Learning\n", "1 S1005 Tim Novak Machine Learning\n", "2 S1014 Mary Jane Python Programming\n", "3 S1005 Tim Novak Python Programming\n", "4 S1021 Kim Smith Python Programming" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "for first, last in teachers:\n", " sql = f\"CALL students_of('{first}', '{last}')\"\n", " print()\n", " print(sql)\n", " print()\n", " \n", " generator = cursor.execute(sql, multi=True)\n", " \n", " for result in generator:\n", " display_results(result)" ] }, { "cell_type": "code", "execution_count": null, "id": "de500553-7851-44b3-b366-66b29e9df44f", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.12.4" } }, "nbformat": 4, "nbformat_minor": 5 }