<commit_before>.. _orm_quickstart:


ORM Quick Start
===============

For new users who want to quickly see what basic ORM use looks like, here's an
abbreviated form of the mappings and examples used in the
:ref:`unified_tutorial`. The code here is fully runnable from a clean command
line.

As the descriptions in this section are intentionally **very short**, please
proceed to the full :ref:`unified_tutorial` for a much more in-depth
description of each of the concepts being illustrated here.


Declare Models
---------------

Here, we define module-level constructs that will form the structures
which we will be querying from the database.  This structure, known as a
:ref:`Declarative Mapping <orm_declarative_mapping>`, defines at once both a
Python object model, as well as
:term:`database metadata` that describes
real SQL tables that exist, or will exist, in a particular database::

    >>> from sqlalchemy import Column
    >>> from sqlalchemy import ForeignKey
    >>> from sqlalchemy import Integer
    >>> from sqlalchemy import String
    >>> from sqlalchemy.orm import declarative_base
    >>> from sqlalchemy.orm import relationship

    >>> Base = declarative_base()

    >>> class User(Base):
    ...     __tablename__ = "user_account"
    ...
    ...     id = Column(Integer, primary_key=True)
    ...     name = Column(String(30))
    ...     fullname = Column(String)
    ...
    ...     addresses = relationship(
    ...         "Address", back_populates="user", cascade="all, delete-orphan"
    ...     )
    ...
    ...     def __repr__(self):
    ...         return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

    >>> class Address(Base):
    ...     __tablename__ = "address"
    ...
    ...     id = Column(Integer, primary_key=True)
    ...     email_address = Column(String, nullable=False)
    ...     user_id = Column(Integer, ForeignKey("user_account.id"), nullable=False)
    ...
    ...     user = relationship("User", back_populates="addresses")
    ...
    ...     def __repr__(self):
    ...         return f"Address(id={self.id!r}, email_address={self.email_address!r})"

Above, the declarative mapping makes use of :class:`_schema.Column` objects
to define the basic units of data storage that will be in the database.
The :func:`_orm.relationship` construct defines linkages between two
:term:`mapped` classes, ``User`` and ``Address`` above.

The schema contains necessary elements such as primary key constraints set up
by the :paramref:`_schema.Column.primary_key` parameter, a
:term:`foreign key constraint` configured using :class:`_schema.ForeignKey`
(which is used by :func:`_orm.relationship` as well), and datatypes for columns
including :class:`_types.Integer` and :class:`_types.String`.

More on table metadata and an intro to ORM declared mapping is in the
Tutorial at :ref:`tutorial_working_with_metadata`.

Create an Engine
------------------


The :class:`_engine.Engine` is a **factory** that can create new
database connections for us, which also holds onto connections inside
of a :ref:`Connection Pool <pooling_toplevel>` for fast reuse.  For learning
purposes, we normally use a :ref:`SQLite <sqlite_toplevel>` memory-only database
for convenience::

    >>> from sqlalchemy import create_engine
    >>> engine = create_engine("sqlite://", echo=True, future=True)

.. tip::

    The ``echo=True`` parameter indicates that SQL emitted by connections will
    be logged to standard out.  ``future=True`` is to ensure we are using
    the latest SQLAlchemy :term:`2.0-style` APIs.

A full intro to the :class:`_engine.Engine` starts at :ref:`tutorial_engine`.

Emit CREATE TABLE DDL
----------------------


Using our table metadata and our engine, we can generate our schema at once
in our target SQLite database, using a method called :meth:`_schema.MetaData.create_all`:

.. sourcecode:: pycon+sql

    >>> Base.metadata.create_all(engine)
    {opensql}BEGIN (implicit)
    PRAGMA main.table_...info("user_account")
    ...
    PRAGMA main.table_...info("address")
    ...
    CREATE TABLE user_account (
        id INTEGER NOT NULL,
        name VARCHAR(30),
        fullname VARCHAR,
        PRIMARY KEY (id)
    )
    ...
    CREATE TABLE address (
        id INTEGER NOT NULL,
        email_address VARCHAR NOT NULL,
        user_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES user_account (id)
    )
    ...
    COMMIT

A lot just happened from that bit of Python code we wrote.  For a complete
overview of what's going on on with Table metadata, proceed in the
Tutorial at :ref:`tutorial_working_with_metadata`.

Create Objects and Persist
---------------------------
<commit_msg>update quickstart for 2.0<commit_after>.. _orm_quickstart:


ORM Quick Start
===============

For new users who want to quickly see what basic ORM use looks like, here's an
abbreviated form of the mappings and examples used in the
:ref:`unified_tutorial`. The code here is fully runnable from a clean command
line.

As the descriptions in this section are intentionally **very short**, please
proceed to the full :ref:`unified_tutorial` for a much more in-depth
description of each of the concepts being illustrated here.

.. versionchanged:: 2.0  The ORM Quickstart is updated for the latest
    :pep:`484`-aware features using new constructs including
    :func:`_orm.mapped_column`.   At the moment, the rest of the documentation
    may not be yet updated.   Features such as a new dataclass-native style
    of mapping are also not documented yet.

Declare Models
---------------

