getting lastrowid when executing todb()

get lastrowid with petl.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>foo</th>\n",
       "<th>bar</th>\n",
       "<th>baz</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td style='text-align: right'>81</td>\n",
       "<td>apples</td>\n",
       "<td style='text-align: right'>0.025010755222666936</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>35</td>\n",
       "<td>pears</td>\n",
       "<td style='text-align: right'>0.22321073814882275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>94</td>\n",
       "<td>apples</td>\n",
       "<td style='text-align: right'>0.6766994874229113</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+-----+----------+----------------------+\n",
       "| foo | bar      | baz                  |\n",
       "+=====+==========+======================+\n",
       "|  81 | 'apples' | 0.025010755222666936 |\n",
       "+-----+----------+----------------------+\n",
       "|  35 | 'pears'  |  0.22321073814882275 |\n",
       "+-----+----------+----------------------+\n",
       "|  94 | 'apples' |   0.6766994874229113 |\n",
       "+-----+----------+----------------------+"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Goal: save this petl table to a database table \n",
    "import petl as etl\n",
    "tbl = etl.dummytable(3, seed=42)\n",
    "tbl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the database connection\n",
    "import pymysql\n",
    "connection = pymysql.connect(host='172.17.0.2',\n",
    "                             user='root',\n",
    "                             password='BWStevenson',\n",
    "                             database='mymaria')\n",
    "connection.cursor().execute('SET SQL_MODE=ANSI_QUOTES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The database table has an 'id' column that is generated by the database manager \n",
    "connection.cursor().execute(\"\"\"\n",
    "CREATE or REPLACE TABLE table_with_auto_increment_id (\n",
    "    id      integer unsigned auto_increment primary key,\n",
    "    foo     integer,\n",
    "    bar     text,\n",
    "    baz     float\n",
    ")\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# As a simple example, append the generated 'id's and tbl rows to this list \n",
    "inserted = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here's where the magic happens\n",
    "class CursorProxy(object):\n",
    "    \"\"\"Execute a callback after every inserted row.\"\"\"\n",
    "    def __init__(self, cursor, callback=None):\n",
    "        nop = lambda *a,**kw: 1\n",
    "        self.callback = callback or nop \n",
    "        self._cursor = cursor\n",
    "    def executemany(self, statement, parameters, **kwargs):\n",
    "        (cursor, callback) = (self._cursor, self.callback)\n",
    "        inserted = 0 \n",
    "        for row in parameters:\n",
    "            cursor.execute(statement, row)\n",
    "            callback(cursor.lastrowid, row)\n",
    "            inserted += 1\n",
    "        return inserted\n",
    "    def __getattr__(self, item):\n",
    "        return getattr(self._cursor, item)\n",
    "\n",
    "def get_cursor(*a, **kw):\n",
    "    return CursorProxy(connection.cursor(), *a, **kw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import partial\n",
    "\n",
    "# Callbacks should not propagate the id before commit() !!!\n",
    "def carelessly_process_inserted_row(rowid, row):\n",
    "    inserted.append([rowid,row])\n",
    "\n",
    "carelessly = partial(get_cursor, carelessly_process_inserted_row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "etl.todb(tbl, carelessly, 'table_with_auto_increment_id', commit=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>foo</th>\n",
       "<th>bar</th>\n",
       "<th>baz</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+----+-----+-----+-----+\n",
       "| id | foo | bar | baz |\n",
       "+====+=====+=====+=====+"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.rollback()  \n",
    "etl.fromdb(connection, \"select * from table_with_auto_increment_id\")\n",
    "# no rows in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[1, (81, 'apples', 0.025010755222666936)],\n",
       " [2, (35, 'pears', 0.22321073814882275)],\n",
       " [3, (94, 'apples', 0.6766994874229113)]]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inserted # Ruh Roh!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "inserted = []\n",
    "def judiciously_process_inserted_row(rowid, row):\n",
    "    connection.commit()\n",
    "    inserted.append([rowid,row])\n",
    "\n",
    "judiciously = partial(get_cursor, judiciously_process_inserted_row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "etl.todb(tbl, judiciously, 'table_with_auto_increment_id', commit=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>foo</th>\n",
       "<th>bar</th>\n",
       "<th>baz</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td style='text-align: right'>4</td>\n",
       "<td style='text-align: right'>81</td>\n",
       "<td>apples</td>\n",
       "<td style='text-align: right'>0.0250108</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>5</td>\n",
       "<td style='text-align: right'>35</td>\n",
       "<td>pears</td>\n",
       "<td style='text-align: right'>0.223211</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>6</td>\n",
       "<td style='text-align: right'>94</td>\n",
       "<td>apples</td>\n",
       "<td style='text-align: right'>0.676699</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+----+-----+----------+-----------+\n",
       "| id | foo | bar      | baz       |\n",
       "+====+=====+==========+===========+\n",
       "|  4 |  81 | 'apples' | 0.0250108 |\n",
       "+----+-----+----------+-----------+\n",
       "|  5 |  35 | 'pears'  |  0.223211 |\n",
       "+----+-----+----------+-----------+\n",
       "|  6 |  94 | 'apples' |  0.676699 |\n",
       "+----+-----+----------+-----------+"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.rollback()  \n",
    "etl.fromdb(connection, \"select * from table_with_auto_increment_id\")\n",
    "# remember that id column counters increment independent of UOW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[4, (81, 'apples', 0.025010755222666936)],\n",
       " [5, (35, 'pears', 0.22321073814882275)],\n",
       " [6, (94, 'apples', 0.6766994874229113)]]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inserted "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}