[Sqlalchemy-commits] [1362] sqlalchemy/branches/schema/doc/build/content: first pass completed
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1362] sqlalchemy/branches/schema/doc/build/content: first pass completed
|
From: <co...@sq...> - 2006-04-30 00:23:53
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1362] sqlalchemy/branches/schema/doc/build/content: first pass completed</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1362</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-04-29 19:23:42 -0500 (Sat, 29 Apr 2006)</dd> </dl> <h3>Log Message</h3> <pre>first pass completed</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontentdocstringsmyt">sqlalchemy/branches/schema/doc/build/content/docstrings.myt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildcontenttutorialtxt">sqlalchemy/branches/schema/doc/build/content/tutorial.txt</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontentdocstringsmyt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/docstrings.myt (1361 => 1362)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/docstrings.myt 2006-04-29 19:42:15 UTC (rev 1361) +++ sqlalchemy/branches/schema/doc/build/content/docstrings.myt 2006-04-30 00:23:42 UTC (rev 1362) </span><span class="lines">@@ -7,7 +7,7 @@ </span><span class="cx"> import sqlalchemy.engine.strategies as strategies </span><span class="cx"> import sqlalchemy.sql as sql </span><span class="cx"> import sqlalchemy.pool as pool </span><del>- import sqlalchemy.mapping as mapping </del><ins>+ import sqlalchemy.orm as orm </ins><span class="cx"> import sqlalchemy.exceptions as exceptions </span><span class="cx"> import sqlalchemy.ext.proxy as proxy </span><span class="cx"> import sqlalchemy.mods.threadlocal as threadlocal </span><span class="lines">@@ -18,9 +18,9 @@ </span><span class="cx"> <& pydoc.myt:obj_doc, obj=schema &> </span><span class="cx"> <& pydoc.myt:obj_doc, obj=engine, classes=[engine.ComposedSQLEngine, engine.Connection, engine.Transaction, engine.Dialect, engine.ConnectionProvider, engine.ExecutionContext, engine.ResultProxy, engine.RowProxy] &> </span><span class="cx"> <& pydoc.myt:obj_doc, obj=strategies &> </span><del>-<& pydoc.myt:obj_doc, obj=mapping, classes=[mapping.Mapper, mapping.MapperExtension] &> -<& pydoc.myt:obj_doc, obj=mapping.query, classes=[mapping.query.Query] &> -<& pydoc.myt:obj_doc, obj=mapping.objectstore, classes=[mapping.objectstore.Session, mapping.objectstore.SessionTransaction] &> </del><ins>+<& pydoc.myt:obj_doc, obj=orm, classes=[orm.Mapper, orm.MapperExtension] &> +<& pydoc.myt:obj_doc, obj=orm.query, classes=[orm.query.Query] &> +<& pydoc.myt:obj_doc, obj=orm.session, classes=[orm.session.Session, orm.session.SessionTransaction] &> </ins><span class="cx"> <& pydoc.myt:obj_doc, obj=threadlocal &> </span><span class="cx"> <& pydoc.myt:obj_doc, obj=exceptions &> </span><span class="cx"> <& pydoc.myt:obj_doc, obj=pool, classes=[pool.DBProxy, pool.Pool, pool.QueuePool, pool.SingletonThreadPool] &> </span></span></pre></div> <a id="sqlalchemybranchesschemadocbuildcontenttutorialtxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/tutorial.txt (1361 => 1362)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/tutorial.txt 2006-04-29 19:42:15 UTC (rev 1361) +++ sqlalchemy/branches/schema/doc/build/content/tutorial.txt 2006-04-30 00:23:42 UTC (rev 1362) </span><span class="lines">@@ -1,7 +1,8 @@ </span><span class="cx"> Tutorial </span><span class="cx"> ======== </span><del>-This tutorial provides a relatively simple walking tour through the basic concepts of SQLAlchemy. You may wish to skip it and dive into the [main manual][manual] which is more reference-oriented. </del><ins>+This tutorial provides a relatively simple walking tour through the basic concepts of SQLAlchemy. You may wish to skip it and dive into the [main manual][manual] which is more reference-oriented. The examples in this tutorial comprise a fully working interactive Python session, and are guaranteed to be functioning courtesy of [doctest][]. </ins><span class="cx"> </span><ins>+[doctest]: http://www.python.org/doc/lib/module-doctest.html </ins><span class="cx"> [manual]: rel:howtoread </span><span class="cx"> </span><span class="cx"> Installation </span><span class="lines">@@ -98,29 +99,29 @@ </span><span class="cx"> With `metadata` as our established home for tables, lets make a Table for it: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> users = Table('users', metadata, </del><ins>+ >>> users_table = Table('users', metadata, </ins><span class="cx"> ... Column('user_id', Integer, primary_key=True), </span><span class="cx"> ... Column('user_name', String(40)), </span><del>- ... Column('password', String(80)) </del><ins>+ ... Column('password', String(10)) </ins><span class="cx"> ... ) </span><span class="cx"> </span><del>-As you might have guessed, we have just defined a table named `users` which has three columns: `user_id` (which is a primary key column), `user_name` and `password`. Currently it is just an object that doesn't necessarily correspond to an existing table in your database. To actually create the table, we use the `create()` method. To make it interesting, we will have SQLAlchemy echo the SQL statements it sends to the database, by setting the `echo` flag on the `Engine` associated with our `BoundMetaData`: </del><ins>+As you might have guessed, we have just defined a table named `users` which has three columns: `user_id` (which is a primary key column), `user_name` and `password`. Currently it is just an object that doesn't necessarily correspond to an existing table in our database. To actually create the table, we use the `create()` method. To make it interesting, we will have SQLAlchemy echo the SQL statements it sends to the database, by setting the `echo` flag on the `Engine` associated with our `BoundMetaData`: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> >>> metadata.engine.echo = True </span><del>- >>> users.create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE </del><ins>+ >>> users_table.create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE </ins><span class="cx"> CREATE TABLE users( </span><span class="cx"> user_id INTEGER NOT NULL PRIMARY KEY, </span><span class="cx"> user_name VARCHAR(40), </span><del>- password VARCHAR(80) </del><ins>+ password VARCHAR(10) </ins><span class="cx"> ) </span><span class="cx"> ... </span><span class="cx"> </span><span class="cx"> Alternatively, the `users` table might already exist (such as, if you're running examples from this tutorial for the second time), in which case you can just skip the `create()` method call. You can even skip defining the individual columns in the `users` table and ask SQLAlchemy to load its definition from the database: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> users = Table('users', metadata, autoload=True) - >>> list(users.columns)[0].name </del><ins>+ >>> users_table = Table('users', metadata, autoload=True) + >>> list(users_table.columns)[0].name </ins><span class="cx"> 'user_id' </span><span class="cx"> </span><span class="cx"> Documentation on table metadata is available in [metadata](rel:metadata). </span><span class="lines">@@ -130,7 +131,7 @@ </span><span class="cx"> Inserting is achieved via the `insert()` method, which defines a *clause object* (known as a `ClauseElement`) representing an INSERT statement: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> i = users.insert() </del><ins>+ >>> i = users_table.insert() </ins><span class="cx"> >>> i # doctest:+ELLIPSIS </span><span class="cx"> <sqlalchemy.sql.Insert object at 0x...> </span><span class="cx"> >>> print i </span><span class="lines">@@ -161,7 +162,7 @@ </span><span class="cx"> Let's check that the data we have put into `users` table is actually there. The procedure is analogous to the insert example above, except you now call the `select()` method off the `users` table: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> s = users.select() </del><ins>+ >>> s = users_table.select() </ins><span class="cx"> >>> print s </span><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="cx"> FROM users </span><span class="lines">@@ -170,7 +171,7 @@ </span><span class="cx"> FROM users </span><span class="cx"> [] </span><span class="cx"> </span><del>-This time, we won't ignore the return value of `execute()`: </del><ins>+This time, we won't ignore the return value of `execute()`. Its an instance of `ResultProxy`, which is a result-holding object that behaves very similarly to the `cursor` object one deals with directly with a database API: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> >>> r # doctest:+ELLIPSIS </span><span class="lines">@@ -180,10 +181,10 @@ </span><span class="cx"> >>> r.fetchall() </span><span class="cx"> [(2, u'Tom', None), (3, u'Fred', None), (4, u'Harry', None)] </span><span class="cx"> </span><del>-Query criterion for the select can also be specified as regular Python expressions, using the `Column` objects in the `Table` as a base. All expressions constructed from `Column` objects are themselves instances of `ClauseElements`, just like the `Select`, `Insert`, and `Table` objects themselves. </del><ins>+Query criterion for the select is specified using Python expressions, using the `Column` objects in the `Table` as a base. All expressions constructed from `Column` objects are themselves instances of `ClauseElements`, just like the `Select`, `Insert`, and `Table` objects themselves. </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> r = users.select(users.c.user_name=='Harry').execute() </del><ins>+ >>> r = users_table.select(users_table.c.user_name=='Harry').execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="cx"> FROM users </span><span class="cx"> WHERE users.user_name = ? </span><span class="lines">@@ -196,20 +197,20 @@ </span><span class="cx"> </span><span class="cx"> ### Working with Rows </span><span class="cx"> </span><del>-You can see that when we print out the rows returned by an execution result, it prints the rows as tuples. But in fact these rows are special, and can be used either with a list interface or a dictionary interface. The dictionary interface allows the addressing of columns by string column name, or even the original `Column` object: </del><ins>+You can see that when we print out the rows returned by an execution result, it prints the rows as tuples. These rows in fact support both the list and dictionary interfaces. The dictionary interface allows the addressing of columns by string column name, or even the original `Column` object: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> >>> row.keys() </span><span class="cx"> ['user_id', 'user_name', 'password'] </span><del>- >>> row['user_id'], row[1], row[users.c.password] </del><ins>+ >>> row['user_id'], row[1], row[users_table.c.password] </ins><span class="cx"> (4, u'Harry', None) </span><span class="cx"> </span><span class="cx"> Addressing the columns in a row based on the original `Column` object is especially handy, as it eliminates the need to work with literal column names altogether. </span><span class="cx"> </span><del>-Result sets also support the regular Python iterator interface. We'll show this with a slightly different form of `select` that allows you to specify the specific columns to be selected: </del><ins>+Result sets also support iteration. We'll show this with a slightly different form of `select` that allows you to specify the specific columns to be selected: </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> for row in select([users.c.user_id, users.c.user_name]).execute(): # doctest:+NORMALIZE_WHITESPACE </del><ins>+ >>> for row in select([users_table.c.user_id, users_table.c.user_name]).execute(): # doctest:+NORMALIZE_WHITESPACE </ins><span class="cx"> ... print row </span><span class="cx"> SELECT users.user_id, users.user_name </span><span class="cx"> FROM users </span><span class="lines">@@ -224,7 +225,7 @@ </span><span class="cx"> Lets create a second table, `email_addresses`, which references the `users` table. To define the relationship between the two tables, we will use the `ForeignKey` construct. We will also issue the `CREATE` statement for the table in one step: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> email_addresses = Table('email_addresses', metadata, </del><ins>+ >>> email_addresses_table = Table('email_addresses', metadata, </ins><span class="cx"> ... Column('address_id', Integer, primary_key=True), </span><span class="cx"> ... Column('email_address', String(100), nullable=False), </span><span class="cx"> ... Column('user_id', Integer, ForeignKey('users.user_id'))).create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE </span><span class="lines">@@ -240,15 +241,17 @@ </span><span class="cx"> Next, lets put a few rows in: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> email_addresses.insert().execute({'email_address':'to...@to...', 'user_id':2},{'email_address':'ma...@ma...', 'user_id':1}) #doctest:+ELLIPSIS </del><ins>+ >>> email_addresses_table.insert().execute( + ... {'email_address':'to...@to...', 'user_id':2}, + ... {'email_address':'ma...@ma...', 'user_id':1}) #doctest:+ELLIPSIS </ins><span class="cx"> INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?) </span><span class="cx"> [['to...@to...', 2], ['ma...@ma...', 1]] </span><span class="cx"> <sqlalchemy.engine.base.ResultProxy instance at 0x...> </span><span class="cx"> </span><del>-With two related tables, we can now construct a join amongst them, like this: </del><ins>+With two related tables, we can now construct a join amongst them using the `join` method: </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> r = users.join(email_addresses).select().execute() </del><ins>+ >>> r = users_table.join(email_addresses_table).select().execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password, email_addresses.address_id, email_addresses.email_address, email_addresses.user_id </span><span class="cx"> FROM users JOIN email_addresses ON users.user_id = email_addresses.user_id </span><span class="cx"> [] </span><span class="lines">@@ -258,7 +261,10 @@ </span><span class="cx"> The `join` method is also a standalone function in the `sqlalchemy` namespace. The join condition is figured out from the foreign keys of the Table objects given. The condition (also called the "ON clause") can be specified explicitly, such as in this example where we locate all users that used their email address as their password: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> print join(users, email_addresses, and_(users.c.user_id==email_addresses.c.user_id, users.c.password==email_addresses.c.email_address)) </del><ins>+ >>> print join(users_table, email_addresses_table, + ... and_(users_table.c.user_id==email_addresses_table.c.user_id, + ... users_table.c.password==email_addresses_table.c.email_address) + ... ) </ins><span class="cx"> users JOIN email_addresses ON users.user_id = email_addresses.user_id AND users.password = email_addresses.email_address </span><span class="cx"> </span><span class="cx"> Working with Object Mappers {@name=orm} </span><span class="lines">@@ -278,37 +284,25 @@ </span><span class="cx"> The class is a new style class (i.e. it extends `object`) and does not require a constructor (although one may be provided if desired). We just have one `__repr__` method on it which will display basic information about the User. Note that the `__repr__` method references the instance variables `user_name` and `password` which otherwise aren't defined. While we are free to explicitly define these attributes and treat them normally, this is optional; as SQLAlchemy's `Mapper` construct will manage them for us, since their names correspond to the names of columns in the `users` table. Lets create a mapper, and observe that these attributes are now defined: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> usermapper = mapper(User, users) </del><ins>+ >>> usermapper = mapper(User, users_table) </ins><span class="cx"> >>> u1 = User() </span><span class="cx"> >>> print u1.user_name </span><span class="cx"> None </span><span class="cx"> >>> print u1.password </span><span class="cx"> None </span><span class="cx"> </span><del>-When you create a Mapper for a class, that Mapper is now known as the classes' *primary mapper*. SA's ORM can now automatically locate this Mapper when it deals with the class, or instances of that class. </del><ins>+The `mapper` function returns a new instance of `Mapper`. As it is the first Mapper we have created for the `User` class, it is known as the classes' *primary mapper*. We generally don't need to hold onto the `usermapper` instance variable; SA's ORM can automatically locate this Mapper when it deals with the class, or instances of that class. </ins><span class="cx"> </span><del>-### Querying Objects {@name=querying} - -We have assigned the new Mapper we created to the instance variable `usermapper`. Using this object, we can issue queries to load objects from the database. For example, to load all of our existing User objects from the database, we just use the method `select()`, which will return a list containing all the objects. Keep in mind that query echoing is still turned on, so we will also see the SQL queries issued: - - {python} - >>> l = usermapper.select() - SELECT users.user_name AS users_user_name, users.password AS users_password, users.user_id AS users_user_id - FROM users ORDER BY users.oid - [] - >>> l - [(User Mary,password:secure), (User Tom,password:None), (User Fred,password:None), (User Harry,password:None)] - </del><span class="cx"> ### Obtaining a Session {@name=session} </span><span class="cx"> </span><del>-After you create a Mapper, all operations with that Mapper require the usage of an important object called a `Session`. All objects loaded or saved by the Mapper must be *bound* to a `Session` object, which represents a kind of "workspace" of objects that are loaded into memory. A particular object instance can only be bound to one `Session` at a time. </del><ins>+After you create a Mapper, all operations with that Mapper require the usage of an important object called a `Session`. All objects loaded or saved by the Mapper must be *attached* to a `Session` object, which represents a kind of "workspace" of objects that are loaded into memory. A particular object instance can only be attached to one `Session` at a time. </ins><span class="cx"> </span><del>-By default, you have to create a `Session` object explicitly before you can use a `Mapper`, and when loading objects you need to specify the `Session` that will be used to keep track of those objects. But recall that we imported a special *mod* called `threadlocal`, which has made life easier for us by creating a `Session` that is automatically associated with the current thread. Because of that, the `Mapper` was able to use the `Session` that was already associated with the current thread, without us needing to say anything. But now, lets get a handle to that `Session` and deal with it directly. To locate the `Session` corresponding to the current thread, just use `get_session()`: </del><ins>+By default, you have to create a `Session` object explicitly before you can load or save objects. But recall that we imported a special *mod* called `threadlocal`, which has made life easier for us by creating a `Session` that is automatically associated with the current thread. So now, lets get a handle to that `Session` and deal with it directly. To locate the `Session` corresponding to the current thread, just use `current_session()`: </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- >>> session = get_session() </del><ins>+ >>> session = current_session() </ins><span class="cx"> >>> session # doctest:+ELLIPSIS </span><del>- <sqlalchemy.mapping.objectstore.Session object at 0x...> </del><ins>+ <sqlalchemy.orm.session.Session object at 0x...> </ins><span class="cx"> </span><span class="cx"> ### The Query Object {@name=query} </span><span class="cx"> </span><span class="lines">@@ -323,7 +317,7 @@ </span><span class="cx"> ['Harry'] </span><span class="cx"> [(User Harry,password:None)] </span><span class="cx"> </span><del>-All querying for objects is performed via an instance of `Query`. The various `select` methods on an instance of `Mapper` also use an underlying `Query` object to perform the operation. A `Query` can be bound to a specific `Session`, or it can also use `get_session()` to locate the session bound to the current thread, if one is available. </del><ins>+All querying for objects is performed via an instance of `Query`. The various `select` methods on an instance of `Mapper` also use an underlying `Query` object to perform the operation. A `Query` can be bound to a specific `Session`, or it can also use `current_session()` to locate the session bound to the current thread, if one is available. </ins><span class="cx"> </span><span class="cx"> Lets turn off the database echoing for a moment, and try out a few methods on `Query`. Methods that end with the suffix `_by` primarily take keyword arguments which correspond to properties on the object. Other methods take `ClauseElement` objects, which are constructed by using `Column` objects inside of Python expressions, in the same way as we did with our SQL select example in the previous section of this tutorial. Using `ClauseElement` structures to query objects is more verbose but more flexible: </span><span class="cx"> </span><span class="lines">@@ -340,7 +334,7 @@ </span><span class="cx"> >>> print query.count() </span><span class="cx"> 4 </span><span class="cx"> </span><del>-Notice that our `User` class has a special attribute `c` attached to it. This 'c' represents the columns on the User's mapper's Table object. Saying `User.c.user_name` is synonymous with saying `users.c.user_name`, recalling that `User` is the Python class and `users` is our `Table` object. </del><ins>+Notice that our `User` class has a special attribute `c` attached to it. This 'c' represents the columns on the User's mapper's Table object. Saying `User.c.user_name` is synonymous with saying `users_table.c.user_name`, recalling that `User` is the Python class and `users` is our `Table` object. </ins><span class="cx"> </span><span class="cx"> ### Making Changes {@name=changes} </span><span class="cx"> </span><span class="lines">@@ -348,7 +342,7 @@ </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> >>> ed = User() </span><del>- >>> ed.user_name = 'ed' </del><ins>+ >>> ed.user_name = 'Ed' </ins><span class="cx"> >>> ed.password = 'edspassword' </span><span class="cx"> >>> ed in session </span><span class="cx"> True </span><span class="lines">@@ -393,14 +387,110 @@ </span><span class="cx"> INSERT INTO users (user_name, password) VALUES (?, ?) </span><span class="cx"> [None, None] </span><span class="cx"> INSERT INTO users (user_name, password) VALUES (?, ?) </span><del>- ['ed', 'edspassword'] </del><ins>+ ['Ed', 'edspassword'] </ins><span class="cx"> DELETE FROM users WHERE users.user_id = ? </span><span class="cx"> [[3]] </span><span class="cx"> COMMIT </span><span class="cx"> </span><ins>+### Relationships + +When our User object contains relationships to other kinds of information, such as a list of email addresses, we can indicate this by using a function when creating the `Mapper` called `relation()`. While there is a lot you can do with relations, we'll cover a simple one here. First, recall that our `users` table has a foreign key relationship to another table called `email_addresses`. A single row in `email_addresses` has a column `user_id` that references a row in the `users` table; since many rows in the `email_addresses` table can reference a single row in `users`, this is called a *one to many* relationship. + +First, deal with the `email_addresses` table by itself. We will create a new class `Address` which represents a single row in the `email_addresses` table, and a corresponding `Mapper` which will associate the `Address` class with the `email_addresses` table: + + {python} + >>> class Address(object): + ... def __init__(self, email_address): + ... self.email_address = email_address + ... def __repr__(self): + ... return "(Address %s)" % (self.email_address) + >>> mapper(Address, email_addresses_table) # doctest: +ELLIPSIS + <sqlalchemy.orm.mapper.Mapper object at 0x...> </ins><span class="cx"> </span><ins>+Next, we associate the `User` and `Address` classes together by creating a relation using `relation()`, and then adding that relation to the `User` mapper, using the `add_property` function: + + {python} + >>> usermapper.add_property('addresses', relation(Address)) + +The `relation()` function takes either a class or a Mapper as its first argument, and has many options to further control its behavior. The 'User' mapper has now placed additional property on each `User` instance called `addresses`. SQLAlchemy will automatically determine that this relationship is a one-to-many relationship, and will subsequently create `addresses` as a list. When a new `User` is created, this list will begin as empty. + +Lets see what we get for the email addresses already in the database. Since we have made a change to the mapper's configuration, its best that we clear out our `Session`, which is currently holding onto every `User` object we have already loaded: + + {python} + >>> session.clear() + +We can then treat the `addresses` attribute on each `User` object like a regular list: + + {python} + >>> mary = query.get_by(user_name='Mary') # doctest: +NORMALIZE_WHITESPACE + SELECT users.user_name AS users_user_name, users.password AS users_password, users.user_id AS users_user_id + FROM users + WHERE users.user_name = ? ORDER BY users.oid + LIMIT 1 OFFSET 0 + ['Mary'] + >>> print [a for a in mary.addresses] + SELECT email_addresses.user_id AS email_addresses_user_id, email_addresses.address_id AS email_addresses_address_id, email_addresses.email_address AS email_addresses_email_address + FROM email_addresses + WHERE email_addresses.user_id = ? ORDER BY email_addresses.oid + [1] + [(Address ma...@ma...)] + +Adding to the list is just as easy. New `Address` objects will be detected and saved when we `flush` the Session: + + {python} + >>> mary.addresses.append(Address('ma...@gm...')) + >>> session.flush() # doctest: +NORMALIZE_WHITESPACE + BEGIN + INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?) + ['ma...@gm...', 1] + COMMIT + +Main documentation for using mappers: [datamapping](rel:datamapping) + </ins><span class="cx"> ### Transactions </span><span class="cx"> </span><ins>+You may have noticed from the example above that when we say `session.flush()`, SQLAlchemy indicates the names `BEGIN` and `COMMIT` to indicate a transaction with the database. The `flush()` method, since it may execute many statements in a row, will automatically use a transaction in order to execute these instructions. But what if we want to use `flush()` inside of a larger transaction? This is performed via the `SessionTransaction` object, which we can establish using `session.create_transaction()`. Below, we will perform a more complicated `SELECT` statement, make several changes to our collection of users and email addresess, and then create a new user with two email addresses, within the context of a transaction. We will perform a `flush()` in the middle of it to write the changes we have so far, and then allow the remaining changes to be written when we finally `commit()` the transaction. We enclose our operations within a `try/except` block to insu! re that resources are properly freed: + + {python} + >>> transaction = session.create_transaction() + >>> try: # doctest: +NORMALIZE_WHITESPACE + ... (ed, harry, mary) = session.query(User).select( + ... User.c.user_name.in_('Ed', 'Harry', 'Mary'), order_by=User.c.user_name + ... ) + ... del mary.addresses[1] + ... harry.addresses.append(Address('ha...@gm...')) + ... session.flush() + ... print "***flushed the session***" + ... fred = User() + ... fred.user_name = 'fred_again' + ... fred.addresses.append(Address('fr...@fr...')) + ... fred.addresses.append(Address('fre...@fr...')) + ... transaction.commit() + ... except: + ... transaction.rollback() + ... raise + BEGIN + SELECT users.user_name AS users_user_name, users.password AS users_password, users.user_id AS users_user_id + FROM users + WHERE users.user_name IN (?, ?, ?) ORDER BY users.user_name + ['Ed', 'Harry', 'Mary'] + SELECT email_addresses.user_id AS email_addresses_user_id, email_addresses.address_id AS email_addresses_address_id, email_addresses.email_address AS email_addresses_email_address + FROM email_addresses + WHERE email_addresses.user_id = ? ORDER BY email_addresses.oid + [4] + UPDATE email_addresses SET user_id=? WHERE email_addresses.address_id = ? + [None, 3] + INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?) + ['ha...@gm...', 4] + ***flushed the session*** + INSERT INTO users (user_name, password) VALUES (?, ?) + ['fred_again', None] + INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?) + ['fr...@fr...', 7] + INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?) + ['fre...@fr...', 7] + COMMIT + </ins><span class="cx"> Main documentation: [unitofwork](rel:unitofwork), [dbengine_transactions](rel:dbengine_transactions). </span><span class="cx"> </span><span class="cx"> Conclusion </span></span></pre> </div> </div> </body> </html> |
