03_sqlserver.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Databases with Python\n",
    "1. SQLite3 [[Notebook]](01_sqlite.ipynb)\n",
    "2. Postgres [[Notebook]](02_postgres.ipynb)\n",
    "3. **SQL Server** [[Notebook]](03_sqlserver.ipynb)\n",
    "4. Pandas [[Notebook]](04_pandas.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## SQL Server & Pyodbc\n",
    "Implement CRUD operations with Pyodbc prepared by [Joseph Konka](https://www.linkedin.com/in/joseph-koami-konka/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prerequisites\n",
    "* SQL Server\n",
    "* ODBC Driver for SQL Server\n",
    "* Pyodbc\n",
    "\n",
    "### 1. Install SQL Server\n",
    "1. Téléchargemnt\n",
    "\n",
    "2. Installation\n",
    "\n",
    "3. Démarrage\n",
    "\n",
    "### 2. Install ODBC Driver for SQL Server\n",
    "1. Téléchargemnt\n",
    "\n",
    "2. Installation\n",
    "\n",
    "3. Démarrage\n",
    "\n",
    "### 3. Install Pyodbc\n",
    "```bash\n",
    "$ pip install -q pyodbc\n",
    "```\n",
    "\n",
    "### 4. Create a database\n",
    "Create a new database called **algojungle**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "7b5b145a-6195-40df-b85f-824b727881bb",
    "language": "python"
   },
   "source": [
    "## Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "azdata_cell_guid": "88a5cf3a-d265-4458-a8a8-8935c168287f",
    "language": "python"
   },
   "outputs": [],
   "source": [
    "# !pip install -q pyodbc\n",
    "# !pip install -q pandas\n",
    "\n",
    "import pyodbc\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "dfa7ac0d-67c2-4801-96c0-1e46bc548bfb",
    "language": "python"
   },
   "source": [
    "## Paths & Config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "azdata_cell_guid": "728d7ada-6ebd-4bda-a7d4-03fb3b0c1852",
    "language": "python",
    "tags": []
   },
   "outputs": [],
   "source": [
    "HOST = 'localhost'\n",
    "DATABASE = 'algojungle'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connection to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "azdata_cell_guid": "4998f97f-32b1-4484-9d07-78800de8442d",
    "language": "python"
   },
   "outputs": [],
   "source": [
    "conn = pyodbc.connect('Driver={SQL Server};'\n",
    "                      f'Server={HOST};'\n",
    "                      f'Database={DATABASE};'\n",
    "                      'Trusted_Connection=yes;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "azdata_cell_guid": "f6c49d1e-8aea-4c7c-8435-c71df3a03daa",
    "language": "python"
   },
   "outputs": [],
   "source": [
    "# Create a cursor\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interaction with database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0. Create a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql1 = \"DROP TABLE IF EXISTS customers;\";\n",
    "\n",
    "sql2 = \"\"\"\n",
    "CREATE TABLE customers\n",
    "(\n",
    "    DepartmentID INTEGER PRIMARY KEY NOT NULL,\n",
    "    Name VARCHAR(100),\n",
    "    GroupName VARCHAR(100)\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "cur.execute(sql1)\n",
    "cur.execute(sql2)\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. CREATE : Insert data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql3 = \"\"\"\n",
    "INSERT INTO customers (DepartmentID, Name, GroupName) \n",
    "VALUES (1, 'Engineering', 'Research and Development'),\n",
    "(2, 'Tool Design', 'Research and Development'),\n",
    "(3, 'Sales', 'Sales and Marketing'),\n",
    "(4, 'Marketing', 'Sales and Marketing'),\n",
    "(5, 'Purchasing', 'Inventory Management'),\n",
    "(6, 'Research and Development', 'Research and Development'),\n",
    "(7, 'Production', 'Manufacturing'),\n",
    "(8, 'Production Control', 'Manufacturing'),\n",
    "(9, 'Human Resources', 'Executive General and Administration'),\n",
    "(10, 'Finance', 'Executive General and Administration'),\n",
    "(11, 'Information Services', 'Executive General and Administration'),\n",
    "(12, 'Document Control', 'Quality Assurance'),\n",
    "(13, 'Quality Assurance', 'Quality Assurance'),\n",
    "(14, 'Facilities and Maintenance', 'Executive General and Administration'),\n",
    "(15, 'Shipping and Receiving', 'Inventory Management'),\n",
    "(16, 'Executive', 'Executive General and Administration');\n",
    "\"\"\"\n",
    "\n",
    "conn.execute(sql3)\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. READ : Read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DepartmentID</th>\n",
       "      <th>Name</th>\n",
       "      <th>GroupName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>Research and Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Tool Design</td>\n",
       "      <td>Research and Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Sales and Marketing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Marketing</td>\n",
       "      <td>Sales and Marketing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Purchasing</td>\n",
       "      <td>Inventory Management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Research and Development</td>\n",
       "      <td>Research and Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Production</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Production Control</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Human Resources</td>\n",
       "      <td>Executive General and Administration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Executive General and Administration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Information Services</td>\n",
       "      <td>Executive General and Administration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Document Control</td>\n",
       "      <td>Quality Assurance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Quality Assurance</td>\n",
       "      <td>Quality Assurance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Facilities and Maintenance</td>\n",
       "      <td>Executive General and Administration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>Shipping and Receiving</td>\n",
       "      <td>Inventory Management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Executive</td>\n",
       "      <td>Executive General and Administration</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    DepartmentID                        Name  \\\n",
       "0              1                 Engineering   \n",
       "1              2                 Tool Design   \n",
       "2              3                       Sales   \n",
       "3              4                   Marketing   \n",
       "4              5                  Purchasing   \n",
       "5              6    Research and Development   \n",
       "6              7                  Production   \n",
       "7              8          Production Control   \n",
       "8              9             Human Resources   \n",
       "9             10                     Finance   \n",
       "10            11        Information Services   \n",
       "11            12            Document Control   \n",
       "12            13           Quality Assurance   \n",
       "13            14  Facilities and Maintenance   \n",
       "14            15      Shipping and Receiving   \n",
       "15            16                   Executive   \n",
       "\n",
       "                               GroupName  \n",
       "0               Research and Development  \n",
       "1               Research and Development  \n",
       "2                    Sales and Marketing  \n",
       "3                    Sales and Marketing  \n",
       "4                   Inventory Management  \n",
       "5               Research and Development  \n",
       "6                          Manufacturing  \n",
       "7                          Manufacturing  \n",
       "8   Executive General and Administration  \n",
       "9   Executive General and Administration  \n",
       "10  Executive General and Administration  \n",
       "11                     Quality Assurance  \n",
       "12                     Quality Assurance  \n",
       "13  Executive General and Administration  \n",
       "14                  Inventory Management  \n",
       "15  Executive General and Administration  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql4 = \"SELECT * FROM customers;\"\n",
    "\n",
    "cur.execute(sql4)\n",
    "\n",
    "results = pd.DataFrame([tuple(row) for row in cur.fetchall()], \n",
    "                       columns=[desc[0] for desc in cur.description])\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3. UPDATE : Update table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql5 = \"\"\"\n",
    "UPDATE customers \n",
    "SET Name = 'Joseph Koami Konka' \n",
    "WHERE DepartmentID = 1;\n",
    "\"\"\"\n",
    "\n",
    "cur.execute(sql5)\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. DELETE : Delete data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql6 = \"\"\"\n",
    "DELETE FROM customers \n",
    "WHERE DepartmentID = 1;\n",
    "\"\"\"\n",
    "\n",
    "cur.execute(sql6)\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "94f0a1c0-0c6a-4cdc-a4aa-147ae7010204",
    "language": "python"
   },
   "source": [
    "## Close connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "azdata_cell_guid": "d52d50bb-6bbe-4603-bdce-19b9757a5a4b",
    "language": "python",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database connection closed.\n"
     ]
    }
   ],
   "source": [
    "# close the communication with the SQL Server\n",
    "cur.close()\n",
    "conn.close()\n",
    "print('Database connection closed.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## References\n",
    "* SQL Server: https://www.postgresql.org/download/\n",
    "* Pyodbc: https://www.psycopg.org/docs/install.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's get in touch\n",
    "[![Github Badge](https://img.shields.io/badge/-Github-000?style=flat-square&logo=Github&logoColor=white&link=https://github.com/joekakone)](https://github.com/joekakone) [![Linkedin Badge](https://img.shields.io/badge/-LinkedIn-blue?style=flat-square&logo=Linkedin&logoColor=white&link=https://www.linkedin.com/in/joseph-koami-konka/)](https://www.linkedin.com/in/joseph-koami-konka/) [![Twitter Badge](https://img.shields.io/badge/-Twitter-blue?style=flat-square&logo=Twitter&logoColor=white&link=https://www.twitter.com/joekakone)](https://www.twitter.com/joekakone) [![Gmail Badge](https://img.shields.io/badge/-Gmail-c14438?style=flat-square&logo=Gmail&logoColor=white&link=mailto:joseph.kakone@gmail.com)](mailto:joseph.kakone@gmail.com)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}