[Sqlalchemy-commits] [1137] sqlalchemy/trunk: added scalar subqueries within the column clause of an
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1137] sqlalchemy/trunk: added scalar subqueries within the column clause of another select
|
From: <co...@sq...> - 2006-03-13 17:17:08
|
<!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>[1137] sqlalchemy/trunk: added scalar subqueries within the column clause of another select</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1137</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-03-13 11:16:52 -0600 (Mon, 13 Mar 2006)</dd> </dl> <h3>Log Message</h3> <pre>added scalar subqueries within the column clause of another select</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemytrunkCHANGES">sqlalchemy/trunk/CHANGES</a></li> <li><a href="#sqlalchemytrunkdocbuildcontentmetadatamyt">sqlalchemy/trunk/doc/build/content/metadata.myt</a></li> <li><a href="#sqlalchemytrunkdocbuildcontentsqlconstructionmyt">sqlalchemy/trunk/doc/build/content/sqlconstruction.myt</a></li> <li><a href="#sqlalchemytrunklibsqlalchemyansisqlpy">sqlalchemy/trunk/lib/sqlalchemy/ansisql.py</a></li> <li><a href="#sqlalchemytrunklibsqlalchemysqlpy">sqlalchemy/trunk/lib/sqlalchemy/sql.py</a></li> <li><a href="#sqlalchemytrunktestselectpy">sqlalchemy/trunk/test/select.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemytrunkCHANGES"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/CHANGES (1136 => 1137)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/CHANGES 2006-03-13 07:16:53 UTC (rev 1136) +++ sqlalchemy/trunk/CHANGES 2006-03-13 17:16:52 UTC (rev 1137) </span><span class="lines">@@ -1,6 +1,7 @@ </span><span class="cx"> 0.1.4 </span><del>-- create_engine() now uses genericized parameters; host/hostname, db/dbname/database, password/passwd, etc. for all engine connections. makes -engine URIs much more "universal" </del><ins>+- create_engine() now uses genericized parameters; host/hostname, db/dbname/database, password/passwd, etc. for all engine connections. makes engine URIs much more "universal" +- added support for SELECT statements embedded into a column clause, using the flag +"scalar=True" </ins><span class="cx"> - another overhaul to EagerLoading when used in conjunction with mappers that </span><span class="cx"> inherit; improvements to eager loads figuring out their aliased queries </span><span class="cx"> correctly, also relations set up against a mapper with inherited mappers will </span></span></pre></div> <a id="sqlalchemytrunkdocbuildcontentmetadatamyt"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/doc/build/content/metadata.myt (1136 => 1137)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/doc/build/content/metadata.myt 2006-03-13 07:16:53 UTC (rev 1136) +++ sqlalchemy/trunk/doc/build/content/metadata.myt 2006-03-13 17:16:52 UTC (rev 1137) </span><span class="lines">@@ -307,15 +307,15 @@ </span><span class="cx"> </span><span class="cx"> <&|doclib.myt:item, name="primitives", description="Non-engine primitives: TableClause/ColumnClause" &> </span><span class="cx"> </span><del>- <p>TableClause and ColumnClause are "primitive" versions of the Table and Column objects which dont use engines at all; applications that just want to generate SQL strings but not directly communicate with a database can use TableClause and ColumnClause objects, which are non-singleton and serve as the "lexical" base class of Table and Column:</p> </del><ins>+ <p>TableClause and ColumnClause are "primitive" versions of the Table and Column objects which dont use engines at all; applications that just want to generate SQL strings but not directly communicate with a database can use TableClause and ColumnClause objects (accessed via 'table' and 'column'), which are non-singleton and serve as the "lexical" base class of Table and Column:</p> </ins><span class="cx"> <&|formatting.myt:code&> </span><del>- tab1 = TableClause('table1', - ColumnClause('id'), - ColumnClause('name')) </del><ins>+ tab1 = table('table1', + column('id'), + column('name')) </ins><span class="cx"> </span><del>- tab2 = TableClause('table2', - ColumnClause('id'), - ColumnClause('email')) </del><ins>+ tab2 = table('table2', + column('id'), + column('email')) </ins><span class="cx"> </span><span class="cx"> tab1.select(tab1.c.name == 'foo') </span><span class="cx"> </&> </span></span></pre></div> <a id="sqlalchemytrunkdocbuildcontentsqlconstructionmyt"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/doc/build/content/sqlconstruction.myt (1136 => 1137)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/doc/build/content/sqlconstruction.myt 2006-03-13 07:16:53 UTC (rev 1136) +++ sqlalchemy/trunk/doc/build/content/sqlconstruction.myt 2006-03-13 17:16:52 UTC (rev 1137) </span><span class="lines">@@ -593,9 +593,21 @@ </span><span class="cx"> </span><span class="cx"> <P>The sql package supports embedding select statements into other select statements as the criterion in a WHERE condition, or as one of the "selectable" objects in the FROM list of the query. It does not at the moment directly support embedding a SELECT statement as one of the column criterion for a statement, although this can be achieved via direct text insertion, described later.</p> </span><span class="cx"> </span><ins>+ <&|doclib.myt:item, name="scalar", description="Scalar Column Queries"&> + <p>Subqueries can be used in the column clause of a select statement by specifying the <span class="codeline">scalar=True</span> flag:</p> + <&|formatting.myt:code &> +<&formatting.myt:poplink&>select([table2.c.col1, table2.c.col2, select([table1.c.col1], table1.c.col2==7, scalar=True)]) +<&|formatting.myt:codepopper, link="sql" &> +SELECT table2.col1, table2.col2, +(SELECT table1.col1 AS col1 FROM table1 WHERE col2=:table1_col2) +FROM table2 +{'table1_col2': 7} +</&> + </&> + </&> </ins><span class="cx"> </span><span class="cx"> <&|doclib.myt:item, name="correlated", description="Correlated Subqueries" &> </span><del>- <P>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. </del><ins>+ <P>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 <span class="codeline">correlate=False</span> to the Select statement.</p> </ins><span class="cx"> <&|formatting.myt:code &> </span><span class="cx"> # make an alias of a regular select. </span><span class="cx"> s = select([addresses.c.street], addresses.c.user_id==users.c.user_id).alias('s') </span></span></pre></div> <a id="sqlalchemytrunklibsqlalchemyansisqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/lib/sqlalchemy/ansisql.py (1136 => 1137)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/lib/sqlalchemy/ansisql.py 2006-03-13 07:16:53 UTC (rev 1136) +++ sqlalchemy/trunk/lib/sqlalchemy/ansisql.py 2006-03-13 17:16:52 UTC (rev 1137) </span><span class="lines">@@ -280,7 +280,11 @@ </span><span class="cx"> </span><span class="cx"> self.select_stack.append(select) </span><span class="cx"> for c in select._raw_columns: </span><del>- if c.is_selectable(): </del><ins>+ # TODO: make this polymorphic? + if isinstance(c, sql.Select) and c._scalar: + c.accept_visitor(self) + inner_columns[self.get_str(c)] = c + elif c.is_selectable(): </ins><span class="cx"> for co in c.columns: </span><span class="cx"> if select.use_labels: </span><span class="cx"> l = co.label(co._label) </span></span></pre></div> <a id="sqlalchemytrunklibsqlalchemysqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/lib/sqlalchemy/sql.py (1136 => 1137)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/lib/sqlalchemy/sql.py 2006-03-13 07:16:53 UTC (rev 1136) +++ sqlalchemy/trunk/lib/sqlalchemy/sql.py 2006-03-13 17:16:52 UTC (rev 1137) </span><span class="lines">@@ -55,6 +55,10 @@ </span><span class="cx"> """ </span><span class="cx"> return Select(columns, whereclause = whereclause, from_obj = from_obj, **kwargs) </span><span class="cx"> </span><ins>+def subquery(alias, *args, **kwargs): + return Select(*args, **kwargs).alias(alias) + + </ins><span class="cx"> def insert(table, values = None, **kwargs): </span><span class="cx"> """returns an INSERT clause element. </span><span class="cx"> </span><span class="lines">@@ -142,9 +146,6 @@ </span><span class="cx"> def alias(*args, **params): </span><span class="cx"> return Alias(*args, **params) </span><span class="cx"> </span><del>-def subquery(alias, *args, **params): - return Alias(Select(*args, **params), alias) - </del><span class="cx"> def literal(value, type=None): </span><span class="cx"> """returns a literal clause, bound to a bind parameter. </span><span class="cx"> </span><span class="lines">@@ -346,7 +347,8 @@ </span><span class="cx"> """base class for elements of a programmatically constructed SQL expression.""" </span><span class="cx"> def _get_from_objects(self): </span><span class="cx"> """returns objects represented in this ClauseElement that should be added to the </span><del>- FROM list of a query.""" </del><ins>+ FROM list of a query, when this ClauseElement is placed in the column clause of a Select + statement.""" </ins><span class="cx"> raise NotImplementedError(repr(self)) </span><span class="cx"> def _process_from_dict(self, data, asfrom): </span><span class="cx"> """given a dictionary attached to a Select object, places the appropriate </span><span class="lines">@@ -925,7 +927,8 @@ </span><span class="cx"> return [] </span><span class="cx"> </span><span class="cx"> def alias(self, name=None): </span><del>- return self.select(use_labels=True).alias(name) </del><ins>+ """creates a Select out of this Join clause and returns an Alias of it. The Select is not correlating.""" + return self.select(use_labels=True, correlate=False).alias(name) </ins><span class="cx"> def _process_from_dict(self, data, asfrom): </span><span class="cx"> for f in self.onclause._get_from_objects(): </span><span class="cx"> data[f.id] = f </span><span class="lines">@@ -1148,7 +1151,7 @@ </span><span class="cx"> def select(self, whereclauses = None, **params): </span><span class="cx"> return select([self], whereclauses, **params) </span><span class="cx"> def _get_from_objects(self): </span><del>- if self.is_where: </del><ins>+ if self.is_where or self._scalar: </ins><span class="cx"> return [] </span><span class="cx"> else: </span><span class="cx"> return [self] </span><span class="lines">@@ -1196,7 +1199,7 @@ </span><span class="cx"> class Select(SelectBaseMixin, FromClause): </span><span class="cx"> """represents a SELECT statement, with appendable clauses, as well as </span><span class="cx"> the ability to execute itself and return a result set.""" </span><del>- def __init__(self, columns=None, whereclause = None, from_obj = [], order_by = None, group_by=None, having=None, use_labels = False, distinct=False, engine = None, limit=None, offset=None, correlate=False): </del><ins>+ def __init__(self, columns=None, whereclause = None, from_obj = [], order_by = None, group_by=None, having=None, use_labels = False, distinct=False, engine = None, limit=None, offset=None, scalar=False, correlate=True): </ins><span class="cx"> self._froms = util.OrderedDict() </span><span class="cx"> self.use_labels = use_labels </span><span class="cx"> self.id = "Select(%d)" % id(self) </span><span class="lines">@@ -1207,14 +1210,23 @@ </span><span class="cx"> self.oid_column = None </span><span class="cx"> self.limit = limit </span><span class="cx"> self.offset = offset </span><ins>+ + # indicates that this select statement should not expand its columns + # into the column clause of an enclosing select, and should instead + # act like a single scalar column + self._scalar = scalar + + # indicates if this select statement, as a subquery, should correlate + # its FROM clause to that of an enclosing select statement </ins><span class="cx"> self.correlate = correlate </span><span class="cx"> </span><span class="cx"> # indicates if this select statement is a subquery inside another query </span><span class="cx"> self.issubquery = False </span><ins>+ </ins><span class="cx"> # indicates if this select statement is a subquery as a criterion </span><span class="cx"> # inside of a WHERE clause </span><span class="cx"> self.is_where = False </span><del>- </del><ins>+ </ins><span class="cx"> self.distinct = distinct </span><span class="cx"> self._text = None </span><span class="cx"> self._raw_columns = [] </span><span class="lines">@@ -1257,7 +1269,7 @@ </span><span class="cx"> select.is_where = self.is_where </span><span class="cx"> select.issubquery = True </span><span class="cx"> select.parens = True </span><del>- if not self.is_where and not select.correlate: </del><ins>+ if not select.correlate: </ins><span class="cx"> return </span><span class="cx"> if getattr(select, '_correlated', None) is None: </span><span class="cx"> select._correlated = self.select._froms </span><span class="lines">@@ -1268,6 +1280,11 @@ </span><span class="cx"> </span><span class="cx"> self._raw_columns.append(column) </span><span class="cx"> </span><ins>+ # if the column is a Select statement itself, + # accept visitor + column.accept_visitor(self._correlator) + + # visit the FROM objects of the column looking for more Selects </ins><span class="cx"> for f in column._get_from_objects(): </span><span class="cx"> f.accept_visitor(self._correlator) </span><span class="cx"> column._process_from_dict(self._froms, False) </span><span class="lines">@@ -1278,7 +1295,6 @@ </span><span class="cx"> return column._make_proxy(self, name=column._label) </span><span class="cx"> else: </span><span class="cx"> return column._make_proxy(self, name=column.name) </span><del>- </del><span class="cx"> def append_whereclause(self, whereclause): </span><span class="cx"> self._append_condition('whereclause', whereclause) </span><span class="cx"> def append_having(self, having): </span></span></pre></div> <a id="sqlalchemytrunktestselectpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/test/select.py (1136 => 1137)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/test/select.py 2006-03-13 07:16:53 UTC (rev 1136) +++ sqlalchemy/trunk/test/select.py 2006-03-13 17:16:52 UTC (rev 1137) </span><span class="lines">@@ -75,9 +75,11 @@ </span><span class="cx"> self.runtest(select([table1, table2]), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, \ </span><span class="cx"> myothertable.othername FROM mytable, myothertable") </span><span class="cx"> </span><del>- def testsubquery(self): </del><ins>+ def testselectselect(self): + """tests placing select statements in the column clause of another select, for the + purposes of selecting from the exported columns of that select.""" </ins><span class="cx"> s = select([table1], table1.c.name == 'jack') </span><del>- print [key for key in s.c.keys()] </del><ins>+ #print [key for key in s.c.keys()] </ins><span class="cx"> self.runtest( </span><span class="cx"> select( </span><span class="cx"> [s], </span><span class="lines">@@ -92,10 +94,9 @@ </span><span class="cx"> "SELECT myid, name, description FROM (SELECT mytable.myid AS myid, mytable.name AS name, mytable.description AS description FROM mytable)" </span><span class="cx"> ) </span><span class="cx"> </span><del>- sq = subquery( - 'sq', </del><ins>+ sq = select( </ins><span class="cx"> [table1], </span><del>- ) </del><ins>+ ).alias('sq') </ins><span class="cx"> </span><span class="cx"> self.runtest( </span><span class="cx"> sq.select(sq.c.myid == 7), </span><span class="lines">@@ -103,12 +104,11 @@ </span><span class="cx"> (SELECT mytable.myid AS myid, mytable.name AS name, mytable.description AS description FROM mytable) AS sq WHERE sq.myid = :sq_myid" </span><span class="cx"> ) </span><span class="cx"> </span><del>- sq = subquery( - 'sq', </del><ins>+ sq = select( </ins><span class="cx"> [table1, table2], </span><span class="cx"> and_(table1.c.myid ==7, table2.c.otherid==table1.c.myid), </span><span class="cx"> use_labels = True </span><del>- ) </del><ins>+ ).alias('sq') </ins><span class="cx"> </span><span class="cx"> sqstring = "SELECT mytable.myid AS mytable_myid, mytable.name AS mytable_name, \ </span><span class="cx"> mytable.description AS mytable_description, myothertable.otherid AS myothertable_otherid, \ </span><span class="lines">@@ -118,19 +118,75 @@ </span><span class="cx"> self.runtest(sq.select(), "SELECT sq.mytable_myid, sq.mytable_name, sq.mytable_description, sq.myothertable_otherid, \ </span><span class="cx"> sq.myothertable_othername FROM (" + sqstring + ") AS sq") </span><span class="cx"> </span><del>- sq2 = subquery( - 'sq2', </del><ins>+ sq2 = select( </ins><span class="cx"> [sq], </span><span class="cx"> use_labels = True </span><del>- ) </del><ins>+ ).alias('sq2') </ins><span class="cx"> </span><span class="cx"> self.runtest(sq2.select(), "SELECT sq2.sq_mytable_myid, sq2.sq_mytable_name, sq2.sq_mytable_description, \ </span><span class="cx"> sq2.sq_myothertable_otherid, sq2.sq_myothertable_othername FROM \ </span><span class="cx"> (SELECT sq.mytable_myid AS sq_mytable_myid, sq.mytable_name AS sq_mytable_name, \ </span><span class="cx"> sq.mytable_description AS sq_mytable_description, sq.myothertable_otherid AS sq_myothertable_otherid, \ </span><span class="cx"> sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") AS sq) AS sq2") </span><ins>+ + def testwheresubquery(self): + self.runtest( + table1.select(table1.c.myid == select([table2.c.otherid], table1.c.name == table2.c.othername)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.name = myothertable.othername)" + ) + + self.runtest( + table1.select(exists([1], table2.c.otherid == table1.c.myid)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = mytable.myid)" + ) + + talias = table1.alias('ta') + s = subquery('sq2', [talias], exists([1], table2.c.otherid == talias.c.myid)) + self.runtest( + select([s, table1]) + ,"SELECT sq2.myid, sq2.name, sq2.description, mytable.myid, mytable.name, mytable.description FROM (SELECT ta.myid AS myid, ta.name AS name, ta.description AS description FROM mytable AS ta WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = ta.myid)) AS sq2, mytable") + + s = select([addresses.c.street], addresses.c.user_id==users.c.user_id, correlate=True).alias('s') + self.runtest( + select([users, s.c.street], from_obj=[s]), + """SELECT users.user_id, users.user_name, users.password, s.street FROM users, (SELECT addresses.street AS street FROM addresses WHERE addresses.user_id = users.user_id) AS s""") </ins><span class="cx"> </span><ins>+ def testcolumnsubquery(self): + s = select([table1.c.myid], scalar=True, correlate=False) + self.runtest(select([table1, s]), "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid AS myid FROM mytable) FROM mytable") + + s = select([table1.c.myid], scalar=True) + self.runtest(select([table2, s]), "SELECT myothertable.otherid, myothertable.othername, (SELECT mytable.myid AS myid FROM mytable) FROM myothertable") </ins><span class="cx"> </span><ins>+ + zips = table('zips', + column('zipcode'), + column('latitude'), + column('longitude'), + ) + places = table('places', + column('id'), + column('nm') + ) + zip = '12345' + qlat = select([zips.c.latitude], zips.c.zipcode == zip, scalar=True, correlate=False) + qlng = select([zips.c.longitude], zips.c.zipcode == zip, scalar=True, correlate=False) + + q = select([places.c.id, places.c.nm, zips.c.zipcode, func.latlondist(qlat, qlng).label('dist')], + zips.c.zipcode==zip, + order_by = ['dist', places.c.nm] + ) + + self.runtest(q,"SELECT places.id, places.nm, zips.zipcode, latlondist((SELECT zips.latitude AS latitude FROM zips WHERE zips.zipcode = :zips_zipcode_1), (SELECT zips.longitude AS longitude FROM zips WHERE zips.zipcode = :zips_zipcode_2)) AS dist FROM places, zips WHERE zips.zipcode = :zips_zipcode ORDER BY dist, places.nm") + + zalias = zips.alias('main_zip') + qlat = select([zips.c.latitude], zips.c.zipcode == zalias.c.zipcode, scalar=True) + qlng = select([zips.c.longitude], zips.c.zipcode == zalias.c.zipcode, scalar=True) + q = select([places.c.id, places.c.nm, zalias.c.zipcode, func.latlondist(qlat, qlng).label('dist')], + order_by = ['dist', places.c.nm] + ) + self.runtest(q, "SELECT places.id, places.nm, main_zip.zipcode, latlondist((SELECT zips.latitude AS latitude FROM zips WHERE zips.zipcode = main_zip.zipcode), (SELECT zips.longitude AS longitude FROM zips WHERE zips.zipcode = main_zip.zipcode)) AS dist FROM places, zips AS main_zip ORDER BY dist, places.nm") + </ins><span class="cx"> def testand(self): </span><span class="cx"> self.runtest( </span><span class="cx"> select(['*'], and_(table1.c.myid == 12, table1.c.name=='asdf', table2.c.othername == 'foo', "sysdate() = today()")), </span><span class="lines">@@ -410,28 +466,7 @@ </span><span class="cx"> c = s.compile(parameters = {'test' : 7}, engine=db) </span><span class="cx"> self.assert_(c.get_params() == {'test' : 7}) </span><span class="cx"> </span><del>- def testcorrelatedsubquery(self): - self.runtest( - table1.select(table1.c.myid == select([table2.c.otherid], table1.c.name == table2.c.othername)), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.name = myothertable.othername)" - ) </del><span class="cx"> </span><del>- self.runtest( - table1.select(exists([1], table2.c.otherid == table1.c.myid)), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = mytable.myid)" - ) - - talias = table1.alias('ta') - s = subquery('sq2', [talias], exists([1], table2.c.otherid == talias.c.myid)) - self.runtest( - select([s, table1]) - ,"SELECT sq2.myid, sq2.name, sq2.description, mytable.myid, mytable.name, mytable.description FROM (SELECT ta.myid AS myid, ta.name AS name, ta.description AS description FROM mytable AS ta WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = ta.myid)) AS sq2, mytable") - - s = select([addresses.c.street], addresses.c.user_id==users.c.user_id, correlate=True).alias('s') - self.runtest( - select([users, s.c.street], from_obj=[s]), - """SELECT users.user_id, users.user_name, users.password, s.street FROM users, (SELECT addresses.street AS street FROM addresses WHERE addresses.user_id = users.user_id) AS s""") - </del><span class="cx"> def testin(self): </span><span class="cx"> self.runtest(select([table1], table1.c.myid.in_(1, 2, 3)), </span><span class="cx"> "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1, :mytable_myid_2)") </span></span></pre> </div> </div> </body> </html> |
