From 4f43a2970c69a67327cbf0afb95eb2a6751e8e07 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 29 Dec 2005 00:42:16 +0000 Subject: [PATCH] doc updates --- doc/build/content/adv_datamapping.myt | 12 ++++-- doc/build/content/datamapping.myt | 17 ++++++-- doc/build/content/sqlconstruction.myt | 60 +++++++++++++++++++-------- 3 files changed, 65 insertions(+), 24 deletions(-) diff --git a/doc/build/content/adv_datamapping.myt b/doc/build/content/adv_datamapping.myt index 5f7026f8ce..927248e9bc 100644 --- a/doc/build/content/adv_datamapping.myt +++ b/doc/build/content/adv_datamapping.myt @@ -217,12 +217,18 @@ WHERE rowcount.user_id = users.user_id ORDER BY users.oid, addresses.oid # define a mapper for AddressUser that inherits the User.mapper, and joins on the user_id column # inherit_condition is required right now, but will be optional in a future release AddressUser.mapper = mapper( - addresses, inherits = User.mapper, - inherit_condition=User.c.user_id==addresses.c.user_id + addresses, inherits=User.mapper ) items = AddressUser.mapper.select() - + +

Above, the join condition is determined via the foreign keys between the users and the addresses table. To specify the join condition explicitly, use inherit_condition: +<&|formatting.myt:code&> + AddressUser.mapper = mapper( + addresses, inherits=User.mapper, + inherit_condition=users.c.user_id==addresses.c.user_id + ) + <&|doclib.myt:item, name="joins", description="Mapping a Class against Multiple Tables" &> diff --git a/doc/build/content/datamapping.myt b/doc/build/content/datamapping.myt index b514bbda32..a166e9471c 100644 --- a/doc/build/content/datamapping.myt +++ b/doc/build/content/datamapping.myt @@ -64,6 +64,12 @@ password=:password WHERE users.user_id = :user_id userlist = User.mapper.select_by(user_id=12) +

There is also a full-blown "monkeypatch" function that creates a primary mapper, attaches the above mapper class property, and also the methods get, get_by, select, select_by, selectone, commit and delete:

+ <&|formatting.myt:code&> + assign_mapper(User, users) + userlist = User.select_by(user_id=12) + +

Other methods of associating mappers and finder methods with their corresponding classes, such as via common base classes or mixins, can be devised as well. SQLAlchemy does not aim to dictate application architecture and will always allow the broadest variety of architectural patterns, but may include more helper objects and suggested architectures in the future.

<&|doclib.myt:item, name="selecting", description="Selecting from a Mapper" &>

There are a variety of ways to select from a mapper. These range from minimalist to explicit. Below is a synopsis of the these methods:

@@ -71,8 +77,13 @@ password=:password WHERE users.user_id = :user_id # select_by, using property names or column names as keys # the keys are grouped together by an AND operator result = mapper.select_by(name='john', street='123 green street') + + # select_by can also combine SQL criterion with key/value properties + result = mapper.select_by(users.c.user_name=='john', + addresses.c.zip_code=='12345, street='123 green street') - # get_by, which returns a single scalar result or None if no results + # get_by, which takes the same arguments as select_by + # returns a single scalar result or None if no results user = mapper.get_by(id=12) # "dynamic" versions of select_by and get_by - everything past the @@ -101,7 +112,7 @@ password=:password WHERE users.user_id = :user_id # using straight text result = mapper.select_text("select * from users where user_name='fred'") - # or using a "text" object (the 'engine' parameter will not be needed soon) + # or using a "text" object result = mapper.select(text("select * from users where user_name='fred'", engine=engine))

The last few examples above show the usage of the mapper's table object to provide the columns for a WHERE Clause. These columns are also accessible off of the mapped class directly. When a mapper is assigned to a class, it also attaches a special property accessor c to the class itself, which can be used just like the table metadata to access the columns of the table:

@@ -177,7 +188,7 @@ INSERT INTO users (user_name, password) VALUES (:user_name, :password) - +

Recent versions of SQLAlchemy will only put modified object attributes columns into the UPDATE statements generated upon commit, even though some of the examples here dont illustrate that. This is to conserve database traffic and also to successfully interact with a "deferred" attribute, which is a mapped object attribute against the mapper's primary table that isnt loaded until referenced by the application.

