~ubuntu-branches/debian/jessie/sqlalchemy/jessie

<li><a class="reference internal" href="#specifying-alternate-join-conditions-to-relationship">Specifying Alternate Join Conditions to relationship()</a><ul>

107

<li><a class="reference internal" href="#self-referential-many-to-many-relationship">Self-Referential Many-to-Many Relationship</a></li>

102

108

<li><a class="reference internal" href="#specifying-foreign-keys">Specifying Foreign Keys</a></li>

103

109

<li><a class="reference internal" href="#building-query-enabled-properties">Building Query-Enabled Properties</a></li>

104

110

<li><a class="reference internal" href="#multiple-relationships-against-the-same-parent-child">Multiple Relationships against the Same Parent/Child</a></li>

106

112

</li>

107

113

<li><a class="reference internal" href="#rows-that-point-to-themselves-mutually-dependent-rows">Rows that point to themselves / Mutually Dependent Rows</a></li>

108

114

<li><a class="reference internal" href="#mutable-primary-keys-update-cascades">Mutable Primary Keys / Update Cascades</a></li>

109

<li><a class="reference internal" href="#the-relationship-api">The <tt class="docutils literal">relationship()</tt> API</a></li>

115

<li><a class="reference internal" href="#relationships-api">Relationships API</a></li>

110

116

</ul>

111

117

</li>

112

118

</ul>

581

587

</div>

582

588

</div>

583

589

</div>

590

591

<h2>Linking relationships with Backref<a class="headerlink" href="#linking-relationships-with-backref" title="Permalink to this headline">¶</a></h2>

592

The <tt class="docutils literal">backref</tt> keyword argument was first introduced in <a class="reference internal" href="tutorial.html">Object Relational Tutorial</a>, and has been

593

mentioned throughout many of the examples here. What does it actually do ? Let’s start

594

with the canonical <tt class="docutils literal">User</tt> and <tt class="docutils literal">Address</tt> scenario:

595

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import Integer, ForeignKey, String, Column

596

from sqlalchemy.ext.declarative import declarative_base

597

from sqlalchemy.orm import relationship

598

599

Base = declarative_base()

600

601

class User(Base):

602

__tablename__ = 'user'

603

id = Column(Integer, primary_key=True)

604

name = Column(String)

605

606

addresses = relationship("Address", backref="user")

607

608

class Address(Base):

609

__tablename__ = 'address'

610

611

email = Column(String)

612

user_id = Column(Integer, ForeignKey('user.id'))</pre></div>

613

</div>

614

The above configuration establishes a collection of <tt class="docutils literal">Address</tt> objects on <tt class="docutils literal">User</tt> called

615

<tt class="docutils literal">User.addresses</tt>. It also establishes a <tt class="docutils literal">.user</tt> attribute on <tt class="docutils literal">Address</tt> which will

616

refer to the parent <tt class="docutils literal">User</tt> object.

617

In fact, the <tt class="docutils literal">backref</tt> keyword is only a common shortcut for placing a second

618

<tt class="docutils literal">relationship</tt> onto the <tt class="docutils literal">Address</tt> mapping, including the establishment

619

of an event listener on both sides which will mirror attribute operations

620

in both directions. The above configuration is equivalent to:

621

622

from sqlalchemy.ext.declarative import declarative_base

623

from sqlalchemy.orm import relationship

624

625

Base = declarative_base()

626

627

class User(Base):

628

__tablename__ = 'user'

629

630

name = Column(String)

631

632

addresses = relationship("Address", back_populates="user")

633

634

class Address(Base):

635

__tablename__ = 'address'

636

637

email = Column(String)

638

639

640

user = relationship("User", back_populates="addresses")</pre></div>

641

</div>

642

Above, we add a <tt class="docutils literal">.user</tt> relationship to <tt class="docutils literal">Address</tt> explicitly. On

643

both relationships, the <tt class="docutils literal">back_populates</tt> directive tells each relationship

644

about the other one, indicating that they should establish “bi-directional”

