[Sqlalchemy-commits] [1384] sqlalchemy/branches/schema/doc/build/content: dodc
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1384] sqlalchemy/branches/schema/doc/build/content: dodc
|
From: <co...@sq...> - 2006-05-04 00:52:11
|
<!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>[1384] sqlalchemy/branches/schema/doc/build/content: dodc</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1384</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-05-03 19:51:54 -0500 (Wed, 03 May 2006)</dd> </dl> <h3>Log Message</h3> <pre>dodc</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontentadv_datamappingmyt">sqlalchemy/branches/schema/doc/build/content/adv_datamapping.myt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildcontentdatamappingtxt">sqlalchemy/branches/schema/doc/build/content/datamapping.txt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildcontentmetadatatxt">sqlalchemy/branches/schema/doc/build/content/metadata.txt</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontentadv_datamappingmyt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/adv_datamapping.myt (1383 => 1384)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/adv_datamapping.myt 2006-05-03 19:15:39 UTC (rev 1383) +++ sqlalchemy/branches/schema/doc/build/content/adv_datamapping.myt 2006-05-04 00:51:54 UTC (rev 1384) </span><span class="lines">@@ -39,6 +39,26 @@ </span><span class="cx"> </span><span class="cx"> </&> </span><span class="cx"> </span><ins>+### Overriding Properties {@name=overriding} + +A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute. Currently, the easiest way to do this in SQLAlchemy is how it would be done in any Python program; define your attribute with a different name, such as "_attribute", and use a property to get/set its value. The mapper just needs to be told of the special name: + + {python} + class MyClass(object): + def _set_email(self, email): + self._email = email + def _get_email(self, email): + return self._email + email = property(_get_email, _set_email) + + m = mapper(MyClass, mytable, properties = { + # map the '_email' attribute to the "email" column + # on the table + '_email': mytable.c.email + }) + +In a later release, SQLAlchemy will also allow _get_email and _set_email to be attached directly to the "email" property created by the mapper, and will also allow this association to occur via decorators. + </ins><span class="cx"> <&|doclib.myt:item, name="relations", description="More On Relations" &> </span><span class="cx"> <&|doclib.myt:item, name="customjoin", description="Custom Join Conditions" &> </span><span class="cx"> <p>When creating relations on a mapper, most examples so far have illustrated the mapper and relationship joining up based on the foreign keys of the tables they represent. in fact, this "automatic" inspection can be completely circumvented using the <span class="codeline">primaryjoin</span> and <span class="codeline">secondaryjoin</span> arguments to <span class="codeline">relation</span>, as in this example which creates a User object which has a relationship to all of its Addresses which are in Boston: </span></span></pre></div> <a id="sqlalchemybranchesschemadocbuildcontentdatamappingtxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/datamapping.txt (1383 => 1384)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/datamapping.txt 2006-05-03 19:15:39 UTC (rev 1383) +++ sqlalchemy/branches/schema/doc/build/content/datamapping.txt 2006-05-04 00:51:54 UTC (rev 1384) </span><span class="lines">@@ -7,24 +7,30 @@ </span><span class="cx"> </span><span class="cx"> The Mapper's role is to perform SQL operations upon the database, associating individual table rows with instances of those classes, and individual database columns with properties upon those instances, to transparently associate in-memory objects with a persistent database representation. </span><span class="cx"> </span><del>-When a Mapper is created to associate a Table object with a class, all of the columns defined in the Table object are associated with the class via property accessors, which add overriding functionality to the normal process of setting and getting object attributes. These property accessors keep track of changes to object attributes; these changes will be stored to the database when the application "commits" the current transactional context (known as a *Unit of Work*). The `__init__()` method of the object is also decorated to communicate changes when new instances of the object are created. </del><ins>+When a Mapper is created to associate a Table object with a class, all of the columns defined in the Table object are associated with the class via property accessors, which add overriding functionality to the normal process of setting and getting object attributes. These property accessors keep track of changes to object attributes; these changes will be stored to the database when the application "flushes" the current state of objects (known as a *Unit of Work*). The `__init__()` method of the object is also decorated to communicate changes when new instances of the object are created. </ins><span class="cx"> </span><del>-The Mapper also provides the interface by which instances of the object are loaded from the database. The primary method for this is its `select()` method, which has similar arguments to a `sqlalchemy.sql.Select` object. But this select method executes automatically and returns results, instead of awaiting an execute() call. Instead of returning a cursor-like object, it returns an array of objects. </del><ins>+Two objects provide the primary interface for interacting with Mappers and the "unit of work" in SA 0.2, which are the `Query` object and the `Session` object. `Query` deals with selecting objects from the database, whereas `Session` provides a context for objects to be associated with as well as the ability to communicate changes on those objects back to the database. </ins><span class="cx"> </span><del>-The three elements to be defined, i.e. the Table metadata, the user-defined class, and the Mapper, are typically defined as module-level variables, and may be defined in any fashion suitable to the application, with the only requirement being that the class and table metadata are described before the mapper. For the sake of example, we will be defining these elements close together, but this should not be construed as a requirement; since SQLAlchemy is not a framework, those decisions are left to the developer or an external framework. </del><ins>+The primary method on `Query` for selecting arbitrary objects is its `select()` method, which has similar arguments to a `sqlalchemy.sql.Select` object. But this select method executes automatically and returns results, instead of awaiting an execute() call. Instead of returning a cursor-like object, it returns an array of objects. </ins><span class="cx"> </span><ins>+The three configurational elements to be defined, i.e. the `Table` metadata, the user-defined class, and the `Mapper`, are typically defined as module-level variables, and may be defined in any fashion suitable to the application, with the only requirement being that the class and table metadata are described before the mapper. For the sake of example, we will be defining these elements close together, but this should not be construed as a requirement; since SQLAlchemy is not a framework, those decisions are left to the developer or an external framework. + +Also, keep in mind that the examples in this section deal with explicit `Session` objects mapped directly to `Engine` objects, which represents the most explicit style of using the ORM. Options exist for how this is configured, including binding `Table` objects directly to `Engines` (described in [metadata_tables_binding](rel:metadata_tables_binding)), as well as using the "Threadlocal" plugin to provide a thread-local application-scoped `Session` object (described in [plugins_threadlocal](rel:plugins_threadlocal)). + </ins><span class="cx"> ### Synopsis {@name=synopsis} </span><span class="cx"> </span><span class="cx"> This is the simplest form of a full "round trip" of creating table meta data, creating a class, mapping the class to the table, getting some results, and saving changes. For each concept, the following sections will dig in deeper to the available capabilities. </span><span class="cx"> </span><ins>+First, the metadata/mapper configuration code: + </ins><span class="cx"> {python} </span><span class="cx"> from sqlalchemy import * </span><span class="cx"> </span><del>- # engine - engine = create_engine("sqlite://mydb.db") - </del><ins>+ # metadata + meta = MetaData() + </ins><span class="cx"> # table metadata </span><del>- users = Table('users', engine, </del><ins>+ users_table = Table('users', meta, </ins><span class="cx"> Column('user_id', Integer, primary_key=True), </span><span class="cx"> Column('user_name', String(16)), </span><span class="cx"> Column('password', String(20)) </span><span class="lines">@@ -34,11 +40,20 @@ </span><span class="cx"> class User(object): </span><span class="cx"> pass </span><span class="cx"> </span><del>- # create a mapper - usermapper = mapper(User, users) </del><ins>+ # create a mapper and associate it with the User class. + # technically we dont really need the 'usermapper' variable. + usermapper = mapper(User, users_table) + +Note that no database definitions are required. Next we will define an `Engine` and connect a `Session` to it, and perform a simple select: + + # engine + engine = create_engine("sqlite://mydb.db") </ins><span class="cx"> </span><ins>+ # session + session = create_session(bind_to=engine) + </ins><span class="cx"> # select </span><del>- {sql}user = usermapper.select_by(user_name='fred')[0] </del><ins>+ {sql}user = session.query(User).select_by(user_name='fred')[0] </ins><span class="cx"> SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, </span><span class="cx"> users.password AS users_password </span><span class="cx"> FROM users </span><span class="lines">@@ -48,151 +63,111 @@ </span><span class="cx"> # modify </span><span class="cx"> user.user_name = 'fred jones' </span><span class="cx"> </span><del>- # commit - saves everything that changed - {sql}objectstore.commit() </del><ins>+ # flush - saves everything that changed + {sql}session.flush() </ins><span class="cx"> UPDATE users SET user_name=:user_name </span><span class="cx"> WHERE users.user_id = :user_id </span><span class="cx"> [{'user_name': 'fred jones', 'user_id': 1}] </span><del>- - -#### Attaching Mappers to their Class {@name=attaching} </del><span class="cx"> </span><del>-For convenience's sake, the Mapper can be attached as an attribute on the class itself as well: </del><ins>+### The Query Object {@name=query} </ins><span class="cx"> </span><del>- {python} - User.mapper = mapper(User, users) - - 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, selectfirst, commit, expire, refresh, expunge` and `delete`: </del><ins>+The method `session.query(class_or_mapper)` returns a `Query` object. Below is a synopsis of things you can do with `Query`: </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- # "assign" a mapper to the User class/users table - assign_mapper(User, users) </del><span class="cx"> </span><del>- # methods are attached to the class for selecting - userlist = User.select_by(user_id=12) </del><ins>+ # get a query from a Session based on class: + query = session.query(User) </ins><span class="cx"> </span><del>- myuser = User.get(1) </del><ins>+ # get a query from a Session given a Mapper: + query = session.query(usermapper) </ins><span class="cx"> </span><del>- # mark an object as deleted for the next commit - myuser.delete() - - # commit the changes on a specific object - myotheruser.commit() - -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. - -#### Overriding Properties {@name=overriding} - -A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute. Currently, the easiest way to do this in SQLAlchemy is how it would be done in any Python program; define your attribute with a different name, such as "_attribute", and use a property to get/set its value. The mapper just needs to be told of the special name: - - {python} - class MyClass(object): - def _set_email(self, email): - self._email = email - def _get_email(self, email): - return self._email - email = property(_get_email, _set_email) - - m = mapper(MyClass, mytable, properties = { - # map the '_email' attribute to the "email" column - # on the table - '_email': mytable.c.email - }) - -In a later release, SQLAlchemy will also allow _get_email and _set_email to be attached directly to the "email" property created by the mapper, and will also allow this association to occur via decorators. - -### Selecting from a Mapper {@name=selecting} - -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: - - {python} </del><span class="cx"> # select_by, using property names or column names as keys </span><span class="cx"> # the keys are grouped together by an AND operator </span><del>- result = mapper.select_by(name='john', street='123 green street') </del><ins>+ result = query.select_by(name='john', street='123 green street') </ins><span class="cx"> </span><span class="cx"> # select_by can also combine SQL criterion with key/value properties </span><del>- result = mapper.select_by(users.c.user_name=='john', </del><ins>+ result = query.select_by(users.c.user_name=='john', </ins><span class="cx"> addresses.c.zip_code=='12345, street='123 green street') </span><span class="cx"> </span><span class="cx"> # get_by, which takes the same arguments as select_by </span><span class="cx"> # returns a single scalar result or None if no results </span><del>- user = mapper.get_by(id=12) </del><ins>+ user = query.get_by(id=12) </ins><span class="cx"> </span><span class="cx"> # "dynamic" versions of select_by and get_by - everything past the </span><span class="cx"> # "select_by_" or "get_by_" is used as the key, and the function argument </span><span class="cx"> # as the value </span><del>- result = mapper.select_by_name('fred') - u = mapper.get_by_name('fred') </del><ins>+ result = query.select_by_name('fred') + u = query.get_by_name('fred') </ins><span class="cx"> </span><span class="cx"> # get an object directly from its primary key. this will bypass the SQL </span><span class="cx"> # call if the object has already been loaded </span><del>- u = mapper.get(15) </del><ins>+ u = query.get(15) </ins><span class="cx"> </span><span class="cx"> # get an object that has a composite primary key of three columns. </span><span class="cx"> # the order of the arguments matches that of the table meta data. </span><del>- myobj = mapper.get(27, 3, 'receipts') </del><ins>+ myobj = query.get(27, 3, 'receipts') </ins><span class="cx"> </span><span class="cx"> # using a WHERE criterion </span><del>- result = mapper.select(or_(users.c.user_name == 'john', users.c.user_name=='fred')) </del><ins>+ result = query.select(or_(users.c.user_name == 'john', users.c.user_name=='fred')) </ins><span class="cx"> </span><span class="cx"> # using a WHERE criterion to get a scalar </span><del>- u = mapper.selectfirst(users.c.user_name=='john') - </del><ins>+ u = query.selectfirst(users.c.user_name=='john') + </ins><span class="cx"> # selectone() is a stricter version of selectfirst() which </span><span class="cx"> # will raise an exception if there is not exactly one row </span><del>- u = mapper.selectone(users.c.user_name=='john') </del><ins>+ u = query.selectone(users.c.user_name=='john') </ins><span class="cx"> </span><span class="cx"> # using a full select object </span><del>- result = mapper.select(users.select(users.c.user_name=='john')) </del><ins>+ result = query.select(users.select(users.c.user_name=='john')) </ins><span class="cx"> </span><span class="cx"> # using straight text </span><del>- result = mapper.select_text("select * from users where user_name='fred'") - </del><ins>+ result = query.select_text("select * from users where user_name='fred'") + </ins><span class="cx"> # or using a "text" object </span><del>- result = mapper.select(text("select * from users where user_name='fred'", engine=engine)) </del><ins>+ result = query.select(text("select * from users where user_name='fred'", engine=engine)) </ins><span class="cx"> </span><span class="cx"> Some of the above examples above illustrate 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: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- User.mapper = mapper(User, users) - - userlist = User.mapper.select(User.c.user_id==12) - </del><ins>+ userlist = session.query(User).select(User.c.user_id==12) </ins><span class="cx"> </span><span class="cx"> ### Saving Objects {@name=saving} </span><span class="cx"> </span><del>-When objects corresponding to mapped classes are created or manipulated, all changes are logged by a package called `sqlalchemy.mapping.objectstore`. The changes are then written to the database when an application calls `objectstore.commit()`. This pattern is known as a *Unit of Work*, and has many advantages over saving individual objects or attributes on those objects with individual method invocations. Domain models can be built with far greater complexity with no concern over the order of saves and deletes, excessive database round-trips and write operations, or deadlocking issues. The commit() operation uses a transaction as well, and will also perform "concurrency checking" to insure the proper number of rows were in fact affected (not supported with the current MySQL drivers). Transactional resources are used effectively in all cases; the unit of work handles all the details. </del><ins>+When objects corresponding to mapped classes are created or manipulated, all changes are logged by the `Session` object. The changes are then written to the database when an application calls `flush()`. This pattern is known as a *Unit of Work*, and has many advantages over saving individual objects or attributes on those objects with individual method invocations. Domain models can be built with far greater complexity with no concern over the order of saves and deletes, excessive database round-trips and write operations, or deadlocking issues. The `flush()` operation batches its SQL statements into a transaction, and can also perform optimistic concurrency checks (using a version id column) to insure the proper number of rows were in fact affected (not supported with the current MySQL drivers). </ins><span class="cx"> </span><span class="cx"> The Unit of Work is a powerful tool, and has some important concepts that must be understood in order to use it effectively. While this section illustrates rudimentary Unit of Work usage, it is strongly encouraged to consult the [unitofwork](rel:unitofwork) section for a full description on all its operations, including session control, deletion, and developmental guidelines. </span><span class="cx"> </span><del>-When a mapper is created, the target class has its mapped properties decorated by specialized property accessors that track changes, and its `__init__()` method is also decorated to mark new objects as "new". </del><ins>+When a mapper is created, the target class has its mapped properties decorated by specialized property accessors that track changes. New objects by default must be explicitly added to the `Session`, however this can be made more automatic by using [plugins_threadlocal](rel:plugins_threadlocal) or [plugins_sessioncontext](rel:plugins_sessioncontext). </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- User.mapper = mapper(User, users) - </del><ins>+ mapper(User, users_table) + </ins><span class="cx"> # create a new User </span><span class="cx"> myuser = User() </span><span class="cx"> myuser.user_name = 'jane' </span><span class="cx"> myuser.password = 'hello123' </span><del>- </del><ins>+ </ins><span class="cx"> # create another new User </span><span class="cx"> myuser2 = User() </span><span class="cx"> myuser2.user_name = 'ed' </span><span class="cx"> myuser2.password = 'lalalala' </span><del>- </del><ins>+ + # create a Session and save them + sess = create_session() + sess.save(myuser) + sess.save(myuser2) + </ins><span class="cx"> # load a third User from the database </span><del>- {sql}myuser3 = User.mapper.select(User.c.user_name=='fred')[0] </del><ins>+ {sql}myuser3 = sess.query(User).select(User.c.user_name=='fred')[0] </ins><span class="cx"> SELECT users.user_id AS users_user_id, </span><span class="cx"> users.user_name AS users_user_name, users.password AS users_password </span><span class="cx"> FROM users WHERE users.user_name = :users_user_name </span><span class="cx"> {'users_user_name': 'fred'} </span><del>- </del><ins>+ </ins><span class="cx"> myuser3.user_name = 'fredjones' </span><del>- </del><ins>+ </ins><span class="cx"> # save all changes </span><del>- {sql}objectstore.commit() </del><ins>+ {sql}session.flush() </ins><span class="cx"> UPDATE users SET user_name=:user_name </span><span class="cx"> WHERE users.user_id =:users_user_id </span><span class="cx"> [{'users_user_id': 1, 'user_name': 'fredjones'}] </span><span class="lines">@@ -201,11 +176,11 @@ </span><span class="cx"> INSERT INTO users (user_name, password) VALUES (:user_name, :password) </span><span class="cx"> {'password': 'lalalala', 'user_name': 'ed'} </span><span class="cx"> </span><del>-In the examples above, we defined a User class with basically no properties or methods. Theres no particular reason it has to be this way, the class can explicitly set up whatever properties it wants, whether or not they will be managed by the mapper. It can also specify a constructor, with the restriction that the constructor is able to function with no arguments being passed to it (this restriction can be lifted with some extra parameters to the mapper; more on that later): </del><ins>+In the examples above, we defined a User class with basically no properties or methods. Theres no particular reason it has to be this way, the class can explicitly set up whatever properties it wants, whether or not they will be managed by the mapper. In SA 0.2 the class can specify any constructor it wants. </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> class User(object): </span><del>- def __init__(self, user_name = None, password = None): </del><ins>+ def __init__(self, user_name, password): </ins><span class="cx"> self.user_id = None </span><span class="cx"> self.user_name = user_name </span><span class="cx"> self.password = password </span><span class="lines">@@ -214,17 +189,19 @@ </span><span class="cx"> def __repr__(self): </span><span class="cx"> return "User id %s name %s password %s" % (repr(self.user_id), </span><span class="cx"> repr(self.user_name), repr(self.password)) </span><del>- User.mapper = mapper(User, users) </del><ins>+ mapper(User, users_table) </ins><span class="cx"> </span><ins>+ sess = create_session() </ins><span class="cx"> u = User('john', 'foo') </span><del>- {sql}objectstore.commit() </del><ins>+ sess.save(u) + {sql}session.flush() </ins><span class="cx"> INSERT INTO users (user_name, password) VALUES (:user_name, :password) </span><span class="cx"> {'password': 'foo', 'user_name': 'john'} </span><span class="cx"> </span><span class="cx"> >>> u </span><span class="cx"> User id 1 name 'john' password 'foo' </span><span class="cx"> </span><del>-Recent versions of SQLAlchemy will only put modified object attributes columns into the UPDATE statements generated upon commit. 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. </del><ins>+SQLAlchemy will only put modified object attributes columns into the UPDATE statements generated upon flush. 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. </ins><span class="cx"> </span><span class="cx"> ### Defining and Using Relationships {@name=relations} </span><span class="cx"> </span><span class="lines">@@ -232,17 +209,18 @@ </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> from sqlalchemy import * </span><del>- engine = create_engine('sqlite://filename=mydb') </del><ins>+ + metadata = MetaData() </ins><span class="cx"> </span><span class="cx"> # define user table </span><del>- users = Table('users', engine, </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(16)), </span><span class="cx"> Column('password', String(20)) </span><span class="cx"> ) </span><span class="cx"> </span><span class="cx"> # define user address table </span><del>- addresses = Table('addresses', engine, </del><ins>+ addresses_table = Table('addresses', metadata, </ins><span class="cx"> Column('address_id', Integer, primary_key=True), </span><span class="cx"> Column('user_id', Integer, ForeignKey("users.user_id")), </span><span class="cx"> Column('street', String(100)), </span><span class="lines">@@ -251,18 +229,18 @@ </span><span class="cx"> Column('zip', String(10)) </span><span class="cx"> ) </span><span class="cx"> </span><del>-Of importance here is the addresses table's definition of a *foreign key* relationship to the users table, relating the user_id column into a parent-child relationship. When a Mapper wants to indicate a relation of one object to another, this ForeignKey object is the default method by which the relationship is determined (although if you didn't define ForeignKeys, or you want to specify explicit relationship columns, that is available as well). </del><ins>+Of importance here is the addresses table's definition of a *foreign key* relationship to the users table, relating the user_id column into a parent-child relationship. When a `Mapper` wants to indicate a relation of one object to another, this `ForeignKey` object is the default method by which the relationship is determined (although if you didn't define ForeignKeys, or you want to specify explicit relationship columns, that is available as well). </ins><span class="cx"> </span><del>-So then lets define two classes, the familiar User class, as well as an Address class: </del><ins>+So then lets define two classes, the familiar `User` class, as well as an `Address` class: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> class User(object): </span><del>- def __init__(self, user_name = None, password = None): </del><ins>+ def __init__(self, user_name, password): </ins><span class="cx"> self.user_name = user_name </span><span class="cx"> self.password = password </span><span class="cx"> </span><span class="cx"> class Address(object): </span><del>- def __init__(self, street=None, city=None, state=None, zip=None): </del><ins>+ def __init__(self, street, city, state, zip): </ins><span class="cx"> self.street = street </span><span class="cx"> self.city = city </span><span class="cx"> self.state = state </span><span class="lines">@@ -271,19 +249,24 @@ </span><span class="cx"> And then a Mapper that will define a relationship of the User and the Address classes to each other as well as their table metadata. We will add an additional mapper keyword argument `properties` which is a dictionary relating the name of an object property to a database relationship, in this case a `relation` object against a newly defined mapper for the Address class: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- User.mapper = mapper(User, users, properties = { - 'addresses' : relation(mapper(Address, addresses)) - } - ) </del><ins>+ mapper(Address, addresses_table) + mapper(User, users_table, properties = { + 'addresses' : relation(Address) + } + ) </ins><span class="cx"> </span><span class="cx"> Lets do some operations with these classes and see what happens: </span><span class="cx"> </span><span class="cx"> {python} </span><ins>+ engine = create_engine('sqlite://filename=mydb') + sess = create_session(bind_to=engine) + </ins><span class="cx"> u = User('jane', 'hihilala') </span><span class="cx"> u.addresses.append(Address('123 anywhere street', 'big city', 'UT', '76543')) </span><span class="cx"> u.addresses.append(Address('1 Park Place', 'some other city', 'OK', '83923')) </span><span class="cx"> </span><del>- objectstore.commit() </del><ins>+ sess.save(u) + session.flush() </ins><span class="cx"> {opensql}INSERT INTO users (user_name, password) VALUES (:user_name, :password) </span><span class="cx"> {'password': 'hihilala', 'user_name': 'jane'} </span><span class="cx"> INSERT INTO addresses (user_id, street, city, state, zip) VALUES (:user_id, :street, :city, :state, :zip) </span><span class="lines">@@ -291,7 +274,7 @@ </span><span class="cx"> INSERT INTO addresses (user_id, street, city, state, zip) VALUES (:user_id, :street, :city, :state, :zip) </span><span class="cx"> {'city': 'some other city', 'state': 'OK', 'street': '1 Park Place', 'user_id':1, 'zip': '83923'} </span><span class="cx"> </span><del>-A lot just happened there! The Mapper object figured out how to relate rows in the addresses table to the users table, and also upon commit had to determine the proper order in which to insert rows. After the insert, all the User and Address objects have all their new primary and foreign keys populated. </del><ins>+A lot just happened there! The Mapper object figured out how to relate rows in the addresses table to the users table, and also upon flush had to determine the proper order in which to insert rows. After the insert, all the User and Address objects have all their new primary and foreign keys populated. </ins><span class="cx"> </span><span class="cx"> Also notice that when we created a Mapper on the User class which defined an 'addresses' relation, the newly created User instance magically had an "addresses" attribute which behaved like a list. This list is in reality a property accessor function, which returns an instance of `sqlalchemy.util.HistoryArraySet`, which fulfills the full set of Python list accessors, but maintains a *unique* set of objects (based on their in-memory identity), and also tracks additions and deletions to the list: </span><span class="cx"> </span><span class="lines">@@ -299,7 +282,7 @@ </span><span class="cx"> del u.addresses[1] </span><span class="cx"> u.addresses.append(Address('27 New Place', 'Houston', 'TX', '34839')) </span><span class="cx"> </span><del>- objectstore.commit() </del><ins>+ session.flush() </ins><span class="cx"> </span><span class="cx"> {opensql}UPDATE addresses SET user_id=:user_id </span><span class="cx"> WHERE addresses.address_id = :addresses_address_id </span><span class="lines">@@ -307,36 +290,42 @@ </span><span class="cx"> INSERT INTO addresses (user_id, street, city, state, zip) </span><span class="cx"> VALUES (:user_id, :street, :city, :state, :zip) </span><span class="cx"> {'city': 'Houston', 'state': 'TX', 'street': '27 New Place', 'user_id': 1, 'zip': '34839'} </span><del>- </del><ins>+ +Note that when creating a relation with the `relation()` function, the target can either be a class, in which case the primary mapper for that class is used as the target, or a `Mapper` instance itself, as returned by the `mapper()` function. + </ins><span class="cx"> #### Useful Feature: Private Relations {@name=private} </span><span class="cx"> </span><span class="cx"> So our one address that was removed from the list, was updated to have a user_id of `None`, and a new address object was inserted to correspond to the new Address added to the User. But now, theres a mailing address with no user_id floating around in the database of no use to anyone. How can we avoid this ? This is acheived by using the `private=True` parameter of `relation`: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- User.mapper = mapper(User, users, properties = { - 'addresses' : relation(mapper(Address, addresses), private=True) - } - ) </del><ins>+ mapper(Address, addresses_table) + mapper(User, users_table, properties = { + 'addresses' : relation(Address, private=True) + } + ) + </ins><span class="cx"> del u.addresses[1] </span><span class="cx"> u.addresses.append(Address('27 New Place', 'Houston', 'TX', '34839')) </span><span class="cx"> </span><del>- objectstore.commit() </del><ins>+ session.flush() </ins><span class="cx"> {opensql}INSERT INTO addresses (user_id, street, city, state, zip) </span><span class="cx"> VALUES (:user_id, :street, :city, :state, :zip) </span><span class="cx"> {'city': 'Houston', 'state': 'TX', 'street': '27 New Place', 'user_id': 1, 'zip': '34839'} </span><span class="cx"> DELETE FROM addresses WHERE addresses.address_id = :address_id </span><span class="cx"> [{'address_id': 2}] </span><span class="cx"> </span><del>-In this case, with the private flag set, the element that was removed from the addresses list was also removed from the database. By specifying the `private` flag on a relation, it is indicated to the Mapper that these related objects exist only as children of the parent object, otherwise should be deleted. </del><ins>+In this case, with the private flag set, the element that was removed from the addresses list was also removed from the database. The "private" flag indicates that the Address object is a *lifecycle object* of User. </ins><span class="cx"> </span><ins>+`private` is also a synonym for a more configurable set of rules called **cascade rules**. Cascading is described in [session_cascade](rel:session_cascade). + </ins><span class="cx"> #### Useful Feature: Backreferences {@name=backreferences} </span><span class="cx"> </span><span class="cx"> By creating relations with the `backref` keyword, a bi-directional relationship can be created which will keep both ends of the relationship updated automatically, even without any database queries being executed. Below, the User mapper is created with an "addresses" property, and the corresponding Address mapper receives a "backreference" to the User object via the property name "user": </span><span class="cx"> </span><span class="cx"> {python} </span><del>- Address.mapper = mapper(Address, addresses) - User.mapper = mapper(User, users, properties = { - 'addresses' : relation(Address.mapper, backref='user') </del><ins>+ Address = mapper(Address, addresses_table) + User = mapper(User, users_table, properties = { + 'addresses' : relation(Address, backref='user') </ins><span class="cx"> } </span><span class="cx"> ) </span><span class="cx"> </span><span class="lines">@@ -359,48 +348,28 @@ </span><span class="cx"> The backreference feature also works with many-to-many relationships, which are described later. When creating a backreference, a corresponding property is placed on the child mapper. The default arguments to this property can be overridden using the `backref()` function: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- Address.mapper = mapper(Address, addresses) </del><ins>+ mapper(Address, addresseses) </ins><span class="cx"> </span><del>- User.mapper = mapper(User, users, properties = { - 'addresses' : relation(Address.mapper, - backref=backref('user', lazy=False, private=True)) - } - ) </del><ins>+ mapper(User, users, properties = { + 'addresses' : relation(Address, + backref=backref('user', lazy=False, private=True) + ) + } + ) </ins><span class="cx"> </span><del>-#### Creating Relationships Automatically with cascade_mappers {@name=cascade} - -The mapper package has a helper function `cascade_mappers()` which can simplify the task of linking several mappers together. Given a list of classes and/or mappers, it identifies the foreign key relationships between the given mappers or corresponding class mappers, and creates relation() objects representing those relationships, including a backreference. Attempts to find the "secondary" table in a many-to-many relationship as well. The names of the relations are a lowercase version of the related class. In the case of one-to-many or many-to-many, the name is "pluralized", which currently is based on the English language (i.e. an 's' or 'es' added to it): - - {python} - # create two mappers. the 'users' and 'addresses' tables have a foreign key - # relationship - mapper1 = mapper(User, users) - mapper2 = mapper(Address, addresses) - - # cascade the two mappers together (can also specify User, Address as the arguments) - cascade_mappers(mapper1, mapper2) - - # two new object instances - u = User('user1') - a = Address('test') - - # "addresses" and "user" property are automatically added - u.addresses.append(a) - print a.user - </del><span class="cx"> #### Selecting from Relationships: Lazy Load {@name=lazyload} </span><span class="cx"> </span><span class="cx"> We've seen how the `relation` specifier affects the saving of an object and its child items, how does it affect selecting them? By default, the relation keyword indicates that the related property should be attached a *Lazy Loader* when instances of the parent object are loaded from the database; this is just a callable function that when accessed will invoke a second SQL query to load the child objects of the parent. </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> # define a mapper </span><del>- User.mapper = mapper(User, users, properties = { - 'addresses' : relation(mapper(Address, addresses), private=True) - }) </del><ins>+ mapper(User, users_table, properties = { + 'addresses' : relation(mapper(Address, addresses_table), private=True) + }) </ins><span class="cx"> </span><span class="cx"> # select users where username is 'jane', get the first element of the list </span><span class="cx"> # this will incur a load operation for the parent table </span><del>- {sql}user = User.mapper.select(user_name='jane')[0] </del><ins>+ {sql}user = session.query(User).select(user_name='jane')[0] </ins><span class="cx"> SELECT users.user_id AS users_user_id, </span><span class="cx"> users.user_name AS users_user_name, users.password AS users_password </span><span class="cx"> FROM users WHERE users.user_name = :users_user_name ORDER BY users.oid </span><span class="lines">@@ -419,10 +388,10 @@ </span><span class="cx"> </span><span class="cx"> ##### Useful Feature: Creating Joins via select_by {@name=relselectby} </span><span class="cx"> </span><del>-In mappers that have relationships, the `select_by` method and its cousins include special functionality that can be used to create joins. Just specify a key in the argument list which is not present in the primary mapper's list of properties or columns, but *is* present in the property list of one of its relationships: </del><ins>+For mappers that have relationships, the `select_by` method of the Query object and its cousins include special functionality that can be used to create joins. Just specify a key in the argument list which is not present in the primary mapper's list of properties or columns, but *is* present in the property list of one of its relationships: </ins><span class="cx"> </span><span class="cx"> {python} </span><del>- {sql}l = User.mapper.select_by(street='123 Green Street') </del><ins>+ {sql}l = session.query(User).select_by(street='123 Green Street') </ins><span class="cx"> SELECT users.user_id AS users_user_id, </span><span class="cx"> users.user_name AS users_user_name, users.password AS users_password </span><span class="cx"> FROM users, addresses </span><span class="lines">@@ -434,41 +403,23 @@ </span><span class="cx"> The above example is shorthand for: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- l = User.mapper.select(and_( </del><ins>+ l = session.query(User).select(and_( </ins><span class="cx"> Address.c.user_id==User.c.user_id, </span><span class="cx"> Address.c.street=='123 Green Street') </span><span class="cx"> ) </span><del>- -##### How to Refresh the List? {@name=refreshing} </del><span class="cx"> </span><del>-Once the child list of Address objects is loaded, it is done loading for the lifetime of the object instance. Changes to the list will not be interfered with by subsequent loads, and upon commit those changes will be saved. Similarly, if a new User object is created and child Address objects added, a subsequent select operation which happens to touch upon that User instance, will also not affect the child list, since it is already loaded. - -The issue of when the mapper actually gets brand new objects from the database versus when it assumes the in-memory version is fine the way it is, is a subject of *transactional scope*. Described in more detail in the Unit of Work section, for now it should be noted that the total storage of all newly created and selected objects, *within the scope of the current thread*, can be reset via releasing or otherwise disregarding all current object instances, and calling: - - {python} - objectstore.clear() - -This operation will clear out all currently mapped object instances, and subsequent select statements will load fresh copies from the databse. - -To operate upon a single object, just use the `remove` function: - - {python} - # (this function coming soon) - objectstore.remove(myobject) - - </del><span class="cx"> #### Selecting from Relationships: Eager Load {@name=eagerload} </span><span class="cx"> </span><span class="cx"> With just a single parameter "lazy=False" specified to the relation object, the parent and child SQL queries can be joined together. </span><span class="cx"> </span><span class="cx"> {python} </span><del>- Address.mapper = mapper(Address, addresses) - User.mapper = mapper(User, users, properties = { - 'addresses' : relation(Address.mapper, lazy=False) - } - ) </del><ins>+ mapper(Address, addresses_table) + mapper(User, users_table, properties = { + 'addresses' : relation(Address, lazy=False) + } + ) </ins><span class="cx"> </span><del>- {sql}user = User.mapper.get_by(user_name='jane') </del><ins>+ {sql}user = session.query(User).get_by(user_name='jane') </ins><span class="cx"> SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, </span><span class="cx"> users.password AS users_password, </span><span class="cx"> addresses.address_id AS addresses_address_id, addresses.user_id AS addresses_user_id, </span><span class="lines">@@ -487,7 +438,7 @@ </span><span class="cx"> The generation of this query is also immune to the effects of additional joins being specified in the original query. To use our select_by example above, joining against the "addresses" table to locate users with a certain street results in this behavior: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- {sql}users = User.mapper.select_by(street='123 Green Street') </del><ins>+ {sql}users = session.query(User).select_by(street='123 Green Street') </ins><span class="cx"> SELECT users.user_id AS users_user_id, </span><span class="cx"> users.user_name AS users_user_name, users.password AS users_password, </span><span class="cx"> addresses.address_id AS addresses_address_id, </span></span></pre></div> <a id="sqlalchemybranchesschemadocbuildcontentmetadatatxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/metadata.txt (1383 => 1384)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/metadata.txt 2006-05-03 19:15:39 UTC (rev 1383) +++ sqlalchemy/branches/schema/doc/build/content/metadata.txt 2006-05-04 00:51:54 UTC (rev 1384) </span><span class="lines">@@ -93,7 +93,7 @@ </span><span class="cx"> # get the table related by a foreign key </span><span class="cx"> fcolumn = employees.c.employee_dept.foreign_key.column.table </span><span class="cx"> </span><del>-#### Binding MetaData to an Engine </del><ins>+#### Binding MetaData to an Engine {@name=binding} </ins><span class="cx"> </span><span class="cx"> A MetaData object can be associated with one or more Engine instances. This allows the MetaData and the elements within it to perform operations automatically, using the connection resources of that Engine. This includes being able to "reflect" the columns of tables, as well as to perform create and drop operations without needing to pass an `Engine` or `Connection` around. It also allows SQL constructs to be created which know how to execute themselves (called "implicit execution"). </span><span class="cx"> </span></span></pre> </div> </div> </body> </html> |
