{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e149a34b",
   "metadata": {},
   "source": [
    "(sqlatutorial:working-with-metadata)=\n",
    "\n",
    "# Working with Database Metadata\n",
    "\n",
    "With engines and SQL execution down, we are ready to begin some Alchemy.\n",
    "The central element of both SQLAlchemy Core and ORM is the SQL Expression\n",
    "Language which allows for fluent, composable construction of SQL queries.\n",
    "The foundation for these queries are Python objects that represent database\n",
    "concepts like tables and columns.   These objects are known collectively\n",
    "as {term}`database metadata`.\n",
    "\n",
    "The most common foundational objects for database metadata in SQLAlchemy are\n",
    "known as  {class}`~sqlalchemy.schema.MetaData`, {class}`~sqlalchemy.schema.Table`, and {class}`~sqlalchemy.schema.Column`.\n",
    "The sections below will illustrate how these objects are used in both a\n",
    "Core-oriented style as well as an ORM-oriented style.\n",
    "\n",
    ":::{div} orm-header\n",
    "\n",
    "**ORM readers, stay with us!**\n",
    "\n",
    "As with other sections, Core users can skip the ORM sections, but ORM users\n",
    "would best be familiar with these objects from both perspectives.\n",
    ":::\n",
    "\n",
    "<!--\n",
    ".. rst-class:: core-header\n",
    "-->\n",
    "\n",
    "(sqlatutorial:core-metadata)=\n",
    "\n",
    "## Setting up MetaData with Table objects\n",
    "\n",
    "When we work with a relational database, the basic structure that we create and\n",
    "query from is known as a **table**.   In SQLAlchemy, the \"table\" is represented\n",
    "by a Python object similarly named {class}`~sqlalchemy.schema.Table`.\n",
    "\n",
    "To start using the SQLAlchemy Expression Language,\n",
    "we will want to have {class}`~sqlalchemy.schema.Table` objects constructed that represent\n",
    "all of the database tables we are interested in working with.   Each\n",
    "{class}`~sqlalchemy.schema.Table` may be **declared**, meaning we explicitly spell out\n",
    "in source code what the table looks like, or may be **reflected**, which means\n",
    "we generate the object based on what's already present in a particular database.\n",
    "The two approaches can also be blended in many ways.\n",
    "\n",
    "Whether we will declare or reflect our tables, we start out with a collection\n",
    "that will be where we place our tables known as the {class}`~sqlalchemy.schema.MetaData`\n",
    "object.  This object is essentially a {term}`facade` around a Python dictionary\n",
    "that stores a series of {class}`~sqlalchemy.schema.Table` objects keyed to their string name.\n",
    "Constructing this object looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "232db563",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData\n",
    "metadata = MetaData()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91ae546b",
   "metadata": {},
   "source": [
    "Having a single {class}`~sqlalchemy.schema.MetaData` object for an entire application is\n",
    "the most common case, represented as a module-level variable in a single place\n",
    "in an application, often in a \"models\" or \"dbschema\" type of package.  There\n",
    "can be multiple {class}`~sqlalchemy.schema.MetaData` collections as well,  however\n",
    "it's typically most helpful if a series of {class}`~sqlalchemy.schema.Table` objects that are\n",
    "related to each other belong to a single {class}`~sqlalchemy.schema.MetaData` collection.\n",
    "\n",
    "Once we have a {class}`~sqlalchemy.schema.MetaData` object, we can declare some\n",
    "{class}`~sqlalchemy.schema.Table` objects.  This tutorial will start with the classic\n",
    "SQLAlchemy tutorial model, that of the table `user`, which would for\n",
    "example represent the users of a website, and the table `address`,\n",
    "representing a list of email addresses associated with rows in the `user`\n",
    "table.   We normally assign each {class}`~sqlalchemy.schema.Table` object to a variable\n",
    "that will be how we will refer to the table in application code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1264481b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Table, Column, Integer, String\n",
    "user_table = Table(\n",
    "    \"user_account\",\n",
    "    metadata,\n",
    "    Column('id', Integer, primary_key=True),\n",
    "    Column('name', String(30)),\n",
    "    Column('fullname', String)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13e7fcff",
   "metadata": {},
   "source": [
    "We can observe that the above {class}`~sqlalchemy.schema.Table` construct looks a lot like\n",
    "a SQL CREATE TABLE statement; starting with the table name, then listing out\n",
    "each column, where each column has a name and a datatype.   The objects we\n",
    "use above are:\n",
    "\n",
    "- {class}`~sqlalchemy.schema.Table` - represents a database table and assigns itself\n",
    "  to a {class}`~sqlalchemy.schema.MetaData` collection.\n",
    "\n",
    "- {class}`~sqlalchemy.schema.Column` - represents a column in a database table, and\n",
    "  assigns itself to a {class}`~sqlalchemy.schema.Table` object.   The {class}`~sqlalchemy.schema.Column`\n",
    "  usually includes a string name and a type object.   The collection of\n",
    "  {class}`~sqlalchemy.schema.Column` objects in terms of the parent {class}`~sqlalchemy.schema.Table`\n",
    "  are typically accessed via an associative array located at {attr}`~sqlalchemy.schema.Table.c`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "caa8d6c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column('name', String(length=30), table=<user_account>)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table.c.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "aedcc713",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id', 'name', 'fullname']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table.c.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a39fafba",
   "metadata": {},
   "source": [
    "- {class}`~sqlalchemy.types.Integer`, {class}`~sqlalchemy.types.String` - these classes represent\n",
    "  SQL datatypes and can be passed to a {class}`~sqlalchemy.schema.Column` with or without\n",
    "  necessarily being instantiated.  Above, we want to give a length of \"30\" to\n",
    "  the \"name\" column, so we instantiated `String(30)`.  But for \"id\" and\n",
    "  \"fullname\" we did not specify these, so we can send the class itself.\n",
    "\n",
    ":::{seealso}\n",
    "The reference and API documentation for {class}`~sqlalchemy.schema.MetaData`,\n",
    "{class}`~sqlalchemy.schema.Table` and {class}`~sqlalchemy.schema.Column` is at {ref}`metadata_toplevel`.\n",
    "The reference documentation for datatypes is at {ref}`types_toplevel`.\n",
    ":::\n",
    "\n",
    "In an upcoming section, we will illustrate one of the fundamental\n",
    "functions of {class}`~sqlalchemy.schema.Table` which\n",
    "is to generate {term}`DDL` on a particular database connection.  But first\n",
    "we will declare a second {class}`~sqlalchemy.schema.Table`.\n",
    "\n",
    "<!--\n",
    ".. rst-class:: core-header\n",
    "-->\n",
    "\n",
    "## Declaring Simple Constraints\n",
    "\n",
    "The first {class}`~sqlalchemy.schema.Column` in the above `user_table` includes the\n",
    "{paramref}`~sqlalchemy.schema.Column.primary_key` parameter which is a shorthand technique\n",
    "of indicating that this {class}`~sqlalchemy.schema.Column` should be part of the primary\n",
    "key for this table.  The primary key itself is normally declared implicitly\n",
    "and is represented by the {class}`~sqlalchemy.schema.PrimaryKeyConstraint` construct,\n",
    "which we can see on the {attr}`~sqlalchemy.schema.Table.primary_key`\n",
    "attribute on the {class}`~sqlalchemy.schema.Table` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "14563230",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PrimaryKeyConstraint(Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table.primary_key"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fb76450",
   "metadata": {},
   "source": [
    "The constraint that is most typically declared explicitly is the\n",
    "{class}`~sqlalchemy.schema.ForeignKeyConstraint` object that corresponds to a database\n",
    "{term}`foreign key constraint`.  When we declare tables that are related to\n",
    "each other, SQLAlchemy uses the presence of these foreign key constraint\n",
    "declarations not only so that they are emitted within CREATE statements to\n",
    "the database, but also to assist in constructing SQL expressions.\n",
    "\n",
    "A {class}`~sqlalchemy.schema.ForeignKeyConstraint` that involves only a single column\n",
    "on the target table is typically declared using a column-level shorthand notation\n",
    "via the {class}`~sqlalchemy.schema.ForeignKey` object.  Below we declare a second table\n",
    "`address` that will have a foreign key constraint referring to the `user`\n",
    "table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "12d37b2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import ForeignKey\n",
    "address_table = Table(\n",
    "    \"address\",\n",
    "    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",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "443d1be5",
   "metadata": {},
   "source": [
    "The table above also features a third kind of constraint, which in SQL is the\n",
    "\"NOT NULL\" constraint, indicated above using the {paramref}`~sqlalchemy.schema.Column.nullable`\n",
    "parameter.\n",
    "\n",
    ":::{tip}\n",
    "When using the {class}`~sqlalchemy.schema.ForeignKey` object within a\n",
    "{class}`~sqlalchemy.schema.Column` definition, we can omit the datatype for that\n",
    "{class}`~sqlalchemy.schema.Column`; it is automatically inferred from that of the\n",
    "related column, in the above example the {class}`~sqlalchemy.types.Integer` datatype\n",
    "of the `user_account.id` column.\n",
    ":::\n",
    "\n",
    "In the next section we will emit the completed DDL for the `user` and\n",
    "`address` table to see the completed result.\n",
    "\n",
    "<!--\n",
    ".. rst-class:: core-header, orm-dependency\n",
    "-->\n",
    "\n",
    "(sqlatutorial:emitting-ddl)=\n",
    "\n",
    "## Emitting DDL to the Database\n",
    "\n",
    "We've constructed a fairly elaborate object hierarchy to represent\n",
    "two database tables, starting at the root {class}`~sqlalchemy.schema.MetaData`\n",
    "object, then into two {class}`~sqlalchemy.schema.Table` objects, each of which hold\n",
    "onto a collection of {class}`~sqlalchemy.schema.Column` and {class}`~sqlalchemy.schema.Constraint`\n",
    "objects.   This object structure will be at the center of most operations\n",
    "we perform with both Core and ORM going forward.\n",
    "\n",
    "The first useful thing we can do with this structure will be to emit CREATE\n",
    "TABLE statements, or {term}`DDL`, to our SQLite database so that we can insert\n",
    "and query data from them.   We have already all the tools needed to do so, by\n",
    "invoking the\n",
    "{meth}`~sqlalchemy.schema.MetaData.create_all` method on our {class}`~sqlalchemy.schema.MetaData`,\n",
    "sending it the {class}`~sqlalchemy.future.Engine` that refers to the target database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5c4d6a95",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,735 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,736 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"user_account\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,736 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,737 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"user_account\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,738 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,738 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"address\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,739 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,740 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"address\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,740 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,742 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:48,742 INFO sqlalchemy.engine.Engine [no key 0.00060s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,744 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:48,744 INFO sqlalchemy.engine.Engine [no key 0.00048s] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,745 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(\"sqlite+pysqlite:///:memory:\", echo=True, future=True)\n",
    "metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1325dc88",
   "metadata": {},
   "source": [
    "The DDL create process by default includes some SQLite-specific PRAGMA statements\n",
    "that test for the existence of each table before emitting a CREATE.   The full\n",
    "series of steps are also included within a BEGIN/COMMIT pair to accommodate\n",
    "for transactional DDL (SQLite does actually support transactional DDL, however\n",
    "the `sqlite3` database driver historically runs DDL in \"autocommit\" mode).\n",
    "\n",
    "The create process also takes care of emitting CREATE statements in the correct\n",
    "order; above, the FOREIGN KEY constraint is dependent on the `user` table\n",
    "existing, so the `address` table is created second.   In more complicated\n",
    "dependency scenarios the FOREIGN KEY constraints may also be applied to tables\n",
    "after the fact using ALTER.\n",
    "\n",
    "The {class}`~sqlalchemy.schema.MetaData` object also features a\n",
    "{meth}`~sqlalchemy.schema.MetaData.drop_all` method that will emit DROP statements in the\n",
    "reverse order as it would emit CREATE in order to drop schema elements.\n",
    "\n",
    ":::{admonition} Migration tools are usually appropriate\n",
    "Overall, the CREATE / DROP feature of {class}`~sqlalchemy.schema.MetaData` is useful\n",
    "for test suites, small and/or new applications, and applications that use\n",
    "short-lived databases.  For management of an application database schema\n",
    "over the long term however, a schema management tool such as [Alembic](https://alembic.sqlalchemy.org), which builds upon SQLAlchemy, is likely\n",
    "a better choice, as it can manage and orchestrate the process of\n",
    "incrementally altering a fixed database schema over time as the design of\n",
    "the application changes.\n",
    ":::\n",
    "\n",
    "<!--\n",
    ".. rst-class:: core-header, orm-dependency\n",
    "-->\n",
    "\n",
    "(sqlatutorial:orm-table-metadata)=\n",
    "\n",
    "## Defining Table Metadata with the ORM\n",
    "\n",
    "This ORM-only section will provide an example declaring the\n",
    "same database structure illustrated in the previous section, using a more\n",
    "ORM-centric configuration paradigm.   When using\n",
    "the ORM, the process by which we declare {class}`~sqlalchemy.schema.Table` metadata\n",
    "is usually combined with the process of declaring {term}`mapped` classes.\n",
    "The mapped class is any Python class we'd like to create, which will then\n",
    "have attributes on it that will be linked to the columns in a database table.\n",
    "While there are a few varieties of how this is achieved, the most common\n",
    "style is known as\n",
    "{ref}`declarative <orm_declarative_mapper_config_toplevel>`, and allows us\n",
    "to declare our user-defined classes and {class}`~sqlalchemy.schema.Table` metadata\n",
    "at once.\n",
    "\n",
    "### Setting up the Registry\n",
    "\n",
    "When using the ORM, the {class}`~sqlalchemy.schema.MetaData` collection remains present,\n",
    "however it itself is contained within an ORM-only object known as the\n",
    "{class}`~sqlalchemy.orm.registry`.   We create a {class}`~sqlalchemy.orm.registry` by constructing\n",
    "it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "036b1096",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import registry\n",
    "mapper_registry = registry()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "defe838c",
   "metadata": {},
   "source": [
    "The above {class}`~sqlalchemy.orm.registry`, when constructed, automatically includes\n",
    "a {class}`~sqlalchemy.schema.MetaData` object that will store a collection of\n",
    "{class}`~sqlalchemy.schema.Table` objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "20e2c66b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MetaData()"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mapper_registry.metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1866ddf",
   "metadata": {},
   "source": [
    "Instead of declaring {class}`~sqlalchemy.schema.Table` objects directly, we will now\n",
    "declare them indirectly through directives applied to our mapped classes. In\n",
    "the most common approach, each mapped class descends from a common base class\n",
    "known as the **declarative base**.   We get a new declarative base from the\n",
    "{class}`~sqlalchemy.orm.registry` using the {meth}`~sqlalchemy.orm.registry.generate_base` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "97d555db",
   "metadata": {},
   "outputs": [],
   "source": [
    "Base = mapper_registry.generate_base()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a300ce28",
   "metadata": {},
   "source": [
    ":::{tip}\n",
    "The steps of creating the {class}`~sqlalchemy.orm.registry` and \"declarative base\"\n",
    "classes can be combined into one step using the historically familiar\n",
    "{func}`~sqlalchemy.orm.declarative_base` function:\n",
    "\n",
    "```python\n",
    "from sqlalchemy.orm import declarative_base\n",
    "Base = declarative_base()\n",
    "```\n",
    "\n",
    ":::\n",
    "\n",
    "(sqlatutorial:declaring-mapped-classes)=\n",
    "\n",
    "### Declaring Mapped Classes\n",
    "\n",
    "The `Base` object above is a Python class which will serve as the base class\n",
    "for the ORM mapped classes we declare.  We can now define ORM mapped classes\n",
    "for the `user` and `address` table in terms of new classes `User` and\n",
    "`Address`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ebecb5b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import relationship\n",
    "class User(Base):\n",
    "    __tablename__ = 'user_account'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String(30))\n",
    "    fullname = Column(String)\n",
    "\n",
    "    addresses = relationship(\"Address\", back_populates=\"user\")\n",
    "\n",
    "    def __repr__(self):\n",
    "       return f\"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})\"\n",
    "\n",
    "class Address(Base):\n",
    "    __tablename__ = 'address'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    email_address = Column(String, nullable=False)\n",
    "    user_id = Column(Integer, ForeignKey('user_account.id'))\n",
    "\n",
    "    user = relationship(\"User\", back_populates=\"addresses\")\n",
    "\n",
    "    def __repr__(self):\n",
    "        return f\"Address(id={self.id!r}, email_address={self.email_address!r})\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e42cdfdd",
   "metadata": {},
   "source": [
    "The above two classes are now our mapped classes, and are available for use in\n",
    "ORM persistence and query operations, which will be described later. But they\n",
    "also include {class}`~sqlalchemy.schema.Table` objects that were generated as part of the\n",
    "declarative mapping process, and are equivalent to the ones that we declared\n",
    "directly in the previous Core section.   We can see these\n",
    "{class}`~sqlalchemy.schema.Table` objects from a declarative mapped class using the\n",
    "`.__table__` attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "27b9ad22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('user_account', MetaData(), Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False), Column('name', String(length=30), table=<user_account>), Column('fullname', String(), table=<user_account>), schema=None)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "User.__table__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85d4b0ec",
   "metadata": {},
   "source": [
    "This {class}`~sqlalchemy.schema.Table` object was generated from the declarative process\n",
    "based on the `.__tablename__` attribute defined on each of our classes,\n",
    "as well as through the use of {class}`~sqlalchemy.schema.Column` objects assigned\n",
    "to class-level attributes within the classes.   These {class}`~sqlalchemy.schema.Column`\n",
    "objects can usually be declared without an explicit \"name\" field inside\n",
    "the constructor, as the Declarative process will name them automatically\n",
    "based on the attribute name that was used.\n",
    "\n",
    ":::{seealso}\n",
    "{ref}`orm_declarative_mapping` - overview of Declarative class mapping\n",
    ":::\n",
    "\n",
    "### Other Mapped Class Details\n",
    "\n",
    "For a few quick explanations for the classes above, note the following\n",
    "attributes:\n",
    "\n",
    "- **the classes have an automatically generated \\_\\_init\\_\\_() method** - both classes by default\n",
    "  receive an `__init__()` method that allows for parameterized construction\n",
    "  of the objects.  We are free to provide our own `__init__()` method as well.\n",
    "  The `__init__()` allows us to create instances of `User` and `Address`\n",
    "  passing attribute names, most of which above are linked directly to\n",
    "  {class}`~sqlalchemy.schema.Column` objects, as parameter names.\n",
    "  More detail on this method is at {ref}`mapped_class_default_constructor`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e803f74f",
   "metadata": {},
   "outputs": [],
   "source": [
    "sandy = User(name=\"sandy\", fullname=\"Sandy Cheeks\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23b3f7d0",
   "metadata": {},
   "source": [
    "- **we provided a \\_\\_repr\\_\\_() method** - this is **fully optional**, and is\n",
    "  strictly so that our custom classes have a descriptive string representation\n",
    "  and is not otherwise required.\n",
    "  An interesting thing to note, is that the `id` attribute automatically\n",
    "  returns `None` when accessed, rather than raising `AttributeError` as\n",
    "  would be the usual Python behavior for missing attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d80f1bbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(id=None, name='sandy', fullname='Sandy Cheeks')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sandy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30652dae",
   "metadata": {},
   "source": [
    "- **we also included a bidirectional relationship** - this  is another **fully optional**\n",
    "  construct, where we made use of an ORM construct called\n",
    "  {func}`~sqlalchemy.orm.relationship` on both classes, which indicates to the ORM that\n",
    "  these `User` and `Address` classes refer to each other in a {term}`one to\n",
    "  many` / {term}`many to one` relationship.  The use of\n",
    "  {func}`~sqlalchemy.orm.relationship` above is so that we may demonstrate its behavior\n",
    "  later in this tutorial; it is  **not required** in order to define the\n",
    "  {class}`~sqlalchemy.schema.Table` structure.\n",
    "\n",
    "### Emitting DDL to the database\n",
    "\n",
    "This section is named the same as the section {ref}`sqlatutorial:emitting-ddl`\n",
    "discussed in terms of Core.   This is because emitting DDL with our\n",
    "ORM mapped classes is not any different.  If we wanted to emit DDL\n",
    "for the {class}`~sqlalchemy.schema.Table` objects we've created as part of\n",
    "our declaratively mapped classes, we still can use\n",
    "{meth}`~sqlalchemy.schema.MetaData.create_all` as before.\n",
    "\n",
    "In our case, we have already generated the `user` and `address` tables\n",
    "in our SQLite database.   If we had not done so already, we would be free to\n",
    "make use of the {class}`~sqlalchemy.schema.MetaData` associated with our\n",
    "{class}`~sqlalchemy.orm.registry` and ORM declarative base class in order to do so,\n",
    "using {meth}`~sqlalchemy.schema.MetaData.create_all`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f37dbd49",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,845 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,846 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"user_account\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,846 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,848 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"address\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,848 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-10-04 01:45:48,849 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# emit CREATE statements given ORM registry\n",
    "mapper_registry.metadata.create_all(engine)\n",
    "\n",
    "# the identical MetaData object is also present on the\n",
    "# declarative base\n",
    "# Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e19d2e1f",
   "metadata": {},
   "source": [
    "### Combining Core Table Declarations with ORM Declarative\n",
    "\n",
    "As an alternative approach to the mapping process shown previously\n",
    "at {ref}`sqlatutorial:declaring-mapped-classes`, we may also make\n",
    "use of the {class}`~sqlalchemy.schema.Table` objects we created directly in the section\n",
    "{ref}`sqlatutorial:core-metadata` in conjunction with\n",
    "declarative mapped classes from a {func}`~sqlalchemy.orm.declarative_base` generated base\n",
    "class.\n",
    "\n",
    "This form is called  {ref}`hybrid table <orm_imperative_table_configuration>`,\n",
    "and it consists of assigning to the `.__table__` attribute directly, rather\n",
    "than having the declarative process generate it:\n",
    "\n",
    "```python\n",
    "class User(Base):\n",
    "    __table__ = user_table\n",
    "\n",
    "    addresses = relationship(\"Address\", back_populates=\"user\")\n",
    "\n",
    "    def __repr__(self):\n",
    "        return f\"User({self.name!r}, {self.fullname!r})\"\n",
    "\n",
    "class Address(Base):\n",
    "    __table__ = address_table\n",
    "\n",
    "    user = relationship(\"User\", back_populates=\"addresses\")\n",
    "\n",
    "    def __repr__(self):\n",
    "        return f\"Address({self.email_address!r})\"\n",
    "```\n",
    "\n",
    "The above two classes are equivalent to those which we declared in the\n",
    "previous mapping example.\n",
    "\n",
    "The traditional \"declarative base\" approach using `__tablename__` to\n",
    "automatically generate {class}`~sqlalchemy.schema.Table` objects remains the most popular\n",
    "method to declare table metadata.  However, disregarding the ORM mapping\n",
    "functionality it achieves, as far as table declaration it's merely a syntactical\n",
    "convenience on top of the {class}`~sqlalchemy.schema.Table` constructor.\n",
    "\n",
    "We will next refer to our ORM mapped classes above when we talk about data\n",
    "manipulation in terms of the ORM, in the section {ref}`sqlatutorial:inserting-orm`.\n",
    "\n",
    "<!--\n",
    ".. rst-class:: core-header\n",
    "-->\n",
    "\n",
    "(sqlatutorial:table-reflection)=\n",
    "\n",
    "## Table Reflection\n",
    "\n",
    "To round out the section on working with table metadata, we will illustrate\n",
    "another operation that was mentioned at the beginning of the section,\n",
    "that of **table reflection**.   Table reflection refers to the process of\n",
    "generating {class}`~sqlalchemy.schema.Table` and related objects by reading the current\n",
    "state of a database.   Whereas in the previous sections we've been declaring\n",
    "{class}`~sqlalchemy.schema.Table` objects in Python and then emitting DDL to the database,\n",
    "the reflection process does it in reverse.\n",
    "\n",
    "As an example of reflection, we will create a new {class}`~sqlalchemy.schema.Table`\n",
    "object which represents the `some_table` object we created manually in\n",
    "the earlier sections of this document.  There are again some varieties of\n",
    "how this is performed, however the most basic is to construct a\n",
    "{class}`~sqlalchemy.schema.Table` object, given the name of the table and a\n",
    "{class}`~sqlalchemy.schema.MetaData` collection to which it will belong, then\n",
    "instead of indicating individual {class}`~sqlalchemy.schema.Column` and\n",
    "{class}`~sqlalchemy.schema.Constraint` objects, pass it the target {class}`~sqlalchemy.future.Engine`\n",
    "using the {paramref}`~sqlalchemy.schema.Table.autoload_with` parameter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e0af0ba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "some_table = Table(\"user_account\", metadata, autoload_with=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23d6a806",
   "metadata": {},
   "source": [
    "At the end of the process, the `some_table` object now contains the\n",
    "information about the {class}`~sqlalchemy.schema.Column` objects present in the table, and\n",
    "the object is usable in exactly the same way as a {class}`~sqlalchemy.schema.Table` that\n",
    "we declared explicitly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "253c6f35",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('user_account', MetaData(), Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False), Column('name', String(length=30), table=<user_account>), Column('fullname', String(), table=<user_account>), schema=None)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "some_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "233e0c74",
   "metadata": {},
   "source": [
    ":::{seealso}\n",
    "Read more about table and schema reflection at {ref}`metadata_reflection_toplevel`.\n",
    "\n",
    "For ORM-related variants of table reflection, the section\n",
    "{ref}`orm_declarative_reflected` includes an overview of the available\n",
    "options.\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,
   68,
   71,
   88,
   97,
   113,
   117,
   119,
   152,
   154,
   169,
   178,
   217,
   222,
   278,
   281,
   287,
   289,
   297,
   299,
   322,
   347,
   357,
   359,
   386,
   388,
   397,
   399,
   425,
   432,
   503,
   505,
   512,
   514
  ]
 },
 "nbformat": 4,
 "nbformat_minor": 5
}