645

behavior between each other. The primary effect of this configuration

646

is that the relationship adds event handlers to both attributes

647

which have the behavior of “when an append or set event occurs here, set ourselves

648

onto the incoming attribute using this particular attribute name”.

649

The behavior is illustrated as follows. Start with a <tt class="docutils literal">User</tt> and an <tt class="docutils literal">Address</tt>

650

instance. The <tt class="docutils literal">.addresses</tt> collection is empty, and the <tt class="docutils literal">.user</tt> attribute

651

is <tt class="xref docutils literal">None</tt>:

652

<div class="highlight-python"><div class="highlight"><pre>>>> u1 = User()

653

>>> a1 = Address()

654

>>> u1.addresses

655

[]

656

>>> print a1.user

657

None</pre></div>

658

</div>

659

However, once the <tt class="docutils literal">Address</tt> is appended to the <tt class="docutils literal">u1.addresses</tt> collection,

660

both the collection and the scalar attribute have been populated:

661

<div class="highlight-python"><div class="highlight"><pre>>>> u1.addresses.append(a1)

662

>>> u1.addresses

663

[<__main__.Address object at 0x12a6ed0>]

664

>>> a1.user

665

<__main__.User object at 0x12a6590></pre></div>

666

</div>

667

This behavior of course works in reverse for removal operations as well, as well

668

as for equivalent operations on both sides. Such as

669

when <tt class="docutils literal">.user</tt> is set again to <tt class="xref docutils literal">None</tt>, the <tt class="docutils literal">Address</tt> object is removed

670

from the reverse collection:

671

<div class="highlight-python"><div class="highlight"><pre>>>> a1.user = None

672

>>> u1.addresses

673

[]</pre></div>

674

</div>

675

The manipulation of the <tt class="docutils literal">.addresses</tt> collection and the <tt class="docutils literal">.user</tt> attribute

676

occurs entirely in Python without any interaction with the SQL database.

677

Without this behavior, the proper state would be apparent on both sides once the

678

data has been flushed to the database, and later reloaded after a commit or

679

expiration operation occurs. The <tt class="docutils literal">backref</tt>/<tt class="docutils literal">back_populates</tt> behavior has the advantage

680

that common bidirectional operations can reflect the correct state without requiring

681

a database round trip.

682

Remember, when the <tt class="docutils literal">backref</tt> keyword is used on a single relationship, it’s

683

exactly the same as if the above two relationships were created individually

684

using <tt class="docutils literal">back_populates</tt> on each.

685

686

<h3>Backref Arguments<a class="headerlink" href="#backref-arguments" title="Permalink to this headline">¶</a></h3>

687

We’ve established that the <tt class="docutils literal">backref</tt> keyword is merely a shortcut for building

688

two individual <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> constructs that refer to each other. Part of

689

the behavior of this shortcut is that certain configurational arguments applied to

690

the <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a>

691

will also be applied to the other direction - namely those arguments that describe

692

the relationship at a schema level, and are unlikely to be different in the reverse

693

direction. The usual case

694

here is a many-to-many <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> that has a <tt class="docutils literal">secondary</tt> argument,

695

or a one-to-many or many-to-one which has a <tt class="docutils literal">primaryjoin</tt> argument (the

696

<tt class="docutils literal">primaryjoin</tt> argument is discussed in <a class="reference internal" href="#relationship-primaryjoin">Specifying Alternate Join Conditions to relationship()</a>). Such

697

as if we limited the list of <tt class="docutils literal">Address</tt> objects to those which start with “tony”:

698

699

from sqlalchemy.ext.declarative import declarative_base

700

from sqlalchemy.orm import relationship

701

702

Base = declarative_base()

703

704

class User(Base):

705

__tablename__ = 'user'

706

707

name = Column(String)

708

709

addresses = relationship("Address",

710

primaryjoin="and_(User.id==Address.user_id, "

711

"Address.email.startswith('tony'))",

712

backref="user")

713

714

class Address(Base):

715

