text:
:orphan:

.. _faq_toplevel:

============================
Frequently Asked Questions
============================

.. contents::
    :local:
    :class: faq
    :backlinks: none

Connections / Engines
=====================

How do I configure logging?
---------------------------

See :ref:`dbengine_logging`.

How do I pool database connections?   Are my connections pooled?
----------------------------------------------------------------

SQLAlchemy performs application-level connection pooling automatically
in most cases.  With the exception of SQLite, a :class:`.Engine` object
refers to a :class:`.QueuePool` as a source of connectivity.

For more detail, see :ref:`engines_toplevel` and :ref:`pooling_toplevel`.

How do I pass custom connect arguments to my database API?
-----------------------------------------------------------

The :func:`.create_engine` call accepts additional arguments either
directly via the ``connect_args`` keyword argument::

    e = create_engine("mysql://scott:tiger@localhost/test",
                        connect_args={"encoding": "utf8"})

Or for basic string and integer arguments, they can usually be specified
in the query string of the URL::

    e = create_engine("mysql://scott:tiger@localhost/test?encoding=utf8")

.. seealso::

    :ref:`custom_dbapi_args`

"MySQL Server has gone away"
----------------------------

There are two major causes for this error:

1. The MySQL client closes connections which have been idle for a set period
of time, defaulting to eight hours.   This can be avoided by using the ``pool_recycle``
setting with :func:`.create_engine`, described at :ref:`mysql_connection_timeouts`.

2. Usage of the MySQLdb :term:`DBAPI`, or a similar DBAPI, in a non-threadsafe manner, or in an otherwise
inappropriate way.   The MySQLdb connection object is not threadsafe - this expands
out to any SQLAlchemy system that links to a single connection, which includes the ORM
:class:`.Session`.  For background
on how :class:`.Session` should be used in a multithreaded environment,
see :ref:`session_faq_threadsafe`.

Why does SQLAlchemy issue so many ROLLBACKs?
---------------------------------------------

SQLAlchemy currently assumes DBAPI connections are in "non-autocommit" mode -
this is the default behavior of the Python database API, meaning it
must be assumed that a transaction is always in progress. The
connection pool issues ``connection.rollback()`` when a connection is returned.
This is so that any transactional resources remaining on the connection are
released. On a database like Postgresql or MSSQL where table resources are
aggressively locked, this is critical so that rows and tables don't remain
locked within connections that are no longer in use. An application can
otherwise hang. It's not just for locks, however, and is equally critical on
any database that has any kind of transaction isolation, including MySQL with
InnoDB. Any connection that is still inside an old transaction will return
stale data, if that data was already queried on that connection within
isolation. For background on why you might see stale data even on MySQL, see
http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html

I'm on MyISAM - how do I turn it off?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The behavior of the connection pool's connection return behavior can be
configured using ``reset_on_return``::

    from sqlalchemy import create_engine
    from sqlalchemy.pool import QueuePool

    engine = create_engine('mysql://scott:tiger@localhost/myisam_database', pool=QueuePool(reset_on_return=False))

I'm on SQL Server - how do I turn those ROLLBACKs into COMMITs?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

``reset_on_return`` accepts the values ``commit``, ``rollback`` in addition
to ``True``, ``False``, and ``None``.   Setting to ``commit`` will cause
a COMMIT as any connection is returned to the pool::

    engine = create_engine('mssql://scott:tiger@mydsn', pool=QueuePool(reset_on_return='commit'))

I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!
----------------------------------------------------------------------------------------------------------------------------------------------------------

If using a SQLite ``:memory:`` database, or a version of SQLAlchemy prior
to version 0.7, the default connection pool is the :class:`.SingletonThreadPool`,
which maintains exactly one SQLite connection per thread.  So two
connections in use in the same thread will actually be the same SQLite
connection.   Make sure you're not using a :memory: database and
use :class:`.NullPool`, which is the default for non-memory databases in
current SQLAlchemy versions.

.. seealso::

    :ref:`pysqlite_threading_pooling` - info on PySQLite's behavior.

How do I get at the raw DBAPI connection when using an Engine?
--------------------------------------------------------------

With a regular SA engine-level Connection, you can get at a pool-proxied
version of the DBAPI connection via the :attr:`.Connection.connection` attribute on
:class:`.Connection`, and for the really-real DBAPI connection you can call the
:attr:`.ConnectionFairy.connection` attribute on that - but there should never be any need to access
the non-pool-proxied DBAPI connection, as all methods are proxied through::

    engine = create_engine(...)
    conn = engine.connect()
    conn.connection.<do DBAPI things>
    cursor = conn.connection.cursor(<DBAPI specific arguments..>)

You must ensure that you revert any isolation level settings or other
operation-specific settings on the connection back to normal before returning
it to the pool.

As an alternative to reverting settings, you can call the :meth:`.Connection.detach` method on
either :class:`.Connection` or the proxied connection, which will de-associate
the connection from the pool such that it will be closed and discarded
when :meth:`.Connection.close` is called::

    conn = engine.connect()
    conn.detach()  # detaches the DBAPI connection from the connection pool
    conn.connection.<go nuts>
    conn.close()  # connection is closed for real, the pool replaces it with a new connection

MetaData / Schema
==================

My program is hanging when I say ``table.drop()`` / ``metadata.drop_all()``
----------------------------------------------------------------------------

This usually corresponds to two conditions: 1. using PostgreSQL, which is really
strict about table locks, and 2. you have a connection still open which
contains locks on the table and is distinct from the connection being used for
the DROP statement.  Heres the most minimal version of the pattern::

    connection = engine.connect()
    result = connection.execute(mytable.select())

    mytable.drop(engine)

Above, a connection pool connection is still checked out; furthermore, the
result object above also maintains a link to this connection.  If
"implicit execution" is used, the result will hold this connection opened until
the result object is closed or all rows are exhausted.

The call to ``mytable.drop(engine)`` attempts to emit DROP TABLE on a second
connection procured from the :class:`.Engine` which will lock.

The solution is to close out all connections before emitting DROP TABLE::

    connection = engine.connect()
    result = connection.execute(mytable.select())

    # fully read result sets
    result.fetchall()

    # close connections
    connection.close()

    # now locks are removed
    mytable.drop(engine)

Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?
-----------------------------------------------------------------------------------------------

General ALTER support isn't present in SQLAlchemy directly.  For special DDL
on an ad-hoc basis, the :class:`.DDL` and related constructs can be used.
See :doc:`core/ddl` for a discussion on this subject.

A more comprehensive option is to use schema migration tools, such as Alembic
or SQLAlchemy-Migrate; see :ref:`schema_migrations` for discussion on this.

How can I sort Table objects in order of their dependency?
-----------------------------------------------------------

This is available via the :attr:`.MetaData.sorted_tables` function::

    metadata = MetaData()
    # ... add Table objects to metadata
    ti = metadata.sorted_tables:
    for t in ti:
        print t

How can I get the CREATE TABLE/ DROP TABLE output as a string?
---------------------------------------------------------------

Modern SQLAlchemy has clause constructs which represent DDL operations. These
can be rendered to strings like any other SQL expression::

    from sqlalchemy.schema import CreateTable

    print CreateTable(mytable)

To get the string specific to a certain engine::

    print CreateTable(mytable).compile(engine)

There's also a special form of :class:`.Engine` that can let you dump an entire
metadata creation sequence, using this recipe::

    def dump(sql, *multiparams, **params):
        print sql.compile(dialect=engine.dialect)
    engine = create_engine('postgresql://', strategy='mock', executor=dump)
    metadata.create_all(engine, checkfirst=False)

The `Alembic <https://bitbucket.org/zzzeek/alembic>`_ tool also supports
an "offline" SQL generation mode that renders database migrations as SQL scripts.

How can I subclass Table/Column to provide certain behaviors/configurations?
------------------------------------------------------------------------------

:class:`.Table` and :class:`.Column` are not good targets for direct subclassing.
However, there are simple ways to get on-construction behaviors using creation
functions, and behaviors related to the linkages between schema objects such as
constraint conventions or naming conventions using attachment events.
An example of many of these
techniques can be seen at `Naming Conventions <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions>`_.

SQL Expressions
=================

.. _faq_sql_expression_string:

How do I render SQL expressions as strings, possibly with bound parameters inlined?
------------------------------------------------------------------------------------

The "stringification" of a SQLAlchemy statement or Query in the vast majority
of cases is as simple as::

    print str(statement)

this applies both to an ORM :class:`~.orm.query.Query` as well as any :func:`.select` or other
statement.   Additionally, to get the statement as compiled to a
specific dialect or engine, if the statement itself is not already
bound to one you can pass this in to :meth:`.ClauseElement.compile`::

    print statement.compile(someengine)

or without an :class:`.Engine`::

    from sqlalchemy.dialects import postgresql
    print statement.compile(dialect=postgresql.dialect())

When given an ORM :class:`~.orm.query.Query` object, in order to get at the
:meth:`.ClauseElement.compile`
method we only need access the :attr:`~.orm.query.Query.statement`
accessor first::

    statement = query.statement
    print statement.compile(someengine)

The above forms will render the SQL statement as it is passed to the Python
:term:`DBAPI`, which includes that bound parameters are not rendered inline.
SQLAlchemy normally does not stringify bound parameters, as this is handled
appropriately by the Python DBAPI, not to mention bypassing bound
parameters is probably the most widely exploited security hole in
modern web applications.   SQLAlchemy has limited ability to do this
stringification in certain circumstances such as that of emitting DDL.
In order to access this functionality one can use the ``literal_binds``
flag, passed to ``compile_kwargs``::

    from sqlalchemy.sql import table, column, select

    t = table('t', column('x'))

    s = select([t]).where(t.c.x == 5)

    print s.compile(compile_kwargs={"literal_binds": True})

the above approach has the caveats that it is only supported for basic
types, such as ints and strings, and furthermore if a :func:`.bindparam`
without a pre-set value is used directly, it won't be able to
stringify that either.    Additionally, the ``compile_kwargs`` argument
itself is only available as of SQLAlchemy 0.9; and there are slightly
more verbose ways of getting ``literal_binds`` injected with 0.8.

.. topic::  Applying compiler kw arguments prior to 0.9

    We can provide a fixed set of ``**kw`` by calling upon ``process()``
    directly::

        compiled = s.compile()
        print compiled.process(s, literal_binds=True)

    Note that in this approach the statement is actually being stringified
    twice, hence using ``compile_kwargs`` in 0.9 should be preferred.

If we want to skip using ``literal_binds`` altogether due to the above
caveats, we can take the approach of replacing them out ahead of time
with whatever we want::

    from sqlalchemy.sql import visitors, text

    def replace_bindparam(element):
        if hasattr(element, 'effective_value'):
            return text(str(element.effective_value))

    s = visitors.replacement_traverse(s, {}, replace_bindparam)
    print s

