path: root/12-using-sql-from-other-programming-language/using-sql-from-other-programming-language.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8210739c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import agh_db_lectures\n",
    "agh_db_lectures.prepare_notebook_for_sql()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29b991f1",
   "metadata": {},
   "source": [
    "# Using SQL From Other Programming Language"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf1bebfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "!printf \"CREATE DATABASE IF NOT EXISTS programming_examples\" | mariadb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a04a75f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql mysql:///programming_examples?unix_socket=/var/run/mysql/mysql.sock"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0198ccd6",
   "metadata": {},
   "source": [
    "## Operations on an SQL Database From Python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6baf4466",
   "metadata": {},
   "source": [
    "> PEP stands for Python Enhancement Proposal. A PEP is a design document providing information to the Python community, or describing a new feature for Python or its processes or environment. _(from [PEP 1](https://peps.python.org/pep-0001/))_\n",
    "\n",
    "[PEP 249 – Python Database API Specification v2.0](https://peps.python.org/pep-0249/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c004024",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "In any sufficiently popular general purpose programming language, users of the language are going to start implementing interfaces to various databases.  As the number of libraries adding support for various DMBSes grows, it is beneficial for the language's community to have a consistent API for communicating with various database systems.  This decreases the learning and porting effort of programmers.\n",
    "\n",
    "PEP 249 defines object types (even including exception types), methods, behavior, and variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7ef75e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import MySQLdb\n",
    "\n",
    "connection = MySQLdb.connect(\n",
    "    database='programming_examples',\n",
    "    unix_socket='/var/run/mysql/mysql.sock'\n",
    ")\n",
    "\n",
    "connection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3344cbf1",
   "metadata": {},
   "source": [
    "_notes_     \n",
    "\n",
    "https://peps.python.org/pep-0249/#connect\n",
    "\n",
    "Parameters are database-specific, but some keywords (e.g., `user`, `password`, `host`, and `database`) are suggested."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71e5c8fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()\n",
    "\n",
    "cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be9d8edb",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "https://peps.python.org/pep-0249/#cursor\n",
    "\n",
    "Database queries are typically performed using cursors.  Cursor allows fetching query result in parts (which makes a lot of sense for big queries!).  Multiple cursors allow an application to perform other database operations without regard to queries that have been performed but are yet to have their last result row fetched."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1658f3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TABLE IF EXISTS follows')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0366ca52",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "https://peps.python.org/pep-0249/#id19\n",
    "\n",
    "The `Cursor.execute()` method accepts an operation that, in our case, can be a string with some SQL code.  An interface implementation can return whatever it wants from this method.  MySQLdb happens to return the number of rows fetched."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b386073e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cursor.execute('''\n",
    "    DROP TABLE IF EXISTS users;\n",
    "\n",
    "    CREATE TABLE users (\n",
    "        id            INT PRIMARY KEY AUTO_INCREMENT,\n",
    "        login         VARCHAR(50)    UNIQUE NOT NULL,\n",
    "        password_salt VARBINARY(10)  UNIQUE NOT NULL,\n",
    "        password_hash VARBINARY(130)        NOT NULL\n",
    "    );\n",
    "\n",
    "    CREATE TABLE follows (\n",
    "        follower_id INT,\n",
    "        followed_id INT,\n",
    "        since       DATE NOT NULL,\n",
    "        PRIMARY KEY (follower_id, followed_id),\n",
    "        FOREIGN KEY (follower_id) REFERENCES users (id),\n",
    "        FOREIGN KEY (followed_id) REFERENCES users (id),\n",
    "        CHECK (follower_id <> followed_id)\n",
    "    )\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ec58a06",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "This is almost a subset of our git forge schema.  However, it resembles many possible kinds of platforms that have the notion of users who can log in and subscribe to others' profiles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68ccc789",
   "metadata": {},
   "outputs": [],
   "source": [
    "import hashlib\n",
    "\n",
    "def gen_salt():\n",
    "    with open('/dev/random', 'rb') as random:\n",
    "        return random.read(6)\n",
    "\n",
    "def hash_password(salt, password):\n",
    "    salted_password = salt + password.encode()\n",
    "    return hashlib.md5(salted_password).digest()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c80d6c7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "salt = gen_salt()\n",
    "\n",
    "salt #.hex()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62cdcc2e",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Assume we are going to be adding new users to the db.  The application code should pick a random salt value for each user.\n",
    "\n",
    "Try using the functions defined above to generate a salt and to hash some password with it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5edd7051",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT X'deadbeef'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "216ce133",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We can obtain a string of arbitraty, non-ASCII bytes by using the (standard SQL) `X''` notation with a hexadecimal value between the apostrophes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80d72189",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "login_regex = re.compile('^[a-zA-Z_][a-zA-Z_0-9]+$')\n",
    "\n",
    "def add_user(cursor, login, password):\n",
    "    assert login_regex.match(login)\n",
    "    salt = gen_salt()\n",
    "    digest = hash_password(salt, password)\n",
    "    command_string = f'''\n",
    "        INSERT INTO users (login, password_salt, password_hash)\n",
    "        VALUES ('{login}', X'{salt.hex()}', X'{digest.hex()}')\n",
    "        RETURNING id\n",
    "    '''\n",
    "    cursor.execute(command_string)\n",
    "    (new_id,) = cursor.fetchone()\n",
    "    return new_id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32672fa5",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "https://peps.python.org/pep-0249/#fetchone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4eff13c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "add_user(cursor, 'theodore', 'S3cur3 Pa$$word3')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1bd6501",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```python\n",
    "add_user(cursor, 'pan_cratius', 'MUCH passw0rd, VERY secur!ty')\n",
    "add_user(cursor, 'EUgenia', 'hack me, LOL!')\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85713fa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60b795f1",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```python\n",
    "connection.commit()\n",
    "```\n",
    "\n",
    "https://peps.python.org/pep-0249/#commit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e4569fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''\n",
    "    INSERT INTO follows (followed_id, follower_id, since)\n",
    "    VALUES (1, 2, NOW());\n",
    "    INSERT INTO follows (followed_id, follower_id, since)\n",
    "    VALUES (1, 3, NOW() - INTERVAL 1 DAY);\n",
    "    INSERT INTO follows (followed_id, follower_id, since)\n",
    "    VALUES (3, 2, NOW() - INTERVAL 2 DAY)\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2963edd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('SAVEPOINT my_checkpoint')\n",
    "cursor.execute('''\n",
    "    INSERT INTO follows (followed_id, follower_id, since)\n",
    "    VALUES (3, 1, NOW() - INTERVAL 3 DAY);\n",
    "''')\n",
    "cursor.execute('ROLLBACK TO SAVEPOINT my_checkpoint')\n",
    "cursor.execute('COMMIT')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "666984ce",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```python\n",
    "cursor.execute('SAVEPOINT my_checkpoint')\n",
    "cursor.execute('''\n",
    "    INSERT INTO follows (followed_id, follower_id, since)\n",
    "    VALUES (3, 1, NOW() - INTERVAL 2 DAY);\n",
    "''')\n",
    "cursor.execute('ROLLBACK TO SAVEPOINT my_checkpoint')\n",
    "cursor.execute('COMMIT')\n",
    "```\n",
    "\n",
    "`COMMIT` also works when passed as text."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29f29bd0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT dst.login AS followed, src.login AS follower, since \n",
    "FROM users   src\n",
    "JOIN follows     ON src.id = follows.follower_id\n",
    "JOIN users   dst ON follows.followed_id = dst.id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce97bd60",
   "metadata": {},
   "source": [
    "### Unresolved Differences"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "692b86a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT CAST(X'deadbeef' AS BINARY)\")\n",
    "cursor.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd73cfa9",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```python\n",
    "import psycopg2\n",
    "\n",
    "psql_connection = psycopg2.connect(\n",
    "    user = 'demo_user',\n",
    "    password = 'demo_pwd',\n",
    "    host = 'localhost',\n",
    "    port = 25432,\n",
    "    database = 'agh_it_northwind'\n",
    ")\n",
    "\n",
    "psql_cursor = sql_connection.cursor()\n",
    "\n",
    "psql_cursor.execute(\"SELECT CAST(X'deadbeef' AS BINARY)\")\n",
    "psql_cursor.fetchall()\n",
    "```\n",
    "\n",
    "Postgres' closest type is `BYTEA`.\n",
    "\n",
    "```python\n",
    "psql_cursor.execute(\"SELECT CAST(X'deadbeef' AS BYTEA)\")\n",
    "```\n",
    "\n",
    "Note the different returned type (`memoryview` rather than `bytes`)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbf83cc0",
   "metadata": {},
   "source": [
    "## Prepared Statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dea2a7da",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b5255d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "def sign_in_interactive(cursor):\n",
    "    login = input('login: ')\n",
    "    password = input('password: ')\n",
    "\n",
    "    query_string = f'''\n",
    "        SELECT password_salt\n",
    "        FROM users\n",
    "        WHERE login = '{login}'\n",
    "    '''\n",
    "    cursor.execute(query_string)\n",
    "    if cursor.rowcount == 0:\n",
    "        return False\n",
    "    (salt,) = cursor.fetchone()\n",
    "\n",
    "    digest = hash_password(salt, password)\n",
    "\n",
    "    query_string = f'''\n",
    "        SELECT COUNT(*) > 0\n",
    "        FROM users\n",
    "        WHERE password_hash = X'{digest.hex()}' AND\n",
    "              login = '{login}'\n",
    "    '''\n",
    "    cursor.execute(query_string)\n",
    "    (result,) = cursor.fetchone()\n",
    "    \n",
    "    return bool(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fae7a768",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "https://peps.python.org/pep-0249/#rownumber"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17031e37",
   "metadata": {},
   "outputs": [],
   "source": [
    "sign_in_interactive(cursor)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee5fce14",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```\n",
    "login: theodore\n",
    "password: S3cur3 Pa$$word3\n",
    "```\n",
    "\n",
    "Later, use `' OR TRUE -- ` as login.\n",
    "\n",
    "Or `'; DROP TABLE follows; DROP TABLE users; COMMIT -- `, whatever."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d41ef03",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "PREPARE get_salt FROM\n",
    "'SELECT password_salt\n",
    " FROM users\n",
    " WHERE login = ?'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b3ba197",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "EXECUTE get_salt USING 'theodore'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d775f486",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```sql\n",
    "DEALLOCATE PREPARE get_salt\n",
    "```\n",
    "\n",
    "Statements are allocated per-session, so they would get purge at the and of the session, anyway.  But for a long-running session with many statements created from dynamic data it might make a difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b4b90f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "PREPARE verify_hash FROM\n",
    "'SELECT COUNT(*) > 0\n",
    " FROM users\n",
    " WHERE password_hash = ? AND\n",
    "       login = ?'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a984b46a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "EXECUTE verify_hash USING X'deadbeef', 'pan_cratius'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e717266c",
   "metadata": {},
   "source": [
    "### Using Prepared Statements From within Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccdf1a3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def sign_in_interactive(cursor):\n",
    "    login = input('login: ')\n",
    "    password = input('password: ')\n",
    "\n",
    "    query_string = f'''\n",
    "        SELECT password_salt\n",
    "        FROM users\n",
    "        WHERE login = %s\n",
    "    '''\n",
    "    cursor.execute(query_string, (login,))\n",
    "    if cursor.rowcount == 0:\n",
    "        return False\n",
    "    (salt,) = cursor.fetchone()\n",
    "\n",
    "    digest = hash_password(salt, password)\n",
    "\n",
    "    query_string = f'''\n",
    "        SELECT COUNT(*) > 0\n",
    "        FROM users\n",
    "        WHERE password_hash = %s AND\n",
    "              login = %s\n",
    "    '''\n",
    "    cursor.execute(query_string, (digest, login))\n",
    "    (result,) = cursor.fetchone()\n",
    "    \n",
    "    return bool(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "115afdf8",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Question marks are not used uniformly in parameterized statements in Python db interfaces.  PEP 249 [allows an implementation to choose](https://peps.python.org/pep-0249/#paramstyle) from a set of possible paramater marker types.\n",
    "\n",
    "The interface implementation does not, in fact, have to use the prepared statements feature of the DBMS…"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3753aa7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "sign_in_interactive(cursor)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1cbcef4",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "```\n",
    "login: theodore\n",
    "password: S3cur3 Pa$$word3\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2d97f0a",
   "metadata": {},
   "source": [
    "## In Other Programming Languages"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d85dfc4e",
   "metadata": {},
   "source": [
    "- JDBC (not really \"Java Database Connectivity\" 😉)\n",
    "- ODBC (\"Open Database Connectivity\", for C language)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7dbd76d",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "When Oracle failed to trademark \"Java Database Connectivity\", it started using the name \"JDBC\" exclusively, no more treating it as an acronym of something.\n",
    "\n",
    "https://www.oracle.com/database/technologies/appdev/jdbc.html"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "/gnu/store/q35bxk1i5blx0rcgxphhrq9xqmgha9bz-python-3.11.11/bin/python3",
   "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.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}