__tablename__ = 'address'

716

717

email = Column(String)

718

719

</div>

720

We can observe, by inspecting the resulting property, that both sides

721

of the relationship have this join condition applied:

722

<div class="highlight-python"><div class="highlight"><pre>>>> print User.addresses.property.primaryjoin

723

"user".id = address.user_id AND address.email LIKE :email_1 || '%%'

724

>>>

725

>>> print Address.user.property.primaryjoin

726

"user".id = address.user_id AND address.email LIKE :email_1 || '%%'

727

>>></pre></div>

728

</div>

729

This reuse of arguments should pretty much do the “right thing” - it uses

730

only arguments that are applicable, and in the case of a many-to-many

731

relationship, will reverse the usage of <tt class="docutils literal">primaryjoin</tt> and <tt class="docutils literal">secondaryjoin</tt>

732

to correspond to the other direction (see the example in <a class="reference internal" href="#self-referential-many-to-many">Self-Referential Many-to-Many Relationship</a>

733

for this).

734

It’s very often the case however that we’d like to specify arguments that

735

are specific to just the side where we happened to place the “backref”.

736

This includes <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> arguments like <tt class="docutils literal">lazy</tt>, <tt class="docutils literal">remote_side</tt>,

737

<tt class="docutils literal">cascade</tt> and <tt class="docutils literal">cascade_backrefs</tt>. For this case we use the <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal">backref()</tt></a>

738

function in place of a string:

739

740

from sqlalchemy.orm import backref

741

742

class User(Base):

743

__tablename__ = 'user'

744

745

name = Column(String)

746

747

addresses = relationship("Address",

748

backref=backref("user", lazy="joined"))</pre></div>

749

</div>

750

Where above, we placed a <tt class="docutils literal">lazy="joined"</tt> directive only on the <tt class="docutils literal">Address.user</tt>

751

side, indicating that when a query against <tt class="docutils literal">Address</tt> is made, a join to the <tt class="docutils literal">User</tt>

752

entity should be made automatically which will populate the <tt class="docutils literal">.user</tt> attribute of each

753

returned <tt class="docutils literal">Address</tt>. The <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal">backref()</tt></a> function formatted the arguments we gave

754

it into a form that is interpreted by the receiving <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> as additional

755

arguments to be applied to the new relationship it creates.

756

</div>

757

758

<h3>One Way Backrefs<a class="headerlink" href="#one-way-backrefs" title="Permalink to this headline">¶</a></h3>

759

An unusual case is that of the “one way backref”. This is where the “back-populating”

760

behavior of the backref is only desirable in one direction. An example of this

761

is a collection which contains a filtering <tt class="docutils literal">primaryjoin</tt> condition. We’d like to append

762

items to this collection as needed, and have them populate the “parent” object on the

763

incoming object. However, we’d also like to have items that are not part of the collection,

764

but still have the same “parent” association - these items should never be in the

765

collection.

766

Taking our previous example, where we established a <tt class="docutils literal">primaryjoin</tt> that limited the

767

collection only to <tt class="docutils literal">Address</tt> objects whose email address started with the word <tt class="docutils literal">tony</tt>,

768

the usual backref behavior is that all items populate in both directions. We wouldn’t

769

want this behavior for a case like the following:

770

<div class="highlight-python"><div class="highlight"><pre>>>> u1 = User()

771

>>> a1 = Address(email='mary')

772

>>> a1.user = u1

773

>>> u1.addresses

774

[<__main__.Address object at 0x1411910>]</pre></div>

775

</div>

776

Above, the <tt class="docutils literal">Address</tt> object that doesn’t match the criterion of “starts with ‘tony’”

777

is present in the <tt class="docutils literal">addresses</tt> collection of <tt class="docutils literal">u1</tt>. After these objects are flushed,

778

the transaction committed and their attributes expired for a re-load, the <tt class="docutils literal">addresses</tt>

779

collection will hit the database on next access and no longer have this <tt class="docutils literal">Address</tt> object

780

