{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3886822d-f9d6-4849-9826-65fb635cc7d9",
   "metadata": {},
   "source": [
    "# Transactions (SQL Commands Version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78b29880-1f0a-4d08-8f17-7e67da1babd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4180db3-341b-4d6b-bc46-093c34cd6665",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection(config_file = 'transactions.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "599a75a1-2c84-4780-8c05-b133cc1f9949",
   "metadata": {},
   "outputs": [],
   "source": [
    "def show_numbers():\n",
    "    _, df = dataframe_query(conn, 'SELECT * FROM numbers')\n",
    "    display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5861b95-5b21-4785-9c4c-a10ceeb2c81c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TABLE IF EXISTS numbers')\n",
    "\n",
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE TABLE numbers\n",
    "    (\n",
    "        field INT NOT NULL,\n",
    "        PRIMARY KEY (field)\n",
    "    )\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d50fb22-51c8-4e2b-b116-4c2ef039aa3f",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `START TRANSACTION`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23ba4b46-d5ee-4bad-bd59-b7b20926185f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "cursor.execute('START TRANSACTION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97dc12d5-a17a-4422-a130-9d82b8209e9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(f'INSERT INTO numbers VALUES (10)')\n",
    "cursor.execute(f'INSERT INTO numbers VALUES (20)')\n",
    "cursor.execute(f'INSERT INTO numbers VALUES (30)')\n",
    "\n",
    "show_numbers()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fb9e0c5-b49e-40e3-99da-f9642fb62d48",
   "metadata": {},
   "source": [
    "### `COMMIT`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61a56411-2f8b-4201-96b9-88f2429a1527",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('COMMIT')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7479e28a-d630-4f9c-b3a7-723ddbbbc011",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `START TRANSACTION`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae72271d-387c-4971-8484-330f3d5b6db9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "cursor.execute('START TRANSACTION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5393c844-4537-4e85-8812-534eadd5305f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(f'INSERT INTO numbers VALUES (40)')\n",
    "cursor.execute(f'INSERT INTO numbers VALUES (50)')\n",
    "\n",
    "show_numbers()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b64e1118-fec8-4098-9dd0-63f37dbe35c6",
   "metadata": {},
   "source": [
    "### `ROLLBACK`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9db8514a-b17d-4cbf-bd8a-d0178fa2294b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('ROLLBACK')\n",
    "\n",
    "show_numbers()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9288e7a-e8ea-4e5f-8a10-c852f55c4828",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `START TRANSACTION`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae00c736-2caa-4143-b2fe-e7d8459bf6b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('START TRANSACTION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d05b5670-d2ed-49c9-a246-224dcfc5877b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(f'INSERT INTO numbers VALUES (60)')\n",
    "cursor.execute(f'INSERT INTO numbers VALUES (70)')\n",
    "\n",
    "show_numbers()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bc3646c-21f6-469f-bb0e-28d28a4316e8",
   "metadata": {},
   "source": [
    "### `COMMIT`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cf5f7f7-c3de-4253-b48f-f89e0285f49a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('COMMIT')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa07c2c4-d484-4798-b281-4fe4954f9a2b",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `START TRANSACTION`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c88a61c6-649b-400a-b901-43cee4b20f84",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('START TRANSACTION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cd8271f-4d5c-48c3-8dab-2b1dc23172b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(f'INSERT INTO numbers VALUES (80)')\n",
    "cursor.execute(f'INSERT INTO numbers VALUES (90)')\n",
    "\n",
    "show_numbers()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d2e15fe-4167-41df-afb8-832bce39d343",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `START TRANSACTION` -- implicit commit"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3304587e-85de-4259-96ad-5bd5c5931f17",
   "metadata": {},
   "source": [
    "#### SQL commands that cause an **implicit commit** include `ALTER`, `BEGIN`, `CREATE`, `DROP`, `START TRANSACTION`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cb11447-1e18-45ef-aee1-46c449c5d14b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('START TRANSACTION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62d3b770-1efb-4fd9-8b00-e36d2c8ce082",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "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
}