Files changed (1)
<p><b>Note:</b> This section describes how to use SQLAlchemy to construct SQL queries and receive result sets. It does <b>not</b> cover the object relational mapping capabilities of SQLAlchemy; that is covered later on in <&formatting.myt:link, path="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 <&formatting.myt:link, path="sql_select_whereclause"&> construction before moving on.</p>
<p>Once you have used the <span class="codeline">sqlalchemy.schema</span> 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 <span class="codeline">sqlalchemy.sql</span> 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 <span class="codeline">sqlalchemy.sql.ClauseElement</span>. A full query is represented via a structure of ClauseElements. A set of reasonably intuitive creation functions is provided by the <span class="codeline">sqlalchemy.sql</span> package to create these structures; these functions are described in the rest of this section. </p>
- <p>To execute a query, you create its structure, then call the resulting structure's <span class="codeline">execute()</span> method, which returns a cursor-like object (more on that later). This method can be repeated as necessary. A ClauseElement is compiled into a string representation by an underlying SQLEngine object; the ClauseElement locates this engine by searching through its child items for a Table object, which provides a reference to its SQLEngine.
+ <p>To execute a query, you create its structure, then call the resulting structure's <span class="codeline">execute()</span> 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.
- <p>In all examples, bind parameters are illustrated as dictionaries. SQLAlchemy supports DBAPI's which require positional parameters by converting named parameter dictionaries into lists right before execution; so SQLAlchemy functions and methods always expect named parameter dictionaries regardless of DBAPI being used. In the case of specifying literal SQL strings with positional DBAPIs, it is safe to use "Pyformat" arguments (i.e. "%(name)s"), which are internally converted upon compilation into "?", "%s", or "<% ":<number>"|h%>".</p>
+ <p>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.</p>