present, due to the filtering condition. But we can do away with this unwanted side

781

of the “backref” behavior on the Python side by using two separate <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> constructs,

782

placing <tt class="docutils literal">back_populates</tt> only on one side:

783

784

from sqlalchemy.ext.declarative import declarative_base

785

from sqlalchemy.orm import relationship

786

787

Base = declarative_base()

788

789

class User(Base):

790

__tablename__ = 'user'

791

792

name = Column(String)

793

addresses = relationship("Address",

794

primaryjoin="and_(User.id==Address.user_id, "

795

"Address.email.startswith('tony'))",

796

back_populates="user")

797

798

class Address(Base):

799

__tablename__ = 'address'

800

801

email = Column(String)

802

803

user = relationship("User")</pre></div>

804

</div>

805

With the above scenario, appending an <tt class="docutils literal">Address</tt> object to the <tt class="docutils literal">.addresses</tt>

806

collection of a <tt class="docutils literal">User</tt> will always establish the <tt class="docutils literal">.user</tt> attribute on that

807

<tt class="docutils literal">Address</tt>:

808

<div class="highlight-python"><div class="highlight"><pre>>>> u1 = User()

809

810

>>> u1.addresses.append(a1)

811

>>> a1.user

812

<__main__.User object at 0x1411850></pre></div>

813

</div>

814

However, applying a <tt class="docutils literal">User</tt> to the <tt class="docutils literal">.user</tt> attribute of an <tt class="docutils literal">Address</tt>,

815

will not append the <tt class="docutils literal">Address</tt> object to the collection:

816

<div class="highlight-python"><div class="highlight"><pre>>>> a2 = Address(email='mary')

817

>>> a2.user = u1

818

>>> a2 in u1.addresses

819

False</pre></div>

820

</div>

821

Of course, we’ve disabled some of the usefulness of <tt class="docutils literal">backref</tt> here, in that

822

when we do append an <tt class="docutils literal">Address</tt> that corresponds to the criteria of <tt class="docutils literal">email.startswith('tony')</tt>,

823

it won’t show up in the <tt class="docutils literal">User.addresses</tt> collection until the session is flushed,

824

and the attributes reloaded after a commit or expire operation. While we could

825

consider an attribute event that checks this criterion in Python, this starts

826

to cross the line of duplicating too much SQL behavior in Python. The backref behavior

827

itself is only a slight transgression of this philosophy - SQLAlchemy tries to keep

828

these to a minimum overall.

829

</div>

830

</div>

584

831

585

<h2>Specifying Alternate Join Conditions to relationship()<a class="headerlink" href="#specifying-alternate-join-conditions-to-relationship" title="Permalink to this headline">¶</a></h2>

832

<h2>Specifying Alternate Join Conditions to relationship()<a class="headerlink" href="#specifying-alternate-join-conditions-to-relationship" title="Permalink to this headline">¶</a></h2>

586

833

The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> function uses the foreign key

587

834

relationship between the parent and child tables to formulate the primary

588

835

join condition between parent and child; in the case of a many-to-many

603

850

secondaryjoin</pre>

604

851

</div>

605

852

If you are working with a <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> which has no

606

<a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> objects on it (which can be the case

853

607

854

when using reflected tables with MySQL), or if the join condition cannot be

608

expressed by a simple foreign key relationship, use the <tt class="docutils literal">primaryjoin</tt> and

609

possibly <tt class="docutils literal">secondaryjoin</tt> conditions to create the appropriate relationship.

610

In this example we create a relationship <tt class="docutils literal">boston_addresses</tt> which will only

611

load the user addresses with a city of “Boston”:

612

<div class="highlight-python+sql"><div class="highlight"><pre>class User(object):

855

expressed by a simple foreign key relationship, use the <tt class="docutils literal">primaryjoin</tt>, and

856

for many-to-many relationships <tt class="docutils literal">secondaryjoin</tt>, directives

857

to create the appropriate relationship.

858