Here, we define module-level constructs that will form the structures
which we will be querying from the database.  This structure, known as a
:ref:`Declarative Mapping <orm_declarative_mapping>`, defines at once both a
Python object model, as well as :term:`database metadata` that describes
real SQL tables that exist, or will exist, in a particular database::

    >>> from typing import Optional
    >>> from sqlalchemy import ForeignKey
    >>> from sqlalchemy import String
    >>> from sqlalchemy.orm import DeclarativeBase
    >>> from sqlalchemy.orm import Mapped
    >>> from sqlalchemy.orm import mapped_column
    >>> from sqlalchemy.orm import relationship

    >>> class Base(DeclarativeBase):
    ...     pass

    >>> class User(Base):
    ...     __tablename__ = "user_account"
    ...
    ...     id: Mapped[int] = mapped_column(primary_key=True)
    ...     name: Mapped[str] = mapped_column(String(30))
    ...     fullname: Mapped[Optional[str]]
    ...
    ...     addresses: Mapped[list["Address"]] = relationship(
    ...         back_populates="user", cascade="all, delete-orphan"
    ...     )
    ...
    ...     def __repr__(self):
    ...         return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

    >>> class Address(Base):
    ...     __tablename__ = "address"
    ...
    ...     id: Mapped[int] = mapped_column(primary_key=True)
    ...     email_address: Mapped[str]
    ...     user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    ...
    ...     user: Mapped["User"] = relationship(back_populates="addresses")
    ...
    ...     def __repr__(self):
    ...         return f"Address(id={self.id!r}, email_address={self.email_address!r})"

Above, new ORM mapped classes are declared as part of a common base, which is
configured as a user-defined class that inherits from the special
:class:`_orm.DeclarativeBase` class. Then, the declarative mapping makes use of
both the :class:`_orm.Mapped` type annotation construct to indicate class
attributes that are mapped, and then makes use of the
:func:`_orm.mapped_column` and :func:`_orm.relationship` constructs to further
qualify the specifics of how these attributes should be mapped where necessary.
The :func:`_orm.mapped_column` construct indicates the presence of a relational
database column, which is also implied by default when :class:`_orm.Mapped` is
annotated without a right-hand side value. The :func:`_orm.relationship`
construct, by contrast, defines linkages between two :term:`mapped` classes,
``User`` and ``Address`` above.

Most arguments accepted by :func:`_orm.mapped_column` align with the underlying
construct that represents a database table column, which is the
:class:`_schema.Column` object. The schema contains necessary elements such as
primary key constraints set up using the
:paramref:`_orm.mapped_column.primary_key` parameter, a
:term:`foreign key constraint` configured using the :class:`_schema.ForeignKey`
construct, and datatypes for columns including :class:`_types.Integer`, which
is implied by the use of the Python ``int`` datatype, and
:class:`_types.String`, which is implied by the use of the Python ``str``
datatype. The "nullability" of columns by default is inferred by whether or not
the left-hand :class:`_orm.Mapped` datatype is stated as ``Optional[<type>]``
or not.

In all cases, parameters passed to :func:`_orm.mapped_column`, such as specific
datatype specifications such as ``String(30)``, or use of the the boolean parameter
:paramref:`_orm.mapped_column.nullable`, will supersede the annotation-implied
configuration.

While the above classes include an explicitly written ``__repr__()`` method,
which is used here to illustrate usage of the classes, there is also an
option for methods such as ``__repr__()``, ``__eq__()`` and others to be
generated automatically using Python dataclasses.  More on dataclass mapping
at: (the new 2.0 style dataclass documentation is TODO!  but it will be great
:) )

More on table metadata and an intro to ORM declared mapping is in the
Tutorial at :ref:`tutorial_working_with_metadata`.

Create an Engine
------------------


The :class:`_engine.Engine` is a **factory** that can create new
database connections for us, which also holds onto connections inside
of a :ref:`Connection Pool <pooling_toplevel>` for fast reuse.  For learning
purposes, we normally use a :ref:`SQLite <sqlite_toplevel>` memory-only database
for convenience::

    >>> from sqlalchemy import create_engine
    >>> engine = create_engine("sqlite://", echo=True)

.. tip::

    The ``echo=True`` parameter indicates that SQL emitted by connections will
    be logged to standard out.

A full intro to the :class:`_engine.Engine` starts at :ref:`tutorial_engine`.

Emit CREATE TABLE DDL
----------------------


Using our table metadata and our engine, we can generate our schema at once
in our target SQLite database, using a method called :meth:`_schema.MetaData.create_all`:

.. sourcecode:: pycon+sql

    >>> Base.metadata.create_all(engine)
    {opensql}BEGIN (implicit)
    PRAGMA main.table_...info("user_account")
    ...
    PRAGMA main.table_...info("address")
    ...
    CREATE TABLE user_account (
        id INTEGER NOT NULL,
        name VARCHAR(30) NOT NULL,
        fullname VARCHAR,
        PRIMARY KEY (id)
    )
    ...
    CREATE TABLE address (
        id INTEGER NOT NULL,
        email_address VARCHAR NOT NULL,
        user_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES user_account (id)
    )
    ...
    COMMIT

A lot just happened from that bit of Python code we wrote.  For a complete
overview of what's going on on with Table metadata, proceed in the
Tutorial at :ref:`tutorial_working_with_metadata`.

Create Objects and Persist
---------------------------

{'fraction_non_alphanumeric': 0.1138012411502491, 'fraction_numerical': 0.0018355038895201468, 'mean_word_length': 3.373853211009174, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 85, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5421447', 'n_tokens_mistral': 3286, 'n_tokens_neox': 3140, 'n_words': 1236}