[Sqlalchemy-commits] [1386] sqlalchemy/branches/schema/lib/sqlalchemy: docs...
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1386] sqlalchemy/branches/schema/lib/sqlalchemy: docs...
|
From: <co...@sq...> - 2006-05-04 04:06: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>[1386] sqlalchemy/branches/schema/lib/sqlalchemy: docs...</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1386</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-05-03 23:06:42 -0500 (Wed, 03 May 2006)</dd> </dl> <h3>Log Message</h3> <pre>docs...</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontentdatamappingtxt">sqlalchemy/branches/schema/doc/build/content/datamapping.txt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildcontentdbenginetxt">sqlalchemy/branches/schema/doc/build/content/dbengine.txt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildcontentdocument_basemyt">sqlalchemy/branches/schema/doc/build/content/document_base.myt</a></li> <li><a href="#sqlalchemybranchesschemalibsqlalchemyenginestrategiespy">sqlalchemy/branches/schema/lib/sqlalchemy/engine/strategies.py</a></li> <li><a href="#sqlalchemybranchesschemalibsqlalchemyschemapy">sqlalchemy/branches/schema/lib/sqlalchemy/schema.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontentdatamappingtxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/datamapping.txt (1385 => 1386)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/datamapping.txt 2006-05-04 00:52:12 UTC (rev 1385) +++ sqlalchemy/branches/schema/doc/build/content/datamapping.txt 2006-05-04 04:06:42 UTC (rev 1386) </span><span class="lines">@@ -5,22 +5,20 @@ </span><span class="cx"> </span><span class="cx"> Data mapping describes the process of defining *Mapper* objects, which associate table metadata with user-defined classes. </span><span class="cx"> </span><del>-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. </del><ins>+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. </ins><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 "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. </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*). </ins><span class="cx"> </span><del>-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. </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 loaded objects and the ability to communicate changes on those objects back to the database. </ins><span class="cx"> </span><del>-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. </del><ins>+The primary method on `Query` for loading 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><span class="cx"> 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. </span><span class="cx"> </span><del>-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)). </del><ins>+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 which provides various code shortcuts by using an implicit Session associated to the current thread (described in [plugins_threadlocal](rel:plugins_threadlocal)). </ins><span class="cx"> </span><span class="cx"> ### Synopsis {@name=synopsis} </span><span class="cx"> </span><del>-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. - </del><span class="cx"> First, the metadata/mapper configuration code: </span><span class="cx"> </span><span class="cx"> {python} </span><span class="lines">@@ -29,7 +27,7 @@ </span><span class="cx"> # metadata </span><span class="cx"> meta = MetaData() </span><span class="cx"> </span><del>- # table metadata </del><ins>+ # table object </ins><span class="cx"> users_table = Table('users', meta, </span><span class="cx"> Column('user_id', Integer, primary_key=True), </span><span class="cx"> Column('user_name', String(16)), </span><span class="lines">@@ -46,6 +44,7 @@ </span><span class="cx"> </span><span class="cx"> Note that no database definitions are required. Next we will define an `Engine` and connect a `Session` to it, and perform a simple select: </span><span class="cx"> </span><ins>+ {python} </ins><span class="cx"> # engine </span><span class="cx"> engine = create_engine("sqlite://mydb.db") </span><span class="cx"> </span><span class="lines">@@ -74,7 +73,6 @@ </span><span class="cx"> The method `session.query(class_or_mapper)` returns a `Query` object. Below is a synopsis of things you can do with `Query`: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- </del><span class="cx"> # get a query from a Session based on class: </span><span class="cx"> query = session.query(User) </span><span class="cx"> </span><span class="lines">@@ -87,7 +85,7 @@ </span><span class="cx"> </span><span class="cx"> # select_by can also combine SQL criterion with key/value properties </span><span class="cx"> result = query.select_by(users.c.user_name=='john', </span><del>- addresses.c.zip_code=='12345, street='123 green street') </del><ins>+ addresses.c.zip_code=='12345', street=='123 green street') </ins><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><span class="lines">@@ -105,7 +103,7 @@ </span><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 = query.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><span class="cx"> result = query.select(or_(users.c.user_name == 'john', users.c.user_name=='fred')) </span><span class="lines">@@ -119,13 +117,7 @@ </span><span class="cx"> </span><span class="cx"> # using a full select object </span><span class="cx"> result = query.select(users.select(users.c.user_name=='john')) </span><del>- - # using straight text - result = query.select_text("select * from users where user_name='fred'") - - # or using a "text" object - result = query.select(text("select * from users where user_name='fred'", engine=engine)) - </del><ins>+ </ins><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><span class="lines">@@ -135,9 +127,9 @@ </span><span class="cx"> </span><span class="cx"> 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). </span><span class="cx"> </span><del>-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. </del><ins>+The Unit of Work is a powerful tool, and has some important concepts that should be understood in order to use it effectively. See the [unitofwork](rel:unitofwork) section for a full description on all its operations. </ins><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. 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). </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 automatic by using [plugins_threadlocal](rel:plugins_threadlocal) or [plugins_sessioncontext](rel:plugins_sessioncontext). </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> mapper(User, users_table) </span><span class="lines">@@ -176,7 +168,7 @@ </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. In SA 0.2 the class can specify any constructor it wants. </del><ins>+The mapped class can also specify whatever methods and/or constructor it wants: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> class User(object): </span><span class="lines">@@ -229,7 +221,7 @@ </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, th `ForeignKey` relationships are the default method by which the relationship is determined (options also exist to describe the relationships explicitly). </ins><span class="cx"> </span><span class="cx"> So then lets define two classes, the familiar `User` class, as well as an `Address` class: </span><span class="cx"> </span><span class="lines">@@ -259,13 +251,15 @@ </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> engine = create_engine('sqlite://filename=mydb') </span><del>- sess = create_session(bind_to=engine) </del><ins>+ metadata.create_all(engine) </ins><span class="cx"> </span><ins>+ session = 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>- sess.save(u) </del><ins>+ session.save(u) </ins><span class="cx"> session.flush() </span><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="lines">@@ -298,6 +292,7 @@ </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><ins>+ clear_mappers() # clear mappers from the previous example </ins><span class="cx"> mapper(Address, addresses_table) </span><span class="cx"> mapper(User, users_table, properties = { </span><span class="cx"> 'addresses' : relation(Address, private=True) </span><span class="lines">@@ -419,19 +414,20 @@ </span><span class="cx"> } </span><span class="cx"> ) </span><span class="cx"> </span><del>- {sql}user = session.query(User).get_by(user_name='jane') - SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, - users.password AS users_password, - addresses.address_id AS addresses_address_id, addresses.user_id AS addresses_user_id, - addresses.street AS addresses_street, addresses.city AS addresses_city, - addresses.state AS addresses_state, addresses.zip AS addresses_zip - FROM users LEFT OUTER JOIN addresses ON users.user_id = addresses.user_id - WHERE users.user_name = :users_user_name ORDER BY users.oid, addresses.oid </del><ins>+ {sql}users = session.query(User).select(User.c.user_name=='Jane') + SELECT users.user_name AS users_user_name, users.password AS users_password, + users.user_id AS users_user_id, addresses_4fb8.city AS addresses_4fb8_city, + addresses_4fb8.address_id AS addresses_4fb8_address_id, addresses_4fb8.user_id AS addresses_4fb8_user_id, + addresses_4fb8.zip AS addresses_4fb8_zip, addresses_4fb8.state AS addresses_4fb8_state, + addresses_4fb8.street AS addresses_4fb8_street + FROM users LEFT OUTER JOIN addresses AS addresses_4fb8 ON users.user_id = addresses_4fb8.user_id + WHERE users.user_name = :users_user_name ORDER BY users.oid, addresses_4fb8.oid </ins><span class="cx"> {'users_user_name': 'jane'} </span><span class="cx"> </span><del>- for a in user.addresses: - print repr(a) - </del><ins>+ for u in users: + print repr(u) + for a in u.addresses: + print repr(a) </ins><span class="cx"> </span><span class="cx"> Above, a pretty ambitious query is generated just by specifying that the User should be loaded with its child Addresses in one query. When the mapper processes the results, it uses an *Identity Map* to keep track of objects that were already loaded, based on their primary key identity. Through this method, the redundant rows produced by the join are organized into the distinct object instances they represent. </span><span class="cx"> </span><span class="lines">@@ -439,154 +435,168 @@ </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> {sql}users = session.query(User).select_by(street='123 Green Street') </span><del>- SELECT users.user_id AS users_user_id, - users.user_name AS users_user_name, users.password AS users_password, - addresses.address_id AS addresses_address_id, - addresses.user_id AS addresses_user_id, addresses.street AS addresses_street, - addresses.city AS addresses_city, addresses.state AS addresses_state, - addresses.zip AS addresses_zip - FROM addresses AS addresses_417c, - users LEFT OUTER JOIN addresses ON users.user_id = addresses.user_id - WHERE addresses_417c.street = :addresses_street - AND users.user_id = addresses_417c.user_id - ORDER BY users.oid, addresses.oid </del><ins>+ + SELECT users.user_name AS users_user_name, + users.password AS users_password, users.user_id AS users_user_id, + addresses_6ca7.city AS addresses_6ca7_city, + addresses_6ca7.address_id AS addresses_6ca7_address_id, + addresses_6ca7.user_id AS addresses_6ca7_user_id, + addresses_6ca7.zip AS addresses_6ca7_zip, addresses_6ca7.state AS addresses_6ca7_state, + addresses_6ca7.street AS addresses_6ca7_street + FROM addresses, users LEFT OUTER JOIN addresses AS addresses_6ca7 ON users.user_id = addresses_6ca7.user_id + WHERE addresses.street = :addresses_street AND users.user_id = addresses.user_id ORDER BY users.oid, addresses_6ca7.oid </ins><span class="cx"> {'addresses_street': '123 Green Street'} </span><span class="cx"> </span><span class="cx"> The join implied by passing the "street" parameter is converted into an "aliasized" clause by the eager loader, so that it does not conflict with the join used to eager load the child address objects. </span><span class="cx"> </span><span class="cx"> #### Switching Lazy/Eager, No Load {@name=options} </span><span class="cx"> </span><del>-The `options` method of mapper provides an easy way to get alternate forms of a mapper from an original one. The most common use of this feature is to change the "eager/lazy" loading behavior of a particular mapper, via the functions `eagerload()`, `lazyload()` and `noload()`: </del><ins>+The `options` method on the `Query` object provides an easy way to get alternate forms of a mapper query from an original one. The most common use of this feature is to change the "eager/lazy" loading behavior of a particular mapper, via the functions `eagerload()`, `lazyload()` and `noload()`: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> # user mapper with lazy addresses </span><del>- User.mapper = mapper(User, users, properties = { - 'addresses' : relation(mapper(Address, addresses)) </del><ins>+ mapper(User, users_table, properties = { + 'addresses' : relation(mapper(Address, addresses_table)) </ins><span class="cx"> } </span><span class="cx"> ) </span><span class="cx"> </span><del>- # make an eager loader - eagermapper = User.mapper.options(eagerload('addresses')) - u = eagermapper.select() </del><ins>+ # query object + query = session.query(User) </ins><span class="cx"> </span><del>- # make another mapper that wont load the addresses at all - plainmapper = User.mapper.options(noload('addresses')) </del><ins>+ # make an eager loading query + eagerquery = query.options(eagerload('addresses')) + u = eagerquery.select() </ins><span class="cx"> </span><ins>+ # make another query that wont load the addresses at all + plainquery = query.options(noload('addresses')) + </ins><span class="cx"> # multiple options can be specified </span><del>- mymapper = oldmapper.options(lazyload('tracker'), noload('streets'), eagerload('members')) </del><ins>+ myquery = oldquery.options(lazyload('tracker'), noload('streets'), eagerload('members')) </ins><span class="cx"> </span><span class="cx"> # to specify a relation on a relation, separate the property names by a "." </span><del>- mymapper = oldmapper.options(eagerload('orders.items')) </del><ins>+ myquery = oldquery.options(eagerload('orders.items')) </ins><span class="cx"> </span><span class="cx"> ### One to One/Many to One {@name=onetoone} </span><span class="cx"> </span><span class="cx"> The above examples focused on the "one-to-many" relationship. To do other forms of relationship is easy, as the `relation` function can usually figure out what you want: </span><span class="cx"> </span><span class="cx"> {python} </span><ins>+ metadata = MetaData() + </ins><span class="cx"> # a table to store a user's preferences for a site </span><del>- prefs = Table('user_prefs', engine, </del><ins>+ prefs_table = Table('user_prefs', metadata, </ins><span class="cx"> Column('pref_id', Integer, primary_key = True), </span><span class="cx"> Column('stylename', String(20)), </span><span class="cx"> Column('save_password', Boolean, nullable = False), </span><span class="cx"> Column('timezone', CHAR(3), nullable = False) </span><span class="cx"> ) </span><span class="cx"> </span><del>- # user table gets 'preference_id' column added - users = Table('users', engine, </del><ins>+ # user table with a 'preference_id' column + 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), nullable = False), </span><span class="cx"> Column('password', String(20), nullable = False), </span><del>- Column('preference_id', Integer, ForeignKey("prefs.pref_id")) </del><ins>+ Column('preference_id', Integer, ForeignKey("user_prefs.pref_id")) </ins><span class="cx"> ) </span><span class="cx"> </span><del>- # class definition for preferences </del><ins>+ # engine and some test data + engine = create_engine('sqlite:///', echo=True) + metadata.create_all(engine) + engine.execute(prefs_table.insert(), dict(pref_id=1, stylename='green', save_password=1, timezone='EST')) + engine.execute(users_table.insert(), dict(user_name = 'fred', password='45nfss', preference_id=1)) + + # classes + class User(object): + def __init__(self, user_name, password): + self.user_name = user_name + self.password = password + </ins><span class="cx"> class UserPrefs(object): </span><span class="cx"> pass </span><del>- UserPrefs.mapper = mapper(UserPrefs, prefs) </del><ins>+ + mapper(UserPrefs, prefs_table) </ins><span class="cx"> </span><del>- # address mapper - Address.mapper = mapper(Address, addresses) - - # make a new mapper referencing everything. - m = mapper(User, users, properties = dict( - addresses = relation(Address.mapper, lazy=True, private=True), - preferences = relation(UserPrefs.mapper, lazy=False, private=True), </del><ins>+ mapper(User, users_table, properties = dict( + preferences = relation(UserPrefs, lazy=False, private=True), </ins><span class="cx"> )) </span><span class="cx"> </span><span class="cx"> # select </span><del>- {sql}user = m.get_by(user_name='fred') - SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, - users.password AS users_password, users.preference_id AS users_preference_id, - user_prefs.pref_id AS user_prefs_pref_id, user_prefs.stylename AS user_prefs_stylename, - user_prefs.save_password AS user_prefs_save_password, user_prefs.timezone AS user_prefs_timezone - FROM users LEFT OUTER JOIN user_prefs ON user_prefs.pref_id = users.preference_id - WHERE users.user_name = :users_user_name ORDER BY users.oid, user_prefs.oid </del><ins>+ session = create_session(bind_to=engine) + {sql}user = session.query(User).get_by(user_name='fred') + SELECT users.preference_id AS users_preference_id, users.user_name AS users_user_name, + users.password AS users_password, users.user_id AS users_user_id, + user_prefs_4eb2.timezone AS user_prefs_4eb2_timezone, user_prefs_4eb2.stylename AS user_prefs_4eb2_stylename, + user_prefs_4eb2.save_password AS user_prefs_4eb2_save_password, user_prefs_4eb2.pref_id AS user_prefs_4eb2_pref_id + FROM (SELECT users.user_id AS users_user_id FROM users WHERE users.user_name = :users_user_name ORDER BY users.oid + LIMIT 1 OFFSET 0) AS rowcount, + users LEFT OUTER JOIN user_prefs AS user_prefs_4eb2 ON user_prefs_4eb2.pref_id = users.preference_id + WHERE rowcount.users_user_id = users.user_id ORDER BY users.oid, user_prefs_4eb2.oid </ins><span class="cx"> {'users_user_name': 'fred'} </span><del>- </del><ins>+ </ins><span class="cx"> save_password = user.preferences.save_password </span><del>- </del><ins>+ </ins><span class="cx"> # modify </span><span class="cx"> user.preferences.stylename = 'bluesteel' </span><del>- {sql}user.addresses.append(Address('fr...@hi...')) - SELECT email_addresses.address_id AS email_addresses_address_id, - email_addresses.user_id AS email_addresses_user_id, - email_addresses.email_address AS email_addresses_email_address - FROM email_addresses - WHERE email_addresses.user_id = :users_user_id - ORDER BY email_addresses.oid, email_addresses.oid - {'users_user_id': 1} - - # commit - {sql}objectstore.commit() </del><ins>+ + # flush + {sql}session.flush() </ins><span class="cx"> UPDATE user_prefs SET stylename=:stylename </span><span class="cx"> WHERE user_prefs.pref_id = :pref_id </span><span class="cx"> [{'stylename': 'bluesteel', 'pref_id': 1}] </span><del>- INSERT INTO email_addresses (address_id, user_id, email_address) - VALUES (:address_id, :user_id, :email_address) - {'email_address': 'fr...@hi...', 'address_id': None, 'user_id': 1} </del><span class="cx"> </span><span class="cx"> ### Many to Many {@name=manytomany} </span><span class="cx"> </span><span class="cx"> The `relation` function handles a basic many-to-many relationship when you specify the association table: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- articles = Table('articles', engine, </del><ins>+ metadata = MetaData() + + articles_table = Table('articles', metadata, </ins><span class="cx"> Column('article_id', Integer, primary_key = True), </span><span class="cx"> Column('headline', String(150), key='headline'), </span><span class="cx"> Column('body', TEXT, key='body'), </span><span class="cx"> ) </span><span class="cx"> </span><del>- keywords = Table('keywords', engine, </del><ins>+ keywords_table = Table('keywords', metadata, </ins><span class="cx"> Column('keyword_id', Integer, primary_key = True), </span><span class="cx"> Column('keyword_name', String(50)) </span><span class="cx"> ) </span><span class="cx"> </span><del>- itemkeywords = Table('article_keywords', engine, </del><ins>+ itemkeywords_table = Table('article_keywords', metadata, </ins><span class="cx"> Column('article_id', Integer, ForeignKey("articles.article_id")), </span><span class="cx"> Column('keyword_id', Integer, ForeignKey("keywords.keyword_id")) </span><span class="cx"> ) </span><span class="cx"> </span><ins>+ engine = create_engine('sqlite:///') + metadata.create_all(engine) + </ins><span class="cx"> # class definitions </span><span class="cx"> class Keyword(object): </span><del>- def __init__(self, name = None): </del><ins>+ def __init__(self, name): </ins><span class="cx"> self.keyword_name = name </span><span class="cx"> </span><span class="cx"> class Article(object): </span><span class="cx"> pass </span><ins>+ + mapper(Keyword, keywords_table) </ins><span class="cx"> </span><span class="cx"> # define a mapper that does many-to-many on the 'itemkeywords' association </span><span class="cx"> # table </span><del>- Article.mapper = mapper(Article, articles, properties = dict( - keywords = relation(mapper(Keyword, keywords), itemkeywords, lazy=False) </del><ins>+ mapper(Article, articles_table, properties = dict( + keywords = relation(Keyword, secondary=itemkeywords_table, lazy=False) </ins><span class="cx"> ) </span><span class="cx"> ) </span><span class="cx"> </span><ins>+ session = create_session(bind_to=engine) + </ins><span class="cx"> article = Article() </span><span class="cx"> article.headline = 'a headline' </span><span class="cx"> article.body = 'this is the body' </span><span class="cx"> article.keywords.append(Keyword('politics')) </span><span class="cx"> article.keywords.append(Keyword('entertainment')) </span><del>- {sql}objectstore.commit() </del><ins>+ session.save(article) + + {sql}session.flush() </ins><span class="cx"> INSERT INTO keywords (name) VALUES (:name) </span><span class="cx"> {'name': 'politics'} </span><span class="cx"> INSERT INTO keywords (name) VALUES (:name) </span><span class="lines">@@ -597,33 +607,25 @@ </span><span class="cx"> [{'keyword_id': 1, 'article_id': 1}, {'keyword_id': 2, 'article_id': 1}] </span><span class="cx"> </span><span class="cx"> # select articles based on a keyword. select_by will handle the extra joins. </span><del>- {sql}articles = Article.mapper.select_by(keyword_name='politics') - SELECT articles.article_id AS articles_article_id, - articles.article_headline AS articles_article_headline, - articles.article_body AS articles_article_body, - keywords.keyword_id AS keywords_keyword_id, - keywords.keyword_name AS keywords_keyword_name - FROM keywords AS keywords_f008, - article_keywords AS article_keywords_dbf0, - articles LEFT OUTER JOIN article_keywords ON - articles.article_id = article_keywords.article_id - LEFT OUTER JOIN keywords ON - keywords.keyword_id = article_keywords.keyword_id - WHERE (keywords_f008.keyword_name = :keywords_keyword_name - AND articles.article_id = article_keywords_dbf0.article_id) - AND keywords_f008.keyword_id = article_keywords_dbf0.keyword_id - ORDER BY articles.oid, article_keywords.oid </del><ins>+ {sql}articles = session.query(Article).select_by(keyword_name='politics') + SELECT keywords_e2f2.keyword_id AS keywords_e2f2_keyword_id, keywords_e2f2.keyword_name AS keywords_e2f2_keyword_name, + articles.headline AS articles_headline, articles.body AS articles_body, articles.article_id AS articles_article_id + FROM keywords, article_keywords, articles + LEFT OUTER JOIN article_keywords AS article_keyword_3da2 ON articles.article_id = article_keyword_3da2.article_id + LEFT OUTER JOIN keywords AS keywords_e2f2 ON keywords_e2f2.keyword_id = article_keyword_3da2.keyword_id + WHERE (keywords.keyword_name = :keywords_keywords_name AND articles.article_id = article_keywords.article_id) + AND keywords.keyword_id = article_keywords.keyword_id ORDER BY articles.oid, article_keyword_3da2.oid </ins><span class="cx"> {'keywords_keyword_name': 'politics'} </span><span class="cx"> </span><del>- # modify </del><span class="cx"> a = articles[0] </span><del>- del a.keywords[:] </del><ins>+ + # clear out keywords with a new list + a.keywords = [] </ins><span class="cx"> a.keywords.append(Keyword('topstories')) </span><span class="cx"> a.keywords.append(Keyword('government')) </span><span class="cx"> </span><del>- # commit. individual INSERT/DELETE operations will take place only for the list - # elements that changed. - {sql}objectstore.commit() </del><ins>+ # flush + {sql}session.flush() </ins><span class="cx"> INSERT INTO keywords (name) VALUES (:name) </span><span class="cx"> {'name': 'topstories'} </span><span class="cx"> INSERT INTO keywords (name) VALUES (:name) </span><span class="lines">@@ -640,63 +642,83 @@ </span><span class="cx"> Many to Many can also be done with an association object, that adds additional information about how two items are related. This association object is set up in basically the same way as any other mapped object. However, since an association table typically has no primary key columns, you have to tell the mapper what columns will compose its "primary key", which are the two (or more) columns involved in the association. Also, the relation function needs an additional hint as to the fact that this mapped object is an association object, via the "association" argument which points to the class or mapper representing the other side of the association. </span><span class="cx"> </span><span class="cx"> {python} </span><ins>+ from sqlalchemy import * + metadata = MetaData() + + users_table = Table('users', metadata, + Column('user_id', Integer, primary_key = True), + Column('user_name', String(16), nullable = False), + ) + + articles_table = Table('articles', metadata, + Column('article_id', Integer, primary_key = True), + Column('headline', String(150), key='headline'), + Column('body', TEXT, key='body'), + ) + + keywords_table = Table('keywords', metadata, + Column('keyword_id', Integer, primary_key = True), + Column('keyword_name', String(50)) + ) + </ins><span class="cx"> # add "attached_by" column which will reference the user who attached this keyword </span><del>- itemkeywords = Table('article_keywords', engine, </del><ins>+ itemkeywords_table = Table('article_keywords', metadata, </ins><span class="cx"> Column('article_id', Integer, ForeignKey("articles.article_id")), </span><span class="cx"> Column('keyword_id', Integer, ForeignKey("keywords.keyword_id")), </span><span class="cx"> Column('attached_by', Integer, ForeignKey("users.user_id")) </span><span class="cx"> ) </span><span class="cx"> </span><del>- # define an association class </del><ins>+ engine = create_engine('sqlite:///', echo=True) + metadata.create_all(engine) + + # class definitions + class User(object): + pass + class Keyword(object): + def __init__(self, name): + self.keyword_name = name + class Article(object): + pass </ins><span class="cx"> class KeywordAssociation(object): </span><span class="cx"> pass </span><span class="cx"> </span><ins>+ mapper(User, users_table) + mapper(Keyword, keywords_table) + </ins><span class="cx"> # mapper for KeywordAssociation </span><span class="cx"> # specify "primary key" columns manually </span><del>- KeywordAssociation.mapper = mapper(KeywordAssociation, itemkeywords, - primary_key = [itemkeywords.c.article_id, itemkeywords.c.keyword_id], </del><ins>+ mapper(KeywordAssociation, itemkeywords_table, + primary_key = [itemkeywords_table.c.article_id, itemkeywords_table.c.keyword_id], </ins><span class="cx"> properties={ </span><del>- 'keyword' : relation(Keyword, lazy = False), # uses primary Keyword mapper - 'user' : relation(User, lazy = True) # uses primary User mapper </del><ins>+ 'keyword' : relation(Keyword, lazy = False), + 'user' : relation(User, lazy = False) </ins><span class="cx"> } </span><span class="cx"> ) </span><span class="cx"> </span><del>- # mappers for Users, Keywords - User.mapper = mapper(User, users) - Keyword.mapper = mapper(Keyword, keywords) - - # define the mapper. - m = mapper(Article, articles, properties={ - 'keywords':relation(KeywordAssociation.mapper, lazy=False, association=Keyword) </del><ins>+ # Article mapper, relates to Keyword via KeywordAssociation + mapper(Article, articles_table, properties={ + 'keywords':relation(KeywordAssociation, lazy=False, association=Keyword) </ins><span class="cx"> } </span><span class="cx"> ) </span><span class="cx"> </span><del>- # bonus step - well, we do want to load the users in one shot, - # so modify the mapper via an option. - # this returns a new mapper with the option switched on. - m2 = mapper.options(eagerload('keywords.user')) </del><ins>+ session = create_session(bind_to=engine) + # select by keyword + {sql}alist = session.query(Article).select_by(keyword_name='jacks_stories') + SELECT article_keyword_f9af.keyword_id AS article_keyword_f9af_key_b3e1, + article_keyword_f9af.attached_by AS article_keyword_f9af_att_95d4, + article_keyword_f9af.article_id AS article_keyword_f9af_art_fd49, + users_9c30.user_name AS users_9c30_user_name, users_9c30.user_id AS users_9c30_user_id, + keywords_dc54.keyword_id AS keywords_dc54_keyword_id, keywords_dc54.keyword_name AS keywords_dc54_keyword_name, + articles.headline AS articles_headline, articles.body AS articles_body, articles.article_id AS articles_article_id + FROM keywords, article_keywords, articles + LEFT OUTER JOIN article_keywords AS article_keyword_f9af ON articles.article_id = article_keyword_f9af.article_id + LEFT OUTER JOIN users AS users_9c30 ON users_9c30.user_id = article_keyword_f9af.attached_by + LEFT OUTER JOIN keywords AS keywords_dc54 ON keywords_dc54.keyword_id = article_keyword_f9af.keyword_id + WHERE (keywords.keyword_name = :keywords_keywords_name AND keywords.keyword_id = article_keywords.keyword_id) + AND articles.article_id = article_keywords.article_id + ORDER BY articles.oid, article_keyword_f9af.oid, users_9c30.oid, keywords_dc54.oid + {'keywords_keywords_name': 'jacks_stories'} </ins><span class="cx"> </span><del>- # select by keyword again - {sql}alist = m2.select_by(keyword_name='jacks_stories') - SELECT articles.article_id AS articles_article_id, - articles.article_headline AS articles_article_headline, - articles.article_body AS articles_article_body, - article_keywords.article_id AS article_keywords_article_id, - article_keywords.keyword_id AS article_keywords_keyword_id, - article_keywords.attached_by AS article_keywords_attached_by, - users.user_id AS users_user_id, users.user_name AS users_user_name, - users.password AS users_password, users.preference_id AS users_preference_id, - keywords.keyword_id AS keywords_keyword_id, keywords.name AS keywords_name - FROM article_keywords article_keywords_3a64, keywords keywords_11b7, - articles LEFT OUTER JOIN article_keywords ON articles.article_id = article_keywords.article_id - LEFT OUTER JOIN users ON users.user_id = article_keywords.attached_by - LEFT OUTER JOIN keywords ON keywords.keyword_id = article_keywords.keyword_id - WHERE keywords_11b7.keyword_id = article_keywords_3a64.keyword_id - AND article_keywords_3a64.article_id = articles.article_id - AND keywords_11b7.name = :keywords_name - ORDER BY articles.oid, article_keywords.oid, users.oid, keywords.oid - {'keywords_name': 'jacks_stories'} - </del><span class="cx"> # user is available </span><span class="cx"> for a in alist: </span><span class="cx"> for k in a.keywords: </span></span></pre></div> <a id="sqlalchemybranchesschemadocbuildcontentdbenginetxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/dbengine.txt (1385 => 1386)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/dbengine.txt 2006-05-04 00:52:12 UTC (rev 1385) +++ sqlalchemy/branches/schema/doc/build/content/dbengine.txt 2006-05-04 04:06:42 UTC (rev 1386) </span><span class="lines">@@ -37,6 +37,8 @@ </span><span class="cx"> mysql_db = create_engine('mysql://localhost/foo') </span><span class="cx"> oracle_db = create_engine('oracle://scott:tiger@dsn') </span><span class="cx"> </span><ins>+The `Engine` will create its first connection to the database when a SQL statement is executed. As concurrent statements are executed, the underlying connection pool will grow to a default size of five connections, and will allow a default "overflow" of ten. Since the `Engine` is essentially "home base" for the connection pool, it follows that you should keep a single `Engine` per database established within an application, rather than creating a new one for each connection. + </ins><span class="cx"> ### Database Engine Options {@name=options} </span><span class="cx"> </span><span class="cx"> Keyword options can also be specified to `create_engine()`, following the string URL as follows: </span><span class="lines">@@ -61,6 +63,8 @@ </span><span class="cx"> </span><span class="cx"> engine = create_engine('mysql', pool=pool.QueuePool(getconn, pool_size=20, max_overflow=40)) </span><span class="cx"> </span><ins>+* pool_size=5 : the number of connections to keep open inside the connection pool. This is only used with `QueuePool`. +* max_overflow=10 : the number of connections to allow in "overflow", that is connections that can be opened above and beyond the initial five. this is only used with `QueuePool`. </ins><span class="cx"> * echo=False : if True, the Engine will log all statements as well as a repr() of their parameter lists to the engines logger, which defaults to sys.stdout. A SQLEngine instances' "echo" data member can be modified at any time to turn logging on and off. If set to the string 'debug', result rows will be printed to the standard output as well. </span><span class="cx"> * logger=None : a file-like object where logging output can be sent, if echo is set to True. This defaults to sys.stdout. </span><span class="cx"> * module=None : used by Oracle and Postgres, this is a reference to a DBAPI2 module to be used instead of the engine's default module. For Postgres, the default is psycopg2, or psycopg1 if 2 cannot be found. For Oracle, its cx_Oracle. </span></span></pre></div> <a id="sqlalchemybranchesschemadocbuildcontentdocument_basemyt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/document_base.myt (1385 => 1386)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/document_base.myt 2006-05-04 00:52:12 UTC (rev 1385) +++ sqlalchemy/branches/schema/doc/build/content/document_base.myt 2006-05-04 04:06:42 UTC (rev 1386) </span><span class="lines">@@ -12,6 +12,7 @@ </span><span class="cx"> 'adv_datamapping', </span><span class="cx"> 'types', </span><span class="cx"> 'pooling', </span><ins>+ 'plugins', </ins><span class="cx"> 'docstrings', </span><span class="cx"> ] </span><span class="cx"> </span></span></pre></div> <a id="sqlalchemybranchesschemalibsqlalchemyenginestrategiespy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/lib/sqlalchemy/engine/strategies.py (1385 => 1386)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/lib/sqlalchemy/engine/strategies.py 2006-05-04 00:52:12 UTC (rev 1385) +++ sqlalchemy/branches/schema/lib/sqlalchemy/engine/strategies.py 2006-05-04 04:06:42 UTC (rev 1386) </span><span class="lines">@@ -33,6 +33,8 @@ </span><span class="cx"> </span><span class="cx"> poolargs = kwargs.copy() </span><span class="cx"> poolargs['echo'] = poolargs.get('echo_pool', False) </span><ins>+ poolargs['pool_size'] = poolargs.get('pool_size', False) + poolargs['max_overflow'] = poolargs.get('max_overflow', False) </ins><span class="cx"> poolclass = getattr(module, 'poolclass', None) </span><span class="cx"> if poolclass is not None: </span><span class="cx"> poolargs.setdefault('poolclass', poolclass) </span><span class="lines">@@ -53,6 +55,8 @@ </span><span class="cx"> </span><span class="cx"> poolargs = kwargs.copy() </span><span class="cx"> poolargs['echo'] = poolargs.get('echo_pool', False) </span><ins>+ poolargs['pool_size'] = poolargs.get('pool_size', False) + poolargs['max_overflow'] = poolargs.get('max_overflow', False) </ins><span class="cx"> poolclass = getattr(module, 'poolclass', None) </span><span class="cx"> if poolclass is not None: </span><span class="cx"> poolargs.setdefault('poolclass', poolclass) </span></span></pre></div> <a id="sqlalchemybranchesschemalibsqlalchemyschemapy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/lib/sqlalchemy/schema.py (1385 => 1386)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/lib/sqlalchemy/schema.py 2006-05-04 00:52:12 UTC (rev 1385) +++ sqlalchemy/branches/schema/lib/sqlalchemy/schema.py 2006-05-04 04:06:42 UTC (rev 1386) </span><span class="lines">@@ -611,7 +611,7 @@ </span><span class="cx"> def table_iterator(self, reverse=True): </span><span class="cx"> return self._sort_tables(self.tables.values(), reverse=reverse) </span><span class="cx"> </span><del>- def create_all(self, tables=None, engine=None): </del><ins>+ def create_all(self, engine=None, tables=None): </ins><span class="cx"> if not tables: </span><span class="cx"> tables = self.tables.values() </span><span class="cx"> </span><span class="lines">@@ -627,7 +627,7 @@ </span><span class="cx"> conn.create(table) </span><span class="cx"> engine.run_callable(do) </span><span class="cx"> </span><del>- def drop_all(self, tables=None, engine=None): </del><ins>+ def drop_all(self, engine=None, tables=None): </ins><span class="cx"> if not tables: </span><span class="cx"> tables = self.tables.values() </span><span class="cx"> </span></span></pre> </div> </div> </body> </html> |