In this example, using the <tt class="docutils literal">User</tt> class as well as an <tt class="docutils literal">Address</tt> class

859

which stores a street address, we create a relationship <tt class="docutils literal">boston_addresses</tt> which will only

860

load those <tt class="docutils literal">Address</tt> objects which specify a city of “Boston”:

861

862

from sqlalchemy.ext.declarative import declarative_base

863

from sqlalchemy.orm import relationship

864

865

Base = declarative_base()

866

867

class User(Base):

868

__tablename__ = 'user'

869

870

name = Column(String)

871

addresses = relationship("Address",

872

primaryjoin="and_(User.id==Address.user_id, "

873

"Address.city=='Boston')")

874

875

class Address(Base):

876

__tablename__ = 'address'

877

878

879

880

street = Column(String)

881

city = Column(String)

882

state = Column(String)

883

zip = Column(String)</pre></div>

884

</div>

885

Note above we specified the <tt class="docutils literal">primaryjoin</tt> argument as a string - this feature

886

is available only when the mapping is constructed using the Declarative extension,

887

and allows us to specify a full SQL expression

888

between two entities before those entities have been fully constructed. When

889

all mappings have been defined, an automatic “mapper configuration” step interprets

890

these string arguments when first needed.

891

Within this string SQL expression, we also made usage of the <a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal">and_()</tt></a> conjunction construct to establish

892

two distinct predicates for the join condition - joining both the <tt class="docutils literal">User.id</tt> and

893

<tt class="docutils literal">Address.user_id</tt> columns to each other, as well as limiting rows in <tt class="docutils literal">Address</tt>

894

to just <tt class="docutils literal">city='Boston'</tt>. When using Declarative, rudimentary SQL functions like

895

<a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal">and_()</tt></a> are automatically available in the evaulated namespace of a string

896

<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> argument.

897

When using classical mappings, we have the advantage of the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects

898

already being present when the mapping is defined, so that the SQL expression

899

can be created immediately:

900

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy.orm import relationship, mapper

901

902

class User(object):

613

903

pass

614

904

class Address(object):

615

905

pass

617

907

mapper(Address, addresses_table)

618

908

mapper(User, users_table, properties={

619

909

'boston_addresses': relationship(Address, primaryjoin=

620

and_(users_table.c.user_id==addresses_table.c.user_id,

910

and_(users_table.c.id==addresses_table.c.user_id,

621

911

addresses_table.c.city=='Boston'))

622

912

})</pre></div>

623

913

</div>

914

Note that the custom criteria we use in a <tt class="docutils literal">primaryjoin</tt> is generally only significant

915

when SQLAlchemy is rendering SQL in order to load or represent this relationship.

916

That is, it’s used

917

in the SQL statement that’s emitted in order to perform a per-attribute lazy load, or when a join is

918

constructed at query time, such as via <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal">Query.join()</tt></a>, or via the eager “joined” or “subquery”

919

styles of loading. When in-memory objects are being manipulated, we can place any <tt class="docutils literal">Address</tt> object

920

we’d like into the <tt class="docutils literal">boston_addresses</tt> collection, regardless of what the value of the <tt class="docutils literal">.city</tt>

921

attribute is. The objects will remain present in the collection until the attribute is expired

922

and re-loaded from the database where the criterion is applied. When

923

a flush occurs, the objects inside of <tt class="docutils literal">boston_addresses</tt> will be flushed unconditionally, assigning

924

value of the primary key <tt class="docutils literal">user.id</tt> column onto the foreign-key-holding <tt class="docutils literal">address.user_id</tt> column

925

for each row. The <tt class="docutils literal">city</tt> criteria has no effect here, as the flush process only cares about synchronizing primary

926

key values into referencing foreign key values.

927

928

<h3>Self-Referential Many-to-Many Relationship<a class="headerlink" href="#self-referential-many-to-many-relationship" title="Permalink to this headline">¶</a></h3>

624

929

Many to many relationships can be customized by one or both of <tt class="docutils literal">primaryjoin</tt>

625

and <tt class="docutils literal">secondaryjoin</tt>, shown below with just the default many-to-many

626

relationship explicitly set:

627

<div class="highlight-python+sql"><div class="highlight"><pre>class User(object):

628

pass

629

class Keyword(object):

630

pass

631

mapper(Keyword, keywords_table)

632

633

'keywords': relationship(Keyword, secondary=userkeywords_table,

634

primaryjoin=users_table.c.user_id==userkeywords_table.c.user_id,

635

secondaryjoin=userkeywords_table.c.keyword_id==keywords_table.c.keyword_id

636

)

637

})</pre></div>

