sandbox.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2c1206b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6932104d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SQL Server',\n",
       " 'PostgreSQL ANSI(x64)',\n",
       " 'PostgreSQL Unicode(x64)',\n",
       " 'SQL Server Native Client 11.0',\n",
       " 'SQL Server Native Client RDA 11.0',\n",
       " 'ODBC Driver 17 for SQL Server',\n",
       " 'Microsoft Access Driver (*.mdb, *.accdb)',\n",
       " 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',\n",
       " 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)',\n",
       " 'Microsoft Access Text Driver (*.txt, *.csv)']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pyodbc.drivers()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6529a90d-3d6a-4451-93c6-2718c0e304a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "c = {    \n",
    "    \"driver\": \"SQL SERVER\",\n",
    "    \"server\": \"LT-W1828-HH\\SQLSERVER22019\",\n",
    "    \"database\": \"AdventureWorks2019\",\n",
    "    \"username\": \"sandbox\",\n",
    "    \"password\": \"mytestpassword\"\n",
    "}\n",
    "\n",
    "connection_string = f\"DRIVER={{{c['driver']}}}; SERVER={c['server']}; Database={c['database']}; UID={c['username']}; PWD={c['password']};\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "089081b5",
   "metadata": {},
   "source": [
    "# Query 1\n",
    "This query gets all of the UnitPrices in the PurchaseOrderDetail table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "01475599",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    FROM AdventureWorks2019.Purchasing.PurchaseOrderDetail;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fea2c259",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()). (2)')",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Input \u001b[1;32mIn [5]\u001b[0m, in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[43mpyodbc\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[43mconnection_string\u001b[49m\u001b[43m)\u001b[49m \u001b[38;5;28;01mas\u001b[39;00m conx:\n\u001b[0;32m      2\u001b[0m     cursor \u001b[38;5;241m=\u001b[39m conx\u001b[38;5;241m.\u001b[39mcursor()\n\u001b[0;32m      3\u001b[0m     cursor\u001b[38;5;241m.\u001b[39mexecute(query)\n",
      "\u001b[1;31mOperationalError\u001b[0m: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()). (2)')"
     ]
    }
   ],
   "source": [
    "with pyodbc.connect(connection_string) as conx:\n",
    "    cursor = conx.cursor()\n",
    "    cursor.execute(query)\n",
    "    data = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "49994cd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "unitPrices = np.array([float(row[5]) for row in data])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d73e2169-7e08-4911-a054-7b1c2b35259c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The median is 39.2805\n",
      "The lower and upper quartiles are [25.4205 45.444 ]\n"
     ]
    }
   ],
   "source": [
    "print(f\"The median is {np.median(unitPrices)}\")\n",
    "print(f\"The lower and upper quartiles are {np.quantile(unitPrices, [.25, .75])}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88677eb0-6f37-4952-b346-258569d5a0bd",
   "metadata": {},
   "source": [
    "# Query 2\n",
    "This query gets all of the UnitPrices in the PurchaseOrderDetail table that are less than $80 (eliminate outliers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "55b3e206-e44a-47ea-8802-39e1322e7e96",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    FROM AdventureWorks2019.Purchasing.PurchaseOrderDetail\n",
    "    WHERE UnitPrice < 80;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "6b0172f4-ec02-4401-96c8-a403ab2a36d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pyodbc.connect(connection_string) as conx:\n",
    "    cursor = conx.cursor()\n",
    "    cursor.execute(query)\n",
    "    data = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "6c5a5262-3f51-4ece-b915-96e1ccdfe170",
   "metadata": {},
   "outputs": [],
   "source": [
    "unitPrices = np.array([float(row[5]) for row in data])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "8e61d30e-b56f-4ffa-a57a-7d7f607da769",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The median is 39.2385\n",
      "The lower and upper quartiles are [25.4205 45.423 ]\n"
     ]
    }
   ],
   "source": [
    "print(f\"The median is {np.median(unitPrices)}\")\n",
    "print(f\"The lower and upper quartiles are {np.quantile(unitPrices, [.25, .75])}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "50f51b49-c254-413b-85eb-9d5300c32706",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.21 ,  0.21 ,  0.21 , ..., 63.693, 63.693, 63.693])"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(unitPrices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9eacdbe6-d000-4630-bd4a-67803f88a0e0",
   "metadata": {},
   "source": [
    "## ----------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "eee1eb45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('AdventureWorks2019', 'Sales', 'Currency', 'CurrencyCode', -8, 'nchar', 3, 6, None, None, 0, None, None, -8, None, 6, 1, 'NO', 47),\n",
       " ('AdventureWorks2019', 'Sales', 'Currency', 'ModifiedDate', 93, 'datetime', 23, 16, 3, None, 0, None, '(getdate())', 9, 3, None, 3, 'NO', 61)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[row for row in cursor.columns(table=\"Currency\")] # Where is \"Name?\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35fcb016",
   "metadata": {},
   "source": [
    "This query will get only the United States Dollar and Colombian Peso from the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "50acf730",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    SELECT *\n",
    "    FROM AdventureWorks2019.Sales.Currency\n",
    "    WHERE Name IN ('US Dollar', 'Colombian Peso');\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "285e63ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pyodbc.connect(connection_string) as conx:\n",
    "    cursor = conx.cursor()\n",
    "    cursor.execute(query)\n",
    "    data = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "80668a70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('COP', 'Colombian Peso', datetime.datetime(2008, 4, 30, 0, 0)),\n",
       " ('USD', 'US Dollar', datetime.datetime(2008, 4, 30, 0, 0))]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20c36822-9a63-479e-a9a5-500f743381db",
   "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.10.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}