[Sqlalchemy-commits] [1256] sqlalchemy/trunk: Added cast() to allow use of cast(tbl.c.col as Numeric
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1256] sqlalchemy/trunk: Added cast() to allow use of cast(tbl.c.col as Numeric(4,2)) in select and where clauses.
|
From: <co...@sq...> - 2006-04-04 00:29:18
|
<!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>[1256] sqlalchemy/trunk: Added cast() to allow use of cast(tbl.c.col as Numeric(4,2)) in select and where clauses.</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1256</dd> <dt>Author</dt> <dd>rtl</dd> <dt>Date</dt> <dd>2006-04-03 19:28:33 -0500 (Mon, 03 Apr 2006)</dd> </dl> <h3>Log Message</h3> <pre>Added cast() to allow use of cast(tbl.c.col as Numeric(4,2)) in select and where clauses. Unit tests for same.</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemytrunklibsqlalchemysqlpy">sqlalchemy/trunk/lib/sqlalchemy/sql.py</a></li> <li><a href="#sqlalchemytrunktestselectpy">sqlalchemy/trunk/test/select.py</a></li> </ul> <h3>Property Changed</h3> <ul> <li>sqlalchemy/trunk/</li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemytrunk"></a> <div class="propset"><h4>Property changes: sqlalchemy/trunk</h4> <pre class="diff"><span> <span class="cx">Name: svk:merge </span><span class="cx"> - 8b31e7cb-c107-0410-ae80-e86de58984b7:/local/sqlalchemy:2426 </span><span class="cx"> + 8b31e7cb-c107-0410-ae80-e86de58984b7:/local/sqlalchemy:2709 </span></span></pre></div> <a id="sqlalchemytrunklibsqlalchemysqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/lib/sqlalchemy/sql.py (1255 => 1256)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/lib/sqlalchemy/sql.py 2006-04-03 22:48:28 UTC (rev 1255) +++ sqlalchemy/trunk/lib/sqlalchemy/sql.py 2006-04-04 00:28:33 UTC (rev 1256) </span><span class="lines">@@ -13,7 +13,7 @@ </span><span class="cx"> import string, re, random </span><span class="cx"> types = __import__('types') </span><span class="cx"> </span><del>-__all__ = ['text', 'table', 'column', 'func', 'select', 'update', 'insert', 'delete', 'join', 'and_', 'or_', 'not_', 'union', 'union_all', 'null', 'desc', 'asc', 'outerjoin', 'alias', 'subquery', 'literal', 'bindparam', 'exists'] </del><ins>+__all__ = ['text', 'table', 'column', 'func', 'select', 'update', 'insert', 'delete', 'join', 'and_', 'or_', 'not_', 'between_', 'cast', 'union', 'union_all', 'null', 'desc', 'asc', 'outerjoin', 'alias', 'subquery', 'literal', 'bindparam', 'exists'] </ins><span class="cx"> </span><span class="cx"> def desc(column): </span><span class="cx"> """returns a descending ORDER BY clause element, e.g.: </span><span class="lines">@@ -132,6 +132,25 @@ </span><span class="cx"> """ returns BETWEEN predicate clause (clausetest BETWEEN clauseleft AND clauseright) """ </span><span class="cx"> return BooleanExpression(ctest, and_(cleft, cright), 'BETWEEN') </span><span class="cx"> </span><ins>+def cast(clause, totype, **kwargs): + """ returns CAST function CAST(clause AS totype) + Use with a sqlalchemy.types.TypeEngine object, i.e + cast(table.c.unit_price * table.c.qty, Numeric(10,4)) + or + cast(table.c.timestamp, DATE) + """ + engine = kwargs.get('engine', None) + if engine is None: + engine = getattr(clause, 'engine', None) + if engine is not None: + totype_desc = engine.type_descriptor(totype) + # handle non-column clauses (e.g. cast(1234, TEXT) + if not hasattr(clause, 'label'): + clause = literal(clause) + return Function('CAST', clause.label(totype_desc.get_col_spec()), type=totype, **kwargs) + else: + raise InvalidRequestError("No engine available, cannot generate cast for " + str(clause) + " to type " + str(totype)) + </ins><span class="cx"> def exists(*args, **params): </span><span class="cx"> params['correlate'] = True </span><span class="cx"> s = select(*args, **params) </span></span></pre></div> <a id="sqlalchemytrunktestselectpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/test/select.py (1255 => 1256)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/test/select.py 2006-04-03 22:48:28 UTC (rev 1255) +++ sqlalchemy/trunk/test/select.py 2006-04-04 00:28:33 UTC (rev 1256) </span><span class="lines">@@ -4,6 +4,7 @@ </span><span class="cx"> import sqlalchemy.databases.postgres as postgres </span><span class="cx"> import sqlalchemy.databases.oracle as oracle </span><span class="cx"> import sqlalchemy.databases.sqlite as sqlite </span><ins>+import sqlalchemy.databases.mysql as mysql </ins><span class="cx"> </span><span class="cx"> db = ansisql.engine() </span><span class="cx"> #db = create_engine('mssql') </span><span class="lines">@@ -532,6 +533,34 @@ </span><span class="cx"> </span><span class="cx"> self.runtest(table1.select(table1.c.name=='jack'), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid AND mytable.name = :mytable_name", params={'myid':'3', 'name':'fred'}) </span><span class="cx"> </span><ins>+ def testcast(self): + tbl = table('casttest', + Column('id', Integer), + Column('v1', Float), + Column('v2', Float), + Column('ts', TIMESTAMP), + ) + + def check_results(engine, expected_results, literal): + self.assertEqual(len(expected_results), 5, 'Incorrect number of expected results') + self.assertEqual(str(cast(tbl.c.v1, Numeric, engine=engine)), 'CAST(casttest.v1 AS %s)' %expected_results[0]) + self.assertEqual(str(cast(tbl.c.v1, Numeric(12, 9), engine=engine)), 'CAST(casttest.v1 AS %s)' %expected_results[1]) + self.assertEqual(str(cast(tbl.c.ts, Date, engine=engine)), 'CAST(casttest.ts AS %s)' %expected_results[2]) + self.assertEqual(str(cast(1234, TEXT, engine=engine)), 'CAST(%s AS %s)' %(literal, expected_results[3])) + self.assertEqual(str(cast('test', String(20), engine=engine)), 'CAST(%s AS %s)' %(literal, expected_results[4])) + + # first test with Postgres engine + check_results(postgres.engine({}), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%(literal)s') + + # then the Oracle engine + check_results(oracle.engine({}, use_ansi = False), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20)'], ':literal') + + # then the sqlite engine + check_results(sqlite.engine({}), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '?') + + # and the MySQL engine + check_results(mysql.engine({}), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%s') + </ins><span class="cx"> class CRUDTest(SQLTest): </span><span class="cx"> def testinsert(self): </span><span class="cx"> # generic insert, will create bind params for all columns </span></span></pre> </div> </div> </body> </html> |