<&|doclib.myt:item, name="relations", description="Defining and Using Relationships" &> diff --git a/doc/build/content/sqlconstruction.myt b/doc/build/content/sqlconstruction.myt index 59856bafbb..63f06f21ee 100644 --- a/doc/build/content/sqlconstruction.myt +++ b/doc/build/content/sqlconstruction.myt @@ -375,28 +375,51 @@ WHERE addresses.user_id = users.user_id {} -

There is also an explicit join constructor, which is used like this:

+

There is also an explicit join constructor, which can be embedded into a select query via the from_obj parameter of the select statement:

+ <&|formatting.myt:code &> -<&formatting.myt:poplink&>\ -addresses.select(from_obj=[ - addresses.join(users, addresses.c.user_id==users.c.user_id) - ]).execute() -<&|formatting.myt:codepopper, link="sql" &> -SELECT addresses.address_id, addresses.user_id, addresses.street, addresses.city, -addresses.state, addresses.zip -FROM addresses JOIN users ON addresses.user_id = users.user_id -{} - + <&formatting.myt:poplink&>addresses.select(from_obj=[ + addresses.join(users, addresses.c.user_id==users.c.user_id) + ]).execute() + <&|formatting.myt:codepopper, link="sql" &> + SELECT addresses.address_id, addresses.user_id, addresses.street, addresses.city, + addresses.state, addresses.zip + FROM addresses JOIN users ON addresses.user_id = users.user_id + {} + + -

The join criterion in a join() call is currently required, but in a future release will be derived automatically from the two table's primary keys if not specified.

+ +

The join constructor can also be used by itself:

+ <&|formatting.myt:code &> + <&formatting.myt:poplink&>join(users, addresses, users.c.user_id==addresses.c.user_id).select().execute() + <&|formatting.myt:codepopper, link="sql" &> + SELECT users.user_id, users.user_name, users.password, + addresses.address_id, addresses.user_id, addresses.street, addresses.city, + addresses.state, addresses.zip + FROM addresses JOIN users ON addresses.user_id = users.user_id + {} + + +

The join criterion in a join() call is optional. If not specified, the condition will be derived from the foreign key relationships of the two tables. If no criterion can be constructed, an exception will be raised.

+ + <&|formatting.myt:code &> + <&formatting.myt:poplink&>join(users, addresses).select().execute() + <&|formatting.myt:codepopper, link="sql" &> + SELECT users.user_id, users.user_name, users.password, + addresses.address_id, addresses.user_id, addresses.street, addresses.city, + addresses.state, addresses.zip + FROM addresses JOIN users ON addresses.user_id = users.user_id + {} + + +

Notice that this is the first example where the FROM criterion of the select statement is explicitly specified. In most cases, the FROM criterion is automatically determined from the columns requested as well as the WHERE clause. The from_obj keyword argument indicates a list of explicit FROM clauses to be used in the statement.

A join can be created on its own using the join or outerjoin functions, or can be created off of an existing Table or other selectable unit via the join or outerjoin methods:

<&|formatting.myt:code &> - <&formatting.myt:poplink&>select([users, addresses], from_obj=[ - outerjoin(users, addresses, users.c.user_id==addresses.c.address_id) - ]).execute() + <&formatting.myt:poplink&>outerjoin(users, addresses, users.c.user_id==addresses.c.address_id).select().execute() <&|formatting.myt:codepopper, link="sql" &> SELECT users.user_id, users.user_name, users.password, addresses.address_id, addresses.user_id, addresses.street, addresses.city, addresses.state, addresses.zip @@ -861,16 +884,17 @@ WHERE users.user_name = :users_user_name AND keywords.name IN ('jack', 'foo') <&|formatting.myt:code &> s = select([addresses.c.city], addresses.c.user_id==users.c.user_id) <&formatting.myt:poplink&>users.update( - and_(users.c.user_id>10, users.c.user_id<20), + and_(users.c.user_id>10, users.c.user_id<20), values={users.c.user_name:s} - ).execute() + ).execute() <&|formatting.myt:codepopper, link="sql" &> UPDATE users SET user_name=(SELECT addresses.city FROM addresses WHERE addresses.user_id = users.user_id) - WHERE users.user_id > :users_user_id AND users.user_id < :users_user_id_1 + WHERE users.user_id > :users_user_id AND users.user_id < :users_user_id_1 {'users_user_id_1': 20, 'users_user_id': 10} + -- 2.47.2