Transactions_DuckDB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "78915f46",
   "metadata": {},
   "source": [
    "# Transactions with `DuckDB`\n",
    "\n",
    "In this notebook, we showcase how to connect to a [DuckDB](https://duckdb.org/) database, execute queries, and run transactions using the [`DuckDB Python API`](https://duckdb.org/docs/api/python/overview). \n",
    "\n",
    "\n",
    "The notebook is based on our analog notebook [Transactions](https://github.com/BigDataAnalyticsGroup/bigdataengineering/blob/master/Transactions.ipynb), that shows transactions in the context of a [PostgreSQL](https://www.postgresql.org/) database using the [`psycopg`](https://www.psycopg.org/psycopg3/docs/index.html) package for Python.\n",
    "\n",
    "Copyright Joris Nix & Jens Dittrich, [Big Data Analytics Group](https://bigdata.uni-saarland.de/), [CC-BY-SA](https://creativecommons.org/licenses/by-sa/4.0/legalcode)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "921c7e80",
   "metadata": {},
   "source": [
    "# Setup\n",
    "\n",
    "The following cell serves as setup. We will explain the syntax in more detail below. Here, we simply connect to the database, create a new table `accounts` with attributes `id` and `balance`, and add some toy data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5101e208",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "\n",
    "def reset_database():\n",
    "    # establish connection to 'accounts' database\n",
    "    conn = duckdb.connect(database='accounts.duckdb')\n",
    "\n",
    "    # drop table if it exists\n",
    "    conn.execute(\"\"\"DROP TABLE IF EXISTS accounts;\"\"\")\n",
    "\n",
    "    # create accounts table\n",
    "    conn.execute(\"\"\"\n",
    "    CREATE TABLE accounts (\n",
    "        id int PRIMARY KEY,\n",
    "        balance float(2)\n",
    "    );\"\"\")\n",
    "\n",
    "    # insert sample data into accounts table\n",
    "    conn.execute(\"\"\"\n",
    "    INSERT INTO accounts VALUES\n",
    "        (1, 2000.0),\n",
    "        (2, 520.0),\n",
    "        (3, 470.0),\n",
    "        (4, 1700.0),\n",
    "        (5, 2400.0);\n",
    "    \"\"\")\n",
    "    \n",
    "    # close connection\n",
    "    conn.close()\n",
    "reset_database()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e52716f",
   "metadata": {},
   "source": [
    "# Basics\n",
    "\n",
    "### Connection\n",
    "In order to send queries to the database, we first need to establish a `connection`.The `connect()` method lets you decide between an **in-memory database** and a **persistent database** in form of a DuckDB file, by specifying the `database` parameter.\n",
    "```python\n",
    "conn = duckdb.connect(database=':memory:') # creates an in-memory database\n",
    "conn = duckdb.connect(databaes='my-db.duckdb') # creates a persistent database file called 'my-db.duckdb'\n",
    "```\n",
    "\n",
    "In our setup above, we create a persistent database. The default is creating an in-memory database.\n",
    "\n",
    "### Querying\n",
    "We can send queries to the database using the connection object `conn`. In contrast to the PostgreSQL database adapter for Python, we do not need a _cursor_ that has to be openend from an established connection. The connection directly allows us send queries (`execute()`) and retrieve results (`fetchone()`, `fetchall()`). Results are always tuples, even if they consist of a single integer. We have to consider this when parsing the results. In addition,\n",
    "DuckDB provides multiple additional methods that can be used to convert query results into well-established formats, e.g., `fetchdf()` fetches the data as a Pandas DataFrame. When we are done, we close the connection using the `close()` method (this happens implicitly if the connection goes out of scope).\n",
    "\n",
    "The following example shows how to query the database for an entire table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ff553022",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The query returned the following tuples:\n",
      "[(1, 2000.0), (2, 520.0), (3, 470.0), (4, 1700.0), (5, 2400.0)]\n"
     ]
    }
   ],
   "source": [
    "# establish connection to 'accounts' database\n",
    "conn = duckdb.connect(database='accounts.duckdb')\n",
    "\n",
    "# define a SQL query\n",
    "q_accounts = \"\"\"SELECT * FROM accounts;\"\"\"\n",
    "\n",
    "# execute the query using the connection\n",
    "conn.execute(q_accounts)\n",
    "\n",
    "# retrieve the tuples\n",
    "accounts = conn.fetchall()\n",
    "\n",
    "# print sorted results\n",
    "print(f\"The query returned the following tuples:\\n{sorted(accounts)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "35ecf83b",
   "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>id</th>\n",
       "      <th>balance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>470.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  balance\n",
       "0   1   2000.0\n",
       "1   2    520.0\n",
       "2   3    470.0\n",
       "3   4   1700.0\n",
       "4   5   2400.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# query the database again and retrieve the tuples as a Pandas DataFrame\n",
    "conn.execute(q_accounts)\n",
    "\n",
    "accounts_df = conn.fetchdf()\n",
    "display(accounts_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ddfb04b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# close the current connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43784f57",
   "metadata": {},
   "source": [
    "# Read-Only Connection\n",
    "\n",
    "The DuckDB allows us the set a connection to **read_only** by specifying the corresponding paramter. If we set a connection to read-only mode, write operations will not be executed and instead will raise an `InvalidInputException`. However, this only works for **persistent databases**.\n",
    "\n",
    "The following example demonstrates this behavior."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fb469e47",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ERROR: The query failed due to the connection being read-only.\n"
     ]
    }
   ],
   "source": [
    "# establish a read-only connection\n",
    "conn = duckdb.connect(database='accounts.duckdb', read_only=True)\n",
    "\n",
    "try:\n",
    "    # try to insert a new tuple into the table\n",
    "    conn.execute(\"INSERT INTO accounts VALUES (6, 100000.0);\")\n",
    "    \n",
    "    # if successful, retrieve newly added tuple\n",
    "    conn.execute(\"SELECT * FROM accounts WHERE id=6;\")\n",
    "    print(conn.fetchone())\n",
    "except duckdb.InvalidInputException:\n",
    "    print(f\"ERROR: The query failed due to the connection being read-only.\")\n",
    "    \n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ca62793",
   "metadata": {},
   "source": [
    "# Transactions\n",
    "\n",
    "As a default, every **execute()** statement sent to the database has an immediate effect, i.e. each statement is an individual transaction that is implicitly commited upon successful completion. However, DuckDB also provides functionality to bundle multiple statements into one transaction.\n",
    "\n",
    "In particular, the DuckDB Python adapter provides the following functions for transactional processing:\n",
    "* `begin()`: Start a new transaction.\n",
    "* `commit()`: Commit changes performed within a transaction.\n",
    "* `rollback()`: Roll back changes performed within a transaction."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8c8440d",
   "metadata": {},
   "source": [
    "## Immediate Commit\n",
    "The following example shows that without starting a transactions, each modification is immediately visible to other connections to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "43ad40b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Due to immediate commit, the tuple with id=6 is already visible to the other connection: [(6, 237.0)]\n"
     ]
    }
   ],
   "source": [
    "reset_database()\n",
    "\n",
    "# establish two connections\n",
    "conn1 = duckdb.connect(database='accounts.duckdb')\n",
    "conn2 = duckdb.connect(database='accounts.duckdb')\n",
    "\n",
    "# insert a new value using the first connection\n",
    "conn1.execute(\"INSERT INTO accounts VALUES (6, 237.0);\")\n",
    "# retrieve this new tuple using the second connection\n",
    "conn2.execute(\"SELECT * FROM accounts WHERE id=6;\")\n",
    "\n",
    "print(f'Due to immediate commit, the tuple with id=6 is already visible to the other connection: {conn2.fetchall()}')\n",
    "\n",
    "conn1.close()\n",
    "conn2.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a7966ad",
   "metadata": {},
   "source": [
    "##  Begin & Commit Transactions\n",
    "The example below transfers money from one account to another inside a transaction. It is equivalent to running the following SQL statements:\n",
    "\n",
    "```SQL\n",
    "BEGIN;\n",
    "UPDATE accounts SET balance = balance - 100 WHERE id=3;\n",
    "UPDATE accounts SET balance = balance + 100 WHERE id=1;\n",
    "COMMIT;\n",
    "```\n",
    "The example also shows that as long as the transaction is not committed, changes are not visible to other connections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6e3df59b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Account balances observed by each connection before COMMIT:\n",
      "Transaction 1: [(1, 2100.0), (3, 370.0)]\n",
      "Transaction 2: [(1, 2000.0), (3, 470.0)]\n",
      "Changes not yet visible to connection 2.\n",
      "--Transaction 1 commited--\n",
      "Account balances observed by each connection after COMMIT:\n",
      "Transaction 1: [(1, 2100.0), (3, 370.0)]\n",
      "Transaction 2: [(1, 2100.0), (3, 370.0)]\n",
      "Changes visible to connection 2.\n"
     ]
    }
   ],
   "source": [
    "reset_database()\n",
    "\n",
    "# establish two connections\n",
    "conn1 = duckdb.connect(database='accounts.duckdb')\n",
    "conn2 = duckdb.connect(database='accounts.duckdb')\n",
    "\n",
    "# start a new transaction\n",
    "conn1.begin()\n",
    "# update balance of account 3\n",
    "conn1.execute(\"\"\"UPDATE accounts SET balance = balance - 100 WHERE id=3;\"\"\") \n",
    "# update balance of account 1\n",
    "conn1.execute(\"\"\"UPDATE accounts SET balance = balance + 100 WHERE id=1;\"\"\")\n",
    "# compare states visible to both connections (transactions)\n",
    "q_acc = \"\"\"SELECT * FROM accounts WHERE id=1 OR id=3;\"\"\"\n",
    "conn1.execute(q_acc)\n",
    "conn2.execute(q_acc)\n",
    "print(f\"Account balances observed by each connection before COMMIT:\\n\"\\\n",
    "      f\"Transaction 1: {conn1.fetchall()}\\n\"\\\n",
    "      f\"Transaction 2: {conn2.fetchall()}\\n\"\\\n",
    "      f\"Changes not yet visible to connection 2.\"\\\n",
    "     )\n",
    "\n",
    "# explicitly commit the changes performed by the first connection\n",
    "conn1.commit()\n",
    "print(\"--Transaction 1 commited--\")\n",
    "\n",
    "# compare states visible to both connections again\n",
    "conn1.execute(q_acc)\n",
    "conn2.execute(q_acc)\n",
    "print(f\"Account balances observed by each connection after COMMIT:\\n\"\\\n",
    "      f\"Transaction 1: {conn1.fetchall()}\\n\"\\\n",
    "      f\"Transaction 2: {conn2.fetchall()}\\n\"\\\n",
    "      f\"Changes visible to connection 2.\"\\\n",
    "     )\n",
    "# close both connections\n",
    "conn1.close()\n",
    "conn2.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46654e3c",
   "metadata": {},
   "source": [
    "## Rollback Transactions\n",
    "\n",
    "The next example shows a similar transaction as above. The only difference is that instead of making the changes persistent, we decide to `ABORT` the transaction by calling `rollback()` on the connection. It is equivalent to running the following SQL statements:\n",
    "```SQL\n",
    "BEGIN;\n",
    "UPDATE accounts SET balance = balance - 100 WHERE id=3;\n",
    "UPDATE accounts SET balance = balance + 100 WHERE id=1;\n",
    "ABORT;\n",
    "```\n",
    "All changes performed by the aborted transaction must not become durable in the database. Note that if we `close()` an open connection, `rollback()` will be performed implicitly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "db507e21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Account balances observed by each connection before COMMIT:\n",
      "Transaction 1: [(1, 2100.0), (3, 370.0)]\n",
      "Transaction 2: [(1, 2000.0), (3, 470.0)]\n",
      "Changes not yet visible to connection 2.\n",
      "--Transaction 1 aborted--\n",
      "Account balances observed by each connection after COMMIT:\n",
      "Transaction 1: [(1, 2000.0), (3, 470.0)]\n",
      "Transaction 2: [(1, 2000.0), (3, 470.0)]\n",
      "Changes of connection 1 undone.\n"
     ]
    }
   ],
   "source": [
    "reset_database()\n",
    "\n",
    "# establish two connections\n",
    "conn1 = duckdb.connect(database='accounts.duckdb')\n",
    "conn2 = duckdb.connect(database='accounts.duckdb')\n",
    "\n",
    "# start a new transaction\n",
    "conn1.begin()\n",
    "\n",
    "# update balance of account 3\n",
    "conn1.execute(\"\"\"UPDATE accounts SET balance = balance - 100 WHERE id=3;\"\"\") \n",
    "# update balance of account 1\n",
    "conn1.execute(\"\"\"UPDATE accounts SET balance = balance + 100 WHERE id=1;\"\"\")\n",
    "# compare states visible to both connections (transactions)\n",
    "q_acc = \"\"\"SELECT * FROM accounts WHERE id=1 OR id=3;\"\"\"\n",
    "conn1.execute(q_acc)\n",
    "conn2.execute(q_acc)\n",
    "print(f\"Account balances observed by each connection before COMMIT:\\n\"\\\n",
    "      f\"Transaction 1: {conn1.fetchall()}\\n\"\\\n",
    "      f\"Transaction 2: {conn2.fetchall()}\\n\"\\\n",
    "      f\"Changes not yet visible to connection 2.\"\\\n",
    "     )\n",
    "\n",
    "# explicitly rollback the changes performed by the first connection\n",
    "conn1.rollback()\n",
    "print(\"--Transaction 1 aborted--\")\n",
    "\n",
    "# compare states visible to both connections again\n",
    "conn1.execute(q_acc)\n",
    "conn2.execute(q_acc)\n",
    "print(f\"Account balances observed by each connection after COMMIT:\\n\"\\\n",
    "      f\"Transaction 1: {conn1.fetchall()}\\n\"\\\n",
    "      f\"Transaction 2: {conn2.fetchall()}\\n\"\\\n",
    "      f\"Changes of connection 1 undone.\"\\\n",
    "     )\n",
    "# close both connections\n",
    "conn1.close()\n",
    "conn2.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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}