[Sqlalchemy-commits] [1383] sqlalchemy/branches/schema/doc: docs...
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1383] sqlalchemy/branches/schema/doc: docs...
|
From: <co...@sq...> - 2006-05-03 19:15:59
|
<!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>[1383] sqlalchemy/branches/schema/doc: docs...</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1383</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-05-03 14:15:39 -0500 (Wed, 03 May 2006)</dd> </dl> <h3>Log Message</h3> <pre>docs...</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontentdbenginetxt">sqlalchemy/branches/schema/doc/build/content/dbengine.txt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildcontentsqlconstructiontxt">sqlalchemy/branches/schema/doc/build/content/sqlconstruction.txt</a></li> <li><a href="#sqlalchemybranchesschemadocdocscss">sqlalchemy/branches/schema/doc/docs.css</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontentdbenginetxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/dbengine.txt (1382 => 1383)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/dbengine.txt 2006-05-03 17:53:04 UTC (rev 1382) +++ sqlalchemy/branches/schema/doc/build/content/dbengine.txt 2006-05-03 19:15:39 UTC (rev 1383) </span><span class="lines">@@ -78,7 +78,7 @@ </span><span class="cx"> {python title="Explicit Connection"} </span><span class="cx"> engine = create_engine('sqlite:///:memory:') </span><span class="cx"> connection = engine.connect() </span><del>- result = connection.execute("select * from mytable where col1=:col1", {'col1':5}) </del><ins>+ result = connection.execute("select * from mytable where col1=:col1", col1=5) </ins><span class="cx"> for row in result: </span><span class="cx"> print row['col1'], row['col2'] </span><span class="cx"> connection.close() </span><span class="lines">@@ -87,7 +87,7 @@ </span><span class="cx"> </span><span class="cx"> {python title="Implicit Connection"} </span><span class="cx"> engine = create_engine('sqlite:///:memory:') </span><del>- result = engine.execute("select * from mytable where col1=:col1", {'col1':5}) </del><ins>+ result = engine.execute("select * from mytable where col1=:col1", col1=5) </ins><span class="cx"> for row in result: </span><span class="cx"> print row['col1'], row['col2'] </span><span class="cx"> result.close() </span><span class="lines">@@ -181,7 +181,7 @@ </span><span class="cx"> trans = connection.begin() </span><span class="cx"> try: </span><span class="cx"> r1 = connection.execute(table1.select()) </span><del>- connection.execute(table1.insert().execute(col1=7, col2='this is some data)) </del><ins>+ connection.execute(table1.insert(), col1=7, col2='this is some data') </ins><span class="cx"> trans.commit() </span><span class="cx"> except: </span><span class="cx"> trans.rollback() </span><span class="lines">@@ -205,7 +205,7 @@ </span><span class="cx"> trans.begin() </span><span class="cx"> try: </span><span class="cx"> connection.execute("insert into mytable values ('bat', 'lala')") </span><del>- connection.execute(mytable.insert(), dict(col1='bat', col2='lala')) </del><ins>+ connection.execute(mytable.insert(), col1='bat', col2='lala') </ins><span class="cx"> trans.commit() # transaction is not committed yet </span><span class="cx"> except: </span><span class="cx"> trans.rollback() # this rolls back the transaction unconditionally </span></span></pre></div> <a id="sqlalchemybranchesschemadocbuildcontentsqlconstructiontxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/sqlconstruction.txt (1382 => 1383)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/sqlconstruction.txt 2006-05-03 17:53:04 UTC (rev 1382) +++ sqlalchemy/branches/schema/doc/build/content/sqlconstruction.txt 2006-05-03 19:15:39 UTC (rev 1383) </span><span class="lines">@@ -3,27 +3,27 @@ </span><span class="cx"> </span><span class="cx"> *Note:* This section describes how to use SQLAlchemy to construct SQL queries and receive result sets. It does *not* cover the object relational mapping capabilities of SQLAlchemy; that is covered later on in [datamapping](rel:datamapping). However, both areas of functionality work similarly in how selection criterion is constructed, so if you are interested just in ORM, you should probably skim through basic [sql_select_whereclause](rel:sql_select_whereclause) construction before moving on. </span><span class="cx"> </span><del>-Once you have used the `sqlalchemy.schema` module to construct your tables and/or reflect them from the database, performing SQL queries using those table meta data objects is done via the `sqlalchemy.sql` package. This package defines a large set of classes, each of which represents a particular kind of lexical construct within a SQL query; all are descendants of the common base class `sqlalchemy.sql.ClauseElement`. A full query is represented via a structure of ClauseElements. A set of reasonably intuitive creation functions is provided by the `sqlalchemy.sql` package to create these structures; these functions are described in the rest of this section. </del><ins>+Once you have used the `sqlalchemy.schema` module to construct your tables and/or reflect them from the database, performing SQL queries using those table meta data objects is done via the `sqlalchemy.sql` package. This package defines a large set of classes, each of which represents a particular kind of lexical construct within a SQL query; all are descendants of the common base class `sqlalchemy.sql.ClauseElement`. A full query is represented via a structure of `ClauseElement`s. A set of reasonably intuitive creation functions is provided by the `sqlalchemy.sql` package to create these structures; these functions are described in the rest of this section. </ins><span class="cx"> </span><del>-To execute a query, you create its structure, then call the resulting structure's `execute()` method, which returns a cursor-like object (more on that later). The same clause structure can be used repeatedly. A ClauseElement is compiled into a string representation by an underlying SQLEngine object, which is located by searching through the clause's child items for a Table object, which provides a reference to its SQLEngine. - </del><ins>+Executing a `ClauseElement` structure can be performed in two general ways. You can use an `Engine` or a `Connection` object's `execute()` method to which you pass the query structure; this is known as **explicit style**. Or, if the `ClauseElement` structure is built upon Table metadata which is bound to an `Engine` directly, you can simply call `execute()` on the structure itself, known as **implicit style**. In both cases, the execution returns a cursor-like object (more on that later). The same clause structure can be executed repeatedly. The `ClauseElement` is compiled into a string representation by an underlying `Compiler` object which is associated with the `Engine` via its `Dialect`. </ins><span class="cx"> </span><del>-The examples below all include a dump of the generated SQL corresponding to the query object, as well as a dump of the statement's bind parameters. In all cases, bind parameters are named parameters using the colon format (i.e. ':name'). A named parameter scheme, either ':name' or '%(name)s', is used with all databases, including those that use positional schemes. For those, the named-parameter statement and its bind values are converted to the proper list-based format right before execution. Therefore a SQLAlchemy application that uses ClauseElements can standardize on named parameters for all databases. </del><ins>+The examples below all include a dump of the generated SQL corresponding to the query object, as well as a dump of the statement's bind parameters. In all cases, bind parameters are shown as named parameters using the colon format (i.e. ':name'). When the statement is compiled into a database-specific version, the named-parameter statement and its bind values are converted to the proper paramstyle for that database automatically. </ins><span class="cx"> </span><del>-For this section, we will assume the following tables: </del><ins>+For this section, we will mostly use the implcit style of execution, meaning the `Table` objects are associated with an instance of `BoundMetaData`, and constructed `ClauseElement` objects support self-execution. We will also assume the following configuration: </ins><span class="cx"> </span><del>- {python}from sqlalchemy import * - db = create_engine('sqlite://filename=mydb', echo=True) </del><ins>+ {python} + from sqlalchemy import * + metadata = BoundMetaData('sqlite://filename=mydb', strategy='threadlocal', echo=True) </ins><span class="cx"> </span><span class="cx"> # a table to store users </span><del>- users = Table('users', db, </del><ins>+ users = Table('users', metadata, </ins><span class="cx"> Column('user_id', Integer, primary_key = True), </span><span class="cx"> Column('user_name', String(40)), </span><span class="cx"> Column('password', String(80)) </span><span class="cx"> ) </span><span class="cx"> </span><span class="cx"> # a table that stores mailing addresses associated with a specific user </span><del>- addresses = Table('addresses', db, </del><ins>+ addresses = 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">@@ -33,13 +33,13 @@ </span><span class="cx"> ) </span><span class="cx"> </span><span class="cx"> # a table that stores keywords </span><del>- keywords = Table('keywords', db, </del><ins>+ keywords = Table('keywords', metadata, </ins><span class="cx"> Column('keyword_id', Integer, primary_key = True), </span><span class="cx"> Column('name', VARCHAR(50)) </span><span class="cx"> ) </span><span class="cx"> </span><span class="cx"> # a table that associates keywords with users </span><del>- userkeywords = Table('userkeywords', db, </del><ins>+ userkeywords = Table('userkeywords', metadata, </ins><span class="cx"> Column('user_id', INT, ForeignKey("users")), </span><span class="cx"> Column('keyword_id', INT, ForeignKey("keywords")) </span><span class="cx"> ) </span><span class="lines">@@ -48,7 +48,8 @@ </span><span class="cx"> </span><span class="cx"> A select is done by constructing a `Select` object with the proper arguments, adding any extra arguments if desired, then calling its `execute()` method. </span><span class="cx"> </span><del>- {python}from sqlalchemy import * </del><ins>+ {python title="Basic Select"} + from sqlalchemy import * </ins><span class="cx"> </span><span class="cx"> # use the select() function defined in the sql package </span><span class="cx"> s = select([users]) </span><span class="lines">@@ -57,7 +58,7 @@ </span><span class="cx"> s = users.select() </span><span class="cx"> </span><span class="cx"> # then, call execute on the Select object: </span><del>- {sql}c = s.execute() </del><ins>+ {sql}result = s.execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password FROM users </span><span class="cx"> {} </span><span class="cx"> </span><span class="lines">@@ -65,15 +66,48 @@ </span><span class="cx"> >>> str(s) </span><span class="cx"> SELECT users.user_id, users.user_name, users.password FROM users </span><span class="cx"> </span><del>-The object returned by the execute call is a `sqlalchemy.engine.ResultProxy` object, which acts much like a DBAPI `cursor` object in the context of a result set, except that the rows returned can address their columns by ordinal position, column name, or even column object: </del><ins>+#### Explicit Execution {@name=explicit} </ins><span class="cx"> </span><del>- {python}# select rows, get resulting ResultProxy object - {sql}c = users.select().execute() </del><ins>+As mentioned above, `ClauseElement` structures can also be executed with a `Connection` object explicitly: + + {python} + engine = create_engine('sqlite:///myfile.db') + conn = engine.connect() + + s = users.select() + {sql}result = conn.execute(s) </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password FROM users </span><span class="cx"> {} </span><span class="cx"> </span><ins>+ conn.close() + +#### Binding ClauseElements to Engines {@name=binding} + +For queries that don't contain any tables, `ClauseElement`s that represent a fully executeable statement support an `engine` keyword parameter which can bind the object to an `Engine`, thereby allowing implicit execution: + + {python} + # select a literal + select(["current_time"], engine=myengine).execute() + {sql}SELECT current_time + {} + + # select a function + select([func.now()], engine=db).execute() + {sql}SELECT now() + {} + +#### Getting Results {@name=resultproxy} + +The object returned by `execute()` is a `sqlalchemy.engine.ResultProxy` object, which acts much like a DBAPI `cursor` object in the context of a result set, except that the rows returned can address their columns by ordinal position, column name, or even column object: + + {python title="Using the ResultProxy"} + # select rows, get resulting ResultProxy object + {sql}result = users.select().execute() + SELECT users.user_id, users.user_name, users.password FROM users + {} + </ins><span class="cx"> # get one row </span><del>- row = c.fetchone() </del><ins>+ row = result.fetchone() </ins><span class="cx"> </span><span class="cx"> # get the 'user_id' column via integer index: </span><span class="cx"> user_id = row[0] </span><span class="lines">@@ -88,16 +122,23 @@ </span><span class="cx"> password = row.password </span><span class="cx"> </span><span class="cx"> # ResultProxy object also supports fetchall() </span><del>- rows = c.fetchall() </del><ins>+ rows = result.fetchall() </ins><span class="cx"> </span><span class="cx"> # or get the underlying DBAPI cursor object </span><del>- cursor = c.cursor </del><ins>+ cursor = result.cursor + + # close the result. If the statement was implicitly executed (i.e. without an explicit Connection), this will + # return the underlying connection resources back to the connection pool. de-referencing the result + # will also have the same effect. + # if an explicit Connection was used, then close() does nothing. + result.close() </ins><span class="cx"> </span><span class="cx"> #### Using Column Labels {@name=labels} </span><span class="cx"> </span><span class="cx"> A common need when writing statements that reference multiple tables is to create labels for columns, thereby separating columns from different tables with the same name. The Select construct supports automatic generation of column labels via the `use_labels=True` parameter: </span><span class="cx"> </span><del>- {python}{sql}c = select([users, addresses], </del><ins>+ {python title="use_labels Flag"} + {sql}c = select([users, addresses], </ins><span class="cx"> users.c.user_id==addresses.c.address_id, </span><span class="cx"> use_labels=True).execute() </span><span class="cx"> SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, </span><span class="lines">@@ -111,7 +152,8 @@ </span><span class="cx"> </span><span class="cx"> The table name part of the label is affected if you use a construct such as a table alias: </span><span class="cx"> </span><del>- {python}person = users.alias('person') </del><ins>+ {python title="use_labels with an Alias"} + person = users.alias('person') </ins><span class="cx"> {sql}c = select([person, addresses], </span><span class="cx"> person.c.user_id==addresses.c.address_id, </span><span class="cx"> use_labels=True).execute() </span><span class="lines">@@ -122,9 +164,21 @@ </span><span class="cx"> addresses.zip AS addresses_zip FROM users AS person, addresses </span><span class="cx"> WHERE person.user_id = addresses.address_id </span><span class="cx"> </span><ins>+Labels are also generated in such a way as to never go beyond 30 characters. Most databases support a limit on the length of symbols, such as Postgres, and particularly Oracle which has a rather short limit of 30: + + {python title="use_labels Generates Abbreviated Labels"} + long_named_table = users.alias('this_is_the_person_table') + {sql}c = select([person], use_labels=True).execute() + SELECT this_is_the_person_table.user_id AS this_is_the_person_table_b36c, + this_is_the_person_table.user_name AS this_is_the_person_table_f76a, + this_is_the_person_table.password AS this_is_the_person_table_1e7c + FROM users AS this_is_the_person_table + {} + </ins><span class="cx"> You can also specify custom labels on a per-column basis using the `label()` function: </span><span class="cx"> </span><del>- {python}{sql}c = select([users.c.user_id.label('id'), users.c.user_name.label('name')]).execute() </del><ins>+ {python title="label() Function on Column"} + {sql}c = select([users.c.user_id.label('id'), users.c.user_name.label('name')]).execute() </ins><span class="cx"> SELECT users.user_id AS id, users.user_name AS name </span><span class="cx"> FROM users </span><span class="cx"> {} </span><span class="lines">@@ -135,7 +189,8 @@ </span><span class="cx"> </span><span class="cx"> But in addition to selecting all the columns off a single table, any set of columns can be specified, as well as full tables, and any combination of the two: </span><span class="cx"> </span><del>- {python}# individual columns </del><ins>+ {python title="Specify Columns to Select"} + # individual columns </ins><span class="cx"> {sql}c = select([users.c.user_id, users.c.user_name]).execute() </span><span class="cx"> SELECT users.user_id, users.user_name FROM users </span><span class="cx"> {} </span><span class="lines">@@ -154,13 +209,14 @@ </span><span class="cx"> addresses.zip FROM users, addresses </span><span class="cx"> {} </span><span class="cx"> </span><del>-#### WHERE Clause {@name=whereclause} </del><ins>+### WHERE Clause {@name=whereclause} </ins><span class="cx"> </span><span class="cx"> The WHERE condition is the named keyword argument `whereclause`, or the second positional argument to the `select()` constructor and the first positional argument to the `select()` method of `Table`. </span><span class="cx"> </span><span class="cx"> WHERE conditions are constructed using column objects, literal values, and functions defined in the `sqlalchemy.sql` module. Column objects override the standard Python operators to provide clause compositional objects, which compile down to SQL operations: </span><span class="cx"> </span><del>- {python}{sql}c = users.select(users.c.user_id == 7).execute() </del><ins>+ {python title="Basic WHERE Clause"} + {sql}c = users.select(users.c.user_id == 7).execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password, </span><span class="cx"> FROM users WHERE users.user_id = :users_user_id </span><span class="cx"> {'users_user_id': 7} </span><span class="lines">@@ -169,7 +225,8 @@ </span><span class="cx"> </span><span class="cx"> More where clauses: </span><span class="cx"> </span><del>- {python}# another comparison operator </del><ins>+ {python} + # another comparison operator </ins><span class="cx"> {sql}c = select([users], users.c.user_id>7).execute() </span><span class="cx"> SELECT users.user_id, users.user_name, users.password, </span><span class="cx"> FROM users WHERE users.user_id > :users_user_id </span><span class="lines">@@ -233,7 +290,8 @@ </span><span class="cx"> </span><span class="cx"> Select statements can also generate a WHERE clause based on the parameters you give it. If a given parameter, which matches the name of a column or its "label" (the combined tablename + "_" + column name), and does not already correspond to a bind parameter in the select object, it will be added as a comparison against that column. This is a shortcut to creating a full WHERE clause: </span><span class="cx"> </span><del>- {python}# specify a match for the "user_name" column </del><ins>+ {python} + # specify a match for the "user_name" column </ins><span class="cx"> {sql}c = users.select().execute(user_name='ed') </span><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="cx"> FROM users WHERE users.user_name = :users_user_name </span><span class="lines">@@ -246,11 +304,12 @@ </span><span class="cx"> FROM users WHERE users.user_name = :users_user_name AND users.user_id = :users_user_id </span><span class="cx"> {'users_user_name': 'ed', 'users_user_id': 10} </span><span class="cx"> </span><del>-##### Operators {@name=operators} </del><ins>+#### Operators {@name=operators} </ins><span class="cx"> </span><span class="cx"> Supported column operators so far are all the numerical comparison operators, i.e. '==', '>', '>=', etc., as well as like(), startswith(), endswith(), between(), and in(). Boolean operators include not_(), and_() and or_(), which also can be used inline via '~', '&amp;', and '|'. Math operators are '+', '-', '*', '/'. Any custom operator can be specified via the op() function shown below. </span><span class="cx"> </span><del>- {python}# "like" operator </del><ins>+ {python} + # "like" operator </ins><span class="cx"> users.select(users.c.user_name.like('%ter')) </span><span class="cx"> </span><span class="cx"> # equality operator </span><span class="lines">@@ -274,21 +333,13 @@ </span><span class="cx"> # any custom operator </span><span class="cx"> select([users.c.user_name.op('||')('_category')]) </span><span class="cx"> </span><del>-#### Specifying the Engine {@name=engine} </del><span class="cx"> </span><del>-For queries that don't contain any tables, the SQLEngine can be specified to any constructed statement via the `engine` keyword parameter: - - {python}# select a literal - select(["hi"], engine=myengine) - - # select a function - select([func.now()], engine=db) - </del><span class="cx"> #### Functions {@name=functions} </span><span class="cx"> </span><span class="cx"> Functions can be specified using the `func` keyword: </span><span class="cx"> </span><del>- {python}{sql}select([func.count(users.c.user_id)]).execute() </del><ins>+ {python} + {sql}select([func.count(users.c.user_id)]).execute() </ins><span class="cx"> SELECT count(users.user_id) FROM users </span><span class="cx"> </span><span class="cx"> {sql}users.select(func.substr(users.c.user_name, 1) == 'J').execute() </span><span class="lines">@@ -298,7 +349,8 @@ </span><span class="cx"> </span><span class="cx"> Functions also are callable as standalone values: </span><span class="cx"> </span><del>- {python}# call the "now()" function </del><ins>+ {python} + # call the "now()" function </ins><span class="cx"> time = func.now(engine=myengine).scalar() </span><span class="cx"> </span><span class="cx"> # call myfunc(1,2,3) </span><span class="lines">@@ -311,7 +363,8 @@ </span><span class="cx"> </span><span class="cx"> You can drop in a literal value anywhere there isnt a column to attach to via the `literal` keyword: </span><span class="cx"> </span><del>- {python}{sql}select([literal('foo') + literal('bar'), users.c.user_name]).execute() </del><ins>+ {python} + {sql}select([literal('foo') + literal('bar'), users.c.user_name]).execute() </ins><span class="cx"> SELECT :literal + :literal_1, users.user_name </span><span class="cx"> FROM users </span><span class="cx"> {'literal_1': 'bar', 'literal': 'foo'} </span><span class="lines">@@ -323,7 +376,8 @@ </span><span class="cx"> </span><span class="cx"> Literals also take an optional `type` parameter to give literals a type. This can sometimes be significant, for example when using the "+" operator with SQLite, the String type is detected and the operator is converted to "||": </span><span class="cx"> </span><del>- {python}{sql}select([literal('foo', type=String) + 'bar'], engine=e).execute() </del><ins>+ {python} + {sql}select([literal('foo', type=String) + 'bar'], engine=e).execute() </ins><span class="cx"> SELECT ? || ? </span><span class="cx"> ['foo', 'bar'] </span><span class="cx"> </span><span class="lines">@@ -331,7 +385,8 @@ </span><span class="cx"> </span><span class="cx"> The ORDER BY clause of a select statement can be specified as individual columns to order by within an array specified via the `order_by` parameter, and optional usage of the asc() and desc() functions: </span><span class="cx"> </span><del>- {python}# straight order by </del><ins>+ {python} + # straight order by </ins><span class="cx"> {sql}c = users.select(order_by=[users.c.user_name]).execute() </span><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="cx"> FROM users ORDER BY users.user_name </span><span class="lines">@@ -349,7 +404,8 @@ </span><span class="cx"> </span><span class="cx"> These are specified as keyword arguments: </span><span class="cx"> </span><del>- {python}{sql}c = select([users.c.user_name], distinct=True).execute() </del><ins>+ {python} + {sql}c = select([users.c.user_name], distinct=True).execute() </ins><span class="cx"> SELECT DISTINCT users.user_name FROM users </span><span class="cx"> </span><span class="cx"> {sql}c = users.select(limit=10, offset=20).execute() </span><span class="lines">@@ -361,7 +417,8 @@ </span><span class="cx"> </span><span class="cx"> As some of the examples indicated above, a regular inner join can be implicitly stated, just like in a SQL expression, by just specifying the tables to be joined as well as their join conditions: </span><span class="cx"> </span><del>- {python}{sql}addresses.select(addresses.c.user_id==users.c.user_id).execute() </del><ins>+ {python} + {sql}addresses.select(addresses.c.user_id==users.c.user_id).execute() </ins><span class="cx"> SELECT addresses.address_id, addresses.user_id, addresses.street, </span><span class="cx"> addresses.city, addresses.state, addresses.zip FROM addresses, users </span><span class="cx"> WHERE addresses.user_id = users.user_id </span><span class="lines">@@ -369,7 +426,8 @@ </span><span class="cx"> </span><span class="cx"> There is also an explicit join constructor, which can be embedded into a select query via the `from_obj` parameter of the select statement: </span><span class="cx"> </span><del>- {python}{sql}addresses.select(from_obj=[ </del><ins>+ {python} + {sql}addresses.select(from_obj=[ </ins><span class="cx"> addresses.join(users, addresses.c.user_id==users.c.user_id) </span><span class="cx"> ]).execute() </span><span class="cx"> SELECT addresses.address_id, addresses.user_id, addresses.street, addresses.city, </span><span class="lines">@@ -379,7 +437,8 @@ </span><span class="cx"> </span><span class="cx"> The join constructor can also be used by itself: </span><span class="cx"> </span><del>- {python}{sql}join(users, addresses, users.c.user_id==addresses.c.user_id).select().execute() </del><ins>+ {python} + {sql}join(users, addresses, users.c.user_id==addresses.c.user_id).select().execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password, </span><span class="cx"> addresses.address_id, addresses.user_id, addresses.street, addresses.city, </span><span class="cx"> addresses.state, addresses.zip </span><span class="lines">@@ -388,7 +447,8 @@ </span><span class="cx"> </span><span class="cx"> The join criterion in a join() call is optional. If not specified, the condition will be derived from the foreign key relationships of the two tables. If no criterion can be constructed, an exception will be raised. </span><span class="cx"> </span><del>- {python}{sql}join(users, addresses).select().execute() </del><ins>+ {python} + {sql}join(users, addresses).select().execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password, </span><span class="cx"> addresses.address_id, addresses.user_id, addresses.street, addresses.city, </span><span class="cx"> addresses.state, addresses.zip </span><span class="lines">@@ -399,7 +459,8 @@ </span><span class="cx"> </span><span class="cx"> A join can be created on its own using the `join` or `outerjoin` functions, or can be created off of an existing Table or other selectable unit via the `join` or `outerjoin` methods: </span><span class="cx"> </span><del>- {python}{sql}outerjoin(users, addresses, users.c.user_id==addresses.c.address_id).select().execute() </del><ins>+ {python} + {sql}outerjoin(users, addresses, users.c.user_id==addresses.c.address_id).select().execute() </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password, addresses.address_id, </span><span class="cx"> addresses.user_id, addresses.street, addresses.city, addresses.state, addresses.zip </span><span class="cx"> FROM users LEFT OUTER JOIN addresses ON users.user_id = addresses.address_id </span><span class="lines">@@ -420,7 +481,8 @@ </span><span class="cx"> </span><span class="cx"> Aliases are used primarily when you want to use the same table more than once as a FROM expression in a statement: </span><span class="cx"> </span><del>- {python}address_b = addresses.alias('addressb') </del><ins>+ {python} + address_b = addresses.alias('addressb') </ins><span class="cx"> {sql}# select users who have an address on Green street as well as Orange street </span><span class="cx"> users.select(and_( </span><span class="cx"> users.c.user_id==addresses.c.user_id, </span><span class="lines">@@ -440,10 +502,12 @@ </span><span class="cx"> </span><span class="cx"> SQLAlchemy allows the creation of select statements from not just Table objects, but from a whole class of objects that implement the `Selectable` interface. This includes Tables, Aliases, Joins and Selects. Therefore, if you have a Select, you can select from the Select: </span><span class="cx"> </span><ins>+ {python} </ins><span class="cx"> >>> s = users.select() </span><span class="cx"> >>> str(s) </span><span class="cx"> SELECT users.user_id, users.user_name, users.password FROM users </span><span class="cx"> </span><ins>+ {python} </ins><span class="cx"> >>> s = s.select() </span><span class="cx"> >>> str(s) </span><span class="cx"> SELECT user_id, user_name, password </span><span class="lines">@@ -451,13 +515,15 @@ </span><span class="cx"> </span><span class="cx"> Any Select, Join, or Alias object supports the same column accessors as a Table: </span><span class="cx"> </span><ins>+ {python} </ins><span class="cx"> >>> s = users.select() </span><span class="cx"> >>> [c.key for c in s.columns] </span><span class="cx"> ['user_id', 'user_name', 'password'] </span><span class="cx"> </span><span class="cx"> When you use `use_labels=True` in a Select object, the label version of the column names become the keys of the accessible columns. In effect you can create your own "view objects": </span><span class="cx"> </span><del>- {python}s = select([users, addresses], users.c.user_id==addresses.c.user_id, use_labels=True) </del><ins>+ {python} + s = select([users, addresses], users.c.user_id==addresses.c.user_id, use_labels=True) </ins><span class="cx"> {sql}select([ </span><span class="cx"> s.c.users_user_name, s.c.addresses_street, s.c.addresses_zip </span><span class="cx"> ], s.c.addresses_city=='San Francisco').execute() </span><span class="lines">@@ -474,7 +540,8 @@ </span><span class="cx"> </span><span class="cx"> To specify a SELECT statement as one of the selectable units in a FROM clause, it usually should be given an alias. </span><span class="cx"> </span><del>- {python}{sql}s = users.select().alias('u') </del><ins>+ {python} + {sql}s = users.select().alias('u') </ins><span class="cx"> select([addresses, s]).execute() </span><span class="cx"> SELECT addresses.address_id, addresses.user_id, addresses.street, addresses.city, </span><span class="cx"> addresses.state, addresses.zip, u.user_id, u.user_name, u.password </span><span class="lines">@@ -484,7 +551,8 @@ </span><span class="cx"> </span><span class="cx"> Select objects can be used in a WHERE condition, in operators such as IN: </span><span class="cx"> </span><del>- {python}# select user ids for all users whos name starts with a "p" </del><ins>+ {python} + # select user ids for all users whos name starts with a "p" </ins><span class="cx"> s = select([users.c.user_id], users.c.user_name.like('p%')) </span><span class="cx"> </span><span class="cx"> # now select all addresses for those users </span><span class="lines">@@ -501,7 +569,8 @@ </span><span class="cx"> </span><span class="cx"> Subqueries can be used in the column clause of a select statement by specifying the `scalar=True` flag: </span><span class="cx"> </span><del>- {python}{sql}select([table2.c.col1, table2.c.col2, select([table1.c.col1], table1.c.col2==7, scalar=True)]) </del><ins>+ {python} + {sql}select([table2.c.col1, table2.c.col2, select([table1.c.col1], table1.c.col2==7, scalar=True)]) </ins><span class="cx"> SELECT table2.col1, table2.col2, </span><span class="cx"> (SELECT table1.col1 AS col1 FROM table1 WHERE col2=:table1_col2) </span><span class="cx"> FROM table2 </span><span class="lines">@@ -511,7 +580,8 @@ </span><span class="cx"> </span><span class="cx"> When a select object is embedded inside of another select object, and both objects reference the same table, SQLAlchemy makes the assumption that the table should be correlated from the child query to the parent query. To disable this behavior, specify the flag `correlate=False` to the Select statement. </span><span class="cx"> </span><del>- {python}# make an alias of a regular select. </del><ins>+ {python} + # make an alias of a regular select. </ins><span class="cx"> s = select([addresses.c.street], addresses.c.user_id==users.c.user_id).alias('s') </span><span class="cx"> >>> str(s) </span><span class="cx"> SELECT addresses.street FROM addresses, users </span><span class="lines">@@ -529,7 +599,8 @@ </span><span class="cx"> </span><span class="cx"> An EXISTS clause can function as a higher-scaling version of an IN clause, and is usually used in a correlated fashion: </span><span class="cx"> </span><del>- {python}# find all users who have an address on Green street: </del><ins>+ {python} + # find all users who have an address on Green street: </ins><span class="cx"> {sql}users.select( </span><span class="cx"> exists( </span><span class="cx"> [addresses.c.address_id], </span><span class="lines">@@ -549,7 +620,8 @@ </span><span class="cx"> </span><span class="cx"> Unions come in two flavors, UNION and UNION ALL, which are available via module level functions or methods off a Selectable: </span><span class="cx"> </span><del>- {python}{sql}union( </del><ins>+ {python} + {sql}union( </ins><span class="cx"> addresses.select(addresses.c.street=='123 Green Street'), </span><span class="cx"> addresses.select(addresses.c.street=='44 Park Ave.'), </span><span class="cx"> addresses.select(addresses.c.street=='3 Mill Road'), </span><span class="lines">@@ -589,28 +661,27 @@ </span><span class="cx"> </span><span class="cx"> ### Custom Bind Parameters {@name=bindparams} </span><span class="cx"> </span><del>-Throughout all these examples, SQLAlchemy is busy creating bind parameters wherever literal expressions occur. You can also specify your own bind parameters with your own names, and use the same statement repeatedly. As mentioned at the top of this section, named bind parameters are always used regardless of the type of DBAPI being used; for DBAPI's that expect positional arguments, bind parameters are converted to lists right before execution, and Pyformat strings in statements, i.e. '%(name)s', are converted to the appropriate positional style. </del><ins>+Throughout all these examples, SQLAlchemy is busy creating bind parameters wherever literal expressions occur. You can also specify your own bind parameters with your own names, and use the same statement repeatedly. The bind parameters, shown here in the "named" format, will be converted to the appropriate named or positional style according to the database implementation being used. </ins><span class="cx"> </span><del>- {python}s = users.select(users.c.user_name==bindparam('username')) </del><ins>+ {python title="Custom Bind Params"} + s = users.select(users.c.user_name==bindparam('username')) + + # execute implicitly </ins><span class="cx"> {sql}s.execute(username='fred') </span><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="cx"> FROM users WHERE users.user_name = :username </span><span class="cx"> {'username': 'fred'} </span><span class="cx"> </span><del>- {sql}s.execute(username='jane') </del><ins>+ # execute explicitly + conn = engine.connect() + {sql}conn.execute(s, username='fred') </ins><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="cx"> FROM users WHERE users.user_name = :username </span><del>- {'username': 'jane'} </del><ins>+ {'username': 'fred'} </ins><span class="cx"> </span><del>- {sql}s.execute(username='mary') - SELECT users.user_id, users.user_name, users.password - FROM users WHERE users.user_name = :username - {'username': 'mary'} </del><span class="cx"> </span><del>-`executemany()` is also available, but that applies more to INSERT/UPDATE/DELETE, described later. </del><ins>+`executemany()` is also available by supplying multiple dictionary arguments instead of keyword arguments to the `execute()` method of `ClauseElement` or `Connection`. Examples can be found later in the sections on INSERT/UPDATE/DELETE. </ins><span class="cx"> </span><del>-The generation of bind parameters is performed specific to the engine being used. The examples in this document all show "named" parameters like those used in sqlite and oracle. Depending on the parameter type specified by the DBAPI module, the correct bind parameter scheme will be used. - </del><span class="cx"> #### Precompiling a Query {@name=precompiling} </span><span class="cx"> </span><span class="cx"> By throwing the `compile()` method onto the end of any query object, the query can be "compiled" by the SQLEngine into a `sqlalchemy.sql.Compiled` object just once, and the resulting compiled object reused, which eliminates repeated internal compilation of the SQL string: </span><span class="lines">@@ -709,24 +780,25 @@ </span><span class="cx"> </span><span class="cx"> One of the primary motivations for a programmatic SQL library is to allow the piecemeal construction of a SQL statement based on program variables. All the above examples typically show Select objects being created all at once. The Select object also includes "builder" methods to allow building up an object. The below example is a "user search" function, where users can be selected based on primary key, user name, street address, keywords, or any combination: </span><span class="cx"> </span><del>- {python}def find_users(id=None, name=None, street=None, keywords=None): - statement = users.select() - if id is not None: - statement.append_whereclause(users.c.user_id==id) - if name is not None: - statement.append_whereclause(users.c.user_name==name) - if street is not None: - # append_whereclause joins "WHERE" conditions together with AND - statement.append_whereclause(users.c.user_id==addresses.c.user_id) - statement.append_whereclause(addresses.c.street==street) - if keywords is not None: - statement.append_from( - users.join(userkeywords, users.c.user_id==userkeywords.c.user_id).join( - keywords, userkeywords.c.keyword_id==keywords.c.keyword_id)) - statement.append_whereclause(keywords.c.name.in_(keywords)) - # to avoid multiple repeats, set query to be DISTINCT: - statement.distinct=True - return statement.execute() </del><ins>+ {python} + def find_users(id=None, name=None, street=None, keywords=None): + statement = users.select() + if id is not None: + statement.append_whereclause(users.c.user_id==id) + if name is not None: + statement.append_whereclause(users.c.user_name==name) + if street is not None: + # append_whereclause joins "WHERE" conditions together with AND + statement.append_whereclause(users.c.user_id==addresses.c.user_id) + statement.append_whereclause(addresses.c.street==street) + if keywords is not None: + statement.append_from( + users.join(userkeywords, users.c.user_id==userkeywords.c.user_id).join( + keywords, userkeywords.c.keyword_id==keywords.c.keyword_id)) + statement.append_whereclause(keywords.c.name.in_(keywords)) + # to avoid multiple repeats, set query to be DISTINCT: + statement.distinct=True + return statement.execute() </ins><span class="cx"> </span><span class="cx"> {sql}find_users(id=7) </span><span class="cx"> SELECT users.user_id, users.user_name, users.password </span><span class="lines">@@ -753,7 +825,8 @@ </span><span class="cx"> </span><span class="cx"> The values to be populated for an INSERT or an UPDATE can be specified to the insert()/update() functions as the `values` named argument, or the query will be compiled based on the values of the parameters sent to the execute() method. </span><span class="cx"> </span><del>- {python}# basic insert </del><ins>+ {python title="Using insert()"} + # basic insert </ins><span class="cx"> {sql}users.insert().execute(user_id=1, user_name='jack', password='asdfdaf') </span><span class="cx"> INSERT INTO users (user_id, user_name, password) </span><span class="cx"> VALUES (:user_id, :user_name, :password) </span><span class="lines">@@ -799,7 +872,8 @@ </span><span class="cx"> </span><span class="cx"> Updates work a lot like INSERTS, except there is an additional WHERE clause that can be specified. </span><span class="cx"> </span><del>- {python}# change 'jack' to 'ed' </del><ins>+ {python title="Using update()"} + # change 'jack' to 'ed' </ins><span class="cx"> {sql}users.update(users.c.user_name=='jack').execute(user_name='ed') </span><span class="cx"> UPDATE users SET user_name=:user_name WHERE users.user_name = :users_user_name </span><span class="cx"> {'users_user_name': 'jack', 'user_name': 'ed'} </span></span></pre></div> <a id="sqlalchemybranchesschemadocdocscss"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/docs.css (1382 => 1383)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/docs.css 2006-05-03 17:53:04 UTC (rev 1382) +++ sqlalchemy/branches/schema/doc/docs.css 2006-05-03 19:15:39 UTC (rev 1383) </span><span class="lines">@@ -88,7 +88,7 @@ </span><span class="cx"> font-size: 12px; </span><span class="cx"> font-weight: bold; </span><span class="cx"> text-decoration:underline; </span><del>- padding:5px; </del><ins>+ padding:5px 5px 5px 0px; </ins><span class="cx"> } </span><span class="cx"> </span><span class="cx"> code { </span></span></pre> </div> </div> </body> </html> |
