{ "cells": [ { "cell_type": "markdown", "id": "37395fc7-7f0a-4ef9-80bd-420900e2154c", "metadata": {}, "source": [ "# Invoke Car Dealership Stored Procedures" ] }, { "cell_type": "code", "execution_count": 1, "id": "e00faceb-fdae-451a-8016-22cedde52e45", "metadata": {}, "outputs": [], "source": [ "from DATA225utils import make_connection, dataframe_query\n", "from pandas import DataFrame" ] }, { "cell_type": "code", "execution_count": 2, "id": "32e96d54-3da1-45c3-9790-5b55c8752c6f", "metadata": { "tags": [] }, "outputs": [], "source": [ "conn = make_connection(config_file = 'CarDealership.ini')\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "c6a5f84b-cc2a-4b37-85fe-398cdd096e66", "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": "e930a82a-fec1-4c91-becb-4432ff5c5b5b", "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 = result.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": "85c5391d-d3c2-4487-a52b-71316d275d21", "metadata": { "tags": [] }, "source": [ "## **1.a** `sales_of_month()`" ] }, { "cell_type": "code", "execution_count": 4, "id": "ab6a4082-848f-4bc8-83e3-47de4ce3daaf", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CALL sales_of_month(9)\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", " \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", " \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", "
pnamemakemodelcnamecgenderprice
0BrunoVolvoS60Ronm16000.0
1JudyFordFiestaTomm10000.0
2JudyFordFocusTomm700.0
3LeslieHondaAccordSusanf2200.0
4LeslieVolvo240 DLMaraf1000.0
5LeslieVolvo850Susanf5000.0
6LeslieVolvoX90Maraf11000.0
7SarahChevyNovaTomm1000.0
8SarahHondaCivicArnoldm5000.0
9SarahToyotaPriusTomm15000.0
10SarahVolkswagenBeetleArnoldm200.0
11SteveFordTaurusMaxm10000.0
\n", "
" ], "text/plain": [ " pname make model cname cgender price\n", "0 Bruno Volvo S60 Ron m 16000.0\n", "1 Judy Ford Fiesta Tom m 10000.0\n", "2 Judy Ford Focus Tom m 700.0\n", "3 Leslie Honda Accord Susan f 2200.0\n", "4 Leslie Volvo 240 DL Mara f 1000.0\n", "5 Leslie Volvo 850 Susan f 5000.0\n", "6 Leslie Volvo X90 Mara f 11000.0\n", "7 Sarah Chevy Nova Tom m 1000.0\n", "8 Sarah Honda Civic Arnold m 5000.0\n", "9 Sarah Toyota Prius Tom m 15000.0\n", "10 Sarah Volkswagen Beetle Arnold m 200.0\n", "11 Steve Ford Taurus Max m 10000.0" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sql = \"CALL sales_of_month(9)\"\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": "acba41f3-2b72-486d-9648-a901c6fbfd55", "metadata": { "tags": [] }, "source": [ "## **1.b** `sales_counts_of_month()`" ] }, { "cell_type": "code", "execution_count": 5, "id": "f4be8d5b-ce30-4fa0-a278-68359f04dff4", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CALL sales_counts_of_month(9)\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", "
pnamecars_sold
0Bruno2
1Judy2
2Leslie4
3Sarah4
4Steve1
\n", "
" ], "text/plain": [ " pname cars_sold\n", "0 Bruno 2\n", "1 Judy 2\n", "2 Leslie 4\n", "3 Sarah 4\n", "4 Steve 1" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sql = \"CALL sales_counts_of_month(9)\"\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": "836ee418-3ad5-4806-9458-c6f409b8d88c", "metadata": { "tags": [] }, "source": [ "# **1.c** `highest_sales_of_month()`" ] }, { "cell_type": "markdown", "id": "45f2fc3c-cb29-409d-883e-641425c76a52", "metadata": {}, "source": [ "#### **ALSO ACCEPTABLE SOLUTION:** It wasn't clear that I meant the highest number of cars sold by a salesperson during a given month. Some students thought the answer involved the highest revenue (dollar amount). Either answer for this and the following question will get full credit." ] }, { "cell_type": "code", "execution_count": 6, "id": "23cf626a-eebc-48ee-8681-9fa008e02c9b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CALL highest_sales_of_month(9, @highest_sales)\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
highest_sales_of_the_month
04
\n", "
" ], "text/plain": [ " highest_sales_of_the_month\n", "0 4" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# @highest_sales is a user-defined variable.\n", "sql = \"CALL highest_sales_of_month(9, @highest_sales)\"\n", "print(sql)\n", "print()\n", "\n", "generator = cursor.execute(sql, multi=True)\n", "\n", "for result in generator:\n", " for row in result.fetchall():\n", " pass\n", "\n", "sql = 'SELECT @highest_sales AS highest_sales_of_the_month'\n", "_, df = dataframe_query(conn, sql)\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "7375984c-0a4f-41d7-a3a6-fc5506a496a4", "metadata": { "tags": [] }, "source": [ "## **1.d** `salespersons_of_the_month()`" ] }, { "cell_type": "code", "execution_count": 7, "id": "8d3247fd-0d8f-4dd8-8b5b-1f69fad7a31c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CALL salespersons_of_the_month(9)\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", "
pnamecars_sold
0Leslie4
1Sarah4
\n", "
" ], "text/plain": [ " pname cars_sold\n", "0 Leslie 4\n", "1 Sarah 4" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sql = \"CALL salespersons_of_the_month(9)\"\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": "eed65000-c1a0-4cbd-8f5a-3ea6f829ae6c", "metadata": {}, "outputs": [], "source": [ "cursor.close()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "5904fbce-8544-4cac-9b1d-1c98d75484af", "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.9.13" } }, "nbformat": 4, "nbformat_minor": 5 }