930

and <tt class="docutils literal">secondaryjoin</tt>. A common situation for custom primary and secondary joins

931

is when establishing a many-to-many relationship from a class to itself, as shown below:

932

933

from sqlalchemy.ext.declarative import declarative_base

934

from sqlalchemy.orm import relationship

935

936

Base = declarative_base()

937

938

node_to_node = Table("node_to_node", Base.metadata,

939

Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),

940

Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)

941

)

942

943

class Node(Base):

944

__tablename__ = 'node'

945

946

label = Column(String)

947

right_nodes = relationship("Node",

948

secondary=node_to_node,

949

primaryjoin=id==node_to_node.c.left_node_id,

950

secondaryjoin=id==node_to_node.c.right_node_id,

951

backref="left_nodes"

952

)</pre></div>

953

</div>

954

Where above, SQLAlchemy can’t know automatically which columns should connect

955

to which for the <tt class="docutils literal">right_nodes</tt> and <tt class="docutils literal">left_nodes</tt> relationships. The <tt class="docutils literal">primaryjoin</tt>

956

and <tt class="docutils literal">secondaryjoin</tt> arguments establish how we’d like to join to the association table.

957

In the Declarative form above, as we are declaring these conditions within the Python

958

block that corresponds to the <tt class="docutils literal">Node</tt> class, the <tt class="docutils literal">id</tt> variable is available directly

959

as the <tt class="docutils literal">Column</tt> object we wish to join with.

960

A classical mapping situation here is similar, where <tt class="docutils literal">node_to_node</tt> can be joined

961

to <tt class="docutils literal">node.c.id</tt>:

962

963

from sqlalchemy.orm import relationship, mapper

964

965

metadata = MetaData()

966

967

968

969

970

)

971

972

node = Table("node", metadata,

973

Column('id', Integer, primary_key=True),

974

Column('label', String)

975

)

976

class Node(object):

977

pass

978

979

mapper(Node, node, properties={

980

'right_nodes':relationship(Node,

981

secondary=node_to_node,

982

primaryjoin=node.c.id==node_to_node.c.left_node_id,

983

secondaryjoin=node.c.id==node_to_node.c.right_node_id,

984

backref="left_nodes"

985

)})</pre></div>

986

</div>

987

Note that in both examples, the <tt class="docutils literal">backref</tt> keyword specifies a <tt class="docutils literal">left_nodes</tt>

988

backref - when <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> creates the second relationship in the reverse

989

direction, it’s smart enough to reverse the <tt class="docutils literal">primaryjoin</tt> and <tt class="docutils literal">secondaryjoin</tt> arguments.

638

990

</div>

639

991

640

992

<h3>Specifying Foreign Keys<a class="headerlink" href="#specifying-foreign-keys" title="Permalink to this headline">¶</a></h3>

798

1150

map for objects that may be referencing the one with a

799

1151

mutating primary key, not throughout the database.

800

1152

</div>

801

802

<h2>The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> API<a class="headerlink" href="#the-relationship-api" title="Permalink to this headline">¶</a></h2>

1153

1154

<h2>Relationships API<a class="headerlink" href="#relationships-api" title="Permalink to this headline">¶</a></h2>

803

1155

804

1156

805

1157

<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">relationship</tt><big>(</big>argument, secondary=None, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.orm.relationship" title="Permalink to this definition">¶</a></dt>

810

1162

