Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. _tutorial:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutorial\n",
    "\n",
    "This tutorial is created directly from an ipython notebook. If you'd like to interactively run this tutorial, do the following:\n",
    "\n",
    "```\n",
    "# Go to your pgmock directory with the cloned code\n",
    "cd pgmock\n",
    "make setup\n",
    "jupyter notebook Tutorial.ipynb\n",
    "# Follow the instructions to open the notebook in your browser\n",
    "```\n",
    "\n",
    "The setup for this ipython notebook is below and includes the creation of the testing database and required imports."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import testing.postgresql\n",
    "import sqlalchemy\n",
    "import pgmock\n",
    "import pgmock.exceptions\n",
    "\n",
    "test_db = testing.postgresql.Postgresql()\n",
    "test_engine = sqlalchemy.create_engine(test_db.url())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Terminology and Key Concepts\n",
    "\n",
    "It's useful to understand some commonly-used terminology and key concepts before going\n",
    "into the ``pgmock`` tutorial.\n",
    "\n",
    "**Selectors** - ``pgmock`` *selectors* are objects used to obtain portions of SQL within a query. Selectors\n",
    "can represent subqueries, tables, select statements, and other types of SQL expressions. All selectors in ``pgmock`` are chainable, meaning they can be called after one another\n",
    "to refine a selection.\n",
    "\n",
    "**Patching** - ``pgmock`` *patching* is concerned with converting SQL select expressions or tables into\n",
    "postgres ``VALUES`` expressions. For example, this statement:\n",
    "\n",
    "```\n",
    "SELECT c1 from test_table\n",
    "```\n",
    "\n",
    "could have ``test_table`` patched to be:\n",
    "\n",
    "```\n",
    "SELECT c1 from (VALUES ('hi!'),('hello!') AS test_table(c1)\n",
    "```\n",
    "\n",
    "Patching can also take place on joins, subqueries, ``INSERT INTO`` expressions, and other SQL expressions that\n",
    "allow postgres ``VALUES``.\n",
    "\n",
    "**Rendering** - Whenever SQL is obtained from a query or modified with a patch, it is *rendered*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Obtaining Expressions in a Query\n",
    "\n",
    "Obtaining specific portions of a query can be useful when wanting to execute smaller parts of your SQL or for (later in the tutorial) patching out portions of your SQL in tests.\n",
    "\n",
    "This part of the tutorial demonstrates obtaining expressions inside a query using the ``pgmock.sql`` function. ``pgmock.sql`` takes a SQL string and a selector as input. The expression in the query referenced by the selector is then rendered and returned as a string. Uses of different selectors to obtain expressions is shown in the following.\n",
    "\n",
    "### Obtaining Statements\n",
    "\n",
    "The ``pgmock.statement`` selector can be used to render ranges of statements in a query as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * from table1\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT * from table1;\n",
    "SELECT * from table2;\n",
    "SELECT * from table3\n",
    "'''\n",
    "\n",
    "# Obtain the first statement\n",
    "print(pgmock.sql(query, pgmock.statement(0)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * from table1;\n",
      "SELECT * from table2;\n",
      "SELECT * from table3\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Obtain the first three statements using a range of 0 - 3 (3 is the exclusive ending index)\n",
    "print(pgmock.sql(query, pgmock.statement(0, 3)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found 3 statements. Range of [4:5] is out of bounds. The following SQL was used: \n",
      "\n",
      "SELECT * from table1;\n",
      "SELECT * from table2;\n",
      "SELECT * from table3\n",
      "\n",
      "View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.\n"
     ]
    }
   ],
   "source": [
    "# Going out of range will throw an exception\n",
    "try:\n",
    "    pgmock.sql(query, pgmock.statement(4))\n",
    "except pgmock.exceptions.StatementParseError as exc:\n",
    "    print(exc)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    Rendering statements splits the SQL with the semicolon character. If the semicolon appears in any comments or string literals, this can interfere with obtaining statements. Use ``safe_mode=True`` to `pgmock.sql` in order to fix this issue if it happens. This comes at a performance cost, and more details can be read at `pgmock.config.set_safe_mode`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Obtaining Subqueries\n",
    "\n",
    "The ``pgmock.subquery`` selector can be used to render subqueries in SQL as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM test_table\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;'\n",
    "\n",
    "# Obtain the subquery named \"sub\"\n",
    "print(pgmock.sql(query, pgmock.subquery('sub')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No subquery found for alias \"bad\". The following SQL was used: \n",
      "\n",
      "SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;\n",
      "\n",
      "View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.\n"
     ]
    }
   ],
   "source": [
    "# An exception will be raised if the subquery alias cannot be found\n",
    "try:\n",
    "    pgmock.sql(query, pgmock.subquery('bad'))\n",
    "except pgmock.exceptions.NoMatchError as exc:\n",
    "    print(exc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nested matches were found in your selection. The following multiple matches of SQL were used: \n",
      "\n",
      "SELECT * FROM test_table\n",
      "---\n",
      "SELECT * FROM (SELECT * FROM test_table) sub\n",
      "\n",
      "View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.\n"
     ]
    }
   ],
   "source": [
    "# pgmock does not handle the case when the same subquery alias is used twice or nested\n",
    "query = 'SELECT sub.c1, sub.c2 FROM (SELECT * FROM (SELECT * FROM test_table) sub) sub;'\n",
    "\n",
    "try:\n",
    "    pgmock.sql(query, pgmock.subquery('sub'))\n",
    "except pgmock.exceptions.MultipleMatchError as exc:\n",
    "    print(exc)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    Almost all ``pgmock`` selectors can only render exactly one match like the subquery selector above. These cases will not be illustrated for later examples. Note that there is a distinction between \"rendering\" selectors with ``pgmock.sql`` like above and patching them with ``pgmock.patch``. ``pgmock.patch`` supports patching multiple occurences in a selection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Obtaining Insert Into Expressions\n",
    "\n",
    "The ``pgmock.insert_into`` selector can be used to render ``INSERT INTO`` expressions in SQL as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO table_a\n",
      "SELECT * FROM other_table\n"
     ]
    }
   ],
   "source": [
    "query = '''INSERT INTO table_a\n",
    "SELECT * FROM other_table;\n",
    "\n",
    "INSERT INTO table_b\n",
    "SELECT * FROM other_table'''\n",
    "\n",
    "# The insert_into selector takes the table name that is inserted into\n",
    "print(pgmock.sql(query, pgmock.insert_into('table_a')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to obtain the body of the expression, the ``body`` selector can be chained to the ``insert_into`` selector."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM other_table\n"
     ]
    }
   ],
   "source": [
    "print(pgmock.sql(query, pgmock.insert_into('table_a').body()))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    All selectors can be chained like the above example where it makes sense. For example, one could obtain the ``INSERT INTO`` expression of the first statement with ``pgmock.statement(0).insert_into('table_a')``\n",
    "    \n",
    "    Along with that, multiple selectors can also be provided to ``pgmock.sql`` or ``pgmock.sql_file``, which in turn will chain them underneath the hood. For example, doing:\n",
    "    \n",
    "    .. code-block:: python\n",
    "    \n",
    "        pgmock.sql(query, pgmock.insert_into('table_a'), pgmock.body())\n",
    "        \n",
    "    is equivalent to:\n",
    "\n",
    "    .. code-block:: python\n",
    "    \n",
    "        pgmock.sql(query, pgmock.insert_into('table_a').body())\n",
    "        \n",
    "    It is up to the user to pick which style they prefer. ``pgmock`` suggests using multiple arguments when applying multiple ``pgmock.patch`` selectors and using chaining syntax for all other cases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Obtaining Create Table As Expressions\n",
    "\n",
    "The ``pgmock.create_table_as`` selector can be used to render ``CREATE TABLE AS`` expressions in SQL as shown below. It's similar to ``pgmock.insert_into``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE table_a AS (\n",
      "  SELECT * FROM other_table\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "query = '''CREATE TABLE table_a AS (\n",
    "  SELECT * FROM other_table\n",
    ");\n",
    "\n",
    "CREATE TABLE table_b AS SELECT * FROM other_table'''\n",
    "\n",
    "# The insert_into selector takes the table name that is inserted into\n",
    "print(pgmock.sql(query, pgmock.create_table_as('table_a')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to obtain the body of the ``CREATE TABLE AS`` expression, the ``body`` selector can be chained to the ``create_table_as`` selector."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " (\n",
      "  SELECT * FROM other_table\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "print(pgmock.sql(query, pgmock.create_table_as('table_a').body()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Obtaining Common Table Expressions (CTEs)\n",
    "\n",
    "The ``pgmock.cte`` selector can be used to render common table expressions (CTEs) in SQL as shown below. It's similar to ``pgmock.subquery``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT * FROM table1\n",
      "\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "WITH cte1 AS (\n",
    "    SELECT * FROM table1\n",
    "), cte2 AS (\n",
    "    SELECT * FROM table2\n",
    ")\n",
    "'''\n",
    "\n",
    "# Obtain the CTE aliased \"cte1\"\n",
    "print(pgmock.sql(query, pgmock.cte('cte1')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT * FROM table2\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Obtain the \"cte2\" CTE\n",
    "print(pgmock.sql(query, pgmock.cte('cte2')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Patching Expressions in a Query\n",
    "\n",
    "As mentioned before, patching parts of a query will transform the relevant expression into [Postgres VALUES](https://www.postgresql.org/docs/9.5/static/sql-values.html). Why is this useful?\n",
    "\n",
    "1. When using ``VALUES`` lists, there is no need to create database tables and data before executing the query\n",
    "2. Testing queries will run much faster in automated tests since there is no overhead of database setup and teardown\n",
    "3. Only data that is relevant to the test can be patched, resulting in smaller and more readable tests. ``pgmock`` allows other useless columns to be filled in with nulls by default if desired\n",
    "\n",
    "Below are some illustrations of patching queries and running some assertions on those queries. This section uses the test engine that we created at the beginning of the tutorial."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    In an automated `pytest <https://docs.pytest.org/en/latest/>`_ test case, we'd use the fixtures from `pytest-pgsql <https://github.com/CloverHealth/pytest-pgsql>`_ when testing our queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patching Tables and Joins on Tables\n",
    "\n",
    "Tables, whether those tables are being selected or joined, can be patched with ``pgmock`` by using the ``pgmock.table`` selector. Some examples of patching tables and joins are below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT c2 FROM  (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c1,c2) WHERE c1 = 'value'\n"
     ]
    }
   ],
   "source": [
    "# Create a query and filter a column\n",
    "query = \"SELECT c2 FROM my_table WHERE c1 = 'value'\"\n",
    "\n",
    "# Create a patch for the table. The patch takes a selector,\n",
    "# rows (a list of lists for each column or a list of dictionaries keyed on column),\n",
    "# and column names\n",
    "patch = pgmock.patch(pgmock.table('my_table'),\n",
    "                     [('dummy_data', 'data'), ('value', 'hello'), ('value', 'hi')],\n",
    "                     ['c1', 'c2'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute the SQL and verify that filtering happened correctly\n",
    "results = list(test_engine.execute(sql))\n",
    "assert results == [('hello',), ('hi',)]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    `pgmock.patch` accepts lists of dictionaries as rows of input as well. For example,\n",
    "    \n",
    "    .. code-block:: python\n",
    "    \n",
    "        patch = pgmock.patch(pgmock.table('my_table'),\n",
    "                     [{'c1': 'dummy_data', 'c2': 'data'}, {'c1': 'value', 'c2': 'hello'}, {'c1': 'value', 'c2': 'hi'}],\n",
    "                     ['c1', 'c2'])\n",
    "                     \n",
    "    is equivalent to the example from above.\n",
    "    \n",
    "    Using this format allows for only specifying values for columns that matter. All missing columns will be filled with null values. Both formats of patching will be used throughout the rest of the tutorial."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to selectors, patches are also chainable or can be provided as multiple arguments to ``pgmock.sql`` or ``pgmock.sql_file``. This is useful for the case of patching multiple expressions in a query. For example, we can patch a table and a join on another table like so."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT one.c1 FROM  (VALUES ('val1.1'),('val1.2')) AS one(c1) JOIN  (VALUES ('val1.1'),('val1.2'),('val1.3')) AS two(c1) ON one.c1 = two.c1\n"
     ]
    }
   ],
   "source": [
    "# Create a query with a join\n",
    "query = 'SELECT one.c1 FROM t1 one JOIN t2 two ON one.c1 = two.c1'\n",
    "\n",
    "# When making the patches, keep in mind these tables have aliases and\n",
    "# the alias must also be provided when obtaining the table\n",
    "t1_patch = pgmock.patch(\n",
    "    pgmock.table('t1', alias='one'),\n",
    "    [('val1.1',), ('val1.2',)],\n",
    "    ['c1']\n",
    ")\n",
    "\n",
    "t2_patch = pgmock.patch(\n",
    "    pgmock.table('t2', alias='two'),\n",
    "    [('val1.1',), ('val1.2',), ('val1.3',)],\n",
    "    ['c1']\n",
    ")\n",
    "\n",
    "# Render the SQL that has both tables patched\n",
    "sql = pgmock.sql(query, t1_patch, t2_patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute the SQL and verify that the join happened correctly\n",
    "results = list(test_engine.execute(sql))\n",
    "assert results == [('val1.1',), ('val1.2',)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patching Multiple Occurrences of Tables\n",
    "\n",
    "If a table appears multiple times in your SQL, it will be patched in all occurrences by default. This holds true for any selector. If you want to only patch a specific occurrence or range of tables, use list syntax. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT c2 FROM  (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c2,c3); SELECT c3 from  (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c2,c3)\n",
      "SELECT c2 FROM my_table; SELECT c3 from  (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c2,c3)\n"
     ]
    }
   ],
   "source": [
    "# Create a query and filter a column\n",
    "query = \"SELECT c2 FROM my_table; SELECT c3 from my_table\"\n",
    "\n",
    "# Patch both occurrences of the table\n",
    "patch = pgmock.patch(pgmock.table('my_table'),\n",
    "                     [('dummy_data', 'data'), ('value', 'hello'), ('value', 'hi')],\n",
    "                     ['c2', 'c3'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)\n",
    "\n",
    "# Use list syntax to only patch the second occurrence of the table\n",
    "patch = pgmock.patch(pgmock.table('my_table')[1],\n",
    "                     [('dummy_data', 'data'), ('value', 'hello'), ('value', 'hi')],\n",
    "                     ['c2', 'c3'])\n",
    "print(pgmock.sql(query, patch))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patching Subqueries\n",
    "\n",
    "Patching subqueries (and almost all other expressions) works in the same way as patching tables. Create a selector you want to patch and provide the data to be patched."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT sub.c1, sub.c2 FROM  (VALUES ('val1','val2'),('val3','val4')) AS sub(c1,c2);\n"
     ]
    }
   ],
   "source": [
    "# Create a query with a subquery\n",
    "query = \"SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;\"\n",
    "\n",
    "# Create a patch for the subquery. Similar to patching tables, provide a subquery selector and the data for the subquery\n",
    "patch = pgmock.patch(pgmock.subquery('sub'),\n",
    "                     [('val1', 'val2'), ('val3', 'val4')],\n",
    "                     ['c1', 'c2'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute the SQL and verify that the subquery was patched properly\n",
    "results = list(test_engine.execute(sql))\n",
    "assert results == [('val1', 'val2'), ('val3', 'val4')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patching CTEs, Create Table As, Insert Into, and Other Expressions\n",
    "\n",
    "``pgmock`` can patch almost every selector available in the library, such as `pgmock.insert_into`, `pgmock.create_table_as`, and `pgmock.cte`. Patching these expressions result in different types of patches depending on what is being patched.\n",
    "\n",
    "For example, patching an ``INSERT INTO`` statement will result in replacing the body of the ``INSERT INTO`` with a ``VALUES`` list that has no alias (Postgres doesn't support the syntax of ``INSERT INTO table (VALUES ..) AS ...``).\n",
    "\n",
    "When patching ``CREATE TABLE AS`` or a CTE, the patch will insert a ``SELECT * FROM (VALUES ...) AS ...``. Doing this syntax allows column names of the patch to be preserved and gets around the restriction of not being able to do ``CREATE TABLE AS (VALUES ...) AS ...``."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    Keep in mind that when patching statements when the table structure is defined, such as ``CREATE TABLE t(col1, col2) AS`` or ``WITH cte_name(col1, col2) AS``, the columns provided to the patch need to be in the same order as they are defined in the alias definition."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is an example of patching a CTE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "WITH cte_name AS ( SELECT * FROM (VALUES ('val1','val2','val3')) AS pgmock(c1,c2,c3))\n",
      "\n",
      "SELECT c1, c2, c3 from cte_name;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Create an example of selecting from a CTE\n",
    "query = '''\n",
    "WITH cte_name AS (\n",
    "    SELECT * from some_other_table\n",
    ")\n",
    "\n",
    "SELECT c1, c2, c3 from cte_name;\n",
    "'''\n",
    "\n",
    "# Patch the CTE with the data you want returned\n",
    "patch = pgmock.patch(\n",
    "    pgmock.cte('cte_name'),\n",
    "               [('val1', 'val2', 'val3')],\n",
    "               ['c1', 'c2', 'c3']\n",
    ")\n",
    "\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = list(test_engine.execute(sql))\n",
    "assert results == [('val1', 'val2', 'val3')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patching and Executing Smaller Components of Queries\n",
    "\n",
    "Sometimes one may only be interested in testing a small part of their SQL. This is especially true in testing the selects of an ``INSERT INTO`` or a subquery. An example of pulling out a subquery and testing it is shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM  (VALUES ('value','val2'),('val3','val4')) AS test_table(c1,c2) where c1 = 'value'\n"
     ]
    }
   ],
   "source": [
    "# Create a query with a subquery\n",
    "query = \"SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table where c1 = 'value') sub;\"\n",
    "\n",
    "# Obtain the subquery so that it can be patched and tested\n",
    "subquery = pgmock.sql(query, pgmock.subquery('sub'))\n",
    "\n",
    "# Create a patch for the subquery's table. Similar to patching tables, provide a subquery selector and the data for the subquery\n",
    "patch = pgmock.patch(pgmock.table('test_table'),\n",
    "                     [('value', 'val2'), ('val3', 'val4')],\n",
    "                     ['c1', 'c2'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(subquery, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute the SQL and verify that the subquery performs its select properly\n",
    "results = list(test_engine.execute(sql))\n",
    "assert results == [('value', 'val2')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Patching Queries Executed by SQLAlchemy\n",
    "\n",
    "Sometimes it's not always possible to have full control of the SQL that's being executed. For example, one might want to test code that issues many different SQLAlchemy statements and still want to patch out the underlying tables.\n",
    "\n",
    "For these cases, ``pgmock.mock`` can be used as a context manager. ``pgmock.mock`` takes the SQLAlchemy connectable as an argument and listens for any queries executed against the connectable. When queries are executed, they are patched on the fly before they are executed. Some examples of this are shown below.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pgmock.mock(test_engine) as mocker:\n",
    "    # Apply patches to the mocker object we created. For this example, we are going to\n",
    "    # patch \"test_table\"\n",
    "    mocker.patch(pgmock.table('test_table'), [('val1', 'val2', 'val3')], ['c1', 'c2', 'c3'])\n",
    "    \n",
    "    # When executing this query, it will be patched on the fly with the values provided\n",
    "    results = list(test_engine.execute('SELECT * from test_table'))\n",
    "    assert results == [('val1', 'val2', 'val3')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patching Multiple Queries with Side Effects\n",
    "\n",
    "In most testing situations, one will have more complex SQLAlchemy code that may issue multiple queries. For example, lets take the previous test example and put our SQLAlchemy code in a function that executes two different queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def my_sqla_func(engine):\n",
    "    \"\"\"A function that issues a couple different queries that we want to test\"\"\"\n",
    "    cursors = engine.execute('SELECT * from pg_cursors')\n",
    "    # Do something important with the cursors...\n",
    "    \n",
    "    # Now return results from a table\n",
    "    return list(engine.execute('SELECT * from test_table'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we try to test this function the same way as before, an error will happen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No table \"test_table\" found. The following SQL was used: \n",
      "\n",
      "SELECT * from pg_cursors\n",
      "\n",
      "View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.\n"
     ]
    }
   ],
   "source": [
    "with pgmock.mock(test_engine) as mocker:\n",
    "    # Apply patches to the mocker object we created. For this example, we are going to\n",
    "    # patch \"test_table\"\n",
    "    mocker.patch(pgmock.table('test_table'), [('val1', 'val2', 'val3')], ['c1', 'c2', 'c3'])\n",
    "    \n",
    "    # When executing this query, it will be patched on the fly with the values provided\n",
    "    try:\n",
    "        results = my_sqla_func(test_engine)\n",
    "        assert results == [('val1', 'val2', 'val3')]\n",
    "    except pgmock.exceptions.NoMatchError as exc:\n",
    "        print(exc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above, running ``my_sqla_func`` with the patched \"test_table\" threw a ``NoMatchError``. When looking at the error message, it appears that this error occurred on our first query of our function (``SELECT * from pg_cursors``).\n",
    "\n",
    "This happens because the patch on \"test_table\" will be applied to every single query that's issued, including the first query that cannot be patched. Instead of silently continuing, ``pgmock`` will raise errors anytime something cannot be matched.\n",
    "\n",
    "In order to get around this, use a ``side_effect`` argument to the patch instead of a single return value. A side effect is a list of return values to use every time the patch is applied to a query. The first side effect will be applied to the first query issued and so forth. If more queries are issued than the number of side effects, a ``SideEffectExhaustedError`` will be raised. If ``None`` is provided as a return value, the patch will be completely ignored for the query.\n",
    "\n",
    "The previous example can be changed to use a side effect in the following way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pgmock.mock(test_engine) as mocker:\n",
    "    # Apply patches to the mocker object we created. For this example, we are going to\n",
    "    # patch \"test_table\" on the second query that is issued by using a side effect\n",
    "    mocker.patch(\n",
    "        pgmock.table('test_table'),\n",
    "        side_effect=[\n",
    "            # Ignore patching test_table for the first query\n",
    "            None,\n",
    "            # Use pgmock.data to construct rows and columns of return data for\n",
    "            # the second query\n",
    "            pgmock.data([('val1', 'val2', 'val3')], ['c1', 'c2', 'c3'])\n",
    "        ])\n",
    "    \n",
    "    # When executing this query, it will be patched on the fly with the values provided\n",
    "    results = my_sqla_func(test_engine)\n",
    "    assert results == [('val1', 'val2', 'val3')]\n",
    "    \n",
    "    # As a precaution, it is always good practice to assert that the number of renderings of\n",
    "    # the mocker matches the number of queries you expected your test code to issue\n",
    "    assert len(mocker.renderings) == 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Advanced Usage\n",
    "\n",
    "### Patching Custom Types and Using Type Hinting\n",
    "\n",
    "``pgmock`` allows the user to provide quite a few different types of Python objects to patched values lists. Python objects are converted into their proper postgres type. For example, a datetime object is converted to a timestamp and a dictionary is converted to a json object. An example of this is shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM  (VALUES ('2017-06-14T00:00:00'::TIMESTAMP,'{\"my\": \"json_data\"}'::JSON,null)) AS my_table(c1,c2,c3)\n"
     ]
    }
   ],
   "source": [
    "import datetime as dt\n",
    "query = \"SELECT * FROM my_table\"\n",
    "\n",
    "# Create a patch for the table. The patch takes a selector, rows (a list of lists for each column), and column names\n",
    "patch = pgmock.patch(pgmock.table('my_table'),\n",
    "                     [(dt.datetime(2017, 6, 14), {'my': 'json_data'}, None)],\n",
    "                     ['c1', 'c2', 'c3'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The amount of Python types supported out of the box in ``pgmock`` is rather limited. Along with that, it's impossible to specify certain datatypes one might need for their tests in Python (e.g. a null datetime). ``pgmock`` allows the user to specify type hints to cast their values to a particular type. The type is specified by placing ``::type_name`` after the column name. For example, the following illustrates how to cast patched values to datetimes and bigints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM  (VALUES ('2017, 6, 14'::timestamp,10000::bigint,null::timestamp)) AS my_table(c1,c2,c3)\n"
     ]
    }
   ],
   "source": [
    "# Create a patch for the table. The patch takes a selector, rows (a list of lists for each column), and column names\n",
    "patch = pgmock.patch(pgmock.table('my_table'),\n",
    "                     [('2017, 6, 14', 10000, None)],\n",
    "                     ['c1::timestamp', 'c2::bigint', 'c3::timestamp'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    ".. note::\n",
    "\n",
    "    Type hints can only be used on python strings, floats, and ints. In other words, if you use a \"timestamp\" type, a string must be used as the value instead of a datetime object. Otherwise a ``ColumnTypeError`` will be raised."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testing Postgres Arrays\t\n",
    "\t\n",
    "Postgres arrays can be modeled in pgmock but cannot be passed in as python lists instead they must be strings in the Postgres array syntax. The syntax is similar to python except for curly brackets. If it's a text array then each string should be surrounded by double quotes. Remember to cast the field as the correct array type (ex. ::text[] or integer[])."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM  (VALUES ('2017, 6, 14'::timestamp,10000::bigint,'{\"apple\", \"iphone\"}'::text[])) AS my_table(c1,c2,c3)\n"
     ]
    }
   ],
   "source": [
    "# Create a patch for the table. The patch takes a selector, rows (a list of lists for each column), and column names\n",
    "patch = pgmock.patch(pgmock.table('my_table'),\n",
    "                     [('2017, 6, 14', 10000, '{\"apple\", \"iphone\"}')],\n",
    "                     ['c1::timestamp', 'c2::bigint', 'c3::text[]'])\n",
    "\n",
    "# Render the patched SQL so that we can execute it\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling in Meaningless Columns with nulls\n",
    "\n",
    "Sometimes the logic in a query only depends on the values of a couple columns and it isn't necessary to provide values for all of the other columns. ``pgmock`` allows users to ignore providing values for columns and fills in the empty values with null. Below is an example that illustrates how to do this when passing in rows to ``pgmock.patch``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT c1, c2, c3, c4, c5 from  (VALUES ('value',null,null,null,null),('not_filtered',null,null,null,null)) AS test_table(c1,c2,c3,c4,c5) where c1 = 'value'\n"
     ]
    }
   ],
   "source": [
    "# Create a query that returns many columns\n",
    "query = \"SELECT c1, c2, c3, c4, c5 from test_table where c1 = 'value'\"\n",
    "\n",
    "# When patching out the table, only provide values for \"c1\" since we're testing the filtering of the select\n",
    "patch = pgmock.patch(pgmock.table('test_table'), [('value', ), ('not_filtered', )], ['c1', 'c2', 'c3', 'c4', 'c5'])\n",
    "\n",
    "# Render the patched SQL. All other values for columns will be null\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT c1, c2, c3, c4, c5 from  (VALUES ('value',null,null,null,null),('not_filtered',null,null,null,null)) AS test_table(c1,c2,c3,c4,c5) where c1 = 'value'\n"
     ]
    }
   ],
   "source": [
    "# The patch can also take a list of dictionaries that only specifies which column values to use. This is another\n",
    "# way to fill in meaningless values with nulls\n",
    "patch = pgmock.patch(pgmock.table('test_table'), [{'c1': 'value'}, {'c1': 'not_filtered'}], ['c1', 'c2', 'c3', 'c4', 'c5'])\n",
    "\n",
    "# Render the patched SQL. All other values for columns will be null\n",
    "sql = pgmock.sql(query, patch)\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only one row should have matched the filter\n",
    "results = list(test_engine.execute(sql))\n",
    "assert len(results) == 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Be sure to stop the testing DB for this tutorial\n",
    "test_db.stop()\n",
    "test_engine.dispose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Configuring for Accuracy and Performance\n",
    "\n",
    "``pgmock`` comes with a configuration module (``pgmock.config``) that can be used to set flags that aid in accuracy of selectors / patching. These flags come with accuracy/performance hits that should be known before using them.\n",
    "\n",
    "#### Safe Mode\n",
    "``pgmock`` searches SQL with regular expressions. Regular expressions can get tripped up whenever special characters appear in comments of SQL or in string literals. For example, ``-- this is my comment; hello!`` will mess up ``pgmock.statement`` since it splits the SQL by the semicolon character. Turning on **safe mode** will search a pre-formatted version of the supplied SQL that is stripped of comments and string literals.\n",
    "\n",
    "Safe mode can be turned on with ``pgmock.config.set_safe_mode``. By default, it is set to ``False`` because it incurs a major performance hit when using it. Safe mode doesn't have to be configured globally with ``pgmock.config.set_safe_mode``. It can be passed to ``pgmock.sql`` or ``pgmock.sql_file`` as an argument. It can also be used in a context manager so that it is only set during the duration of execution like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pgmock.config.set_safe_mode(True):\n",
    "    # Run SQL that cant natively be searched by pgmock because of regex issues\n",
    "    ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's recommended to pass it as an argument to ``pgmock.sql`` when needing to be modified. For configuring it in a pytest fixture, one can use the context manager like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pytest\n",
    "\n",
    "@pytest.fixture(scope='module')\n",
    "def use_safe_mode():\n",
    "    with pgmock.config.set_safe_mode(True):\n",
    "        yield"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Replace New Patch Aliases\n",
    "Since ``pgmock`` turns expressions into ``VALUES`` expressions when patching, it is not always possible to preserve the original name of what's being patched. For example, ``SELECT * from schema.table_name`` is impossible to patch as ``SELECT * FROM (VALUES ...) AS schema.table_name`` since ``schema.table_name`` is not a valid alias.\n",
    "\n",
    "When this case happens in the case of the ``pgmock.table`` selector, ``pgmock`` will make an alias as the table name and then replace any refences to the old table name.\n",
    "\n",
    "For example, ``SELECT schema.table_name.col FROM schema.table_name`` would be replaced with ``SELECT table_name.col FROM (VALUES ...) AS table_name(...)``. Note that this only matters when the full schema and table name is used to reference columns.\n",
    "\n",
    "By default, this mode is turned on. To turn it off globally, call ``pgmock.config.set_replace_new_patch_aliases(False)``. Similar to ``pgmock.config.set_safe_mode``, this function can be used as a context manager or in a pytest fixture. It can also be given as an argument to ``pgmock.mock`` since SQLAlchemy will use this style of selects by default when making a ``sqlalchemy.insert`` object from a table with a schema.\n",
    "\n",
    "If users are never using the full schema and table name when referencing columns, it is safe to turn this option off and will improve ``pgmock.table`` selector performance by about 20%."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using pgmock with pytest\n",
    "\n",
    "The examples above illustrate programmatically making a test database and running assertions. For examples of how to use ``pgmock`` with pytest, check out the [test_examples.py file](https://github.com/CloverHealth/pgmock/blob/master/pgmock/tests/test_examples.py) in ``pgmock``. This file shows how to use ``pgmock`` with [pytest-pgsql](https://github.com/CloverHealth/pytest-pgsql). An example of using the context manager and reading from a SQL file is provided."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}