Still another approach to injecting functionality where bound
parameters are concerned is to use the :doc:`Compilation Extension
API <core/compiler>`::

    from sqlalchemy.ext.compiler import compiles
    from sqlalchemy.sql.expression import BindParameter

    s = select([t]).where(t.c.x == 5)

    @compiles(BindParameter)
    def as_str(element, compiler, **kw):
        if 'binds_as_str' in kw:
            return str(element.effective_value)
        else:
            return compiler.visit_bindparam(element, **kw)

    print s.compile(compile_kwargs={"binds_as_str": True})

Above, we pass a self-defined flag ``binds_as_str`` through the compiler,
which we then intercept within our custom render method for :class:`.BindParameter`.

Why does ``.col.in_([])`` Produce ``col != col``? Why not ``1=0``?
-------------------------------------------------------------------

A little introduction to the issue. The IN operator in SQL, given a list of
elements to compare against a column, generally does not accept an empty list,
that is while it is valid to say::

    column IN (1, 2, 3)

it's not valid to say::

    column IN ()

SQLAlchemy's :meth:`.Operators.in_` operator, when given an empty list, produces this
expression::

    column != column

As of version 0.6, it also produces a warning stating that a less efficient
comparison operation will be rendered. This expression is the only one that is
both database agnostic and produces correct results.

For example, the naive approach of "just evaluate to false, by comparing 1=0
or 1!=1", does not handle nulls properly. An expression like::

    NOT column != column

will not return a row when "column" is null, but an expression which does not
take the column into account::

    NOT 1=0

will.

Closer to the mark is the following CASE expression::

    CASE WHEN column IS NOT NULL THEN 1=0 ELSE NULL END

We don't use this expression due to its verbosity, and its also not
typically accepted by Oracle within a WHERE clause - depending
on how you phrase it, you'll either get "ORA-00905: missing keyword" or
"ORA-00920: invalid relational operator". It's also still less efficient than
just rendering SQL without the clause altogether (or not issuing the SQL at
all, if the statement is just a simple search).

The best approach therefore is to avoid the usage of IN given an argument list
of zero length.  Instead, don't emit the Query in the first place, if no rows
should be returned.  The warning is best promoted to a full error condition
using the Python warnings filter (see http://docs.python.org/library/warnings.html).

ORM Configuration
==================

.. _faq_mapper_primary_key:

How do I map a table that has no primary key?
---------------------------------------------

The SQLAlchemy ORM, in order to map to a particular table, needs there to be
at least one column denoted as a primary key column; multiple-column,
i.e. composite, primary keys are of course entirely feasible as well.  These
columns do **not** need to be actually known to the database as primary key
columns, though it's a good idea that they are.  It's only necessary that the columns
*behave* as a primary key does, e.g. as a unique and not nullable identifier
for a row.

Most ORMs require that objects have some kind of primary key defined
because the object in memory must correspond to a uniquely identifiable
row in the database table; at the very least, this allows the
object can be targeted for UPDATE and DELETE statements which will affect only
that object's row and no other.   However, the importance of the primary key
goes far beyond that.  In SQLAlchemy, all ORM-mapped objects are at all times
linked uniquely within a :class:`.Session`
to their specific database row using a pattern called the :term:`identity map`,
a pattern that's central to the unit of work system employed by SQLAlchemy,
and is also key to the most common (and not-so-common) patterns of ORM usage.

.. note::

    It's important to note that we're only talking about the SQLAlchemy ORM; an
    application which builds on Core and deals only with :class:`.Table` objects,
    :func:`.select` constructs and the like, **does not** need any primary key
    to be present on or associated with a table in any way (though again, in SQL, all tables
    should really have some kind of primary key, lest you need to actually
    update or delete specific rows).

In almost all cases, a table does have a so-called :term:`candidate key`, which is a column or series
of columns that uniquely identify a row.  If a table truly doesn't have this, and has actual
fully duplicate rows, the table is not corresponding to `first normal form <http://en.wikipedia.org/wiki/First_normal_form>`_ and cannot be mapped.   Otherwise, whatever columns comprise the best candidate key can be
applied directly to the mapper::

    class SomeClass(Base):
        __table__ = some_table_with_no_pk
        __mapper_args__ = {
            'primary_key':[some_table_with_no_pk.c.uid, some_table_with_no_pk.c.bar]
        }

Better yet is when using fully declared table metadata, use the ``primary_key=True``
flag on those columns::

    class SomeClass(Base):
        __tablename__ = "some_table_with_no_pk"

        uid = Column(Integer, primary_key=True)
        bar = Column(String, primary_key=True)

All tables in a relational database should have primary keys.   Even a many-to-many
association table - the primary key would be the composite of the two association
columns::

    CREATE TABLE my_association (
      user_id INTEGER REFERENCES user(id),
      account_id INTEGER REFERENCES account(id),
      PRIMARY KEY (user_id, account_id)
    )

How do I configure a Column that is a Python reserved word or similar?
----------------------------------------------------------------------------

Column-based attributes can be given any name desired in the mapping. See
:ref:`mapper_column_distinct_names`.

How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?
-------------------------------------------------------------------------------------------------

This information is all available from the :class:`.Mapper` object.

To get at the :class:`.Mapper` for a particular mapped class, call the
:func:`.inspect` function on it::

    from sqlalchemy import inspect

    mapper = inspect(MyClass)

From there, all information about the class can be acquired using such methods as:

* :attr:`.Mapper.attrs` - a namespace of all mapped attributes.  The attributes
  themselves are instances of :class:`.MapperProperty`, which contain additional
  attributes that can lead to the mapped SQL expression or column, if applicable.

* :attr:`.Mapper.column_attrs` - the mapped attribute namespace
  limited to column and SQL expression attributes.   You might want to use
  :attr:`.Mapper.columns` to get at the :class:`.Column` objects directly.

* :attr:`.Mapper.relationships` - namespace of all :class:`.RelationshipProperty` attributes.

* :attr:`.Mapper.all_orm_descriptors` - namespace of all mapped attributes, plus user-defined
  attributes defined using systems such as :class:`.hybrid_property`, :class:`.AssociationProxy` and others.

* :attr:`.Mapper.columns` - A namespace of :class:`.Column` objects and other named
  SQL expressions associated with the mapping.

* :attr:`.Mapper.mapped_table` - The :class:`.Table` or other selectable to which
  this mapper is mapped.

* :attr:`.Mapper.local_table` - The :class:`.Table` that is "local" to this mapper;
  this differs from :attr:`.Mapper.mapped_table` in the case of a mapper mapped
  using inheritance to a composed selectable.

.. _faq_combining_columns:

I'm getting a warning or error about "Implicitly combining column X under attribute Y"
--------------------------------------------------------------------------------------

This condition refers to when a mapping contains two columns that are being
mapped under the same attribute name due to their name, but there's no indication
that this is intentional.  A mapped class needs to have explicit names for
every attribute that is to store an independent value; when two columns have the
same name and aren't disambiguated, they fall under the same attribute and
the effect is that the value from one column is **copied** into the other, based
on which column was assigned to the attribute first.

This behavior is often desirable and is allowed without warning in the case
where the two columns are linked together via a foreign key relationship
within an inheritance mapping.   When the warning or exception occurs, the
issue can be resolved by either assigning the columns to differently-named
attributes, or if combining them together is desired, by using
:func:`.column_property` to make this explicit.

Given the example as follows::

    from sqlalchemy import Integer, Column, ForeignKey
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    class A(Base):
        __tablename__ = 'a'

        id = Column(Integer, primary_key=True)

    class B(A):
        __tablename__ = 'b'

        id = Column(Integer, primary_key=True)
        a_id = Column(Integer, ForeignKey('a.id'))

As of SQLAlchemy version 0.9.5, the above condition is detected, and will
warn that the ``id`` column of ``A`` and ``B`` is being combined under
the same-named attribute ``id``, which above is a serious issue since it means
that a ``B`` object's primary key will always mirror that of its ``A``.

A mapping which resolves this is as follows::

    class A(Base):
        __tablename__ = 'a'

        id = Column(Integer, primary_key=True)

    class B(A):
        __tablename__ = 'b'

        b_id = Column('id', Integer, primary_key=True)
        a_id = Column(Integer, ForeignKey('a.id'))

Suppose we did want ``A.id`` and ``B.id`` to be mirrors of each other, despite
the fact that ``B.a_id`` is where ``A.id`` is related.  We could combine
them together using :func:`.column_property`::

    class A(Base):
        __tablename__ = 'a'

        id = Column(Integer, primary_key=True)

    class B(A):
        __tablename__ = 'b'

        # probably not what you want, but this is a demonstration
        id = column_property(Column(Integer, primary_key=True), A.id)
        a_id = Column(Integer, ForeignKey('a.id'))

I'm using Declarative and setting primaryjoin/secondaryjoin using an ``and_()`` or ``or_()``, and I am getting an error message about foreign keys.
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Are you doing this?::

    class MyClass(Base):
        # ....

        foo = relationship("Dest", primaryjoin=and_("MyClass.id==Dest.foo_id", "MyClass.foo==Dest.bar"))

That's an ``and_()`` of two string expressions, which SQLAlchemy cannot apply any mapping towards.  Declarative allows :func:`.relationship` arguments to be specified as strings, which are converted into expression objects using ``eval()``.   But this doesn't occur inside of an ``and_()`` expression - it's a special operation declarative applies only to the *entirety* of what's passed to primaryjoin or other arguments as a string::

    class MyClass(Base):
        # ....

        foo = relationship("Dest", primaryjoin="and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar)")

Or if the objects you need are already available, skip the strings::

    class MyClass(Base):
        # ....

        foo = relationship(Dest, primaryjoin=and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar))

The same idea applies to all the other arguments, such as ``foreign_keys``::

    # wrong !
    foo = relationship(Dest, foreign_keys=["Dest.foo_id", "Dest.bar_id"])

    # correct !
    foo = relationship(Dest, foreign_keys="[Dest.foo_id, Dest.bar_id]")

    # also correct !
    foo = relationship(Dest, foreign_keys=[Dest.foo_id, Dest.bar_id])

    # if you're using columns from the class that you're inside of, just use the column objects !
    class MyClass(Base):
        foo_id = Column(...)
        bar_id = Column(...)
        # ...

        foo = relationship(Dest, foreign_keys=[foo_id, bar_id])

Performance
===========

How can I profile a SQLAlchemy powered application?
---------------------------------------------------

Looking for performance issues typically involves two stratgies.  One
is query profiling, and the other is code profiling.

