{ "cells": [ { "cell_type": "markdown", "id": "9e2a50c2-3aaa-462e-a261-98932a4230d6", "metadata": {}, "source": [ "# Test stored procedure `pivot()`." ] }, { "cell_type": "code", "execution_count": 21, "id": "b233ac84-d04a-4417-a292-9934ec483449", "metadata": {}, "outputs": [], "source": [ "from data201 import db_connection, df_query" ] }, { "cell_type": "code", "execution_count": 22, "id": "272a8425-c3de-4789-95c0-0aa7309f6e25", "metadata": {}, "outputs": [], "source": [ "conn = db_connection(config_file = 'pivot_test.ini')\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "8bbbe797-855f-418c-98d4-a2827e9ab103", "metadata": {}, "source": [ "### A sample database to test `pivot`" ] }, { "cell_type": "code", "execution_count": 23, "id": "5f7cd0d7-aa06-4570-b6ce-a2da439aa5aa", "metadata": {}, "outputs": [], "source": [ "cursor.execute('DROP TABLE IF EXISTS fruits')\n", "\n", "cursor.execute(\n", " \"\"\"\n", " CREATE TABLE fruits\n", " (\n", " store_id VARCHAR(8) NOT NULL,\n", " apples INT,\n", " oranges INT,\n", " bananas INT,\n", " peaches INT,\n", " pears INT,\n", " PRIMARY KEY (store_id)\n", " )\n", " \"\"\"\n", ")" ] }, { "cell_type": "code", "execution_count": 24, "id": "03518d72-8768-4425-ab4a-0a60f37cfb3c", "metadata": {}, "outputs": [], "source": [ "insert_sql = (\n", " \"\"\"\n", " INSERT INTO fruits\n", " VALUES (%(store_id)s, %(apples)s, %(oranges)s, \n", " %(bananas)s, %(peaches)s, %(pears)s)\n", " \"\"\"\n", ")" ] }, { "cell_type": "code", "execution_count": 25, "id": "9ade3448-a69b-4fbb-9736-ad3238b818bc", "metadata": {}, "outputs": [], "source": [ "fruit_data = [\n", " {\n", " 'store_id': 'store_1',\n", " 'apples': 101,\n", " 'oranges': 102,\n", " 'bananas': 103,\n", " 'peaches': 104,\n", " 'pears': 105\n", " },\n", " {\n", " 'store_id': 'store_2',\n", " 'apples': 201,\n", " 'oranges': 202,\n", " 'bananas': 203,\n", " 'peaches': 204,\n", " 'pears': 205\n", " },\n", " {\n", " 'store_id': 'store_3',\n", " 'apples': 301,\n", " 'oranges': 302,\n", " 'bananas': 303,\n", " 'peaches': 304,\n", " 'pears': 305\n", " }\n", "]" ] }, { "cell_type": "code", "execution_count": 26, "id": "1a25b0f5-4459-4b85-a712-1ea3cd05726e", "metadata": {}, "outputs": [], "source": [ "cursor.executemany(insert_sql, fruit_data) # NOTE: executemany()\n", "conn.commit()" ] }, { "cell_type": "markdown", "id": "4fe05c7a-2a7b-4e4a-a39d-6765cafaa34e", "metadata": {}, "source": [ "### Table `fruits` to pivot." ] }, { "cell_type": "code", "execution_count": 27, "id": "18593d5c-7e49-497f-86da-2fa93f896c0c", "metadata": {}, "outputs": [ { "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", "
store_idapplesorangesbananaspeachespears
0store_1101102103104105
1store_2201202203204205
2store_3301302303304305
\n", "
" ], "text/plain": [ " store_id apples oranges bananas peaches pears\n", "0 store_1 101 102 103 104 105\n", "1 store_2 201 202 203 204 205\n", "2 store_3 301 302 303 304 305" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_query(conn, 'SELECT * FROM fruits')" ] }, { "cell_type": "markdown", "id": "dfb1bdd5-822d-44ea-97a8-308ee5856b78", "metadata": {}, "source": [ "### Call stored procedure `pivot()` to pivot the table." ] }, { "cell_type": "code", "execution_count": 28, "id": "6ccb3dd9-72e6-4675-bd5e-9e7fe5c876e3", "metadata": {}, "outputs": [], "source": [ "# database: pivot_test\n", "# base table: fruits\n", "# base table primary key: store_id\n", "# pivot table primary key: fruit_id\n", "\n", "cursor.execute(\"CALL pivot('pivot_test', 'fruits', 'store_id', 'fruit_id')\")" ] }, { "cell_type": "markdown", "id": "df096642-afcb-40f5-9b27-f79ac67f21ba", "metadata": {}, "source": [ "### The resulting pivoted table `fruits_pivoted`." ] }, { "cell_type": "code", "execution_count": 29, "id": "ea544c57-da47-417c-95d3-f36f86f73a42", "metadata": {}, "outputs": [ { "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", "
fruit_idstore_1store_2store_3
0apples101201301
1bananas103203303
2oranges102202302
3peaches104204304
4pears105205305
\n", "
" ], "text/plain": [ " fruit_id store_1 store_2 store_3\n", "0 apples 101 201 301\n", "1 bananas 103 203 303\n", "2 oranges 102 202 302\n", "3 peaches 104 204 304\n", "4 pears 105 205 305" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_query(conn, 'SELECT * FROM fruits_pivoted')" ] }, { "cell_type": "code", "execution_count": 30, "id": "3cdf086e-66e5-4961-9565-8f148e7686ac", "metadata": {}, "outputs": [], "source": [ "cursor.close()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "aac007de-5f4c-45cf-a52e-f38a8c845c4d", "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 }