<a class="reference internal" href="#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal">relation()</tt></a> prior to version 0.6.

811

1163

</div>

812

1164

This corresponds to a parent-child or associative table relationship. The

813

constructed class is an instance of <tt class="xref py py-class docutils literal">RelationshipProperty</tt>.

1165

constructed class is an instance of <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty" title="sqlalchemy.orm.properties.RelationshipProperty"><tt class="xref py py-class docutils literal">RelationshipProperty</tt></a>.

814

1166

A typical <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a>:

815

1167

<div class="highlight-python"><div class="highlight"><pre>mapper(Parent, properties={

816

1168

'children': relationship(Children)

821

1173

822

1174

823

1175

<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

824

<li>argument – a class or <tt class="xref py py-class docutils literal">Mapper</tt> instance, representing the target of

1176

<li>argument – a class or <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal">Mapper</tt></a> instance, representing the target of

825

1177

the relationship.</li>

826

1178

<li>secondary – for a many-to-many relationship, specifies the intermediary

827

1179

table. The secondary keyword argument should generally only

878

1230

</ul>

879

1231

</li>

880

1232

<li>cascade_backrefs=True – a boolean value indicating if the <tt class="docutils literal">save-update</tt> cascade should

881

operate along a backref event. When set to <tt class="xref docutils literal">False</tt> on a

882

one-to-many relationship that has a many-to-one backref, assigning

883

a persistent object to the many-to-one attribute on a transient object

884

will not add the transient to the session. Similarly, when

885

set to <tt class="xref docutils literal">False</tt> on a many-to-one relationship that has a one-to-many

886

backref, appending a persistent object to the one-to-many collection

887

on a transient object will not add the transient to the session.

1233

operate along an assignment event intercepted by a backref.

1234

When set to <tt class="xref docutils literal">False</tt>,

1235

the attribute managed by this relationship will not cascade

1236

an incoming transient object into the session of a

1237

persistent parent, if the event is received via backref.

1238

That is:

1239

<div class="highlight-python"><div class="highlight"><pre>mapper(A, a_table, properties={

1240

'bs':relationship(B, backref="a", cascade_backrefs=False)

1241

})</pre></div>

1242

</div>

1243

If an <tt class="docutils literal">A()</tt> is present in the session, assigning it to

1244

the “a” attribute on a transient <tt class="docutils literal">B()</tt> will not place

1245

the <tt class="docutils literal">B()</tt> into the session. To set the flag in the other

1246

direction, i.e. so that <tt class="docutils literal">A().bs.append(B())</tt> won’t add

1247

a transient <tt class="docutils literal">A()</tt> into the session for a persistent <tt class="docutils literal">B()</tt>:

1248

1249

'bs':relationship(B,

1250

backref=backref("a", cascade_backrefs=False)

1251

)

1252

})</pre></div>

1253

</div>

888

1254

<tt class="docutils literal">cascade_backrefs</tt> is new in 0.6.5.

889

1255

</li>

890

1256

<li>collection_class – a class or callable that returns a new list-holding object. will

891

1257

be used in place of a plain list for storing elements.

892

1258

Behavior of this attribute is described in detail at

893

1259

<a class="reference internal" href="collections.html#custom-collections">Customizing Collection Access</a>.</li>

894

<li>comparator_factory – a class which extends <tt class="xref py py-class docutils literal">RelationshipProperty.Comparator</tt> which

1260

<li>comparator_factory – a class which extends <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator"><tt class="xref py py-class docutils literal">RelationshipProperty.Comparator</tt></a> which

895

1261

provides custom SQL clause generation for comparison operations.</li>

896

1262

<li>doc – docstring which will be applied to the resulting descriptor.</li>

897

<li>extension – an <tt class="xref py py-class docutils literal">AttributeExtension</tt> instance, or list of extensions,

1263

<li>extension – an <a class="reference internal" href="interfaces.html#sqlalchemy.orm.interfaces.AttributeExtension" title="sqlalchemy.orm.interfaces.AttributeExtension"><tt class="xref py py-class docutils literal">AttributeExtension</tt></a> instance, or list of extensions,