Query Profiling
^^^^^^^^^^^^^^^^

Sometimes just plain SQL logging (enabled via python's logging module
or via the ``echo=True`` argument on :func:`.create_engine`) can give an
idea how long things are taking.  For example, if you log something
right after a SQL operation, you'd see something like this in your
log::

    17:37:48,325 INFO  [sqlalchemy.engine.base.Engine.0x...048c] SELECT ...
    17:37:48,326 INFO  [sqlalchemy.engine.base.Engine.0x...048c] {<params>}
    17:37:48,660 DEBUG [myapp.somemessage]

if you logged ``myapp.somemessage`` right after the operation, you know
it took 334ms to complete the SQL part of things.

Logging SQL will also illustrate if dozens/hundreds of queries are
being issued which could be better organized into much fewer queries.
When using the SQLAlchemy ORM, the "eager loading"
feature is provided to partially (:func:`.contains_eager()`) or fully
(:func:`.joinedload()`, :func:`.subqueryload()`)
automate this activity, but without
the ORM "eager loading" typically means to use joins so that results across multiple
tables can be loaded in one result set instead of multiplying numbers
of queries as more depth is added (i.e. ``r + r*r2 + r*r2*r3`` ...)

For more long-term profiling of queries, or to implement an application-side
"slow query" monitor, events can be used to intercept cursor executions,
using a recipe like the following::

    from sqlalchemy import event
    from sqlalchemy.engine import Engine
    import time
    import logging

    logging.basicConfig()
    logger = logging.getLogger("myapp.sqltime")
    logger.setLevel(logging.DEBUG)

    @event.listens_for(Engine, "before_cursor_execute")
    def before_cursor_execute(conn, cursor, statement,
                            parameters, context, executemany):
        conn.info.setdefault('query_start_time', []).append(time.time())
        logger.debug("Start Query: %s" % statement)

    @event.listens_for(Engine, "after_cursor_execute")
    def after_cursor_execute(conn, cursor, statement,
                            parameters, context, executemany):
        total = time.time() - conn.info['query_start_time'].pop(-1)
        logger.debug("Query Complete!")
        logger.debug("Total Time: %f" % total)

Above, we use the :meth:`.ConnectionEvents.before_cursor_execute` and
:meth:`.ConnectionEvents.after_cursor_execute` events to establish an interception
point around when a statement is executed.  We attach a timer onto the
connection using the :class:`._ConnectionRecord.info` dictionary; we use a
stack here for the occasional case where the cursor execute events may be nested.

Code Profiling
^^^^^^^^^^^^^^

If logging reveals that individual queries are taking too long, you'd
need a breakdown of how much time was spent within the database
processing the query, sending results over the network, being handled
by the :term:`DBAPI`, and finally being received by SQLAlchemy's result set
and/or ORM layer.   Each of these stages can present their own
individual bottlenecks, depending on specifics.

For that you need to use the
`Python Profiling Module <https://docs.python.org/2/library/profile.html>`_.
Below is a simple recipe which works profiling into a context manager::

    import cProfile
    import StringIO
    import pstats
    import contextlib

    @contextlib.contextmanager
    def profiled():
        pr = cProfile.Profile()
        pr.enable()
        yield
        pr.disable()
        s = StringIO.StringIO()
        ps = pstats.Stats(pr, stream=s).sort_stats('cumulative')
        ps.print_stats()
        # uncomment this to see who's calling what
        # ps.print_callers()
        print s.getvalue()

To profile a section of code::

    with profiled():
        Session.query(FooClass).filter(FooClass.somevalue==8).all()

The output of profiling can be used to give an idea where time is
being spent.   A section of profiling output looks like this::

    13726 function calls (13042 primitive calls) in 0.014 seconds

    Ordered by: cumulative time

    ncalls  tottime  percall  cumtime  percall filename:lineno(function)
    222/21    0.001    0.000    0.011    0.001 lib/sqlalchemy/orm/loading.py:26(instances)
    220/20    0.002    0.000    0.010    0.001 lib/sqlalchemy/orm/loading.py:327(_instance)
    220/20    0.000    0.000    0.010    0.000 lib/sqlalchemy/orm/loading.py:284(populate_state)
       20    0.000    0.000    0.010    0.000 lib/sqlalchemy/orm/strategies.py:987(load_collection_from_subq)
       20    0.000    0.000    0.009    0.000 lib/sqlalchemy/orm/strategies.py:935(get)
        1    0.000    0.000    0.009    0.009 lib/sqlalchemy/orm/strategies.py:940(_load)
       21    0.000    0.000    0.008    0.000 lib/sqlalchemy/orm/strategies.py:942(<genexpr>)
        2    0.000    0.000    0.004    0.002 lib/sqlalchemy/orm/query.py:2400(__iter__)
        2    0.000    0.000    0.002    0.001 lib/sqlalchemy/orm/query.py:2414(_execute_and_instances)
        2    0.000    0.000    0.002    0.001 lib/sqlalchemy/engine/base.py:659(execute)
        2    0.000    0.000    0.002    0.001 lib/sqlalchemy/sql/elements.py:321(_execute_on_connection)
        2    0.000    0.000    0.002    0.001 lib/sqlalchemy/engine/base.py:788(_execute_clauseelement)

    ...

Above, we can see that the ``instances()`` SQLAlchemy function was called 222
times (recursively, and 21 times from the outside), taking a total of .011
seconds for all calls combined.

Execution Slowness
^^^^^^^^^^^^^^^^^^

The specifics of these calls can tell us where the time is being spent.
If for example, you see time being spent within ``cursor.execute()``,
e.g. against the DBAPI::

    2    0.102    0.102    0.204    0.102 {method 'execute' of 'sqlite3.Cursor' objects}

this would indicate that the database is taking a long time to start returning
results, and it means your query should be optimized, either by adding indexes
or restructuring the query and/or underlying schema.  For that task,
analysis of the query plan is warranted, using a system such as EXPLAIN,
SHOW PLAN, etc. as is provided by the database backend.

Result Fetching Slowness - Core
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

If on the other hand you see many thousands of calls related to fetching rows,
or very long calls to ``fetchall()``, it may
mean your query is returning more rows than expected, or that the fetching
of rows itself is slow.   The ORM itself typically uses ``fetchall()`` to fetch
rows (or ``fetchmany()`` if the :meth:`.Query.yield_per` option is used).

An inordinately large number of rows would be indicated
by a very slow call to ``fetchall()`` at the DBAPI level::

    2    0.300    0.600    0.300    0.600 {method 'fetchall' of 'sqlite3.Cursor' objects}

An unexpectedly large number of rows, even if the ultimate result doesn't seem
to have many rows, can be the result of a cartesian product - when multiple
sets of rows are combined together without appropriately joining the tables
together.   It's often easy to produce this behavior with SQLAlchemy Core or
ORM query if the wrong :class:`.Column` objects are used in a complex query,
pulling in additional FROM clauses that are unexpected.

On the other hand, a fast call to ``fetchall()`` at the DBAPI level, but then
slowness when SQLAlchemy's :class:`.ResultProxy` is asked to do a ``fetchall()``,
may indicate slowness in processing of datatypes, such as unicode conversions
and similar::

    # the DBAPI cursor is fast...
    2    0.020    0.040    0.020    0.040 {method 'fetchall' of 'sqlite3.Cursor' objects}

    ...

    # but SQLAlchemy's result proxy is slow, this is type-level processing
    2    0.100    0.200    0.100    0.200 lib/sqlalchemy/engine/result.py:778(fetchall)

In some cases, a backend might be doing type-level processing that isn't
needed.   More specifically, seeing calls within the type API that are slow
are better indicators - below is what it looks like when we use a type like
this::

    from sqlalchemy import TypeDecorator
    import time

    class Foo(TypeDecorator):
        impl = String

        def process_result_value(self, value, thing):
            # intentionally add slowness for illustration purposes
            time.sleep(.001)
            return value

the profiling output of this intentionally slow operation can be seen like this::

      200    0.001    0.000    0.237    0.001 lib/sqlalchemy/sql/type_api.py:911(process)
      200    0.001    0.000    0.236    0.001 test.py:28(process_result_value)
      200    0.235    0.001    0.235    0.001 {time.sleep}

that is, we see many expensive calls within the ``type_api`` system, and the actual
time consuming thing is the ``time.sleep()`` call.

Make sure to check the :doc:`Dialect documentation <dialects/index>`
for notes on known performance tuning suggestions at this level, especially for
databases like Oracle.  There may be systems related to ensuring numeric accuracy
or string processing that may not be needed in all cases.

There also may be even more low-level points at which row-fetching performance is suffering;
for example, if time spent seems to focus on a call like ``socket.receive()``,
that could indicate that everything is fast except for the actual network connection,
and too much time is spent with data moving over the network.

Result Fetching Slowness - ORM
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

To detect slowness in ORM fetching of rows (which is the most common area
of performance concern), calls like ``populate_state()`` and ``_instance()`` will
illustrate individual ORM object populations::

    # the ORM calls _instance for each ORM-loaded row it sees, and
    # populate_state for each ORM-loaded row that results in the population
    # of an object's attributes
    220/20    0.001    0.000    0.010    0.000 lib/sqlalchemy/orm/loading.py:327(_instance)
    220/20    0.000    0.000    0.009    0.000 lib/sqlalchemy/orm/loading.py:284(populate_state)

The ORM's slowness in turning rows into ORM-mapped objects is a product
of the complexity of this operation combined with the overhead of cPython.
Common strategies to mitigate this include:

* fetch individual columns instead of full entities, that is::

      session.query(User.id, User.name)

  instead of::

      session.query(User)

* Use :class:`.Bundle` objects to organize column-based results::

      u_b = Bundle('user', User.id, User.name)
      a_b = Bundle('address', Address.id, Address.email)

      for user, address in session.query(u_b, a_b).join(User.addresses):
          # ...

* Use result caching - see :ref:`examples_caching` for an in-depth example
  of this.

* Consider a faster interpreter like that of Pypy.

The output of a profile can be a little daunting but after some
practice they are very easy to read.

If you're feeling ambitious, there's also a more involved example of
SQLAlchemy profiling within the SQLAlchemy unit tests in the
``tests/aaa_profiling`` section.  Tests in this area
use decorators that assert a
maximum number of method calls being used for particular operations,
so that if something inefficient gets checked in, the tests will
reveal it (it is important to note that in cPython, function calls have
the highest overhead of any operation, and the count of calls is more
often than not nearly proportional to time spent).   Of note are the
the "zoomark" tests which use a fancy "SQL capturing" scheme which
cuts out the overhead of the DBAPI from the equation - although that
technique isn't really necessary for garden-variety profiling.

