demos/transaction_control.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "scientific-comedy",
   "metadata": {},
   "source": [
    "# Description\n",
    "\n",
    "This is a demonstration of the new possibilities to control transactions with `pangres` version 4.\n",
    "\n",
    "As an example we will pretend that we setup a user in a system."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "occupied-maintenance",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pangres import upsert\n",
    "from sqlalchemy import create_engine, inspect as sqla_inspect, text\n",
    "from typing import Optional\n",
    "# the line below is only for informational purposes\n",
    "# this is automatically imported in notebooks/IPython\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regional-modern",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Helpers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32d537c1-3a4d-4e0d-a467-43be15c91773",
   "metadata": {},
   "source": [
    "## Generic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "46fcfb29-6ef0-4a25-8af8-969ce9c0582c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def version_equal_or_greater_than(version_string, minimal_version_string) -> bool:\n",
    "    # different implementations of this function depending on the python version\n",
    "    # check if Python >= 3.10\n",
    "    import sys\n",
    "    py_v = sys.version_info\n",
    "    py_gt3_10 = (py_v.major > 3) or ((py_v.major >= 3) and (py_v.minor >= 10))\n",
    "\n",
    "    # for Python >= 3.10 use packaging.version otherwise use distutils.version.LooseVersion\n",
    "    if py_gt3_10:\n",
    "        from packaging import version\n",
    "        return version.parse(version_string) >= version.parse(minimal_version_string)\n",
    "    else:\n",
    "        from distutils.version import LooseVersion\n",
    "        return LooseVersion(version_string) >= LooseVersion(minimal_version_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "832b8ab1-1356-4e7f-bd5a-cfdfe4087cbe",
   "metadata": {},
   "source": [
    "## SQL helpers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "normal-material",
   "metadata": {},
   "outputs": [],
   "source": [
    "def table_exists(connection, table_name, schema=None) -> bool:\n",
    "    insp = sqla_inspect(connection)\n",
    "    import sqlalchemy\n",
    "    if version_equal_or_greater_than(sqlalchemy.__version__, '1.4.0'):\n",
    "        return insp.has_table(table_name=table_name, schema=schema)\n",
    "    else:\n",
    "        return table_name in insp.get_table_names(schema=schema)\n",
    "\n",
    "\n",
    "def drop_table(engine, table_name, schema=None):\n",
    "    with engine.connect() as connection:\n",
    "        # avoid MySQL warnings when table does not exist by checking beforehand\n",
    "        if not table_exists(connection=connection, table_name=table_name,\n",
    "                            schema=schema):\n",
    "            return\n",
    "        ns = f'{schema}.{table_name}' if schema is not None else table_name\n",
    "        connection.execute(text(f'DROP TABLE {ns};'))\n",
    "        if hasattr(connection, 'commit'):\n",
    "            connection.commit()\n",
    "\n",
    "\n",
    "def display_table(connection, table_name, schema=None):\n",
    "    ns = f'{schema}.{table_name}' if schema is not None else table_name\n",
    "    query = text(f'SELECT * FROM {ns}')\n",
    "    try:\n",
    "        display(pd.read_sql(query, con=connection, index_col='id'))\n",
    "    except Exception:\n",
    "        if not table_exists(connection=connection, table_name=table_name):\n",
    "            print('Could not display table as it does not exist')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d82afb9-227c-4ee7-808a-2957f08c4af4",
   "metadata": {},
   "source": [
    "## Dummy helpers for imitating a Python-based app\n",
    "\n",
    "This will be used to make the demo a bit more realistic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d8a3f3d2-fa54-41d4-a070-25c34b760db1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def setup_user(id_:int):\n",
    "    \"\"\"\n",
    "    Dummy function that pretends to setup a user account in some system\n",
    "    \"\"\"\n",
    "    print(f'User with id {id_} created')\n",
    "\n",
    "\n",
    "def add_user_to_db(connection, id_:int, name:str,  table_name:str,\n",
    "                   schema:Optional[str]=None):\n",
    "    \"\"\"\n",
    "    Uses pangres to add a user to a database\n",
    "    \"\"\"\n",
    "    df = pd.DataFrame([{'id':id_, 'name':name}]).set_index('id')\n",
    "    upsert(con=connection, df=df, table_name=table_name, if_row_exists='update',\n",
    "           chunksize=1000, create_table=True)  # default\n",
    "    print(f'User with id {id_} added to the db')\n",
    "\n",
    "\n",
    "def post_user_setup_operation(id_:int, simulate_error:bool=False):\n",
    "    \"\"\"\n",
    "    Dummy function that pretends to do an operation after a user\n",
    "    has been created \n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    simulate_error\n",
    "        If True, raises an error to simulate the failure of the creation\n",
    "        of the user's account\n",
    "    \"\"\"\n",
    "    if simulate_error:\n",
    "        raise AssertionError('Could not do post setup operation')\n",
    "    else:\n",
    "        print(f'Post setup operation for user with id {id_} successful')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "useful-agency",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "strategic-sugar",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection_string = \"sqlite:///:memory:\"\n",
    "# connection_string = \"postgresql://username:password@localhost:5432/postgres\"\n",
    "# connection_string = \"mysql+pymysql://username:password@localhost:3306/db\"\n",
    "schema = None  # only relevant for PostgreSQL, leave this to None for other database types\n",
    "\n",
    "# add parameter future=True to try out sqlalchemy v2\n",
    "engine = create_engine(connection_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sexual-destination",
   "metadata": {},
   "source": [
    "# 1. With an explicit transaction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "338d7488-c4fe-400f-8f03-c399b854de6e",
   "metadata": {},
   "source": [
    "**Important notes on transactions**:\n",
    "\n",
    "* All transactions operate at **connection level** and not at **engine level**.\n",
    "* Depending on several factors such as these below, not all operations in a transaction may be rolled back\n",
    "    * driver (e.g. it seems that we cannot rollback a CREATE TABLE operation with Python's sqlite3 driver)\n",
    "    * SQL flavor (e.g. MySQL does not support rollback for DDL so rolling back the creation of a table is also not possible)\n",
    "    * database version"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8579c36-4cf7-48bf-ad58-f24a8bef95fd",
   "metadata": {},
   "source": [
    "Below are some examples of how you can use transactions together with `pangres`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fee3cbe1-a30d-41f6-aa1d-831e7c357788",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 1.1. With a context manager for the connection and the transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "seventh-reference",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User with id 1 created\n",
      "User with id 1 added to the db\n",
      "Post setup operation for user with id 1 successful\n"
     ]
    },
    {
     "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>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name\n",
       "id     \n",
       "1   bar"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "id_, table_name = 1, 'example1'\n",
    "drop_table(engine=engine, schema=schema, table_name=table_name)  # reset tests\n",
    "\n",
    "# start a connection that will autoclose when we are done (`with` statement)\n",
    "with engine.connect() as connection:\n",
    "    # start a transaction that will autocommit when we are done or rollback (`with` statement)\n",
    "    # and then close\n",
    "    with connection.begin():\n",
    "        setup_user(id_=id_)\n",
    "        add_user_to_db(connection=connection, id_=id_, name='bar', table_name=table_name)\n",
    "        post_user_setup_operation(id_=id_)\n",
    "\n",
    "    # this needs to be outside \"with connection...\" as a commit is issued upon closing of the context manager\n",
    "    display_table(connection=connection, schema=schema, table_name=table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a83f53f-5062-4d32-85b1-caae870747a7",
   "metadata": {},
   "source": [
    "## 1.2. With a single context manager for both (shorter variant using `engine.begin`)\n",
    "\n",
    "`with engine.begin() as connection_with_trans` does the following:\n",
    "\n",
    "1. start a connection\n",
    "2. start a transaction within this connection\n",
    "3. commit if everything went fine otherwise rollback\n",
    "4. close the transaction\n",
    "5. close the connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "spiritual-spider",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User with id 1 created\n",
      "User with id 1 added to the db\n",
      "Post setup operation for user with id 1 successful\n"
     ]
    },
    {
     "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>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name\n",
       "id     \n",
       "1   bar"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "id_, table_name = 1, 'example2'\n",
    "drop_table(engine=engine, schema=schema, table_name=table_name)  # reset tests\n",
    "\n",
    "with engine.begin() as connection_with_trans:\n",
    "    setup_user(id_=id_)\n",
    "    add_user_to_db(connection=connection_with_trans, id_=id_, name='bar', table_name=table_name)\n",
    "    post_user_setup_operation(id_=id_)\n",
    "\n",
    "# this needs to be outside \"with engine.begin...\" as a commit is issued upon closing of the context manager\n",
    "with engine.connect() as connection:\n",
    "    display_table(connection=connection, schema=schema, table_name=table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d9a4e3d-75b9-4c92-99eb-7f86597e83f3",
   "metadata": {},
   "source": [
    "## 1.3. With a context manager for the connection and manual control for the transaction\n",
    "\n",
    "This will give you finer control than the two previous options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "617a1ec1-b39c-45dd-903d-4520f6de71ec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User with id 1 created\n",
      "User with id 1 added to the db\n",
      "Post setup operation for user with id 1 successful\n",
      "COMMITING\n"
     ]
    },
    {
     "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>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name\n",
       "id     \n",
       "1   bar"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "id_, table_name = 1, 'transaction_control_example'\n",
    "drop_table(engine=engine, schema=schema, table_name=table_name)  # reset tests\n",
    "\n",
    "# start a connection that will autoclose when we are done (`with` statement)\n",
    "with engine.connect() as connection:\n",
    "    trans = connection.begin()\n",
    "    try:\n",
    "        setup_user(id_=id_)\n",
    "        add_user_to_db(connection=connection, id_=id_, name='bar', table_name=table_name)\n",
    "        # change `simulate_error` to False|True to observe the behavior\n",
    "        post_user_setup_operation(id_=id_, simulate_error=False)\n",
    "    except Exception as e:\n",
    "        print(f'ERROR: {e}')\n",
    "        print('ROLLING BACK')\n",
    "        trans.rollback()\n",
    "    else:\n",
    "        # commit if no exception occured\n",
    "        print('COMMITING')\n",
    "        trans.commit()\n",
    "    finally:\n",
    "        display_table(connection=connection, schema=schema, table_name=table_name)\n",
    "        trans.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01f93d82-69f1-4c78-bc99-18b85c5d9c2e",
   "metadata": {},
   "source": [
    "## 1.4. Manual control for both the connection and transaction\n",
    "\n",
    "For even finer control"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "204330eb-ce97-4fb4-8176-5fac3241ef0e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User with id 1 created\n",
      "User with id 1 added to the db\n",
      "Post setup operation for user with id 1 successful\n",
      "COMMITING\n"
     ]
    },
    {
     "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>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>baz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name\n",
       "id     \n",
       "1   baz"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "id_, table_name = 1, 'fine_control'\n",
    "drop_table(engine=engine, table_name=table_name, schema=schema)  # reset tests\n",
    "\n",
    "\n",
    "connection = engine.connect()\n",
    "try:\n",
    "    trans = connection.begin()\n",
    "    try:\n",
    "        setup_user(id_=id_)\n",
    "        add_user_to_db(connection=connection, id_=id_, name='baz', table_name=table_name)\n",
    "        # change `simulate_error` to False|True to observe the behavior\n",
    "        post_user_setup_operation(id_=id_, simulate_error=False)\n",
    "    except Exception as e:\n",
    "        print(f'ERROR: {e}')\n",
    "        print('ROLLING BACK')\n",
    "        trans.rollback()\n",
    "    else:\n",
    "        print('COMMITING')\n",
    "        trans.commit()\n",
    "    finally:\n",
    "        display_table(connection=connection, schema=schema, table_name=table_name)\n",
    "        trans.close()\n",
    "except Exception:\n",
    "    # do something\n",
    "    pass\n",
    "finally:\n",
    "    connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa507999-367b-4d7d-9951-7269c8ea2c00",
   "metadata": {},
   "source": [
    "# 2. Without an explicit transaction (autocommit | commit-as-you-go or rollback)\n",
    "\n",
    "The functionality and features depend on the **sqlalchemy version**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbac63ee-f380-4ac9-9404-042541e3e89b",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2.1. sqlalchemy < 2.0\n",
    "\n",
    "Everything gets **autocommited** by default. So we can not rollback when operating this way.\n",
    "\n",
    "It might be possible to get similar functionality to sqlalchemy 2.0 (see subchapter \"sqlalchemy >= 2.0\") but I could not find the information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5e43fe2a-41f2-44b5-92ec-2d6828dbf08f",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(connection_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "55e4b5b6-af9c-4ba8-bc37-27ed9ec4a3c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User with id 1 created\n",
      "User with id 1 added to the db\n",
      "Post setup operation for user with id 1 successful\n",
      "User with id 2 created\n",
      "User with id 2 added to the db\n",
      "Post setup operation for user with id 2 successful\n"
     ]
    }
   ],
   "source": [
    "table_name = 'autocommit'\n",
    "drop_table(engine=engine, table_name=table_name, schema=schema)  # reset tests\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    # add a first user\n",
    "    setup_user(id_=1)\n",
    "    add_user_to_db(connection=connection, table_name=table_name, id_=1, name='foo')\n",
    "    post_user_setup_operation(id_=1)\n",
    "\n",
    "    # add a second user\n",
    "    setup_user(id_=2)\n",
    "    add_user_to_db(connection=connection, table_name=table_name, id_=2, name='foo')\n",
    "    post_user_setup_operation(id_=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ceacead-d177-44c2-bf84-b0827fa302a9",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2.2. sqlalchemy >= 2.0\n",
    "\n",
    "Here we can **commit as we go or rollback**.\n",
    "\n",
    "Until version **2.0** is out we can also use the flag **`future=True`** when creating an engine in sqlalchemy **1.4**. This is what we are doing below.\n",
    "\n",
    "This flag does not exist in sqlalchemy **1.3** so an Exception will be raised. **The rest of this notebook**, however, is **>= 1.3 compatible**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "88ecb1b5-7960-4d60-8e7f-3c5cd3774005",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine_future = create_engine(connection_string, future=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b0eeb2dd-7ab8-4f91-ae8b-91abe324a8f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User with id 1 created\n",
      "User with id 1 added to the db\n",
      "Post setup operation for user with id 1 successful\n",
      "User with id 2 created\n",
      "User with id 2 added to the db\n",
      "Post setup operation for user with id 2 successful\n",
      "!!!OOOPS rolling back for second user!!!\n"
     ]
    },
    {
     "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>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name\n",
       "id     \n",
       "1   foo"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "table_name = 'commit_as_you_go'\n",
    "drop_table(engine=engine_future, table_name=table_name, schema=schema)  # reset tests\n",
    "\n",
    "with engine_future.connect() as connection:\n",
    "    # add a first user and commit\n",
    "    setup_user(id_=1)\n",
    "    add_user_to_db(connection=connection, table_name=table_name, id_=1, name='foo')\n",
    "    post_user_setup_operation(id_=1)\n",
    "    connection.commit()\n",
    "\n",
    "    # for some reason we will rollback this second user\n",
    "    setup_user(id_=2)\n",
    "    add_user_to_db(connection=connection, table_name=table_name, id_=2, name='bar')\n",
    "    post_user_setup_operation(id_=2)\n",
    "    print('!!!OOOPS rolling back for second user!!!')\n",
    "    connection.rollback()\n",
    "\n",
    "    display_table(connection=connection, schema=schema, table_name=table_name)"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}