898

1264

which will be prepended to the list of attribute listeners for

899

the resulting descriptor placed on the class. These listeners

900

will receive append and set events before the operation

901

proceeds, and may be used to halt (via exception throw) or

902

change the value used in the operation.</li>

1265

the resulting descriptor placed on the class.

1266

Deprecated. Please see <a class="reference internal" href="events.html#sqlalchemy.orm.events.AttributeEvents" title="sqlalchemy.orm.events.AttributeEvents"><tt class="xref py py-class docutils literal">AttributeEvents</tt></a>.</li>

903

1267

<li>foreign_keys – a list of columns which are to be used as “foreign key” columns.

904

1268

Normally, <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> uses the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>

905

1269

and <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> objects present within the

1059

1423

table).</li>

1060

1424

<li>remote_side – used for self-referential relationships, indicates the column or

1061

1425

list of columns that form the “remote side” of the relationship.</li>

1426

<li>query_class – a <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal">Query</tt></a> subclass that will be used as the base of the

1427

“appender query” returned by a “dynamic” relationship, that

1428

is, a relationship that specifies <tt class="docutils literal">lazy="dynamic"</tt> or was

1429

otherwise constructed using the <a class="reference internal" href="#sqlalchemy.orm.dynamic_loader" title="sqlalchemy.orm.dynamic_loader"><tt class="xref py py-func docutils literal">orm.dynamic_loader()</tt></a>

1430

function.</li>

1062

1431

<li>secondaryjoin – a ColumnElement (i.e. WHERE criterion) that will be used as the join of

1063

1432

an association table to the child object. By default, this value is

1064

1433

computed based on the foreign key relationships of the association and

1094

1463

1095

1464

1096

1465

<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">backref</tt><big>(</big>name, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.orm.backref" title="Permalink to this definition">¶</a></dt>

1097

<dd>Create a back reference with explicit arguments, which are the same

1466

<dd>Create a back reference with explicit keyword arguments, which are the same

1098

1467

arguments one can send to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a>.

1099

Used with the <cite>backref</cite> keyword argument to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> in

1100

place of a string argument.

1468

Used with the <tt class="docutils literal">backref</tt> keyword argument to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> in

1469

place of a string argument, e.g.:

1470

<div class="highlight-python"><pre>'items':relationship(SomeItem, backref=backref('parent', lazy='subquery'))</pre>

1471

</div>

1101

1472

</dd></dl>

1102

1473

1103

1474

1106

1477

<dd>A synonym for <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a>.

1107

1478

</dd></dl>

1108

1479

1480

1481

1482

<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">dynamic_loader</tt><big>(</big>argument, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.orm.dynamic_loader" title="Permalink to this definition">¶</a></dt>

1483

<dd>Construct a dynamically-loading mapper property.

1484

This is essentially the same as

1485

using the <tt class="docutils literal">lazy='dynamic'</tt> argument with <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a>:

1486

<div class="highlight-python"><div class="highlight"><pre>dynamic_loader(SomeClass)

1487

1488

# vs.

1489

1490

relationship(SomeClass, lazy="dynamic")</pre></div>

1491

</div>

1492

A <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> that is “dynamic” features the behavior

1493

that read operations return an active <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal">Query</tt></a> object which

1494

reads from the database when accessed. Items may be appended to the

1495

attribute via <tt class="docutils literal">append()</tt>, or removed via <tt class="docutils literal">remove()</tt>; changes will be

1496

persisted to the database during a <tt class="xref py py-meth docutils literal">Sesion.flush()</tt>. However, no other

1497

Python list or collection mutation operations are available.

1498

All arguments accepted by <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> are

1499

accepted here, other than <tt class="docutils literal">lazy</tt> which is fixed at <tt class="docutils literal">dynamic</tt>.

1500

</dd></dl>

1501

1109

1502

</div>

1110

1503

</div>

1111

1504

Older »