I'm inserting 400,000 rows with the ORM and it's really slow!
--------------------------------------------------------------

The SQLAlchemy ORM uses the :term:`unit of work` pattern when synchronizing
changes to the database. This pattern goes far beyond simple "inserts"
of data. It includes that attributes which are assigned on objects are
received using an attribute instrumentation system which tracks
changes on objects as they are made, includes that all rows inserted
are tracked in an identity map which has the effect that for each row
SQLAlchemy must retrieve its "last inserted id" if not already given,
and also involves that rows to be inserted are scanned and sorted for
dependencies as needed. Objects are also subject to a fair degree of
bookkeeping in order to keep all of this running, which for a very
large number of rows at once can create an inordinate amount of time
spent with large data structures, hence it's best to chunk these.

Basically, unit of work is a large degree of automation in order to
automate the task of persisting a complex object graph into a
relational database with no explicit persistence code, and this
automation has a price.

ORMs are basically not intended for high-performance bulk inserts -
this is the whole reason SQLAlchemy offers the Core in addition to the
ORM as a first-class component.

For the use case of fast bulk inserts, the
SQL generation and execution system that the ORM builds on top of
is part of the Core.  Using this system directly, we can produce an INSERT that
is competitive with using the raw database API directly.

The example below illustrates time-based tests for four different
methods of inserting rows, going from the most automated to the least.
With cPython 2.7, runtimes observed::

    classics-MacBook-Pro:sqlalchemy classic$ python test.py
    SQLAlchemy ORM: Total time for 100000 records 14.3528850079 secs
    SQLAlchemy ORM pk given: Total time for 100000 records 10.0164160728 secs
    SQLAlchemy Core: Total time for 100000 records 0.775382995605 secs
    sqlite3: Total time for 100000 records 0.676795005798 sec

We can reduce the time by a factor of three using recent versions of `Pypy <http://pypy.org/>`_::

    classics-MacBook-Pro:sqlalchemy classic$ /usr/local/src/pypy-2.1-beta2-osx64/bin/pypy test.py
    SQLAlchemy ORM: Total time for 100000 records 5.88369488716 secs
    SQLAlchemy ORM pk given: Total time for 100000 records 3.52294301987 secs
    SQLAlchemy Core: Total time for 100000 records 0.613556146622 secs
    sqlite3: Total time for 100000 records 0.442467927933 sec

Script::

    import time
    import sqlite3

    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String,  create_engine
    from sqlalchemy.orm import scoped_session, sessionmaker

    Base = declarative_base()
    DBSession = scoped_session(sessionmaker())
    engine = None

    class Customer(Base):
        __tablename__ = "customer"
        id = Column(Integer, primary_key=True)
        name = Column(String(255))

    def init_sqlalchemy(dbname='sqlite:///sqlalchemy.db'):
        global engine
        engine = create_engine(dbname, echo=False)
        DBSession.remove()
        DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)

    def test_sqlalchemy_orm(n=100000):
        init_sqlalchemy()
        t0 = time.time()
        for i in range(n):
            customer = Customer()
            customer.name = 'NAME ' + str(i)
            DBSession.add(customer)
            if i % 1000 == 0:
                DBSession.flush()
        DBSession.commit()
        print("SQLAlchemy ORM: Total time for " + str(n) +
                    " records " + str(time.time() - t0) + " secs")

    def test_sqlalchemy_orm_pk_given(n=100000):
        init_sqlalchemy()
        t0 = time.time()
        for i in range(n):
            customer = Customer(id=i+1, name="NAME " + str(i))
            DBSession.add(customer)
            if i % 1000 == 0:
                DBSession.flush()
        DBSession.commit()
        print("SQLAlchemy ORM pk given: Total time for " + str(n) +
            " records " + str(time.time() - t0) + " secs")

    def test_sqlalchemy_core(n=100000):
        init_sqlalchemy()
        t0 = time.time()
        engine.execute(
            Customer.__table__.insert(),
            [{"name": 'NAME ' + str(i)} for i in range(n)]
        )
        print("SQLAlchemy Core: Total time for " + str(n) +
            " records " + str(time.time() - t0) + " secs")

    def init_sqlite3(dbname):
        conn = sqlite3.connect(dbname)
        c = conn.cursor()
        c.execute("DROP TABLE IF EXISTS customer")
        c.execute("CREATE TABLE customer (id INTEGER NOT NULL, "
                                    "name VARCHAR(255), PRIMARY KEY(id))")
        conn.commit()
        return conn

    def test_sqlite3(n=100000, dbname='sqlite3.db'):
        conn = init_sqlite3(dbname)
        c = conn.cursor()
        t0 = time.time()
        for i in range(n):
            row = ('NAME ' + str(i),)
            c.execute("INSERT INTO customer (name) VALUES (?)", row)
        conn.commit()
        print("sqlite3: Total time for " + str(n) +
            " records " + str(time.time() - t0) + " sec")

    if __name__ == '__main__':
        test_sqlalchemy_orm(100000)
        test_sqlalchemy_orm_pk_given(100000)
        test_sqlalchemy_core(100000)
        test_sqlite3(100000)

Sessions / Queries
===================

"This Session's transaction has been rolled back due to a previous exception during flush." (or similar)
---------------------------------------------------------------------------------------------------------

This is an error that occurs when a :meth:`.Session.flush` raises an exception, rolls back
the transaction, but further commands upon the `Session` are called without an
explicit call to :meth:`.Session.rollback` or :meth:`.Session.close`.

It usually corresponds to an application that catches an exception
upon :meth:`.Session.flush` or :meth:`.Session.commit` and
does not properly handle the exception.    For example::

    from sqlalchemy import create_engine, Column, Integer
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base(create_engine('sqlite://'))

    class Foo(Base):
        __tablename__ = 'foo'
        id = Column(Integer, primary_key=True)

    Base.metadata.create_all()

    session = sessionmaker()()

    # constraint violation
    session.add_all([Foo(id=1), Foo(id=1)])

    try:
        session.commit()
    except:
        # ignore error
        pass

    # continue using session without rolling back
    session.commit()

The usage of the :class:`.Session` should fit within a structure similar to this::

    try:
        <use session>
        session.commit()
    except:
       session.rollback()
       raise
    finally:
       session.close()  # optional, depends on use case

Many things can cause a failure within the try/except besides flushes. You
should always have some kind of "framing" of your session operations so that
connection and transaction resources have a definitive boundary, otherwise
your application doesn't really have its usage of resources under control.
This is not to say that you need to put try/except blocks all throughout your
application - on the contrary, this would be a terrible idea.  You should
architect your application such that there is one (or few) point(s) of
"framing" around session operations.

For a detailed discussion on how to organize usage of the :class:`.Session`,
please see :ref:`session_faq_whentocreate`.

But why does flush() insist on issuing a ROLLBACK?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It would be great if :meth:`.Session.flush` could partially complete and then not roll
back, however this is beyond its current capabilities since its internal
bookkeeping would have to be modified such that it can be halted at any time
and be exactly consistent with what's been flushed to the database. While this
is theoretically possible, the usefulness of the enhancement is greatly
decreased by the fact that many database operations require a ROLLBACK in any
case. Postgres in particular has operations which, once failed, the
transaction is not allowed to continue::

    test=> create table foo(id integer primary key);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
    CREATE TABLE
    test=> begin;
    BEGIN
    test=> insert into foo values(1);
    INSERT 0 1
    test=> commit;
    COMMIT
    test=> begin;
    BEGIN
    test=> insert into foo values(1);
    ERROR:  duplicate key value violates unique constraint "foo_pkey"
    test=> insert into foo values(2);
    ERROR:  current transaction is aborted, commands ignored until end of transaction block

What SQLAlchemy offers that solves both issues is support of SAVEPOINT, via
:meth:`.Session.begin_nested`. Using :meth:`.Session.begin_nested`, you can frame an operation that may
potentially fail within a transaction, and then "roll back" to the point
before its failure while maintaining the enclosing transaction.

But why isn't the one automatic call to ROLLBACK enough?  Why must I ROLLBACK again?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This is again a matter of the :class:`.Session` providing a consistent interface and
refusing to guess about what context its being used. For example, the
:class:`.Session` supports "framing" above within multiple levels. Such as, suppose
you had a decorator ``@with_session()``, which did this::

    def with_session(fn):
       def go(*args, **kw):
           session.begin(subtransactions=True)
           try:
               ret = fn(*args, **kw)
               session.commit()
               return ret
           except:
               session.rollback()
               raise
       return go

The above decorator begins a transaction if one does not exist already, and
then commits it, if it were the creator. The "subtransactions" flag means that
if :meth:`.Session.begin` were already called by an enclosing function, nothing happens
except a counter is incremented - this counter is decremented when :meth:`.Session.commit`
is called and only when it goes back to zero does the actual COMMIT happen. It
allows this usage pattern::

    @with_session
    def one():
       # do stuff
       two()

    @with_session
    def two():
       # etc.

    one()

    two()

``one()`` can call ``two()``, or ``two()`` can be called by itself, and the
``@with_session`` decorator ensures the appropriate "framing" - the transaction
boundaries stay on the outermost call level. As you can see, if ``two()`` calls
``flush()`` which throws an exception and then issues a ``rollback()``, there will
*always* be a second ``rollback()`` performed by the decorator, and possibly a
third corresponding to two levels of decorator. If the ``flush()`` pushed the
``rollback()`` all the way out to the top of the stack, and then we said that
all remaining ``rollback()`` calls are moot, there is some silent behavior going
on there. A poorly written enclosing method might suppress the exception, and
then call ``commit()`` assuming nothing is wrong, and then you have a silent
failure condition. The main reason people get this error in fact is because
they didn't write clean "framing" code and they would have had other problems
down the road.

If you think the above use case is a little exotic, the same kind of thing
comes into play if you want to SAVEPOINT- you might call ``begin_nested()``
several times, and the ``commit()``/``rollback()`` calls each resolve the most
recent ``begin_nested()``. The meaning of ``rollback()`` or ``commit()`` is
dependent upon which enclosing block it is called, and you might have any
sequence of ``rollback()``/``commit()`` in any order, and its the level of nesting
that determines their behavior.

In both of the above cases, if ``flush()`` broke the nesting of transaction
blocks, the behavior is, depending on scenario, anywhere from "magic" to
silent failure to blatant interruption of code flow.

``flush()`` makes its own "subtransaction", so that a transaction is started up
regardless of the external transactional state, and when complete it calls
``commit()``, or ``rollback()`` upon failure - but that ``rollback()`` corresponds
to its own subtransaction - it doesn't want to guess how you'd like to handle
the external "framing" of the transaction, which could be nested many levels
with any combination of subtransactions and real SAVEPOINTs. The job of
starting/ending the "frame" is kept consistently with the code external to the
``flush()``, and we made a decision that this was the most consistent approach.

