python-azure-sql/3-manipulate-data-sql.ipynb

{
 "metadata": {
  "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.8.5"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python385jvsc74a57bd09ceeaccb2d4b8fc120d52b4319c20d729c878e30972ae0f296691445edd93f69",
   "display_name": "Python 3.8.5 32-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "# Manipulate data in Azure SQL Database using Python and Jupyter Notebook"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## Import the library"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc"
   ]
  },
  {
   "source": [
    "## Open the connection"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "server = '<server>.database.windows.net'\n",
    "database = '<database>'\n",
    "username = '<username>'\n",
    "password = '<password>'\n",
    "driver= '{ODBC Driver 17 for SQL Server}'\n",
    "conn = pyodbc.connect('DRIVER=' + driver + ';SERVER=' +\n",
    "    server + ';PORT=1433;DATABASE=' + database +\n",
    "    ';UID=' + username + ';PWD=' + password)"
   ]
  },
  {
   "source": [
    "## Create a cursor object"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()"
   ]
  },
  {
   "source": [
    "## Insert a new movie"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"INSERT INTO movies \"\n",
    "    \"(movie_title, released_year, runtime, genre, rating, director, \"\n",
    "    \"star1, star2, number_of_votes, gross) \"\n",
    "    \"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\", \n",
    "    (\"The Imitation Game\", 2014, 114, \"Biography\", 80, \n",
    "    \"Morten Tyldum\", \"Benedict Cumberbatch\", \"Keira Knightley\", \n",
    "    685201, 91125683))"
   ]
  },
  {
   "source": [
    "## Commit the changes"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "source": [
    "## Read data"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read data\n",
    "cursor.execute(\"SELECT \"\n",
    "    \"movie_id, movie_title, director \"\n",
    "    \"FROM movies\")\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "# Print data\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "source": [
    "## Update a data row in the table"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"UPDATE movies \"\n",
    "    \"SET genre = ? \"\n",
    "    \"WHERE movie_title = ?\", \n",
    "    (\"Biography\", \"The Theory of Everything\"))\n",
    "\n",
    "print(\"Updated\",cursor.rowcount,\"row(s) of data.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read data\n",
    "cursor.execute(\"SELECT \"\n",
    "    \"movie_id, movie_title, genre \"\n",
    "    \"FROM movies \"\n",
    "    \"WHERE movie_title = ?\",\n",
    "    (\"The Theory of Everything\", ))\n",
    "rows = cursor.fetchall()\n",
    "print(rows[0])"
   ]
  },
  {
   "source": [
    "## Delete data"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"DELETE FROM movies \"\n",
    "    \"WHERE genre = ?\", \n",
    "    (\"Horror\", ))\n",
    "print(\"Deleted\",cursor.rowcount,\"row(s) of data.\")"
   ]
  },
  {
   "source": [
    "## Order data"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read data\n",
    "cursor.execute(\"SELECT movie_title, released_year, genre, gross \"\n",
    "    \"FROM movies \"\n",
    "    \"WHERE genre = ? AND released_year BETWEEN ? AND ? \"\n",
    "    \"ORDER BY released_year ASC, gross DESC\",\n",
    "    (\"Action\", 2009, 2019))\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "# Print data\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "source": [
    "## Use the *MAX()* function"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read data\n",
    "cursor.execute(\"SELECT MAX(gross) \"\n",
    "    \"FROM movies \"\n",
    "    \"WHERE director = ? \",\n",
    "    (\"Martin Scorsese\", ))\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "# Print data\n",
    "print(rows[0])"
   ]
  },
  {
   "source": [
    "## Close the connection"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ]
}