{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2caf5e9a-01de-4e1e-83d7-1d98eb37ab13",
   "metadata": {},
   "source": [
    "# Transactions (Connection Methods Version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78b29880-1f0a-4d08-8f17-7e67da1babd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4180db3-341b-4d6b-bc46-093c34cd6665",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_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 = df_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",
    "    CREATE TABLE numbers\n",
    "    (\n",
    "        field INT NOT NULL,\n",
    "        PRIMARY KEY (field)\n",
    "    )\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49e44807-9da7-43ee-94e2-d72ca7e06a06",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.in_transaction"
   ]
  },
  {
   "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": [
    "conn.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": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "570845c8-16f4-4c58-9981-634de2e98369",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `start_transaction()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae72271d-387c-4971-8484-330f3d5b6db9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "conn.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": [
    "conn.rollback()\n",
    "\n",
    "show_numbers()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6af4107-f6b1-4e89-81ac-f9ed4c705f6f",
   "metadata": {},
   "source": [
    "#### **WARNING:** `rollback()` does not implicitly end the previous transaction."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c2964bd-e469-4302-84a7-210f906b0b47",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `start_transaction()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae00c736-2caa-4143-b2fe-e7d8459bf6b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.start_transaction()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c00a8f0-a531-45a2-868e-174709cb93fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.in_transaction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "710ff449-c0fd-4012-b65f-be3df5e26f5a",
   "metadata": {},
   "source": [
    "### `commit()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ba9af04-4db5-4db0-b898-d9f551147f92",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "\n",
    "conn.in_transaction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c414be3a-999e-4c4a-ac84-0e8bd3ecce94",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `start_transaction()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae6bfccc-e2d1-43f1-b92f-9f68b42f5333",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.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": "3971fbdd-7eff-40ab-8145-3c9efb832b36",
   "metadata": {},
   "source": [
    "### `commit()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cf5f7f7-c3de-4253-b48f-f89e0285f49a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f42fb8a0-f451-4530-8382-b4dec3ebe3a0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `start_transaction()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4c4ae8b-a83d-4086-bb4b-aff8d346380e",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.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": "0154bb10-f911-43a4-ad8b-6a67e61506c4",
   "metadata": {},
   "source": [
    "#### **WARNING:** `start_transaction()` does not implicitly end the previous transaction."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "370adf98-abad-4c71-9121-f6573ac4a980",
   "metadata": {
    "tags": []
   },
   "source": [
    "### `start_transaction()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cb11447-1e18-45ef-aee1-46c449c5d14b",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.start_transaction()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad082c5c-4e2c-44d5-9943-b90ee480fb8d",
   "metadata": {},
   "source": [
    "### `commit()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f7e6418-2ff6-4e02-8902-37cac7b5f947",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62d3b770-1efb-4fd9-8b00-e36d2c8ce082",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cec2469-1e8d-4aef-8538-e29805384f10",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}