How do I make a Query that always adds a certain filter to every query?
------------------------------------------------------------------------------------------------

See the recipe at `PreFilteredQuery <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery>`_.

I've created a mapping against an Outer Join, and while the query returns rows, no objects are returned.  Why not?
------------------------------------------------------------------------------------------------------------------

Rows returned by an outer join may contain NULL for part of the primary key,
as the primary key is the composite of both tables.  The :class:`.Query` object ignores incoming rows
that don't have an acceptable primary key.   Based on the setting of the ``allow_partial_pks``
flag on :func:`.mapper`, a primary key is accepted if the value has at least one non-NULL
value, or alternatively if the value has no NULL values.  See ``allow_partial_pks``
at :func:`.mapper`.

I'm using ``joinedload()`` or ``lazy=False`` to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The joins generated by joined eager loading are only used to fully load related
collections, and are designed to have no impact on the primary results of the query.
Since they are anonymously aliased, they cannot be referenced directly.

For detail on this beahvior, see :doc:`orm/loading`.

Query has no ``__len__()``, why not?
------------------------------------

The Python ``__len__()`` magic method applied to an object allows the ``len()``
builtin to be used to determine the length of the collection. It's intuitive
that a SQL query object would link ``__len__()`` to the :meth:`.Query.count`
method, which emits a `SELECT COUNT`. The reason this is not possible is
because evaluating the query as a list would incur two SQL calls instead of
one::

    class Iterates(object):
        def __len__(self):
            print "LEN!"
            return 5

        def __iter__(self):
            print "ITER!"
            return iter([1, 2, 3, 4, 5])

    list(Iterates())

output::

    ITER!
    LEN!

How Do I use Textual SQL with ORM Queries?
-------------------------------------------

See:

* :ref:`orm_tutorial_literal_sql` - Ad-hoc textual blocks with :class:`.Query`

* :ref:`session_sql_expressions` - Using :class:`.Session` with textual SQL directly.

I'm calling ``Session.delete(myobject)`` and it isn't removed from the parent collection!
------------------------------------------------------------------------------------------

See :ref:`session_deleting_from_collections` for a description of this behavior.

why isn't my ``__init__()`` called when I load objects?
-------------------------------------------------------

See :ref:`mapping_constructors` for a description of this behavior.

how do I use ON DELETE CASCADE with SA's ORM?
----------------------------------------------

SQLAlchemy will always issue UPDATE or DELETE statements for dependent
rows which are currently loaded in the :class:`.Session`.  For rows which
are not loaded, it will by default issue SELECT statements to load
those rows and udpate/delete those as well; in other words it assumes
there is no ON DELETE CASCADE configured.
To configure SQLAlchemy to cooperate with ON DELETE CASCADE, see
:ref:`passive_deletes`.

I set the "foo_id" attribute on my instance to "7", but the "foo" attribute is still ``None`` - shouldn't it have loaded Foo with id #7?
----------------------------------------------------------------------------------------------------------------------------------------------------

The ORM is not constructed in such a way as to support
immediate population of relationships driven from foreign
key attribute changes - instead, it is designed to work the
other way around - foreign key attributes are handled by the
ORM behind the scenes, the end user sets up object
relationships naturally. Therefore, the recommended way to
set ``o.foo`` is to do just that - set it!::

    foo = Session.query(Foo).get(7)
    o.foo = foo
    Session.commit()

Manipulation of foreign key attributes is of course entirely legal.  However,
setting a foreign-key attribute to a new value currently does not trigger
an "expire" event of the :func:`.relationship` in which it's involved.  This means
that for the following sequence::

    o = Session.query(SomeClass).first()
    assert o.foo is None  # accessing an un-set attribute sets it to None
    o.foo_id = 7

``o.foo`` is initialized to ``None`` when we first accessed it.  Setting
``o.foo_id = 7`` will have the value of "7" as pending, but no flush
has occurred - so ``o.foo`` is still ``None``::

    # attribute is already set to None, has not been
    # reconciled with o.foo_id = 7 yet
    assert o.foo is None

For ``o.foo`` to load based on the foreign key mutation is usually achieved
naturally after the commit, which both flushes the new foreign key value
and expires all state::

    Session.commit()  # expires all attributes

    foo_7 = Session.query(Foo).get(7)

    assert o.foo is foo_7  # o.foo lazyloads on access

A more minimal operation is to expire the attribute individually - this can
be performed for any :term:`persistent` object using :meth:`.Session.expire`::

    o = Session.query(SomeClass).first()
    o.foo_id = 7
    Session.expire(o, ['foo'])  # object must be persistent for this

    foo_7 = Session.query(Foo).get(7)

    assert o.foo is foo_7  # o.foo lazyloads on access

Note that if the object is not persistent but present in the :class:`.Session`,
it's known as :term:`pending`.   This means the row for the object has not been
INSERTed into the database yet.  For such an object, setting ``foo_id`` does not
have meaning until the row is inserted; otherwise there is no row yet::

    new_obj = SomeClass()
    new_obj.foo_id = 7

    Session.add(new_obj)

    # accessing an un-set attribute sets it to None
    assert new_obj.foo is None

    Session.flush()  # emits INSERT

    # expire this because we already set .foo to None
    Session.expire(o, ['foo'])

    assert new_obj.foo is foo_7  # now it loads

.. topic:: Attribute loading for non-persistent objects

    One variant on the "pending" behavior above is if we use the flag
    ``load_on_pending`` on :func:`.relationship`.   When this flag is set, the
    lazy loader will emit for ``new_obj.foo`` before the INSERT proceeds; another
    variant of this is to use the :meth:`.Session.enable_relationship_loading`
    method, which can "attach" an object to a :class:`.Session` in such a way that
    many-to-one relationships load as according to foreign key attributes
    regardless of the object being in any particular state.
    Both techniques are **not recommended for general use**; they were added to suit
    specific programming scenarios encountered by users which involve the repurposing
    of the ORM's usual object states.

The recipe `ExpireRelationshipOnFKChange <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange>`_ features an example using SQLAlchemy events
in order to coordinate the setting of foreign key attributes with many-to-one
relationships.

Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

When people read the many-to-many example in the docs, they get hit with the
fact that if you create the same ``Keyword`` twice, it gets put in the DB twice.
Which is somewhat inconvenient.

This `UniqueObject <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject>`_ recipe was created to address this issue.


The office closes early today! Yeah for Christmas! I plan on spending the rest of the day finishing up my shopping (what do you get your sister-in-law?) and baking some desserts from yesterday's Desserts post. I am leaning towards the PB&J cookies after Ali's review of them.
If you're still trying to decide what to make for Christmas dinner, don't forget to check out Rachael Ray's Christmas dinner recipes from her Christmas in 60 show. You can also see all of the posts from the A Rachael Ray Christmas series by clicking on the Holidays label in the left-hand sidebar.
If you write a post about Rachael Ray, her recipes, or her travel picks, please drop me a line at everythingrachaelray at gmail dot com so I can make sure it gets included in the Roundup. Merry Christmas and Happy Holidays!
The Chicago Tribune reveals that Rachael surprised fans by stopping by Chicago's Kitsch'n. She "held court for almost an hour, posing for pictures and handing out copies of her latest cookbook, Rachael Ray 2, 4, 6, 8: Great Meals for Couples or Crowds.
Celebrity Dog Watcher tracked down more information about Rachael's dog Isaboo.
The Home Cook at Taste of Home tried several Rachael Ray recipes this week including: Turkey and Spinach Meatballs and Cream of Mushroom Casserole.
Angela M. at Shelterrific tried Rachael Ray's Sweet Potato and Sausage Hash, which is one of my favorites. She says the fresh salsa is a must (I am usually to undermotivated to make it).
Wes Thorpe is glad to see some balance in the Rachael Ray universe. My pleasure, Wes!
Ana at Kitchen Space adapted a recipe from Every Day With Rachael Ray: Parmesan Crusted Tilapia. The roasted veggies and spinach fettuccine that she served with it look good too!
There's a very interesting post about autograph dealers and how Rachael Ray tried to thwart them by Hugh at Confessions of a Paparazzi.
The Boston Globe provides some Tasty Travels gossip; Rachael was in Boston filming the show Rachael Ray was in Boston December 20 shooting segments "at chef Ken Oringer's South End hotspot, Toro."


text:
Home News Moto G 5G Plus launched in UK with 5G, Quad cameras and...
Moto G 5G Plus launched in UK with 5G, Quad cameras and more
In pursuit of gaining its lost fame, Motorola is actively launching fairly reasonable phones. The Lenovo owned company has now come up with a brand new smartphone – Moto G 5G Plus.
This handset supports 5G, has quad-rear cameras, a good battery, and stock Android – all coming for a price under €350 range. Thus, it's supposed to face off OnePlus 7 and Redmi K20 Pro, as a worthy contender.
Moto G 5G Plus Specifications
Moto G 5G Plus Pricing and Availability
The latest smartphone from Motorola has a huge 6.7″ FHD+ display with 1080×2550p resolution. The screen has a dual punch-hole design and supports HDR10+ along with 90Hz refresh rate. Moving into inner hardware, it runs on Snapdragon's 765G coupled with Adreno 620 GPU. This is same as Motorola's Edge and LG's Velvet.
The software is handled by Android 10 stock version, thus not having any bloatware. In the imaging department, Moto G 5G Plus has a quad-rear setup as, a 48-megapixel (f/1.7) primary sensor, followed by an 8-megapixel (f/2.2) ultra-wide angular, a 5-megapixel (f/2.4) macro lens and a 2-megapixel (f/2.2) depth sensor. Upfront, there are two lenses as 16-megapixel (f/2.0) and an 8-megapixel (f/2.2) in the punch-hole design.
This handset is powered by a massive 5000mAh battery with 20W fast charging support, and comes in two storage variants of either 4GB or 6GB RAM, with onboard storage of 64GB and 128GB respectively. The second SIM slot can be used as microSD for storage expansion. But in other countries, it may come with a dedicated slot.
It's having all usual connectivities like Bluetooth, WiFi, USB Type-C, audio jack, 4G VoLTE, along with 5G support! It's touted the phone to be using the average 5G modem as sub-6GHz, unlike mmWave modem used by high-end phones. Yet, it's still a 5G supportive and has amazing speeds. Further, it has NFS support and a fingerprint sensor given onto the power button, at the side.
Moto G 5G Plus is currently available for UK and Europe, with prices starting from €349 to its 4GB/64GB variant and €399 for the 6GB/128GB variant. It's available in Blue colour, and may soon come to Saudi Arabia and UAE. The company also said that the US will be missing this handset, but will get a similar 5G phone under $500 by this year's end.
Motorola One Fusion launched with 48MP quad cameras, Snapdragon 710 SoC, 5000mAh battery
Motorola Edge+ – All you need to know
Moto Edge+ and Edge are Motorola's new hope for revival
Motorola One Fusion and Fusion+ to be launched by Motorola in Q2 2020
motorola g 5g plus
Nereida Clapp July 30, 2020 at 12:10 PM
YOU ARE INTERESTED IN 5G ?
5G HUB is the premier online educating and tutoring library featuring everything about 5G!
Take a tour and explore our unparalleled, extensive archives to access reliable information and products about the latest 5G technology.

