{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "591073c2",
   "metadata": {},
   "source": [
    "(sqlatutorial:selecting-data)=\n",
    "\n",
    "# Selecting Rows with Core or ORM\n",
    "\n",
    "For both Core and ORM, the {func}`~sqlalchemy.sql.expression.select` function generates a {class}`~sqlalchemy.sql.expression.Select` construct which is used for all SELECT queries.\n",
    "Passed to methods like {meth}`~sqlalchemy.future.Connection.execute` in Core and\n",
    "{meth}`~sqlalchemy.orm.Session.execute` in ORM, a SELECT statement is emitted in the current transaction and the result rows available via the returned\n",
    "{class}`~sqlalchemy.engine.Result` object.\n",
    "\n",
    ":::{div} orm-header\n",
    "\n",
    "**ORM Readers** - the content here applies equally well to both Core and ORM\n",
    "use and basic ORM variant use cases are mentioned here.  However there are\n",
    "a lot more ORM-specific features available as well; these are documented\n",
    "at {ref}`queryguide_toplevel`.\n",
    ":::"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9c2d4f20",
   "metadata": {
    "tags": [
     "hide-output"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,039 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,040 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"user_account\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,041 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,042 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"user_account\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,042 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,043 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"address\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,044 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,044 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"address\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,045 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,046 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE user_account (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR(30), \n",
      "\tfullname VARCHAR, \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,047 INFO sqlalchemy.engine.Engine [no key 0.00058s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,048 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE address (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tuser_id INTEGER NOT NULL, \n",
      "\temail_address VARCHAR NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(user_id) REFERENCES user_account (id)\n",
      ")\n",
      "\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,049 INFO sqlalchemy.engine.Engine [no key 0.00047s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,050 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,050 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,052 INFO sqlalchemy.engine.Engine INSERT INTO user_account (id, name, fullname) VALUES (?, ?, ?)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,052 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ((1, 'spongebob', 'Spongebob Squarepants'), (2, 'sandy', 'Sandy Cheeks'), (3, 'patrick', 'Patrick Star'))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,053 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,054 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,055 INFO sqlalchemy.engine.Engine INSERT INTO address (user_id, email_address) VALUES (?, ?)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,056 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ((1, 'spongebob@sqlalchemy.org'), (2, 'sandy@sqlalchemy.org'), (2, 'sandy@squirrelpower.org'))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,057 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, insert\n",
    "from sqlalchemy.orm import declarative_base, Session\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "user_table = Table(\n",
    "    \"user_account\",\n",
    "    Base.metadata,\n",
    "    Column('id', Integer, primary_key=True),\n",
    "    Column('name', String(30)),\n",
    "    Column('fullname', String)\n",
    ")\n",
    "\n",
    "class User(Base):\n",
    "    __table__ = user_table\n",
    "    def __repr__(self):\n",
    "        return f\"User({self.name!r}, {self.fullname!r})\"\n",
    "\n",
    "address_table = Table(\n",
    "    \"address\",\n",
    "    Base.metadata,\n",
    "    Column('id', Integer, primary_key=True),\n",
    "    Column('user_id', ForeignKey('user_account.id'), nullable=False),\n",
    "    Column('email_address', String, nullable=False)\n",
    ")\n",
    "\n",
    "class Address(Base):\n",
    "    __table__ = address_table\n",
    "    def __repr__(self):\n",
    "        return f\"Address({self.email_address!r})\"\n",
    "\n",
    "engine = create_engine(\"sqlite+pysqlite:///:memory:\", echo=True, future=True)\n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "with engine.begin() as conn:\n",
    "    conn.execute(\n",
    "        insert(user_table),\n",
    "        [\n",
    "            {\"id\": 1, \"name\": \"spongebob\", \"fullname\": \"Spongebob Squarepants\"},\n",
    "            {\"id\": 2, \"name\": \"sandy\", \"fullname\": \"Sandy Cheeks\"},\n",
    "            {\"id\": 3, \"name\": \"patrick\", \"fullname\": \"Patrick Star\"}\n",
    "        ]\n",
    "    )\n",
    "with engine.begin() as conn:\n",
    "    conn.execute(\n",
    "        insert(address_table),\n",
    "        [\n",
    "            {\"user_id\": 1, \"email_address\": \"spongebob@sqlalchemy.org\"},\n",
    "            {\"user_id\": 2, \"email_address\": \"sandy@sqlalchemy.org\"},\n",
    "            {\"user_id\": 2, \"email_address\": \"sandy@squirrelpower.org\"},\n",
    "        ]\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8592de23",
   "metadata": {},
   "source": [
    "## The select() SQL Expression Construct\n",
    "\n",
    "The {func}`~sqlalchemy.sql.expression.select` construct builds up a statement in the same way\n",
    "as that of {func}`~sqlalchemy.sql.expression.insert`, using a {term}`generative` approach where\n",
    "each method builds more state onto the object.  Like the other SQL constructs,\n",
    "it can be stringified in place:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "25f5f6fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = :name_1\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "stmt = select(user_table).where(user_table.c.name == 'spongebob')\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dac67af1",
   "metadata": {},
   "source": [
    "Also in the same manner as all other statement-level SQL constructs, to\n",
    "actually run the statement we pass it to an execution method.\n",
    "Since a SELECT statement returns\n",
    "rows we can always iterate the result object to get {class}`~sqlalchemy.engine.Row`\n",
    "objects back:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7a0f28b5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,083 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,083 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = ?\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,084 INFO sqlalchemy.engine.Engine [generated in 0.00105s] ('spongebob',)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'spongebob', 'Spongebob Squarepants')\n",
      "2021-10-04 01:45:37,085 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    for row in conn.execute(stmt):\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea2a364b",
   "metadata": {},
   "source": [
    "When using the ORM, particularly with a {func}`~sqlalchemy.sql.expression.select` construct that's\n",
    "composed against ORM entities, we will want to execute it using the\n",
    "{meth}`~sqlalchemy.orm.Session.execute` method on the {class}`~sqlalchemy.orm.Session`; using\n",
    "this approach, we continue to get {class}`~sqlalchemy.engine.Row` objects from the\n",
    "result, however these rows are now capable of including\n",
    "complete entities, such as instances of the `User` class, as individual\n",
    "elements within each row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "422f2d44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,095 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,097 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = ?\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,098 INFO sqlalchemy.engine.Engine [generated in 0.00079s] ('spongebob',)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(User('spongebob', 'Spongebob Squarepants'),)\n",
      "2021-10-04 01:45:37,100 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "stmt = select(User).where(User.name == 'spongebob')\n",
    "with Session(engine) as session:\n",
    "    for row in session.execute(stmt):\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28f44be7",
   "metadata": {},
   "source": [
    ":::{admonition} select() from a Table vs. ORM class\n",
    "While the SQL generated in these examples looks the same whether we invoke\n",
    "`select(user_table)` or `select(User)`, in the more general case\n",
    "they do not necessarily render the same thing, as an ORM-mapped class\n",
    "may be mapped to other kinds of \"selectables\" besides tables.  The\n",
    "`select()` that's against an ORM entity also indicates that ORM-mapped\n",
    "instances should be returned in a result, which is not the case when\n",
    "SELECTing from a {class}`~sqlalchemy.schema.Table` object.\n",
    ":::\n",
    "\n",
    "The following sections will discuss the SELECT construct in more detail.\n",
    "\n",
    "## Setting the COLUMNS and FROM clause\n",
    "\n",
    "The {func}`~sqlalchemy.sql.expression.select` function accepts positional elements representing any\n",
    "number of {class}`~sqlalchemy.schema.Column` and/or {class}`~sqlalchemy.schema.Table` expressions, as\n",
    "well as a wide range of compatible objects, which are resolved into a list of SQL\n",
    "expressions to be SELECTed from that will be returned as columns in the result\n",
    "set.  These elements also serve in simpler cases to create the FROM clause,\n",
    "which is inferred from the columns and table-like expressions passed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b74f4c91",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "print(select(user_table))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91962362",
   "metadata": {},
   "source": [
    "To SELECT from individual columns using a Core approach,\n",
    "{class}`~sqlalchemy.schema.Column` objects are accessed from the {attr}`~sqlalchemy.schema.Table.c`\n",
    "accessor and can be sent directly; the FROM clause will be inferred as the set\n",
    "of all {class}`~sqlalchemy.schema.Table` and other {class}`~sqlalchemy.sql.expression.FromClause` objects that\n",
    "are represented by those columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2d96b531",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, user_account.fullname \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "print(select(user_table.c.name, user_table.c.fullname))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57e63d13",
   "metadata": {},
   "source": [
    "(sqlatutorial:selecting-orm-entities)=\n",
    "\n",
    "### Selecting ORM Entities and Columns\n",
    "\n",
    "ORM entities, such our `User` class as well as the column-mapped\n",
    "attributes upon it such as `User.name`, also participate in the SQL Expression\n",
    "Language system representing tables and columns.    Below illustrates an\n",
    "example of SELECTing from the `User` entity, which ultimately renders\n",
    "in the same way as if we had used `user_table` directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "02b9d85b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "print(select(User))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "748cbaee",
   "metadata": {},
   "source": [
    "When executing a statement like the above using the ORM {meth}`~sqlalchemy.orm.Session.execute`\n",
    "method, there is an important difference when we select from a full entity\n",
    "such as `User`, as opposed to `user_table`, which is that the **entity\n",
    "itself is returned as a single element within each row**.  That is, when we fetch rows from\n",
    "the above statement, as there is only the `User` entity in the list of\n",
    "things to fetch, we get back {class}`~sqlalchemy.engine.Row` objects that have only one element, which contain\n",
    "instances of the `User` class:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "af13a399",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,123 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,125 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,125 INFO sqlalchemy.engine.Engine [generated in 0.00060s] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(User('spongebob', 'Spongebob Squarepants'),)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row = session.execute(select(User)).first()\n",
    "row"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef9cfdd6",
   "metadata": {},
   "source": [
    "The above {class}`~sqlalchemy.engine.Row` has just one element, representing the `User` entity:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a6fee640",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User('spongebob', 'Spongebob Squarepants')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6418608f",
   "metadata": {},
   "source": [
    "Alternatively, we can select individual columns of an ORM entity as distinct\n",
    "elements within result rows, by using the class-bound attributes; when these\n",
    "are passed to a construct such as {func}`~sqlalchemy.sql.expression.select`, they are resolved into\n",
    "the {class}`~sqlalchemy.schema.Column` or other SQL expression represented by each\n",
    "attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ddf8d1ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, user_account.fullname \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "print(select(User.name, User.fullname))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11ecbf83",
   "metadata": {},
   "source": [
    "When we invoke *this* statement using {meth}`~sqlalchemy.orm.Session.execute`, we now\n",
    "receive rows that have individual elements per value, each corresponding\n",
    "to a separate column or other SQL expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1181d813",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,152 INFO sqlalchemy.engine.Engine SELECT user_account.name, user_account.fullname \n",
      "FROM user_account\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,152 INFO sqlalchemy.engine.Engine [generated in 0.00088s] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "('spongebob', 'Spongebob Squarepants')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row = session.execute(select(User.name, User.fullname)).first()\n",
    "row"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57500ae0",
   "metadata": {},
   "source": [
    "The approaches can also be mixed, as below where we SELECT the `name`\n",
    "attribute of the `User` entity as the first element of the row, and combine\n",
    "it with full `Address` entities in the second element:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "10f6a2c5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,162 INFO sqlalchemy.engine.Engine SELECT user_account.name, address.id, address.user_id, address.email_address \n",
      "FROM user_account, address \n",
      "WHERE user_account.id = address.user_id ORDER BY address.id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,163 INFO sqlalchemy.engine.Engine [generated in 0.00053s] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('spongebob', Address('spongebob@sqlalchemy.org')),\n",
       " ('sandy', Address('sandy@sqlalchemy.org')),\n",
       " ('sandy', Address('sandy@squirrelpower.org'))]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.execute(\n",
    "    select(User.name, Address).\n",
    "    where(User.id==Address.user_id).\n",
    "    order_by(Address.id)\n",
    ").all()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e40a4d41",
   "metadata": {},
   "source": [
    "Approaches towards selecting ORM entities and columns as well as common methods\n",
    "for converting rows are discussed further at {ref}`orm_queryguide_select_columns`.\n",
    "\n",
    ":::{seealso}\n",
    "{ref}`orm_queryguide_select_columns` - in the {ref}`queryguide_toplevel`\n",
    ":::\n",
    "\n",
    "### Selecting from Labeled SQL Expressions\n",
    "\n",
    "The {meth}`~sqlalchemy.sql.expression.ColumnElement.label` method as well as the same-named method\n",
    "available on ORM attributes provides a SQL label of a column or expression,\n",
    "allowing it to have a specific name in a result set.  This can be helpful\n",
    "when referring to arbitrary SQL expressions in a result row by name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "34e2d5ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,172 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,172 INFO sqlalchemy.engine.Engine SELECT ? || user_account.name AS username \n",
      "FROM user_account ORDER BY user_account.name\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,173 INFO sqlalchemy.engine.Engine [generated in 0.00107s] ('Username: ',)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Username: patrick\n",
      "Username: sandy\n",
      "Username: spongebob\n",
      "2021-10-04 01:45:37,174 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import func, cast\n",
    "stmt = (\n",
    "    select(\n",
    "        (\"Username: \" + user_table.c.name).label(\"username\"),\n",
    "    ).order_by(user_table.c.name)\n",
    ")\n",
    "with engine.connect() as conn:\n",
    "    for row in conn.execute(stmt):\n",
    "        print(f\"{row.username}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53932859",
   "metadata": {},
   "source": [
    ":::{seealso}\n",
    "{ref}`sqlatutorial:order-by-label` - the label names we create may also be\n",
    "referred towards in the ORDER BY or GROUP BY clause of the {class}`~sqlalchemy.sql.expression.Select`.\n",
    ":::\n",
    "\n",
    "(sqlatutorial:select-arbtrary-text)=\n",
    "\n",
    "### Selecting with Textual Column Expressions\n",
    "\n",
    "When we construct a {class}`~sqlalchemy.sql.expression.Select` object using the {func}`~sqlalchemy.sql.expression.select`\n",
    "function, we are normally passing to it a series of {class}`~sqlalchemy.schema.Table`\n",
    "and {class}`~sqlalchemy.schema.Column` objects that were defined using\n",
    "{ref}`table metadata <sqlatutorial:working-with-metadata>`, or when using the ORM we may be\n",
    "sending ORM-mapped attributes that represent table columns.   However,\n",
    "sometimes there is also the need to manufacture arbitrary SQL blocks inside\n",
    "of statements, such as constant string expressions, or just some arbitrary\n",
    "SQL that's quicker to write literally.\n",
    "\n",
    "The {func}`~sqlalchemy.sql.expression.text` construct introduced at\n",
    "{ref}`sqlatutorial:working-with-transactions` can in fact be embedded into a\n",
    "{class}`~sqlalchemy.sql.expression.Select` construct directly, such as below where we manufacture\n",
    "a hardcoded string literal `'some label'` and embed it within the\n",
    "SELECT statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3228c464",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,182 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,183 INFO sqlalchemy.engine.Engine SELECT 'some phrase', user_account.name \n",
      "FROM user_account ORDER BY user_account.name\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,183 INFO sqlalchemy.engine.Engine [generated in 0.00118s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]\n",
      "2021-10-04 01:45:37,185 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import text\n",
    "stmt = (\n",
    "    select(\n",
    "        text(\"'some phrase'\"), user_table.c.name\n",
    "    ).order_by(user_table.c.name)\n",
    ")\n",
    "with engine.connect() as conn:\n",
    "    print(conn.execute(stmt).all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "091e9f50",
   "metadata": {},
   "source": [
    "While the {func}`~sqlalchemy.sql.expression.text` construct can be used in most places to inject\n",
    "literal SQL phrases, more often than not we are actually dealing with textual\n",
    "units that each represent an individual\n",
    "column expression.  In this common case we can get more functionality out of\n",
    "our textual fragment using the {func}`~sqlalchemy.sql.expression.literal_column`\n",
    "construct instead.  This object is similar to {func}`~sqlalchemy.sql.expression.text` except that\n",
    "instead of representing arbitrary SQL of any form,\n",
    "it explicitly represents a single \"column\" and can then be labeled and referred\n",
    "towards in subqueries and other expressions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "31a253f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,193 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,194 INFO sqlalchemy.engine.Engine SELECT 'some phrase' AS p, user_account.name \n",
      "FROM user_account ORDER BY user_account.name\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,195 INFO sqlalchemy.engine.Engine [generated in 0.00123s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "some phrase, patrick\n",
      "some phrase, sandy\n",
      "some phrase, spongebob\n",
      "2021-10-04 01:45:37,196 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import literal_column\n",
    "stmt = (\n",
    "    select(\n",
    "        literal_column(\"'some phrase'\").label(\"p\"), user_table.c.name\n",
    "    ).order_by(user_table.c.name)\n",
    ")\n",
    "with engine.connect() as conn:\n",
    "    for row in conn.execute(stmt):\n",
    "        print(f\"{row.p}, {row.name}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57be27c0",
   "metadata": {},
   "source": [
    "Note that in both cases, when using {func}`~sqlalchemy.sql.expression.text` or\n",
    "{func}`~sqlalchemy.sql.expression.literal_column`, we are writing a syntactical SQL expression, and\n",
    "not a literal value. We therefore have to include whatever quoting or syntaxes\n",
    "are necessary for the SQL we want to see rendered.\n",
    "\n",
    "(sqlatutorial:select-where-clause)=\n",
    "\n",
    "## The WHERE clause\n",
    "\n",
    "SQLAlchemy allows us to compose SQL expressions, such as `name = 'squidward'`\n",
    "or `user_id > 10`, by making use of standard Python operators in\n",
    "conjunction with\n",
    "{class}`~sqlalchemy.schema.Column` and similar objects.   For boolean expressions, most\n",
    "Python operators such as `==`, `!=`, `<`, `>=` etc. generate new\n",
    "SQL Expression objects, rather than plain boolean `True`/`False` values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ca7567e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "user_account.name = :name_1\n",
      "address.user_id > :user_id_1\n"
     ]
    }
   ],
   "source": [
    "print(user_table.c.name == 'squidward')\n",
    "print(address_table.c.user_id > 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1541f544",
   "metadata": {},
   "source": [
    "We can use expressions like these to generate the WHERE clause by passing\n",
    "the resulting objects to the {meth}`~sqlalchemy.sql.expression.Select.where` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "672b0645",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = :name_1\n"
     ]
    }
   ],
   "source": [
    "print(select(user_table).where(user_table.c.name == 'squidward'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6f45f32",
   "metadata": {},
   "source": [
    "To produce multiple expressions joined by AND, the {meth}`~sqlalchemy.sql.expression.Select.where`\n",
    "method may be invoked any number of times:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "de13b22d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT address.email_address \n",
      "FROM address, user_account \n",
      "WHERE user_account.name = :name_1 AND address.user_id = user_account.id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(address_table.c.email_address).\n",
    "    where(user_table.c.name == 'squidward').\n",
    "    where(address_table.c.user_id == user_table.c.id)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83808428",
   "metadata": {},
   "source": [
    "A single call to {meth}`~sqlalchemy.sql.expression.Select.where` also accepts multiple expressions\n",
    "with the same effect:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "57f12332",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT address.email_address \n",
      "FROM address, user_account \n",
      "WHERE user_account.name = :name_1 AND address.user_id = user_account.id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(address_table.c.email_address).\n",
    "    where(\n",
    "         user_table.c.name == 'squidward',\n",
    "         address_table.c.user_id == user_table.c.id\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6861747d",
   "metadata": {},
   "source": [
    "\"AND\" and \"OR\" conjunctions are both available directly using the\n",
    "{func}`~sqlalchemy.sql.expression.and_` and {func}`~sqlalchemy.sql.expression.or_` functions, illustrated below in terms\n",
    "of ORM entities:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a3c82075",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT address.email_address \n",
      "FROM address, user_account \n",
      "WHERE (user_account.name = :name_1 OR user_account.name = :name_2) AND address.user_id = user_account.id\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import and_, or_\n",
    "print(\n",
    "    select(Address.email_address).\n",
    "    where(\n",
    "        and_(\n",
    "            or_(User.name == 'squidward', User.name == 'sandy'),\n",
    "            Address.user_id == User.id\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be7a7b5c",
   "metadata": {},
   "source": [
    "For simple \"equality\" comparisons against a single entity, there's also a\n",
    "popular method known as {meth}`~sqlalchemy.sql.expression.Select.filter_by` which accepts keyword\n",
    "arguments that match to column keys or ORM attribute names.  It will filter\n",
    "against the leftmost FROM clause or the last entity joined:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "be1ebbba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(User).filter_by(name='spongebob', fullname='Spongebob Squarepants')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9276da08",
   "metadata": {},
   "source": [
    ":::{seealso}\n",
    "{doc}`core/operators` - descriptions of most SQL operator functions in SQLAlchemy\n",
    ":::\n",
    "\n",
    "(sqlatutorial:select-join)=\n",
    "\n",
    "## Explicit FROM clauses and JOINs\n",
    "\n",
    "As mentioned previously, the FROM clause is usually **inferred**\n",
    "based on the expressions that we are setting in the columns\n",
    "clause as well as other elements of the {class}`~sqlalchemy.sql.expression.Select`.\n",
    "\n",
    "If we set a single column from a particular {class}`~sqlalchemy.schema.Table`\n",
    "in the COLUMNS clause, it puts that {class}`~sqlalchemy.schema.Table` in the FROM\n",
    "clause as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "377ca94f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name \n",
      "FROM user_account"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "print(select(user_table.c.name))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a06df10",
   "metadata": {},
   "source": [
    "If we were to put columns from two tables, then we get a comma-separated FROM\n",
    "clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "26f4a117",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, address.email_address \n",
      "FROM user_account, address\n"
     ]
    }
   ],
   "source": [
    "print(select(user_table.c.name, address_table.c.email_address))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f2e96f3",
   "metadata": {},
   "source": [
    "In order to JOIN these two tables together, we typically use one of two methods\n",
    "on {class}`~sqlalchemy.sql.expression.Select`.  The first is the {meth}`~sqlalchemy.sql.expression.Select.join_from`\n",
    "method, which allows us to indicate the left and right side of the JOIN\n",
    "explicitly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e6ef1dc8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, address.email_address \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(user_table.c.name, address_table.c.email_address).\n",
    "    join_from(user_table, address_table)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5a84d51",
   "metadata": {},
   "source": [
    "The other is the the {meth}`~sqlalchemy.sql.expression.Select.join` method, which indicates only the\n",
    "right side of the JOIN, the left hand-side is inferred:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "70ea1aa1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, address.email_address \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(user_table.c.name, address_table.c.email_address).\n",
    "    join(address_table)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec58c5d8",
   "metadata": {},
   "source": [
    ":::{admonition} The ON Clause is inferred\n",
    "When using {meth}`~sqlalchemy.sql.expression.Select.join_from` or {meth}`~sqlalchemy.sql.expression.Select.join`, we may\n",
    "observe that the ON clause of the join is also inferred for us in simple\n",
    "foreign key cases. More on that in the next section.\n",
    ":::\n",
    "\n",
    "We also have the option to add elements to the FROM clause explicitly, if it is not\n",
    "inferred the way we want from the columns clause.  We use the\n",
    "{meth}`~sqlalchemy.sql.expression.Select.select_from` method to achieve this, as below\n",
    "where we establish `user_table` as the first element in the FROM\n",
    "clause and {meth}`~sqlalchemy.sql.expression.Select.join` to establish `address_table` as\n",
    "the second:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "20a151ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT address.email_address \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(address_table.c.email_address).\n",
    "    select_from(user_table).join(address_table)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c82dea3b",
   "metadata": {},
   "source": [
    "Another example where we might want to use {meth}`~sqlalchemy.sql.expression.Select.select_from`\n",
    "is if our columns clause doesn't have enough information to provide for a\n",
    "FROM clause.  For example, to SELECT from the common SQL expression\n",
    "`count(*)`, we use a SQLAlchemy element known as {data}`~sqlalchemy.sql.expression.func` to\n",
    "produce the SQL `count()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "505c22bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(:count_2) AS count_1 \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import func\n",
    "print (\n",
    "    select(func.count('*')).select_from(user_table)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf186ebc",
   "metadata": {},
   "source": [
    ":::{seealso}\n",
    "{ref}`orm_queryguide_select_from` - in the {ref}`queryguide_toplevel` -\n",
    "contains additional examples and notes\n",
    "regarding the interaction of {meth}`~sqlalchemy.sql.expression.Select.select_from` and\n",
    "{meth}`~sqlalchemy.sql.expression.Select.join`.\n",
    ":::\n",
    "\n",
    "(sqlatutorial:select-join-onclause)=\n",
    "\n",
    "### Setting the ON Clause\n",
    "\n",
    "The previous examples of JOIN illustrated that the {class}`~sqlalchemy.sql.expression.Select` construct\n",
    "can join between two tables and produce the ON clause automatically.  This\n",
    "occurs in those examples because the `user_table` and `address_table`\n",
    "{class}`~sqlalchemy.schema.Table` objects include a single {class}`~sqlalchemy.schema.ForeignKeyConstraint`\n",
    "definition which is used to form this ON clause.\n",
    "\n",
    "If the left and right targets of the join do not have such a constraint, or\n",
    "there are multiple constraints in place, we need to specify the ON clause\n",
    "directly.   Both {meth}`~sqlalchemy.sql.expression.Select.join` and {meth}`~sqlalchemy.sql.expression.Select.join_from`\n",
    "accept an additional argument for the ON clause, which is stated using the\n",
    "same SQL Expression mechanics as we saw about in {ref}`sqlatutorial:select-where-clause`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "27edaf60",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT address.email_address \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(address_table.c.email_address).\n",
    "    select_from(user_table).\n",
    "    join(address_table, user_table.c.id == address_table.c.user_id)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81a3515b",
   "metadata": {},
   "source": [
    ":::{div} orm-header\n",
    "\n",
    "**ORM Tip** - there's another way to generate the ON clause when using\n",
    "ORM entities that make use of the {func}`~sqlalchemy.orm.relationship` construct,\n",
    "like the mapping set up in the previous section at\n",
    "{ref}`sqlatutorial:declaring-mapped-classes`.\n",
    "This is a whole subject onto itself, which is introduced at length\n",
    "at {ref}`sqlatutorial:joining-relationships`.\n",
    ":::\n",
    "\n",
    "### OUTER and FULL join\n",
    "\n",
    "Both the {meth}`~sqlalchemy.sql.expression.Select.join` and {meth}`~sqlalchemy.sql.expression.Select.join_from` methods\n",
    "accept keyword arguments {paramref}`~sqlalchemy.sql.expression.Select.join.isouter` and\n",
    "{paramref}`~sqlalchemy.sql.expression.Select.join.full` which will render LEFT OUTER JOIN\n",
    "and FULL OUTER JOIN, respectively:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "81a7fda2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id\n",
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    select(user_table).join(address_table, isouter=True)\n",
    ")\n",
    "print(\n",
    "    select(user_table).join(address_table, full=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfc8d1c6",
   "metadata": {},
   "source": [
    "There is also a method {meth}`~sqlalchemy.sql.expression.Select.outerjoin` that is equivalent to\n",
    "using `.join(..., isouter=True)`.\n",
    "\n",
    ":::{tip}\n",
    "SQL also has a \"RIGHT OUTER JOIN\".  SQLAlchemy doesn't render this directly;\n",
    "instead, reverse the order of the tables and use \"LEFT OUTER JOIN\".\n",
    ":::\n",
    "\n",
    "(sqlatutorial:order-by-group-by-having)=\n",
    "\n",
    "## ORDER BY, GROUP BY, HAVING\n",
    "\n",
    "The SELECT SQL statement includes a clause called ORDER BY which is used to\n",
    "return the selected rows within a given ordering.\n",
    "\n",
    "The GROUP BY clause is constructed similarly to the ORDER BY clause, and has\n",
    "the purpose of sub-dividing the selected rows into specific groups upon which\n",
    "aggregate functions may be invoked. The HAVING clause is usually used with\n",
    "GROUP BY and is of a similar form to the WHERE clause, except that it's applied\n",
    "to the aggregated functions used within groups.\n",
    "\n",
    "(sqlatutorial:order-by)=\n",
    "\n",
    "### ORDER BY\n",
    "\n",
    "The ORDER BY clause is constructed in terms\n",
    "of SQL Expression constructs typically based on {class}`~sqlalchemy.schema.Column` or\n",
    "similar objects.  The {meth}`~sqlalchemy.sql.expression.Select.order_by` method accepts one or\n",
    "more of these expressions positionally:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "8ceba1b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account ORDER BY user_account.name\n"
     ]
    }
   ],
   "source": [
    "print(select(user_table).order_by(user_table.c.name))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53d995bd",
   "metadata": {},
   "source": [
    "Ascending / descending is available from the {meth}`~sqlalchemy.sql.expression.ColumnElement.asc`\n",
    "and {meth}`~sqlalchemy.sql.expression.ColumnElement.desc` modifiers, which are present\n",
    "from ORM-bound attributes as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b40978e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account ORDER BY user_account.fullname DESC\n"
     ]
    }
   ],
   "source": [
    "print(select(User).order_by(User.fullname.desc()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46e3548d",
   "metadata": {},
   "source": [
    "The above statement will yield rows that are sorted by the\n",
    "`user_account.fullname` column in descending order.\n",
    "\n",
    "(sqlatutorial:group-by-w-aggregates)=\n",
    "\n",
    "### Aggregate functions with GROUP BY / HAVING\n",
    "\n",
    "In SQL, aggregate functions allow column expressions across multiple rows\n",
    "to be aggregated together to produce a single result.  Examples include\n",
    "counting, computing averages, as well as locating the maximum or minimum\n",
    "value in a set of values.\n",
    "\n",
    "SQLAlchemy provides for SQL functions in an open-ended way using a namespace\n",
    "known as {data}`~sqlalchemy.sql.expression.func`.  This is a special constructor object which\n",
    "will create new instances of {class}`~sqlalchemy.sql.functions.Function` when given the name\n",
    "of a particular SQL function, which can have any name, as well as zero or\n",
    "more arguments to pass to the function, which are, like in all other cases,\n",
    "SQL Expression constructs.   For example, to\n",
    "render the SQL COUNT() function against the `user_account.id` column,\n",
    "we call upon the `count()` name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "4ac613b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count(user_account.id)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import func\n",
    "count_fn = func.count(user_table.c.id)\n",
    "print(count_fn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eafc0c3d",
   "metadata": {},
   "source": [
    "SQL functions are described in more detail later in this tutorial at\n",
    "{ref}`sqlatutorial:functions`.\n",
    "\n",
    "When using aggregate functions in SQL, the GROUP BY clause is essential in that\n",
    "it allows rows to be partitioned into groups where aggregate functions will\n",
    "be applied to each group individually.  When requesting non-aggregated columns\n",
    "in the COLUMNS clause of a SELECT statement, SQL requires that these columns\n",
    "all be subject to a GROUP BY clause, either directly or indirectly based on\n",
    "a primary key association.    The HAVING clause is then used in a similar\n",
    "manner as the WHERE clause, except that it filters out rows based on aggregated\n",
    "values rather than direct row contents.\n",
    "\n",
    "SQLAlchemy provides for these two clauses using the {meth}`~sqlalchemy.sql.expression.Select.group_by`\n",
    "and {meth}`~sqlalchemy.sql.expression.Select.having` methods.   Below we illustrate selecting\n",
    "user name fields as well as count of addresses, for those users that have more\n",
    "than one address:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "0be47837",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,314 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,315 INFO sqlalchemy.engine.Engine SELECT user_account.name, count(address.id) AS count \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name \n",
      "HAVING count(address.id) > ?\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,315 INFO sqlalchemy.engine.Engine [generated in 0.00197s] (1,)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('sandy', 2)]\n",
      "2021-10-04 01:45:37,316 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    result = conn.execute(\n",
    "        select(User.name, func.count(Address.id).label(\"count\")).\n",
    "        join(Address).\n",
    "        group_by(User.name).\n",
    "        having(func.count(Address.id) > 1)\n",
    "    )\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b1a9f89",
   "metadata": {},
   "source": [
    "(sqlatutorial:order-by-label)=\n",
    "\n",
    "### Ordering or Grouping by a Label\n",
    "\n",
    "An important technique, in particular on some database backends, is the ability\n",
    "to ORDER BY or GROUP BY an expression that is already stated in the columns\n",
    "clause, without re-stating the expression in the ORDER BY or GROUP BY clause\n",
    "and instead using the column name or labeled name from the COLUMNS clause.\n",
    "This form is available by passing the string text of the name to the\n",
    "{meth}`~sqlalchemy.sql.expression.Select.order_by` or {meth}`~sqlalchemy.sql.expression.Select.group_by` method.  The text\n",
    "passed is **not rendered directly**; instead, the name given to an expression\n",
    "in the columns clause and rendered as that expression name in context, raising an\n",
    "error if no match is found.\n",
    "The unary modifiers {func}`~sqlalchemy.sql.expression.asc` and {func}`~sqlalchemy.sql.expression.desc` may also be used in this form:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "b7e6ce1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT address.user_id, count(address.id) AS num_addresses \n",
      "FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import func, desc\n",
    "stmt = select(\n",
    "        Address.user_id,\n",
    "        func.count(Address.id).label('num_addresses')).\\\n",
    "        group_by(\"user_id\").order_by(\"user_id\", desc(\"num_addresses\"))\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0dab446",
   "metadata": {},
   "source": [
    "(sqlatutorial:using-aliases)=\n",
    "\n",
    "## Using Aliases\n",
    "\n",
    "Now that we are selecting from multiple tables and using joins, we quickly\n",
    "run into the case where we need to refer to the same table mutiple times\n",
    "in the FROM clause of a statement.  We accomplish this using SQL **aliases**,\n",
    "which are a syntax that supplies an alternative name to a table or subquery\n",
    "from which it can be referred towards in the statement.\n",
    "\n",
    "In the SQLAlchemy Expression Language, these \"names\" are instead represented by\n",
    "{class}`~sqlalchemy.sql.expression.FromClause` objects known as the {class}`~sqlalchemy.sql.expression.Alias` construct,\n",
    "which is constructed in Core using the {meth}`~sqlalchemy.sql.expression.FromClause.alias`\n",
    "method. An {class}`~sqlalchemy.sql.expression.Alias` construct is just like a {class}`~sqlalchemy.schema.Table`\n",
    "construct in that it also has a namespace of {class}`~sqlalchemy.schema.Column`\n",
    "objects within the `Alias.c` collection.  The SELECT statement\n",
    "below for example returns all unique pairs of user names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "940642e9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account_1.name, user_account_2.name AS name_1 \n",
      "FROM user_account AS user_account_1 JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id\n"
     ]
    }
   ],
   "source": [
    "user_alias_1 = user_table.alias()\n",
    "user_alias_2 = user_table.alias()\n",
    "print(\n",
    "    select(user_alias_1.c.name, user_alias_2.c.name).\n",
    "    join_from(user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf18be38",
   "metadata": {},
   "source": [
    "(sqlatutorial:orm-entity-aliases)=\n",
    "\n",
    "### ORM Entity Aliases\n",
    "\n",
    "The ORM equivalent of the {meth}`~sqlalchemy.sql.expression.FromClause.alias` method is the\n",
    "ORM {func}`~sqlalchemy.orm.aliased` function, which may be applied to an entity\n",
    "such as `User` and `Address`.  This produces a {class}`~sqlalchemy.sql.expression.Alias` object\n",
    "internally that's against the original mapped {class}`~sqlalchemy.schema.Table` object,\n",
    "while maintaining ORM functionality.  The SELECT below selects from the\n",
    "`User` entity all objects that include two particular email addresses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "722d9ad5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user_account.id = address_2.user_id \n",
      "WHERE address_1.email_address = :email_address_1 AND address_2.email_address = :email_address_2\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.orm import aliased\n",
    "address_alias_1 = aliased(Address)\n",
    "address_alias_2 = aliased(Address)\n",
    "print(\n",
    "    select(User).\n",
    "    join_from(User, address_alias_1).\n",
    "    where(address_alias_1.email_address == 'patrick@aol.com').\n",
    "    join_from(User, address_alias_2).\n",
    "    where(address_alias_2.email_address == 'patrick@gmail.com')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "550d2aea",
   "metadata": {},
   "source": [
    ":::{tip}\n",
    "As mentioned in {ref}`sqlatutorial:select-join-onclause`, the ORM provides\n",
    "for another way to join using the {func}`~sqlalchemy.orm.relationship` construct.\n",
    "The above example using aliases is demonstrated using {func}`~sqlalchemy.orm.relationship`\n",
    "at {ref}`sqlatutorial:joining-relationships-aliased`.\n",
    ":::\n",
    "\n",
    "(sqlatutorial:subqueries-ctes)=\n",
    "\n",
    "## Subqueries and CTEs\n",
    "\n",
    "A subquery in SQL is a SELECT statement that is rendered within parenthesis and\n",
    "placed within the context of an enclosing statement, typically a SELECT\n",
    "statement but not necessarily.\n",
    "\n",
    "This section will cover a so-called \"non-scalar\" subquery, which is typically\n",
    "placed in the FROM clause of an enclosing SELECT.   We will also cover the\n",
    "Common Table Expression or CTE, which is used in a similar way as a subquery,\n",
    "but includes additional features.\n",
    "\n",
    "SQLAlchemy uses the {class}`~sqlalchemy.sql.expression.Subquery` object to represent a subquery and\n",
    "the {class}`~sqlalchemy.sql.expression.CTE` to represent a CTE, usually obtained from the\n",
    "{meth}`~sqlalchemy.sql.expression.Select.subquery` and {meth}`~sqlalchemy.sql.expression.Select.cte` methods, respectively.\n",
    "Either object can be used as a FROM element inside of a larger\n",
    "{func}`~sqlalchemy.sql.expression.select` construct.\n",
    "\n",
    "We can construct a {class}`~sqlalchemy.sql.expression.Subquery` that will select an aggregate count\n",
    "of rows from the `address` table (aggregate functions and GROUP BY were\n",
    "introduced previously at {ref}`sqlatutorial:group-by-w-aggregates`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "2af65ae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "subq = select(\n",
    "    func.count(address_table.c.id).label(\"count\"),\n",
    "    address_table.c.user_id\n",
    ").group_by(address_table.c.user_id).subquery()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da71a215",
   "metadata": {},
   "source": [
    "Stringifying the subquery by itself without it being embedded inside of another\n",
    "{class}`~sqlalchemy.sql.expression.Select` or other statement produces the plain SELECT statement\n",
    "without any enclosing parenthesis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "88e9b08b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(address.id) AS count, address.user_id \n",
      "FROM address GROUP BY address.user_id\n"
     ]
    }
   ],
   "source": [
    "print(subq)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bab44403",
   "metadata": {},
   "source": [
    "The {class}`~sqlalchemy.sql.expression.Subquery` object behaves like any other FROM object such as a {class}`~sqlalchemy.schema.Table`,\n",
    "notably that it includes a `Subquery.c` namespace of the columns which it selects.\n",
    "We can use this namespace to refer to both the `user_id` column as well as our custom labeled `count` expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "86f964a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT anon_1.user_id, anon_1.count \n",
      "FROM (SELECT count(address.id) AS count, address.user_id AS user_id \n",
      "FROM address GROUP BY address.user_id) AS anon_1\n"
     ]
    }
   ],
   "source": [
    "print(select(subq.c.user_id, subq.c.count))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8d10db2",
   "metadata": {},
   "source": [
    "With a selection of rows contained within the `subq` object, we can apply\n",
    "the object to a larger {class}`~sqlalchemy.sql.expression.Select` that will join the data to\n",
    "the `user_account` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c16ea9ec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, user_account.fullname, anon_1.count \n",
      "FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id \n",
      "FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id\n"
     ]
    }
   ],
   "source": [
    "stmt = select(\n",
    "   user_table.c.name,\n",
    "   user_table.c.fullname,\n",
    "   subq.c.count\n",
    ").join_from(user_table, subq)\n",
    "\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5240c051",
   "metadata": {},
   "source": [
    "In order to join from `user_account` to `address`, we made use of the\n",
    "{meth}`~sqlalchemy.sql.expression.Select.join_from` method.   As has been illustrated previously, the\n",
    "ON clause of this join was again **inferred** based on foreign key constraints.\n",
    "Even though a SQL subquery does not itself have any constraints, SQLAlchemy can\n",
    "act upon constraints represented on the columns by determining that the\n",
    "`subq.c.user_id` column is **derived** from the `address_table.c.user_id`\n",
    "column, which does express a foreign key relationship back to the\n",
    "`user_table.c.id` column which is then used to generate the ON clause.\n",
    "\n",
    "### Common Table Expressions (CTEs)\n",
    "\n",
    "Usage of the {class}`~sqlalchemy.sql.expression.CTE` construct in SQLAlchemy is virtually\n",
    "the same as how the {class}`~sqlalchemy.sql.expression.Subquery` construct is used.  By changing\n",
    "the invocation of the {meth}`~sqlalchemy.sql.expression.Select.subquery` method to use\n",
    "{meth}`~sqlalchemy.sql.expression.Select.cte` instead, we can use the resulting object as a FROM\n",
    "element in the same way, but the SQL rendered is the very different common\n",
    "table expression syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "3f97d015",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WITH anon_1 AS \n",
      "(SELECT count(address.id) AS count, address.user_id AS user_id \n",
      "FROM address GROUP BY address.user_id)\n",
      " SELECT user_account.name, user_account.fullname, anon_1.count \n",
      "FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id\n"
     ]
    }
   ],
   "source": [
    "subq = select(\n",
    "    func.count(address_table.c.id).label(\"count\"),\n",
    "    address_table.c.user_id\n",
    ").group_by(address_table.c.user_id).cte()\n",
    "\n",
    "stmt = select(\n",
    "   user_table.c.name,\n",
    "   user_table.c.fullname,\n",
    "   subq.c.count\n",
    ").join_from(user_table, subq)\n",
    "\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3cf5df",
   "metadata": {},
   "source": [
    "The {class}`~sqlalchemy.sql.expression.CTE` construct also features the ability to be used\n",
    "in a \"recursive\" style, and may in more elaborate cases be composed from the\n",
    "RETURNING clause of an INSERT, UPDATE or DELETE statement.  The docstring\n",
    "for {class}`~sqlalchemy.sql.expression.CTE` includes details on these additional patterns.\n",
    "\n",
    "In both cases, the subquery and CTE were named at the SQL level using an\n",
    "\"anonymous\" name.  In the Python code, we don't need to provide these names\n",
    "at all.  The object identity of the {class}`~sqlalchemy.sql.expression.Subquery` or {class}`~sqlalchemy.sql.expression.CTE`\n",
    "instances serves as the syntactical identity of the object when rendered.\n",
    "A name that will be rendered in the SQL can be provided by passing it as the\n",
    "first argument of the {meth}`~sqlalchemy.sql.expression.Select.subquery` or {meth}`~sqlalchemy.sql.expression.Select.cte` methods.\n",
    "\n",
    ":::{seealso}\n",
    "{meth}`~sqlalchemy.sql.expression.Select.subquery` - further detail on subqueries\n",
    "\n",
    "{meth}`~sqlalchemy.sql.expression.Select.cte` - examples for CTE including how to use\n",
    "RECURSIVE as well as DML-oriented CTEs\n",
    ":::\n",
    "\n",
    "### ORM Entity Subqueries/CTEs\n",
    "\n",
    "In the ORM, the {func}`~sqlalchemy.orm.aliased` construct may be used to associate an ORM\n",
    "entity, such as our `User` or `Address` class, with any {class}`~sqlalchemy.sql.expression.FromClause`\n",
    "concept that represents a source of rows.  The preceding section\n",
    "{ref}`sqlatutorial:orm-entity-aliases` illustrates using {func}`~sqlalchemy.orm.aliased`\n",
    "to associate the mapped class with an {class}`~sqlalchemy.sql.expression.Alias` of its\n",
    "mapped {class}`~sqlalchemy.schema.Table`.   Here we illustrate {func}`~sqlalchemy.orm.aliased` doing the same\n",
    "thing against both a {class}`~sqlalchemy.sql.expression.Subquery` as well as a {class}`~sqlalchemy.sql.expression.CTE`\n",
    "generated against a {class}`~sqlalchemy.sql.expression.Select` construct, that ultimately derives\n",
    "from that same mapped {class}`~sqlalchemy.schema.Table`.\n",
    "\n",
    "Below is an example of applying {func}`~sqlalchemy.orm.aliased` to the {class}`~sqlalchemy.sql.expression.Subquery`\n",
    "construct, so that ORM entities can be extracted from its rows.  The result\n",
    "shows a series of `User` and `Address` objects, where the data for\n",
    "each `Address` object ultimately came from a subquery against the\n",
    "`address` table rather than that table directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "bebf418e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,377 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,379 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address \n",
      "FROM user_account JOIN (SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address \n",
      "FROM address \n",
      "WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,380 INFO sqlalchemy.engine.Engine [generated in 0.00057s] ('%@aol.com',)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User('spongebob', 'Spongebob Squarepants') Address('spongebob@sqlalchemy.org')\n",
      "User('sandy', 'Sandy Cheeks') Address('sandy@sqlalchemy.org')\n",
      "User('sandy', 'Sandy Cheeks') Address('sandy@squirrelpower.org')\n",
      "2021-10-04 01:45:37,381 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "subq = select(Address).where(~Address.email_address.like('%@aol.com')).subquery()\n",
    "address_subq = aliased(Address, subq)\n",
    "stmt = select(User, address_subq).join_from(User, address_subq).order_by(User.id, address_subq.id)\n",
    "with Session(engine) as session:\n",
    "    for user, address in session.execute(stmt):\n",
    "        print(f\"{user} {address}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c1931bd",
   "metadata": {},
   "source": [
    "Another example follows, which is exactly the same except it makes use of the\n",
    "{class}`~sqlalchemy.sql.expression.CTE` construct instead:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "22dd445f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,390 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,393 INFO sqlalchemy.engine.Engine WITH anon_1 AS \n",
      "(SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address \n",
      "FROM address \n",
      "WHERE address.email_address NOT LIKE ?)\n",
      " SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address \n",
      "FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,394 INFO sqlalchemy.engine.Engine [generated in 0.00085s] ('%@aol.com',)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User('spongebob', 'Spongebob Squarepants') Address('spongebob@sqlalchemy.org')"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "User('sandy', 'Sandy Cheeks') Address('sandy@sqlalchemy.org')\n",
      "User('sandy', 'Sandy Cheeks') Address('sandy@squirrelpower.org')\n",
      "2021-10-04 01:45:37,396 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "cte = select(Address).where(~Address.email_address.like('%@aol.com')).cte()\n",
    "address_cte = aliased(Address, cte)\n",
    "stmt = select(User, address_cte).join_from(User, address_cte).order_by(User.id, address_cte.id)\n",
    "with Session(engine) as session:\n",
    "    for user, address in session.execute(stmt):\n",
    "        print(f\"{user} {address}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de596d72",
   "metadata": {},
   "source": [
    "(sqlatutorial:scalar-subquery)=\n",
    "\n",
    "## Scalar and Correlated Subqueries\n",
    "\n",
    "A scalar subquery is a subquery that returns exactly zero or one row and\n",
    "exactly one column.  The subquery is then used in the COLUMNS or WHERE clause\n",
    "of an enclosing SELECT statement and is different than a regular subquery in\n",
    "that it is not used in the FROM clause.   A {term}`correlated subquery` is a\n",
    "scalar subquery that refers to a table in the enclosing SELECT statement.\n",
    "\n",
    "SQLAlchemy represents the scalar subquery using the\n",
    "{class}`~sqlalchemy.sql.expression.ScalarSelect` construct, which is part of the\n",
    "{class}`~sqlalchemy.sql.expression.ColumnElement` expression hierarchy, in contrast to the regular\n",
    "subquery which is represented by the {class}`~sqlalchemy.sql.expression.Subquery` construct, which is\n",
    "in the {class}`~sqlalchemy.sql.expression.FromClause` hierarchy.\n",
    "\n",
    "Scalar subqueries are often, but not necessarily, used with aggregate functions,\n",
    "introduced previously at {ref}`sqlatutorial:group-by-w-aggregates`.   A scalar\n",
    "subquery is indicated explicitly by making use of the {meth}`~sqlalchemy.sql.expression.Select.scalar_subquery`\n",
    "method as below.  It's default string form when stringified by itself\n",
    "renders as an ordinary SELECT statement that is selecting from two tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "c978b14e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(SELECT count(address.id) AS count_1 \n",
      "FROM address, user_account \n",
      "WHERE user_account.id = address.user_id)\n"
     ]
    }
   ],
   "source": [
    "subq = select(func.count(address_table.c.id)).\\\n",
    "            where(user_table.c.id == address_table.c.user_id).\\\n",
    "            scalar_subquery()\n",
    "print(subq)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f4de9d8",
   "metadata": {},
   "source": [
    "The above `subq` object now falls within the {class}`~sqlalchemy.sql.expression.ColumnElement`\n",
    "SQL expression hierarchy, in that it may be used like any other column\n",
    "expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "d9c6558e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(SELECT count(address.id) AS count_1 \n",
      "FROM address, user_account \n",
      "WHERE user_account.id = address.user_id) = :param_1\n"
     ]
    }
   ],
   "source": [
    "print(subq == 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d75c5f2",
   "metadata": {},
   "source": [
    "Although the scalar subquery by itself renders both `user_account` and\n",
    "`address` in its FROM clause when stringified by itself, when embedding it\n",
    "into an enclosing {func}`~sqlalchemy.sql.expression.select` construct that deals with the\n",
    "`user_account` table, the `user_account` table is automatically\n",
    "**correlated**, meaning it does not render in the FROM clause of the subquery:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "267c1ca2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT user_account.name, (SELECT count(address.id) AS count_1 \n",
      "FROM address \n",
      "WHERE user_account.id = address.user_id) AS address_count \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "stmt = select(user_table.c.name, subq.label(\"address_count\"))\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a324b27e",
   "metadata": {},
   "source": [
    "Simple correlated subqueries will usually do the right thing that's desired.\n",
    "However, in the case where the correlation is ambiguous, SQLAlchemy will let\n",
    "us know that more clarity is needed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "332b2518",
   "metadata": {
    "tags": [
     "raises-exception"
    ]
   },
   "outputs": [
    {
     "ename": "InvalidRequestError",
     "evalue": "Select statement '<sqlalchemy.sql.selectable.Select object at 0x7fec541ecb80>' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInvalidRequestError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_419/3884627982.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0mjoin_from\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0muser_table\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maddress_table\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0morder_by\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0muser_table\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mid\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maddress_table\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mid\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m__str__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    555\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__str__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    556\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 557\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcompile\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    558\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    559\u001b[0m             return unicode(self.compile()).encode(  # noqa\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36mcompile\u001b[0;34m(self, bind, dialect, **kw)\u001b[0m\n\u001b[1;32m    487\u001b[0m                     ).get_dialect()()\n\u001b[1;32m    488\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 489\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_compiler\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    490\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    491\u001b[0m     def _compile_w_cache(\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_compiler\u001b[0;34m(self, dialect, **kw)\u001b[0m\n\u001b[1;32m    551\u001b[0m         Dialect.\"\"\"\n\u001b[1;32m    552\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 553\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement_compiler\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    554\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    555\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__str__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, dialect, statement, cache_key, column_keys, for_executemany, linting, **kwargs)\u001b[0m\n\u001b[1;32m    763\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtruncated_names\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    764\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 765\u001b[0;31m         \u001b[0mCompiled\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__init__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    766\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    767\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misinsert\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misupdate\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misdelete\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, dialect, statement, schema_translate_map, render_schema_translate, compile_kwargs)\u001b[0m\n\u001b[1;32m    452\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcan_execute\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    453\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_options\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execution_options\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 454\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstring\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprocess\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcompile_kwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    455\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    456\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mrender_schema_translate\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36mprocess\u001b[0;34m(self, obj, **kwargs)\u001b[0m\n\u001b[1;32m    487\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    488\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mprocess\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 489\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_compiler_dispatch\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    490\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    491\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__str__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py\u001b[0m in \u001b[0;36m_compiler_dispatch\u001b[0;34m(self, visitor, **kw)\u001b[0m\n\u001b[1;32m     80\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     81\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 82\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     83\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     84\u001b[0m     cls._compiler_dispatch = (\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36mvisit_select\u001b[0;34m(self, select_stmt, asfrom, fromhints, compound_index, select_wraps_for, lateral, from_linter, **kwargs)\u001b[0m\n\u001b[1;32m   3171\u001b[0m         inner_columns = [\n\u001b[1;32m   3172\u001b[0m             \u001b[0mc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3173\u001b[0;31m             for c in [\n\u001b[0m\u001b[1;32m   3174\u001b[0m                 self._label_select_column(\n\u001b[1;32m   3175\u001b[0m                     \u001b[0mselect_stmt\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36m<listcomp>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m   3172\u001b[0m             \u001b[0mc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3173\u001b[0m             for c in [\n\u001b[0;32m-> 3174\u001b[0;31m                 self._label_select_column(\n\u001b[0m\u001b[1;32m   3175\u001b[0m                     \u001b[0mselect_stmt\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3176\u001b[0m                     \u001b[0mcolumn\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36m_label_select_column\u001b[0;34m(self, select, column, populate_result_map, asfrom, column_clause_args, name, proxy_name, fallback_label_name, within_columns_clause, column_is_repeated, need_column_expressions)\u001b[0m\n\u001b[1;32m   3009\u001b[0m             \u001b[0madd_to_result_map\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0madd_to_result_map\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3010\u001b[0m         )\n\u001b[0;32m-> 3011\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult_expr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_compiler_dispatch\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcolumn_clause_args\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3012\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3013\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mformat_from_hint_text\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msqltext\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhint\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0miscrud\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py\u001b[0m in \u001b[0;36m_compiler_dispatch\u001b[0;34m(self, visitor, **kw)\u001b[0m\n\u001b[1;32m     80\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     81\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 82\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     83\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     84\u001b[0m     cls._compiler_dispatch = (\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36mvisit_label\u001b[0;34m(self, label, add_to_result_map, within_label_clause, within_columns_clause, render_label_as_label, result_map_targets, **kw)\u001b[0m\n\u001b[1;32m   1416\u001b[0m                 )\n\u001b[1;32m   1417\u001b[0m             return (\n\u001b[0;32m-> 1418\u001b[0;31m                 label.element._compiler_dispatch(\n\u001b[0m\u001b[1;32m   1419\u001b[0m                     \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1420\u001b[0m                     \u001b[0mwithin_columns_clause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py\u001b[0m in \u001b[0;36m_compiler_dispatch\u001b[0;34m(self, visitor, **kw)\u001b[0m\n\u001b[1;32m     80\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     81\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 82\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     83\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     84\u001b[0m     cls._compiler_dispatch = (\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36mvisit_grouping\u001b[0;34m(self, grouping, asfrom, **kwargs)\u001b[0m\n\u001b[1;32m   1310\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1311\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mvisit_grouping\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mgrouping\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0masfrom\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1312\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0;34m\"(\"\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mgrouping\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0melement\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_compiler_dispatch\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\")\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1313\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1314\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mvisit_select_statement_grouping\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mgrouping\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py\u001b[0m in \u001b[0;36m_compiler_dispatch\u001b[0;34m(self, visitor, **kw)\u001b[0m\n\u001b[1;32m     80\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     81\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 82\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     83\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     84\u001b[0m     cls._compiler_dispatch = (\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36mvisit_select\u001b[0;34m(self, select_stmt, asfrom, fromhints, compound_index, select_wraps_for, lateral, from_linter, **kwargs)\u001b[0m\n\u001b[1;32m   3140\u001b[0m             \u001b[0;32mdel\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"add_to_result_map\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3141\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3142\u001b[0;31m         froms = self._setup_select_stack(\n\u001b[0m\u001b[1;32m   3143\u001b[0m             \u001b[0mselect_stmt\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompile_state\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mentry\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0masfrom\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlateral\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompound_index\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3144\u001b[0m         )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py\u001b[0m in \u001b[0;36m_setup_select_stack\u001b[0;34m(self, select, compile_state, entry, asfrom, lateral, compound_index)\u001b[0m\n\u001b[1;32m   3310\u001b[0m             )\n\u001b[1;32m   3311\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3312\u001b[0;31m             froms = compile_state._get_display_froms(\n\u001b[0m\u001b[1;32m   3313\u001b[0m                 \u001b[0mexplicit_correlate_froms\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcorrelate_froms\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3314\u001b[0m                 \u001b[0mimplicit_correlate_froms\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0masfrom_froms\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py\u001b[0m in \u001b[0;36m_get_display_froms\u001b[0;34m(self, explicit_correlate_froms, implicit_correlate_froms)\u001b[0m\n\u001b[1;32m   4377\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4378\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfroms\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4379\u001b[0;31m                 raise exc.InvalidRequestError(\n\u001b[0m\u001b[1;32m   4380\u001b[0m                     \u001b[0;34m\"Select statement '%r\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4381\u001b[0m                     \u001b[0;34m\"' returned no FROM clauses \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInvalidRequestError\u001b[0m: Select statement '<sqlalchemy.sql.selectable.Select object at 0x7fec541ecb80>' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually."
     ]
    }
   ],
   "source": [
    "stmt = select(\n",
    "    user_table.c.name,\n",
    "    address_table.c.email_address,\n",
    "    subq.label(\"address_count\")\n",
    ").\\\n",
    "join_from(user_table, address_table).\\\n",
    "order_by(user_table.c.id, address_table.c.id)\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb81a44b",
   "metadata": {},
   "source": [
    "To specify that the `user_table` is the one we seek to correlate we specify\n",
    "this using the {meth}`~sqlalchemy.sql.expression.ScalarSelect.correlate` or\n",
    "{meth}`~sqlalchemy.sql.expression.ScalarSelect.correlate_except` methods:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "819271ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "subq = select(func.count(address_table.c.id)).\\\n",
    "            where(user_table.c.id == address_table.c.user_id).\\\n",
    "            scalar_subquery().correlate(user_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5196d513",
   "metadata": {},
   "source": [
    "The statement then can return the data for this column like any other:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "480d5a5a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,544 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,546 INFO sqlalchemy.engine.Engine SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1 \n",
      "FROM address \n",
      "WHERE user_account.id = address.user_id) AS address_count \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,546 INFO sqlalchemy.engine.Engine [generated in 0.00289s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), ('sandy', 'sandy@squirrelpower.org', 2)]\n",
      "2021-10-04 01:45:37,549 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    result = conn.execute(\n",
    "        select(\n",
    "            user_table.c.name,\n",
    "            address_table.c.email_address,\n",
    "            subq.label(\"address_count\")\n",
    "        ).\n",
    "        join_from(user_table, address_table).\n",
    "        order_by(user_table.c.id, address_table.c.id)\n",
    "    )\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fedbfb70",
   "metadata": {},
   "source": [
    "(sqlatutorial:union)=\n",
    "\n",
    "## UNION, UNION ALL and other set operations\n",
    "\n",
    "In SQL,SELECT statements can be merged together using the UNION or UNION ALL\n",
    "SQL operation, which produces the set of all rows produced by one or more\n",
    "statements together.  Other set operations such as INTERSECT \\[ALL\\] and\n",
    "EXCEPT \\[ALL\\] are also possible.\n",
    "\n",
    "SQLAlchemy's {class}`~sqlalchemy.sql.expression.Select` construct supports compositions of this\n",
    "nature using functions like {func}`~sqlalchemy.sql.expression.union`, {func}`~sqlalchemy.sql.expression.intersect` and\n",
    "{func}`~sqlalchemy.sql.expression.except_`, and the \"all\" counterparts {func}`~sqlalchemy.sql.expression.union_all`,\n",
    "{func}`~sqlalchemy.sql.expression.intersect_all` and {func}`~sqlalchemy.sql.expression.except_all`. These functions all\n",
    "accept an arbitrary number of sub-selectables, which are typically\n",
    "{class}`~sqlalchemy.sql.expression.Select` constructs but may also be an existing composition.\n",
    "\n",
    "The construct produced by these functions is the {class}`~sqlalchemy.sql.expression.CompoundSelect`,\n",
    "which is used in the same manner as the {class}`~sqlalchemy.sql.expression.Select` construct, except\n",
    "that it has fewer methods.   The {class}`~sqlalchemy.sql.expression.CompoundSelect` produced by\n",
    "{func}`~sqlalchemy.sql.expression.union_all` for example may be invoked directly using\n",
    "{meth}`~sqlalchemy.engine.Connection.execute`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "32d62fcd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,556 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,557 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = ?\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,557 INFO sqlalchemy.engine.Engine [generated in 0.00171s] ('sandy', 'spongebob')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]\n",
      "2021-10-04 01:45:37,558 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import union_all\n",
    "stmt1 = select(user_table).where(user_table.c.name == 'sandy')\n",
    "stmt2 = select(user_table).where(user_table.c.name == 'spongebob')\n",
    "u = union_all(stmt1, stmt2)\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(u)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21daf439",
   "metadata": {},
   "source": [
    "To use a {class}`~sqlalchemy.sql.expression.CompoundSelect` as a subquery, just like {class}`~sqlalchemy.sql.expression.Select`\n",
    "it provides a {meth}`~sqlalchemy.sql.expression.SelectBase.subquery` method which will produce a\n",
    "{class}`~sqlalchemy.sql.expression.Subquery` object with a {attr}`~sqlalchemy.sql.expression.FromClause.c`\n",
    "collection that may be referred towards in an enclosing {func}`~sqlalchemy.sql.expression.select`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "f3113214",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,566 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,567 INFO sqlalchemy.engine.Engine SELECT anon_1.name, address.email_address \n",
      "FROM address JOIN (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname \n",
      "FROM user_account \n",
      "WHERE user_account.name = ?) AS anon_1 ON anon_1.id = address.user_id ORDER BY anon_1.name, address.email_address\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,568 INFO sqlalchemy.engine.Engine [generated in 0.00168s] ('sandy', 'spongebob')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]\n",
      "2021-10-04 01:45:37,569 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "u_subq = u.subquery()\n",
    "stmt = (\n",
    "    select(u_subq.c.name, address_table.c.email_address).\n",
    "    join_from(address_table, u_subq).\n",
    "    order_by(u_subq.c.name, address_table.c.email_address)\n",
    ")\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(stmt)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c8d9fbc",
   "metadata": {},
   "source": [
    "(sqlatutorial:exists)=\n",
    "\n",
    "## EXISTS subqueries\n",
    "\n",
    "The SQL EXISTS keyword is an operator that is used with {ref}`scalar subqueries <sqlatutorial:scalar-subquery>` to return a boolean true or false depending on if\n",
    "the SELECT statement would return a row.  SQLAlchemy includes a variant of the\n",
    "{class}`~sqlalchemy.sql.expression.ScalarSelect` object called {class}`~sqlalchemy.sql.expression.Exists`, which will\n",
    "generate an EXISTS subquery and is most conveniently generated using the\n",
    "{meth}`~sqlalchemy.sql.expression.SelectBase.exists` method.  Below we produce an EXISTS so that we\n",
    "can return `user_account` rows that have more than one related row in\n",
    "`address`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "0cb2f3c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,605 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,606 INFO sqlalchemy.engine.Engine SELECT user_account.name \n",
      "FROM user_account \n",
      "WHERE EXISTS (SELECT count(address.id) AS count_1 \n",
      "FROM address \n",
      "WHERE user_account.id = address.user_id GROUP BY address.user_id \n",
      "HAVING count(address.id) > ?)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,609 INFO sqlalchemy.engine.Engine [generated in 0.00400s] (1,)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('sandy',)]\n",
      "2021-10-04 01:45:37,610 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "subq = (\n",
    "    select(func.count(address_table.c.id)).\n",
    "    where(user_table.c.id == address_table.c.user_id).\n",
    "    group_by(address_table.c.user_id).\n",
    "    having(func.count(address_table.c.id) > 1)\n",
    ").exists()\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(\n",
    "        select(user_table.c.name).where(subq)\n",
    "    )\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cf8d3f1",
   "metadata": {},
   "source": [
    "The EXISTS construct is more often than not used as a negation, e.g. NOT EXISTS,\n",
    "as it provides a SQL-efficient form of locating rows for which a related\n",
    "table has no rows.  Below we select user names that have no email addresses;\n",
    "note the binary negation operator (`~`) used inside the second WHERE\n",
    "clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "4f76262a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,617 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,618 INFO sqlalchemy.engine.Engine SELECT user_account.name \n",
      "FROM user_account \n",
      "WHERE NOT (EXISTS (SELECT address.id \n",
      "FROM address \n",
      "WHERE user_account.id = address.user_id))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,618 INFO sqlalchemy.engine.Engine [generated in 0.00168s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('patrick',)]\n",
      "2021-10-04 01:45:37,619 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "subq = (\n",
    "    select(address_table.c.id).\n",
    "    where(user_table.c.id == address_table.c.user_id)\n",
    ").exists()\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(\n",
    "        select(user_table.c.name).where(~subq)\n",
    "    )\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03225353",
   "metadata": {},
   "source": [
    "(sqlatutorial:functions)=\n",
    "\n",
    "## Working with SQL Functions\n",
    "\n",
    "First introduced earlier in this section at\n",
    "{ref}`sqlatutorial:group-by-w-aggregates`, the {data}`~sqlalchemy.sql.expression.func` object serves as a\n",
    "factory for creating new {class}`~sqlalchemy.sql.functions.Function` objects, which when used\n",
    "in a construct like {func}`~sqlalchemy.sql.expression.select`, produce a SQL function display,\n",
    "typically consisting of a name, some parenthesis (although not always), and\n",
    "possibly some arguments. Examples of typical SQL functions include:\n",
    "\n",
    "- the `count()` function, an aggregate function which counts how many\n",
    "  rows are returned:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "b58b242d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(*) AS count_1 \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "print(select(func.count()).select_from(user_table))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8959ed66",
   "metadata": {},
   "source": [
    "- the `lower()` function, a string function that converts a string to lower\n",
    "  case:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "9e1a23b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT lower(:lower_2) AS lower_1\n"
     ]
    }
   ],
   "source": [
    "print(select(func.lower(\"A String With Much UPPERCASE\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d5f4713",
   "metadata": {},
   "source": [
    "- the `now()` function, which provides for the current date and time; as this\n",
    "  is a common function, SQLAlchemy knows how to render this differently for each\n",
    "  backend, in the case of SQLite using the CURRENT_TIMESTAMP function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "86f56f1b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,638 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,639 INFO sqlalchemy.engine.Engine SELECT CURRENT_TIMESTAMP AS now_1\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,640 INFO sqlalchemy.engine.Engine [generated in 0.00166s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(datetime.datetime(2021, 10, 4, 1, 45, 37),)]\n",
      "2021-10-04 01:45:37,641 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "stmt = select(func.now())\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(stmt)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c30d2df6",
   "metadata": {},
   "source": [
    "As most database backends feature dozens if not hundreds of different SQL\n",
    "functions, {data}`~sqlalchemy.sql.expression.func` tries to be as liberal as possible in what it\n",
    "accepts. Any name that is accessed from this namespace is automatically\n",
    "considered to be a SQL function that will render in a generic way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "54713a84",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1 \n",
      "FROM user_account\n"
     ]
    }
   ],
   "source": [
    "print(select(func.some_crazy_function(user_table.c.name, 17)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea1bf9b2",
   "metadata": {},
   "source": [
    "At the same time, a relatively small set of extremely common SQL functions such\n",
    "as {class}`~sqlalchemy.sql.functions.count`, {class}`~sqlalchemy.sql.functions.now`, {class}`~sqlalchemy.sql.functions.max`,\n",
    "{class}`~sqlalchemy.sql.functions.concat` include pre-packaged versions of themselves which\n",
    "provide for proper typing information as well as backend-specific SQL\n",
    "generation in some cases.  The example below contrasts the SQL generation\n",
    "that occurs for the PostgreSQL dialect compared to the Oracle dialect for\n",
    "the {class}`~sqlalchemy.sql.functions.now` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "ece00c83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT now() AS now_1\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.dialects import postgresql\n",
    "print(select(func.now()).compile(dialect=postgresql.dialect()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "f4b8c6a0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.dialects import oracle\n",
    "print(select(func.now()).compile(dialect=oracle.dialect()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57ca4ffa",
   "metadata": {},
   "source": [
    "### Functions Have Return Types\n",
    "\n",
    "As functions are column expressions, they also have\n",
    "SQL {ref}`datatypes <types_toplevel>` that describe the data type of\n",
    "a generated SQL expression.  We refer to these types here as \"SQL return types\",\n",
    "in reference to the type of SQL value that is returned by the function\n",
    "in the context of a database-side SQL expression,\n",
    "as opposed to the \"return type\" of a Python function.\n",
    "\n",
    "The SQL return type of any SQL function may be accessed, typically for\n",
    "debugging purposes, by referring to the {attr}`~sqlalchemy.sql.functions.Function.type`\n",
    "attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "788cdc4c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DateTime()"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "func.now().type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b1cf87b",
   "metadata": {},
   "source": [
    "These SQL return types are significant when making\n",
    "use of the function expression in the context of a larger expression; that is,\n",
    "math operators will work better when the datatype of the expression is\n",
    "something like {class}`~sqlalchemy.types.Integer` or {class}`~sqlalchemy.types.Numeric`, JSON\n",
    "accessors in order to work need to be using a type such as\n",
    "{class}`~sqlalchemy.types.JSON`.  Certain classes of functions return entire rows\n",
    "instead of column values, where there is a need to refer to specific columns;\n",
    "such functions are referred towards\n",
    "as {ref}`table valued functions <sqlatutorial:functions-table-valued>`.\n",
    "\n",
    "The SQL return type of the function may also be significant when executing a\n",
    "statement and getting rows back, for those cases where SQLAlchemy has to apply\n",
    "result-set processing. A prime example of this are date-related functions on\n",
    "SQLite, where SQLAlchemy's {class}`~sqlalchemy.types.DateTime` and related datatypes take\n",
    "on the role of converting from string values to Python `datetime()` objects\n",
    "as result rows are received.\n",
    "\n",
    "To apply a specific type to a function we're creating, we pass it using the\n",
    "{paramref}`~sqlalchemy.sql.functions.Function.type_` parameter; the type argument may be\n",
    "either a {class}`~sqlalchemy.types.TypeEngine` class or an instance.  In the example\n",
    "below we pass the {class}`~sqlalchemy.types.JSON` class to generate the PostgreSQL\n",
    "`json_object()` function, noting that the SQL return type will be of\n",
    "type JSON:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "3e315215",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import JSON\n",
    "function_expr = func.json_object('{a, 1, b, \"def\", c, 3.5}', type_=JSON)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74316163",
   "metadata": {},
   "source": [
    "By creating our JSON function with the {class}`~sqlalchemy.types.JSON` datatype, the\n",
    "SQL expression object takes on JSON-related features, such as that of accessing\n",
    "elements:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "7bddb863",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT json_object(:json_object_1)[:json_object_2] AS anon_1\n"
     ]
    }
   ],
   "source": [
    "stmt = select(function_expr[\"def\"])\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "831b265f",
   "metadata": {},
   "source": [
    "### Built-in Functions Have Pre-Configured Return Types\n",
    "\n",
    "For common aggregate functions like {class}`~sqlalchemy.sql.functions.count`,\n",
    "{class}`~sqlalchemy.sql.functions.max`, {class}`~sqlalchemy.sql.functions.min` as well as a very small number\n",
    "of date functions like {class}`~sqlalchemy.sql.functions.now` and string functions like\n",
    "{class}`~sqlalchemy.sql.functions.concat`, the SQL return type is set up appropriately,\n",
    "sometimes based on usage. The {class}`~sqlalchemy.sql.functions.max` function and similar\n",
    "aggregate filtering functions will set up the SQL return type based on the\n",
    "argument given:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "a395008d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Integer()"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m1 = func.max(Column(\"some_int\", Integer))\n",
    "m1.type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "c50dcf92",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "String()"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m2 = func.max(Column(\"some_str\", String))\n",
    "m2.type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87aecbdd",
   "metadata": {},
   "source": [
    "Date and time functions typically correspond to SQL expressions described by\n",
    "{class}`~sqlalchemy.types.DateTime`, {class}`~sqlalchemy.types.Date` or {class}`~sqlalchemy.types.Time`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "9bb0beaa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DateTime()"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "func.now().type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "1c9f2b6e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date()"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "func.current_date().type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88099493",
   "metadata": {},
   "source": [
    "A known string function such as {class}`~sqlalchemy.sql.functions.concat`\n",
    "will know that a SQL expression would be of type {class}`~sqlalchemy.types.String`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "8ed8d420",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "String()"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "func.concat(\"x\", \"y\").type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fada349",
   "metadata": {},
   "source": [
    "However, for the vast majority of SQL functions, SQLAlchemy does not have them\n",
    "explicitly present in its very small list of known functions.  For example,\n",
    "while there is typically no issue using SQL functions `func.lower()`\n",
    "and `func.upper()` to convert the casing of strings, SQLAlchemy doesn't\n",
    "actually know about these functions, so they have a \"null\" SQL return type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "65434637",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NullType()"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "func.upper(\"lowercase\").type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff32a41a",
   "metadata": {},
   "source": [
    "For simple functions like `upper` and `lower`, the issue is not usually\n",
    "significant, as string values may be received from the database without any\n",
    "special type handling on the SQLAlchemy side, and SQLAlchemy's type\n",
    "coercion rules can often correctly guess intent as well; the Python `+`\n",
    "operator for example will be correctly interpreted as the string concatenation\n",
    "operator based on looking at both sides of the expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "163f3dae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT upper(:upper_1) || :upper_2 AS anon_1\n"
     ]
    }
   ],
   "source": [
    "print(select(func.upper(\"lowercase\") + \" suffix\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32607488",
   "metadata": {},
   "source": [
    "Overall, the scenario where the\n",
    "{paramref}`~sqlalchemy.sql.functions.Function.type_` parameter is likely necessary is:\n",
    "\n",
    "1. the function is not already a SQLAlchemy built-in function; this can be\n",
    "   evidenced by creating the function and observing the {attr}`~sqlalchemy.sql.functions.Function.type`\n",
    "   attribute, that is:\n",
    "\n",
    "   ```python\n",
    "   func.count().type\n",
    "   Integer()\n",
    "   ```\n",
    "\n",
    "   vs.:\n",
    "\n",
    "   ```python\n",
    "   func.json_object('{\"a\", \"b\"}').type\n",
    "   NullType()\n",
    "   ```\n",
    "\n",
    "2. Function-aware expression support is needed; this most typically refers to\n",
    "   special operators related to datatypes such as {class}`~sqlalchemy.types.JSON` or\n",
    "   {class}`~sqlalchemy.types.ARRAY`\n",
    "\n",
    "3. Result value processing is needed, which may include types such as\n",
    "   {class}`~sqlalchemy.types.DateTime`, {class}`~sqlalchemy.types.Boolean`, {class}`~sqlalchemy.types.Enum`,\n",
    "   or again special datatypes such as {class}`~sqlalchemy.types.JSON`,\n",
    "   {class}`~sqlalchemy.types.ARRAY`.\n",
    "\n",
    "(sqlatutorial:window-functions)=\n",
    "\n",
    "### Using Window Functions\n",
    "\n",
    "A window function is a special use of a SQL aggregate function which calculates\n",
    "the aggregate value over the rows being returned in a group as the individual\n",
    "result rows are processed.  Whereas a function like `MAX()` will give you\n",
    "the highest value of a column within a set of rows, using the same function\n",
    "as a \"window function\" will given you the highest value for each row,\n",
    "*as of that row*.\n",
    "\n",
    "In SQL, window functions allow one to specify the rows over which the\n",
    "function should be applied, a \"partition\" value which considers the window\n",
    "over different sub-sets of rows, and an \"order by\" expression which importantly\n",
    "indicates the order in which rows should be applied to the aggregate function.\n",
    "\n",
    "In SQLAlchemy, all SQL functions generated by the {data}`~sqlalchemy.sql.expression.func` namespace\n",
    "include a method {meth}`~sqlalchemy.sql.functions.FunctionElement.over` which\n",
    "grants the window function, or \"OVER\", syntax; the construct produced\n",
    "is the {class}`~sqlalchemy.sql.expression.Over` construct.\n",
    "\n",
    "A common function used with window functions is the `row_number()` function\n",
    "which simply counts rows. We may partition this row count against user name to\n",
    "number the email addresses of individual users.\n",
    "\n",
    ":::{important}\n",
    "Window functions only available in SQLite version [3.25](https://www.sqlite.org/releaselog/3_25_0.html) or newer.\n",
    ":::"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "1f8581f9",
   "metadata": {
    "tags": [
     "raises-exception",
     "hide-output"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,745 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,745 INFO sqlalchemy.engine.Engine SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1, user_account.name, address.email_address \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,746 INFO sqlalchemy.engine.Engine [generated in 0.00171s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,747 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "(sqlite3.OperationalError) near \"(\": syntax error\n[SQL: SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1, user_account.name, address.email_address \nFROM user_account JOIN address ON user_account.id = address.user_id]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1770\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1771\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1772\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: near \"(\": syntax error",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_419/2481446575.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m ).select_from(user_table).join(address_table)\n\u001b[1;32m      6\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m     \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/future/engine.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[1;32m    278\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    279\u001b[0m         \"\"\"\n\u001b[0;32m--> 280\u001b[0;31m         return self._execute_20(\n\u001b[0m\u001b[1;32m    281\u001b[0m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mNO_OPTIONS\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    282\u001b[0m         )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_20\u001b[0;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[1;32m   1581\u001b[0m             )\n\u001b[1;32m   1582\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1583\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs_10style\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwargs_10style\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1584\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1585\u001b[0m     def exec_driver_sql(\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params, execution_options, _force)\u001b[0m\n\u001b[1;32m    321\u001b[0m     ):\n\u001b[1;32m    322\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0m_force\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 323\u001b[0;31m             return connection._execute_clauseelement(\n\u001b[0m\u001b[1;32m    324\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    325\u001b[0m             )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params, execution_options)\u001b[0m\n\u001b[1;32m   1450\u001b[0m             \u001b[0mlinting\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcompiler_linting\u001b[0m \u001b[0;34m|\u001b[0m \u001b[0mcompiler\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mWARN_LINTING\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1451\u001b[0m         )\n\u001b[0;32m-> 1452\u001b[0;31m         ret = self._execute_context(\n\u001b[0m\u001b[1;32m   1453\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1454\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init_compiled\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1812\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1813\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1814\u001b[0;31m             self._handle_dbapi_exception(\n\u001b[0m\u001b[1;32m   1815\u001b[0m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1816\u001b[0m             )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1993\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1994\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1995\u001b[0;31m                 util.raise_(\n\u001b[0m\u001b[1;32m   1996\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1997\u001b[0m                 )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    208\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    209\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1769\u001b[0m                             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1770\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1771\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1772\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1773\u001b[0m                     )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    715\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    719\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) near \"(\": syntax error\n[SQL: SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1, user_account.name, address.email_address \nFROM user_account JOIN address ON user_account.id = address.user_id]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"
     ]
    }
   ],
   "source": [
    "stmt = select(\n",
    "    func.row_number().over(partition_by=user_table.c.name),\n",
    "    user_table.c.name,\n",
    "    address_table.c.email_address\n",
    ").select_from(user_table).join(address_table)\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(stmt)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "302b75dd",
   "metadata": {},
   "source": [
    "Above, the {paramref}`~sqlalchemy.sql.functions.FunctionElement.over.partition_by` parameter\n",
    "is used so that the `PARTITION BY` clause is rendered within the OVER clause.\n",
    "We also may make use of the `ORDER BY` clause using {paramref}`~sqlalchemy.sql.functions.FunctionElement.over.order_by`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "4705c3f0",
   "metadata": {
    "tags": [
     "raises-exception",
     "hide-output"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,769 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,769 INFO sqlalchemy.engine.Engine SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1, user_account.name, address.email_address \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,770 INFO sqlalchemy.engine.Engine [generated in 0.00122s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,771 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "(sqlite3.OperationalError) near \"(\": syntax error\n[SQL: SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1, user_account.name, address.email_address \nFROM user_account JOIN address ON user_account.id = address.user_id]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1770\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1771\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1772\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: near \"(\": syntax error",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_419/2620936865.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      4\u001b[0m     address_table.c.email_address).select_from(user_table).join(address_table)\n\u001b[1;32m      5\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m     \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/future/engine.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[1;32m    278\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    279\u001b[0m         \"\"\"\n\u001b[0;32m--> 280\u001b[0;31m         return self._execute_20(\n\u001b[0m\u001b[1;32m    281\u001b[0m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mNO_OPTIONS\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    282\u001b[0m         )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_20\u001b[0;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[1;32m   1581\u001b[0m             )\n\u001b[1;32m   1582\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1583\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs_10style\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwargs_10style\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1584\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1585\u001b[0m     def exec_driver_sql(\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params, execution_options, _force)\u001b[0m\n\u001b[1;32m    321\u001b[0m     ):\n\u001b[1;32m    322\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0m_force\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 323\u001b[0;31m             return connection._execute_clauseelement(\n\u001b[0m\u001b[1;32m    324\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    325\u001b[0m             )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params, execution_options)\u001b[0m\n\u001b[1;32m   1450\u001b[0m             \u001b[0mlinting\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcompiler_linting\u001b[0m \u001b[0;34m|\u001b[0m \u001b[0mcompiler\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mWARN_LINTING\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1451\u001b[0m         )\n\u001b[0;32m-> 1452\u001b[0;31m         ret = self._execute_context(\n\u001b[0m\u001b[1;32m   1453\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1454\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init_compiled\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1812\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1813\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1814\u001b[0;31m             self._handle_dbapi_exception(\n\u001b[0m\u001b[1;32m   1815\u001b[0m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1816\u001b[0m             )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1993\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1994\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1995\u001b[0;31m                 util.raise_(\n\u001b[0m\u001b[1;32m   1996\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1997\u001b[0m                 )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    208\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    209\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1769\u001b[0m                             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1770\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1771\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1772\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1773\u001b[0m                     )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    715\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    719\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) near \"(\": syntax error\n[SQL: SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1, user_account.name, address.email_address \nFROM user_account JOIN address ON user_account.id = address.user_id]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"
     ]
    }
   ],
   "source": [
    "stmt = select(\n",
    "    func.count().over(order_by=user_table.c.name),\n",
    "    user_table.c.name,\n",
    "    address_table.c.email_address).select_from(user_table).join(address_table)\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(stmt)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "176aad38",
   "metadata": {},
   "source": [
    "Further options for window functions include usage of ranges; see\n",
    "{func}`~sqlalchemy.sql.expression.over` for more examples.\n",
    "\n",
    ":::{tip}\n",
    "It's important to note that the {meth}`~sqlalchemy.sql.functions.FunctionElement.over`\n",
    "method only applies to those SQL functions which are in fact aggregate\n",
    "functions; while the {class}`~sqlalchemy.sql.expression.Over` construct will happily render itself\n",
    "for any SQL function given, the database will reject the expression if the\n",
    "function itself is not a SQL aggregate function.\n",
    ":::\n",
    "\n",
    "(sqlatutorial:functions-within-group)=\n",
    "\n",
    "### Special Modifiers WITHIN GROUP, FILTER\n",
    "\n",
    "The \"WITHIN GROUP\" SQL syntax is used in conjunction with an \"ordered set\"\n",
    "or a \"hypothetical set\" aggregate\n",
    "function.  Common \"ordered set\" functions include `percentile_cont()`\n",
    "and `rank()`.  SQLAlchemy includes built in implementations\n",
    "{class}`~sqlalchemy.sql.functions.rank`, {class}`~sqlalchemy.sql.functions.dense_rank`,\n",
    "{class}`~sqlalchemy.sql.functions.mode`, {class}`~sqlalchemy.sql.functions.percentile_cont` and\n",
    "{class}`~sqlalchemy.sql.functions.percentile_disc` which include a {meth}`~sqlalchemy.sql.functions.FunctionElement.within_group`\n",
    "method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "d9fbee97",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    func.unnest(\n",
    "        func.percentile_disc([0.25,0.5,0.75,1]).within_group(user_table.c.name)\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c495e09b",
   "metadata": {},
   "source": [
    "\"FILTER\" is supported by some backends to limit the range of an aggregate function to a\n",
    "particular subset of rows compared to the total range of rows returned, available\n",
    "using the {meth}`~sqlalchemy.sql.functions.FunctionElement.filter` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "eef98e3b",
   "metadata": {
    "tags": [
     "raises-exception",
     "hide-output"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,797 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,798 INFO sqlalchemy.engine.Engine SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1, count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2 \n",
      "FROM user_account JOIN address ON user_account.id = address.user_id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,799 INFO sqlalchemy.engine.Engine [generated in 0.00182s] ('sandy', 'spongebob')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,800 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "(sqlite3.OperationalError) near \"(\": syntax error\n[SQL: SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1, count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2 \nFROM user_account JOIN address ON user_account.id = address.user_id]\n[parameters: ('sandy', 'spongebob')]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1770\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1771\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1772\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: near \"(\": syntax error",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_419/1232000870.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      4\u001b[0m ).select_from(user_table).join(address_table)\n\u001b[1;32m      5\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m     \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/future/engine.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[1;32m    278\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    279\u001b[0m         \"\"\"\n\u001b[0;32m--> 280\u001b[0;31m         return self._execute_20(\n\u001b[0m\u001b[1;32m    281\u001b[0m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mNO_OPTIONS\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    282\u001b[0m         )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_20\u001b[0;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[1;32m   1581\u001b[0m             )\n\u001b[1;32m   1582\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1583\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs_10style\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwargs_10style\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1584\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1585\u001b[0m     def exec_driver_sql(\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params, execution_options, _force)\u001b[0m\n\u001b[1;32m    321\u001b[0m     ):\n\u001b[1;32m    322\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0m_force\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 323\u001b[0;31m             return connection._execute_clauseelement(\n\u001b[0m\u001b[1;32m    324\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    325\u001b[0m             )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params, execution_options)\u001b[0m\n\u001b[1;32m   1450\u001b[0m             \u001b[0mlinting\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcompiler_linting\u001b[0m \u001b[0;34m|\u001b[0m \u001b[0mcompiler\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mWARN_LINTING\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1451\u001b[0m         )\n\u001b[0;32m-> 1452\u001b[0;31m         ret = self._execute_context(\n\u001b[0m\u001b[1;32m   1453\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1454\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init_compiled\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1812\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1813\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1814\u001b[0;31m             self._handle_dbapi_exception(\n\u001b[0m\u001b[1;32m   1815\u001b[0m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1816\u001b[0m             )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1993\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1994\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1995\u001b[0;31m                 util.raise_(\n\u001b[0m\u001b[1;32m   1996\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1997\u001b[0m                 )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    208\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    209\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1769\u001b[0m                             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1770\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1771\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1772\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1773\u001b[0m                     )\n",
      "\u001b[0;32m~/checkouts/readthedocs.org/user_builds/sqla-tutorials-nb/envs/latest/lib/python3.8/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    715\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    719\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) near \"(\": syntax error\n[SQL: SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1, count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2 \nFROM user_account JOIN address ON user_account.id = address.user_id]\n[parameters: ('sandy', 'spongebob')]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"
     ]
    }
   ],
   "source": [
    "stmt = select(\n",
    "    func.count(address_table.c.email_address).filter(user_table.c.name == 'sandy'),\n",
    "    func.count(address_table.c.email_address).filter(user_table.c.name == 'spongebob')\n",
    ").select_from(user_table).join(address_table)\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(stmt)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a92c43c",
   "metadata": {},
   "source": [
    "(sqlatutorial:functions-table-valued)=\n",
    "\n",
    "### Table-Valued Functions\n",
    "\n",
    "Table-valued SQL functions support a scalar representation that contains named\n",
    "sub-elements. Often used for JSON and ARRAY-oriented functions as well as\n",
    "functions like `generate_series()`, the table-valued function is specified in\n",
    "the FROM clause, and is then referred towards as a table, or sometimes even as\n",
    "a column. Functions of this form are prominent within the PostgreSQL database,\n",
    "however some forms of table valued functions are also supported by SQLite,\n",
    "Oracle, and SQL Server.\n",
    "\n",
    ":::{seealso}\n",
    "{ref}`postgresql_table_valued_overview` - in the {ref}`postgresql_toplevel` documentation.\n",
    "\n",
    "While many databases support table valued and other special\n",
    "forms, PostgreSQL tends to be where there is the most demand for these\n",
    "features.   See this section for additional examples of PostgreSQL\n",
    "syntaxes as well as additional features.\n",
    ":::\n",
    "\n",
    "SQLAlchemy provides the {meth}`~sqlalchemy.sql.functions.FunctionElement.table_valued` method\n",
    "as the basic \"table valued function\" construct, which will convert a\n",
    "{data}`~sqlalchemy.sql.expression.func` object into a FROM clause containing a series of named\n",
    "columns, based on string names passed positionally. This returns a\n",
    "{class}`~sqlalchemy.sql.expression.TableValuedAlias` object, which is a function-enabled\n",
    "{class}`~sqlalchemy.sql.expression.Alias` construct that may be used as any other FROM clause as\n",
    "introduced at {ref}`sqlatutorial:using-aliases`. Below we illustrate the\n",
    "`json_each()` function, which while common on PostgreSQL is also supported by\n",
    "modern versions of SQLite:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "fe492621",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,821 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,821 INFO sqlalchemy.engine.Engine SELECT anon_1.value \n",
      "FROM json_each(?) AS anon_1 \n",
      "WHERE anon_1.value IN (?, ?)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:37,822 INFO sqlalchemy.engine.Engine [generated in 0.00126s] ('[\"one\", \"two\", \"three\"]', 'two', 'three')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('two',), ('three',)]\n",
      "2021-10-04 01:45:37,823 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "onetwothree = func.json_each('[\"one\", \"two\", \"three\"]').table_valued(\"value\")\n",
    "stmt = select(onetwothree).where(onetwothree.c.value.in_([\"two\", \"three\"]))\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(stmt)\n",
    "    print(result.all())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03589916",
   "metadata": {},
   "source": [
    "Above, we used the `json_each()` JSON function supported by SQLite and\n",
    "PostgreSQL to generate a table valued expression with a single column referred\n",
    "towards as `value`, and then selected two of its three rows.\n",
    "\n",
    ":::{seealso}\n",
    "{ref}`postgresql_table_valued` - in the {ref}`postgresql_toplevel` documentation -\n",
    "this section will detail additional syntaxes such as special column derivations\n",
    "and \"WITH ORDINALITY\" that are known to work with PostgreSQL.\n",
    ":::\n",
    "\n",
    "(sqlatutorial:functions-column-valued)=\n",
    "\n",
    "### Column Valued Functions - Table Valued Function as a Scalar Column\n",
    "\n",
    "A special syntax supported by PostgreSQL and Oracle is that of referring\n",
    "towards a function in the FROM clause, which then delivers itself as a\n",
    "single column in the columns clause of a SELECT statement or other column\n",
    "expression context.  PostgreSQL makes great use of this syntax for such\n",
    "functions as `json_array_elements()`, `json_object_keys()`,\n",
    "`json_each_text()`, `json_each()`, etc.\n",
    "\n",
    "SQLAlchemy refers to this as a \"column valued\" function and is available\n",
    "by applying the {meth}`~sqlalchemy.sql.functions.FunctionElement.column_valued` modifier\n",
    "to a {class}`~sqlalchemy.sql.functions.Function` construct:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "f5e3d28f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT x \n",
      "FROM json_array_elements(:json_array_elements_1) AS x\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import select, func\n",
    "stmt = select(func.json_array_elements('[\"one\", \"two\"]').column_valued(\"x\"))\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba6b084d",
   "metadata": {},
   "source": [
    "The \"column valued\" form is also supported by the Oracle dialect, where\n",
    "it is usable for custom SQL functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "59a1eab6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT COLUMN_VALUE s \n",
      "FROM TABLE (scalar_strings(:scalar_strings_1)) s\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.dialects import oracle\n",
    "stmt = select(func.scalar_strings(5).column_valued(\"s\"))\n",
    "print(stmt.compile(dialect=oracle.dialect()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f351234c",
   "metadata": {},
   "source": [
    ":::{seealso}\n",
    "{ref}`postgresql_column_valued` - in the {ref}`postgresql_toplevel` documentation.\n",
    ":::"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "text_representation": {
    "extension": ".md",
    "format_name": "myst"
   }
  },
  "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.8.6"
  },
  "source_map": [
   16,
   35,
   90,
   99,
   103,
   111,
   115,
   125,
   130,
   153,
   155,
   163,
   165,
   177,
   179,
   189,
   192,
   196,
   198,
   206,
   208,
   214,
   217,
   223,
   229,
   245,
   255,
   281,
   290,
   302,
   312,
   330,
   333,
   338,
   340,
   345,
   351,
   356,
   364,
   370,
   381,
   388,
   392,
   410,
   412,
   417,
   419,
   426,
   431,
   436,
   441,
   456,
   461,
   469,
   474,
   499,
   505,
   524,
   531,
   563,
   565,
   571,
   573,
   596,
   600,
   619,
   628,
   645,
   652,
   672,
   679,
   692,
   703,
   735,
   740,
   746,
   748,
   754,
   756,
   762,
   770,
   790,
   803,
   842,
   849,
   854,
   861,
   885,
   890,
   896,
   898,
   906,
   909,
   915,
   926,
   932,
   936,
   940,
   952,
   976,
   984,
   991,
   1001,
   1015,
   1027,
   1035,
   1045,
   1061,
   1063,
   1068,
   1070,
   1076,
   1081,
   1088,
   1090,
   1100,
   1105,
   1108,
   1123,
   1125,
   1151,
   1154,
   1160,
   1163,
   1175,
   1180,
   1183,
   1188,
   1192,
   1194,
   1199,
   1201,
   1209,
   1211,
   1220,
   1222,
   1281,
   1292,
   1298,
   1308,
   1334,
   1340,
   1346,
   1356,
   1389,
   1395,
   1422,
   1426,
   1431,
   1435
  ]
 },
 "nbformat": 4,
 "nbformat_minor": 5
}