redpajama_set_name: RedPajamaCommonCrawl


Susino offers a wide variety of umbrellas including ladies, gentlemen's, children's, golf and occasional ranges, under our two main brands: Susino – our chic, Premium range, and X-brella – our fast-fashion, trend-led range.
Sign up for a wholesale account for FREE today to view wholesale prices and multipacks.
Buying wholesale umbrellas with Susino is as easy as creating an account and logging in, opening a whole world of high quality everyday umbrellas. Our range of fashionable umbrellas is now available to bulk buy, and with a range like ours, you won't find better quality anywhere else.
From bulk walking umbrellas, compacts, golf umbrellas, and novelty multipacks in a whole variety of colours- Susino keeps an eye on the current fashion trends to bring up to the minute styles, prints, and bold colours to your customers.
Buying great value umbrellas in bulk has never been easier with Susino's wholesale service. Choose from folding umbrellas in cartons of 12 – 48, including our polka dot novelty duck handled brollies, colourful creatures' multipack, selfie stick multicolours, and men's slogan umbrellas.
Perfect as gifts for every occasion, or simply to add as an outdoor accessory, Susino umbrellas offer high quality as standard. Now you can offer that same high standard to your customers with Susino's trade and bulk buy service.
Fantastic, quality assured umbrellas at a bulk buy prices – that's the Susino wholesale guarantee.
Over 30 years' experience in the industry means Susino is one of the largest fashion accessory companies in the UK. When you buy from us, you're buying straight from the manufacturer, ensuring high quality goods. All our umbrellas are beautifully crafted, featuring PU covered handle, durable canopy, and a sturdy frame and ribs for wind resistance. And with free delivery on orders over £30, and next day delivery if you order before 2pm, we've got you covered.
Our wholesale services cover our fast fashion umbrellas. This includes promotional, wedding, and golf umbrellas. In a few easy steps, you can create a trade account and view our full range of umbrellas and prices for wholesale buyers.


text:
SWEDISHAMERICAN PARTNERS WITH ILLINOIS DEPARTMENT OF HUMAN SERVICES IN FIGHT AGAINST OPIOID ADDICTION
SwedishAmerican has been recognized by the Illinois Department of Human Services (IDHS) as one of only 14 Illinois hospitals that will participate in its Warm Hand-off Program to combat opioid addiction.
The IDHS Hospital Warm Hand-off Program connects recovery support specialists directly to patients with opioid use disorder while they are in the hospital recovering from an overdose or receiving other hospital treatment.
The Illinois Department of Public Health's data shows that opioid overdoses killed 2,202 people in Illinois in 2017 alone – more than one and a half times the number of homicides and nearly twice the number of fatal motor vehicle accidents.
"It's just going to continue to grow and grow unless we do something," said Ann Gantzer, Vice President of Health and Patient Services and the Chief Nursing Officer at SwedishAmerican. "With this grant, SwedishAmerican will continue to be there for our citizens and be one of the champions to help address addiction issues. We can now be involved from start to finish in their care, which is different than what we have now."
SwedishAmerican will now have a recovery specialist that will do a Warm Hand-off with a patient when they leave the hospital to enter a treatment center. The grant will allow SwedishAmerican to hire and train nine new employees to be involved in the collaborative relationship between the patient and recovery support specialist.
"The grant will allow us to provide transportation to a treatment center for the patient, and the recovery specialist will then continue to care for and support the client for up to one year," said Amy Kirsch, the Director of Psychiatric Services for SwedishAmerican. "This means we are not just giving a referral on paper. We are actually going to the treatment facility and providing ongoing care management."
SwedishAmerican is the only local hospital that will receive the state grant opportunity. The complete list of the IDHS Warm Hand-off Hospitals are:
Advocate Christ Medical Center, Oak Lawn, IL
Advocate Condell, Libertyville, IL
Advocate Good Shepherd, Barrington, IL
Alton Memorial Hospital, Alton, IL
Cook County Health and Hospital System, Chicago, IL
Methodist Hospital, Chicago, IL
Methodist Medical Center of Illinois, Peoria, IL
Mount Sinai Hospital and Medical Center, Chicago, IL
Norwegian American Hospital, Chicago, IL
Presence Saints Mary and Elizabeth Medical Center, Chicago, IL
Roseland Community Hospital, Chicago, IL
Rush University Medical Center, Chicago, IL
Silver Cross Hospital, New Lenox (Joliet), IL
Southern Illinois Healthcare (encompasses three hospitals)
Memorial Hospital of Carbondale, Carbondale, IL
Herrin Hospital, Herrin, IL
St. Joseph Hospital, Murphysboro, IL
Swedish American Hospital, Rockford, IL
St. Anthony Hospital, Chicago, IL
St. Bernard Hospital, Chicago, IL
Thorek Hospital, Chicago, IL
University of Illinois Hospital & Health Sciences System, Chicago, IL
Vista Medical Center East, Waukegan, IL
Emily Tropp
About SwedishAmerican
A division of UW Health, SwedishAmerican is dedicated to providing excellence in healthcare and compassionate care to the community. The organization serves residents of 12 counties in northern Illinois and southern Wisconsin through a dedicated and caring staff, a major acute care hospital and a free-standing outpatient cancer center in Rockford, a medical center in Belvidere, a network of 30 primary care and multi-specialty clinics and the region's largest home healthcare agency. Additionally, SwedishAmerican has achieved Magnet® recognition as a reflection of its nursing professionalism, teamwork and superiority in patient care.


After cold, dark "nuclear winter" phase did temperatures soar on Earth?
Global forest fires increase the greenhouse gases and caused the Earth to warm.
Is there truth in this statement, "In this sense, the Chicxulub crater is the crucible of human evolution."
Yes, because mammals survived the mass extinction event and mammals diversified after Dinosaurs went extinct.
After the extinction, what plants were the first to recover?
The high abundance of what element led to the hypothesis that an asteroid ~10 km collide with the earth?
The Chicxulub crater in ____________ was about __________ in diameter.
The first bird, Archaeopteryx lithographica, lived in the _____________ (about 148 million years ago), and its feathers are ____________ to modern birds.
-------- feathers are used for insulation and feathers with symmetrical vane are used for ------.
Caudipteryx and Protopteryx have -------------------, indicating that ____________ evolved plumage like modern birds.
Birds and ____________ share same ____________________ bone that allowed these animals to flex the wrist sideways in addition to up and down.
What features are found in birds and Theropods?
Is name Oviraptora a misnomer for these animals?
What characteristic(s) of Archaeopteryx both in modern birds and small dinosaurs like, Maniraptors?
What dinosaur group is most closely related to Ankylosaurs?
What animals are considered the closest living relative to the dinosaurs?
What was the diet of this animal?
T. rex reached its full size __________ and its growth rate is similar to _________.
What animal was described as a "10,000-pound roadrunner from hell?"
Sauropod, diplodocid, _________ it could whip its tail so fast that it could break the sound barrier.
Bone fragments from vulnerable very young dinosaurs which may indicate that T. rex was a predator.
Small dinosaurs _______________ and then their growth halts. This small size, miniaturization, would be advantageous for _______________.
T. rex lived it groups and that bite marks indicate that they bite each other.
What is the background extinction rate?
Over geologic time, how did the vast majority of species become extinct?
How fast do Mass extinctions happen?
Why are mass extinctions important?
What is the cause of The late Devonian extinction?
Is it true that mass extinctions are highly selective?
What mass extinction event resulted in the extinction over 90% of the Earth's species?
What is the evidence that the sixth mass extinction is now underway?
1. There are 100 extinctions per million species per year, which is much greated than background extinction.
2. About 1000 species per year are lost.
After the K/T event event what animals became dominate?
What is or are the benefits of the elaborate horns and frill in Ceratopsians?
What is the inferred function of the dome in Pachycephalosaurs?
What did Stegosaurus use for defense?
Did Ankylosaurian and Stegosaurian dinosaur feed in similar fashions?
No, Ankylosaurian fed on plants located near the ground and Stegosaurian's grazed on plants located higher up and lower down.
What do we know about the reproductive behavior of Orthinopodian dinosaurs?
nesting and they took care of their young.
What is the evidence that Ophthalmosaurus was a deep diver?
What is the function of the sclerotic rings?
What caused the extinction of lizard-shaped ichthyosaurs like Utatsusaurus ?
What is the evidence that ichthyosaurs evolved from Terrestrial reptiles?
What is the evidence that Ichthyosaur migrated?
What is true about Ichthyosaurs?
How long did ichthyosaurs exist?
How did Ichthyosaurs like Stenopterygius give birth?


"In a nutshell, the installation deals with the nature and history of the location; its content is the interaction between the form, the medium, and the location. By simple analogy, the installation is located beneath the surface of the Earth, it is made of earth, and occupies the same space once filled with earth. This space now reacts on its own, to itself, and within itself. Incubare is a kind of perpetuum mobile; I am merely an assistant in this situation which I consider Architecture; the shape and architecture of space are the consequence. Of course, the underground bears many associations, all of which may be correct, but everything is born of earth, and everything returns to it. It remembers EVERYTHING." This is how Miran Blažek describes, in a few words, his installation shown in Osijek's pedestrian underpass Pothodnik as part of the 17th POPUP held on 18 December 2014. Blažek is a visual artist, a graduate of the Academy of Fine Arts in Zagreb with a postgraduate degree from the Academy of Fine Arts in Ljubljana. In 2012, he received the Radoslav Putar Award for best Croatian young visual artist.
This time, instead of employing his usual medium, the painting, he presents a sophisticated and carefully thought-out approach to the space that hosts the installation, akin to site specific. Blažek's new exhibition does not represent a radical detour from his earlier work, hence we can view this exhibition in Osijek as a more intellectually refined follow-up to his earlier show in Zagreb, Inside Story, held between the 3rd and 14th December 2013 at the Institute for Contemporary Art. On that occasion, the artist painted all the walls of the gallery black with charcoal, as high as his hands could reach, after which he cast a model of the exhibition space to the scale of 1:100 from the remaining charcoal dust which he bound with wax by boiling.
Before going into a more detailed analysis of Blažek's work, the following is a brief description of what it is about. The artist chose a room in Osijek's Pothodnik in which he decided to intervene. He started by learning about the location, researching what occupied that same space before – not before it was closed off and became an unused space available for rent, but before the room itself was built. Osijek's Pothodnik is located underground, below the city's main square (Dr Starčević Square), built during the office of Mayor Ivica Fekete in the early 1980s. Evidently, the Pothodnik was created by first removing the earth from under the city square and moving it to another place. This is Blažek's starting point. He tries to bring the space, if only on a symbolic and ritualistic level, back to its original state, that which was taken from it by man. The reason? Geomancy teaches that the entire Earth, and all its space, remembers everything. Accordingly, Blažek wants to empty the space of the sediments of memory, to dememorize it and return its substance to its original state. In an introductory note, the artist declares his intent: "(…) everything is born of earth, and everything returns to it; the earth remembers EVERYTHING." By taking measurements of the room's dimensions and making a wooden mold to a scale of 1:25, the artist made a cast of the measured space, its corpus. The cast is made of earth mixed with beeswax. His choice of earth is self-evident, but why beeswax? In Blažek's words, "it is a known fact that beeswax improves in quality through a process of cleaning and bleaching, and I tarnish it with earth, beneath the surface of the Earth, so earth can complement wax in this case. By losing its quality in this way, beeswax actually takes part in creation. Organic beeswax is not here by accident, it is deliberately chosen instead of paraffin wax." We could say the corpus is in fact a sculpture which adequately corresponds to the space it is in, by condensing the empty space of the white room into a thick, dirty, cast mass. However, we need not stop there, thinking about the installation as a sculpture, because Blažek's artwork is also site specific with elements of land art. Walter de Maria was the first to bring earth from the outside environment to galleries in Munich (1968), Darmstadt (1974), and New York (1977), the latter being the most famous intervention of the three, named "New York Earth Room". To return the earth to the underground space of Pothodnik is not only Blažek's goal, but also the purpose of what he does. He considers it his calling, a ritualistic act which must be performed; "I am merely an assistant in this situation I consider architecture", he says.
Blažek's view of space is all-encompassing and, we may safely say, metaphysical, and the result of his intervention is minimal in the physical sense. This is because precedence is given to the spiritual, not the physical component of the artwork. The physical is only here to express the spiritual, the transcendent. Blažek's understanding of space is concerned not only with its dispersion as a consequence of absorbing objects and things; he is much more reminiscent of Mircea Eliade, who explains that both space and time can be viewed as either sacred or profane. "There is, then, a sacred space, and hence a strong, significant space; there are other spaces that are not sacred and so are without structure or consistency, amorphous. Nor is this all. For religious man, this spatial nonhomogeneity finds expression in the experience of an opposition between space that is sacred – the only real and real-lyexisting space – and all other space, the formless expanse surrounding it. It must be said at once that the religious experience of the nonhomogeneity of space is a primordial experience, homologizable to a founding of the world." The space of Blažek's installation has these very characteristics of sacred space which, while not visually different from its surroundings, contains an enhancement, a kind of sanctity. It is the space in which Blažek performs ritualistic practice by returning it to its original, primordial state with the aim (and necessity) of bringing his art to life. In his study Concerning the Spiritual in Art, Kandinsky writes: "The artist uses his strength to flatter his lower needs; in an ostensibly artistic form he presents what is impure, draws the weaker elements to him, mixes them with evil, betrays men and helps them to betray themselves, while they convince themselves and others that they are spiritually thirsty, and that from this pure spring they may quench their thirst. Such art does not help the forward movement, but hinders it, dragging back those who are striving to press onward, and spreading pestilence abroad. Like Kandinsky, Blažek pursues the "soul" in the artwork, primordial art in its purest form. Was it not the aim of Romanticism to connect and unite living, philosophy, and poetry (art)? Not to mention how vehemently the second half of the 20th century insisted on erasing the boundary between art and life! Furthermore, is prehistoric art not a representation of this identity of living and creating, of life and art? Were tribal shamans and medicine men not performers of something that could be referred to in modern art terms as performance art, happening, action (…), but with different motivations, spiritual ones, transcending our material surroundings? Were cave paintings of animals not "living images" materialized in nature in the form of the real animal the hunter was about to kill?


/scle·ro·cor·ne·al/ (-al) pertaining to the sclera and the cornea.
Despite guarded outflow by sclerocorneal tunnel, over drainage may be a potential risk in the immediate post-operative period, which can however resolve by conservative measures.
Inflammation after sclerocorneal versus clear corneal tunnel phacoemulsification.
The human trabecular meshwork (TM) is the most sensitive tissue of the anterior chamber to the oxidative damage, since it is hidden in the sclerocorneal angle and not directly exposed to light and in consequence has fewer, than cornea and iris, antioxidant defence .
Using the crescent knife, a 4 mm long sclerocorneal tunnel was dissected from the scleral incision, extending approximately 2 mm into clear cornea (Figure 1(b)).
0 mm wide and 4 mm long superior sclerocorneal tunnel was constructed, starting 2 mm from the 12 o'clock limbus and extending 2 mm into the cornea.
Excision of sclerocorneal limbus in dogs and resulting clinical events.
If the concern with clarity is a focal opacity that is peripherally located, think of congenital conditions such as dermoid and sclerocornea (poor definition of the sclerocorneal border).
4% Subconjuctival haemorrhage 8 9 16% 18% Loose sutures 5 4 10% 8% Corneal thinning 0 2 00 4% Inclusion cyst 0 0 00 00 Sclerocorneal dallen 3 0 6% 00 Granuloma 1 2 2% 4% Table 5: Recurrences of Pterygium Number of Patients Characteristics Group A Group B No.
Preliminary clinical results of posterior lamellar keratoplasty through a sclerocorneal pocket incision.
Whole eyes are convenient because they provide better stabilisation during dissection of the limbal sclerocorneal rim.
a) Conjunctival peritomy was carried out and a 6mm triplanar sclerocorneal tunnel made along the marked steeper axis the side incisions is made 180 degrees apart.


Equal Justice Works SummerCorps Application is Open!
Don't miss out on this summer funding opportunity!
Summer Corps is now accepting online applications for the 2012 program. The deadline to apply is March 23, 2012 at 11:59 p.m. EDT.
Summer Corps is an AmeriCorps-funded program that will provide law students with the opportunity to earn a Segal AmeriCorps Education Award for dedicating their summer to a qualifying legal project at a nonprofit public interest organization. Summer Corps members may also serve at organizations that currently host Equal Justice Works AmeriCorps Legal Fellows and traditional Equal Justice Works Fellows.
Become an official member of AmeriCorps, one of the largest national service networks in U.S. history.


We adopted Bullet 9 years ago from GFNC. He was 4 years old at the time and he is our very first greyhound. We had 3 cats at home so we had to find a grey that was cat-trainable, thus limiting our choices. At the time, there were only a very few dogs at the kennel that met that criteria. Bullet had been at the kennel for a while and his picture on the website was not a good one. He seemed a little aloof when we took him out and met him–I guess you could say he wasn't our first choice. I'm used to other dogs just jumping and loving on you–this was not the case with Bullet. I had my reservations, but at the same time, I had my mind up that we were going to adopt a greyhound that day! We had spent a lot of time researching and preparing – we were ready! He ran along the perimeter of the fence and just stopped…maybe eyeing a squirrel. My husband said, "He's the one – he looks regal & strong!" We brought him home and the rest is history! Over the months and years, we have seen him blossom into a loving and devoted companion, and is also perfectly behaved in every way! No counter surfing or separation anxiety – a perfect gentleman! My advice to anyone looking to adopt is please don't overlook those dogs that might not seem as warm or as eager or have been at the kennel a while. Each dog has their own personality that grows and develops within a loving family. At 13 years old, Bullet is a treasured part of our family and enjoying a wonderful life. We are truly blessed!


Medium-sized dogs are known for their high energy levels. It is essential for medium-sized dogs to eat a balanced diet to maintain a healthy weight, glossy coat and strong bones.
Royal Canin Medium dog food helps maintain natural defenses and sustain energy levels of a variety of medium-sized dogs that weigh 23-55 pounds.


text:
Old Testament: Gospel Doctrine Teacher's Manual
Helps for the Teacher
Lesson 1: "This Is My Work and My Glory"
Lesson 2: "Thou Wast Chosen Before Thou Wast Born"
Lesson 3: The Creation
Lesson 4: "Because of My Transgression My Eyes Are Opened"
Lesson 5: "If Thou Doest Well, Thou Shalt Be Accepted"
Lesson 6: "Noah … Prepared an Ark to the Saving of His House"
Lesson 7: The Abrahamic Covenant
Lesson 8: Living Righteously in a Wicked World
Lesson 9: "God Will Provide Himself a Lamb"
Lesson 10: Birthright Blessings; Marriage in the Covenant
Lesson 11: "How Can I Do This Great Wickedness?"
Lesson 12: "Fruitful in the Land of My Affliction"
Lesson 13: Bondage, Passover, and Exodus
Lesson 14: "Ye Shall Be a Peculiar Treasure unto Me"
Lesson 15: "Look to God and Live"
Lesson 16: "I Cannot Go Beyond the Word of the Lord"
Lesson 17: "Beware Lest Thou Forget"
Lesson 18: "Be Strong and of a Good Courage"
Lesson 19: The Reign of the Judges
Lesson 20: "All the City … Doth Know That Thou Art a Virtuous Woman"
Lesson 21: God Will Honor Those Who Honor Him
Lesson 22: "The Lord Looketh on the Heart"
Lesson 23: "The Lord Be Between Thee and Me For Ever"
Lesson 24: "Create in Me a Clean Heart"
Lesson 25: "Let Every Thing That Hath Breath Praise the Lord"
Lesson 26: King Solomon: Man of Wisdom, Man of Foolishness
Lesson 27: The Influence of Wicked and Righteous Leaders
Lesson 28: "After the Fire a Still Small Voice"
Lesson 29: "He Took Up … the Mantle of Elijah"
Lesson 30: "Come to the House of the Lord"
Lesson 31: "Happy Is the Man That Findeth Wisdom"
Lesson 32: "I Know That My Redeemer Liveth"
Lesson 33: Sharing the Gospel with the World
Lesson 34: "I Will Betroth Thee unto Me in Righteousness"
Lesson 35: God Reveals His Secrets to His Prophets
Lesson 36: The Glory of Zion Will Be a Defense
Lesson 37: "Thou Hast Done Wonderful Things"
Lesson 38: "Beside Me There Is No Saviour"
Lesson 39: "How Beautiful upon the Mountains"
Lesson 40: "Enlarge the Place of Thy Tent"
Lesson 41: "I Have Made Thee This Day … an Iron Pillar"
Lesson 42: "I Will Write It in Their Hearts"
Lesson 43: The Shepherds of Israel
Lesson 44: "Every Thing Shall Live Whither the River Cometh"
Lesson 45: "If I Perish, I Perish"
Lesson 46: "A Kingdom, Which Shall Never Be Destroyed"
Lesson 47: "Let Us Rise Up and Build"
Lesson 48: "The Great and Dreadful Day of the Lord"
Lesson 41: 'I Have Made Thee This Day … an Iron Pillar'
This Page (MP3) Entire Book (PDF)
"Lesson 41: 'I Have Made Thee This Day … an Iron Pillar'" Old Testament: Gospel Doctrine Teacher's Manual (2001), 194–97
"Lesson 41," Old Testament: Gospel Doctrine Teacher's Manual , 194–97
"I Have Made Thee This Day … an Iron Pillar"
Jeremiah 1–2; 15; 20; 26; 36–38
To encourage class members to remain faithful in times of opposition and adversity.
Prayerfully study the passages from Jeremiah that are discussed in this lesson. This lesson focuses on the courage and commitment of Jeremiah as he faced opposition from everyone around him.
If you use the attention activity, bring a metal bar to class.
Suggested Lesson Development
Attention Activity
You may want to use the following activity (or one of your own) to begin the lesson.
Display a metal bar and invite a class member to try to break it. Then ask the following questions:
If this object represented a person's characteristics, what would it suggest about him or her?
Ask class members to read Jeremiah 1:17–19. What phrases does the Lord use in verse 18 to describe Jeremiah? What do these phrases suggest about the kind of person Jeremiah was? How would you feel if these words were used to describe you as you set out to fulfill a calling or assignment from the Lord?
Explain that kings, princes, priests, and all the people of the land opposed Jeremiah in his mission, but he valiantly did as the Lord commanded. Refer to the metal bar and explain that Jeremiah was an "iron pillar" who had great strength in times of adversity and did not bend or break.
Scripture Discussion and Application
As you teach the following scripture passages, discuss how they apply to daily life. Encourage class members to share experiences that relate to the scriptural principles.
Jeremiah ministered during the reigns of five kings, from Josiah to Zedekiah (626 to 586 B.C.). With Josiah, he tried to turn the people from idolatry and immorality. But the kings after Josiah ruled in wickedness, and the people were in total apostasy. Jeremiah's mission was to raise a voice of warning to these people, and his denunciations of their wickedness are among the strongest in all scripture. His was one of the last voices of warning before Judah was conquered by the Babylonians.
Jeremiah's life was full of sorrow, but his response to trial can teach and inspire us. He was beaten and imprisoned for prophesying against the kingdom of Judah. His life was constantly threatened. But through all the adversity and opposition, Jeremiah was like an "iron pillar" (Jeremiah 1:18). The book of Jeremiah provides a personal, faith-promoting record of the prophet's response to his life's sorrow and frustration.
1. Jeremiah is called of God to be a prophet.
Review the account of Jeremiah's call to be a prophet as recorded in Jeremiah 1:4–10.
What does Jeremiah's call teach us about the doctrine of foreordination? (See Jeremiah 1:5.) How do you think it helped Jeremiah to know that in the premortal existence he had been foreordained to be a prophet?
The Prophet Joseph Smith said, "Every man who has a calling to minister to the inhabitants of the world was ordained to that very purpose in the Grand Council of heaven before this world was" (History of the Church, 6:364).
What did the Lord do when Jeremiah felt inadequate to fulfill his calling? (See Jeremiah 1:6–10.) How have you been reassured by the Lord when you have felt inadequate?
2. Many people oppose Jeremiah and try to prevent him from fulfilling his mission.
Use the following scriptures to discuss the opposition Jeremiah faced as he fulfilled the mission given to him by the Lord:
Jeremiah 20:1–6. Displeased with Jeremiah's prophecies, Pashur, the chief governor of the temple, had Jeremiah beaten and put in the stocks. Jeremiah prophesied that Pashur, his family, and his friends would be taken captive by the Babylonians and would die in Babylon.
Jeremiah 26:7–15. Most of the people in the land, including the priests, opposed Jeremiah and his message (26:7–11). However, Jeremiah courageously delivered the message the Lord had commanded him to give (26:12–15). Jeremiah was even opposed and hated by his neighbors and relatives (11:19–21; 12:6; note that Jeremiah was from the city of Anathoth).
Jeremiah 36:1–6, 20–32. The words of Jeremiah's prophecies were written down and read to the people (36:1–6). The king burned these words, and the Lord commanded Jeremiah to record them again (36:20–32).
Jeremiah 37:12–15; 38:4–13. Jeremiah was accused unjustly and put into prison (37:12–15). He was later cast into a dungeon, where he sank into the mire (38:4–6). By order of King Zedekiah, Jeremiah was released from the dungeon and put back in prison (38:7–13).
What does Jeremiah 20:14–18 reveal about how Jeremiah felt as he endured such overwhelming opposition? What can we learn from Jeremiah to help us when we experience adversity? (Answers may include that Jeremiah continued to obey the Lord and fulfill his calling even when he was persecuted and discouraged; see Jeremiah 26:12–15.)
In Jeremiah 2:13, what two evils did the Lord say his people had committed? (They had forsaken him, the fountain of living waters. And they had made for themselves broken cisterns that could not hold the Lord's living water, meaning they had sought fulfillment and security in worldly things.) Why would people with these characteristics have difficulty accepting the words of Jeremiah? Why would they be unable to respond to adversity as Jeremiah did? How do we sometimes create "broken cisterns" that cannot hold the Savior's living water?
Elder Marion D. Hanks said:
"Material objectives consume too much of our attention. The struggle for what we need or for more than we need exhausts our time and energy. We pursue pleasure or entertainment, or become overinvolved in associations or civic matters. Of course, people need recreation, need to be achieving, need to contribute; but if these come at the cost of friendship with Christ, the price is much too high.
"'For my people have committed two evils,' said the Lord to Israel; 'they have forsaken me the fountain of living waters, and hewed them out cisterns, broken cisterns, that can hold no water.' (Jer. 2:13.)
"The substitutions we fashion to take the place of God in our lives truly hold no water. To the measure we thus refuse the 'living water,' we miss the joy we could have" (in Conference Report, Apr. 1972, 127; or Ensign, July 1972, 105).
Jeremiah's prophecies that the Babylonians would destroy Jerusalem were fulfilled, as recorded in Jeremiah 39–40. Jeremiah had been in prison during the siege, but afterward the Babylonians freed him and allowed him and a remnant of the Jews to remain in the land of Judah. Johanan, the leader of those who remained, asked Jeremiah to seek the Lord's will for them and promised to obey it (Jeremiah 42:1–6). Through Jeremiah the Lord told the people to stay in the land of Judah and promised to bless them if they would do so (Jeremiah 42:9–22). But Johanan led the people into Egypt, where most of them continued in their wickedness (Jeremiah 43–44).
3. Jeremiah is strengthened in adversity by his love for the word of God.
Discuss the following scriptures, which show how the word of God helped Jeremiah remain strong in times of adversity.
As recorded in Jeremiah 1:9, what did the Lord put in Jeremiah's mouth? As recorded in Jeremiah 15:16, what did Jeremiah do with the words of the Lord? (He ate them, which is poetic language meaning that the word of God became part of him.) How did Jeremiah feel about the words of the Lord?
How can we "eat" the words of the Lord as Jeremiah did? (By studying the scriptures and the counsel of latter-day prophets.) The Book of Mormon prophet Nephi said to "feast upon the words of Christ" (2 Nephi 32:3). How can feasting on the words of the Lord strengthen us?
In Jeremiah 20:9, how did Jeremiah describe the word of the Lord inside him? What do you think it means to have the word of the Lord be a burning fire in your bones? Why do you think Jeremiah could not hold back from teaching the word of the Lord?
Express your feelings about the example of Jeremiah and the need to be faithful in times of adversity. Encourage class members to search and ponder the words of the Lord until these words become fire in their bones, strengthening them as they do the Lord's work. If you used the attention activity, display the metal bar and challenge class members to become, like Jeremiah, an iron pillar for the Lord.
Additional Teaching Ideas
The following material supplements the suggested lesson outline. You may want to use one or more of these ideas as part of the lesson.
1. "Clay … in the potter's hand" (Jeremiah 18:6)
Review the account of Jeremiah's visit to the potter, recorded in Jeremiah 18:1–4. What did the Lord teach his people through Jeremiah's experience? (See Jeremiah 18:5–10. The Lord showed them that if they would repent, he would mold them into something better, just as the potter had reshaped the marred vessel. He also reminded them that he had the power to destroy them if they did not repent.)
How does this comparison apply to us today? How can we become better clay in the Lord's hands? (By being humble—by obeying, repenting, trusting the Lord, and seeking his will. Invite class members to share experiences that show how the Lord has shaped and prepared people to fulfill his purposes.) What happens when we resist being molded by the Lord?
President Heber C. Kimball provided the following insights into the comparison in Jeremiah 18:1–10:
"All [who] are pliable in the hands of God and are obedient to His commands, are vessels of honor, and God will receive them" (History of the Church, 4:478).
"There are many vessels that are destroyed after they have been moulded and shaped. Why? Because they are not contented with the shape the potter has given them, but straightaway put themselves into a shape to please themselves; therefore they are beyond understanding what God designs, and they destroy themselves by the power of their own agency. [These people] have to go through a great many modellings and shapes, then … have to be glazed and burned; and even in the burning, some vessels crack" (in Stanley B. Kimball, Heber C. Kimball: Mormon Patriarch and Pioneer [1981], 270).
2. Jeremiah preached at the same time as the Book of Mormon prophet Lehi
The prophet Jeremiah was teaching and warning the people of Judah at the time Lehi left Jerusalem. Both Jeremiah and Lehi prophesied that Jerusalem would be destroyed.
Where was Jeremiah when Lehi and his family left Jerusalem? (See Jeremiah 37:15–16; 1 Nephi 7:14.)
Jeremiah spent much time counseling with Zedekiah, the king of Judah, but Zedekiah refused to obey the words of the Lord delivered by Jeremiah. What happened to Zedekiah as a result of his disobedience? (See Jeremiah 39:4–7.)
From the Book of Mormon we know that one of the sons of Zedekiah was not slain by the Babylonians. What was his name? (See Helaman 8:20–21.) Where did he go? (See Omni 1:15.)