0001"""
0002sqlobject.sqlbuilder
0003--------------------
0004
0005:author: Ian Bicking <ianb@colorstudy.com>
0006
0007Builds SQL expressions from normal Python expressions.
0008
0009Disclaimer
0010----------
0011
0012This program is free software; you can redistribute it and/or modify
0013it under the terms of the GNU Lesser General Public License as
0014published by the Free Software Foundation; either version 2.1 of the
0015License, or (at your option any later version.
0016
0017This program is distributed in the hope that it will be useful,
0018but WITHOUT ANY WARRANTY; without even the implied warranty of
0019MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0020GNU General Public License for more details.
0021
0022You should have received a copy of the GNU Lesser General Public
0023License along with this program; if not, write to the Free Software
0024Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
0025USA.
0026
0027Instructions
0028------------
0029
0030To begin a SQL expression, you must use some sort of SQL object -- a
0031field, table, or SQL statement (``SELECT``, ``INSERT``, etc.) You can
0032then use normal operators, with the exception of: `and`, `or`, `not`,
0033and `in`. You can use the `AND`, `OR`, `NOT`, and `IN` functions
0034instead, or you can also use `&`, `|`, and `~` for `and`, `or`, and
0035`not` respectively (however -- the precidence for these operators
0036doesn't work as you would want, so you must use many parenthesis).
0037
0038To create a sql field, table, or constant/function, use the namespaces
0039`table`, `const`, and `func`. For instance, ``table.address`` refers
0040to the ``address`` table, and ``table.address.state`` refers to the
0041``state`` field in the address table. ``const.NULL`` is the ``NULL``
0042SQL constant, and ``func.NOW()`` is the ``NOW()`` function call
0043(`const` and `func` are actually identicle, but the two names are
0044provided for clarity). Once you create this object, expressions
0045formed with it will produce SQL statements.
0046
0047The ``sqlrepr(obj)`` function gets the SQL representation of these
0048objects, as well as the proper SQL representation of basic Python
0049types (None==NULL).
0050
0051There are a number of DB-specific SQL features that this does not
0052implement. There are a bunch of normal ANSI features also not present.
0053
0054See the bottom of this module for some examples, and run it (i.e.
0055``python sql.py``) to see the results of those examples.
0056
0057"""
0058
0059
0060
0061
0062
0063import fnmatch
0064import operator
0065import re
0066import threading
0067import types
0068import weakref
0069
0070import classregistry
0071from converters import registerConverter, sqlrepr, quote_str, unquote_str
0072
0073
0074class VersionError(Exception):
0075 pass
0076class NoDefault:
0077 pass
0078
0079
0080class SQLObjectState(object):
0081 def __init__(self, soObject, connection=None):
0082 self.soObject = weakref.proxy(soObject)
0083 self.connection = connection
0084
0085
0086safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
0087def sqlIdentifier(obj):
0088
0089 return isinstance(obj, basestring) and bool(safeSQLRE.search(obj.strip()))
0090
0091
0092def execute(expr, executor):
0093 if hasattr(expr, 'execute'):
0094 return expr.execute(executor)
0095 else:
0096 return expr
0097
0098
0099def _str_or_sqlrepr(expr, db):
0100 if isinstance(expr, basestring):
0101 return expr
0102 return sqlrepr(expr, db)
0103
0104
0105
0106
0107
0108class SQLExpression:
0109 def __add__(self, other):
0110 return SQLOp("+", self, other)
0111 def __radd__(self, other):
0112 return SQLOp("+", other, self)
0113 def __sub__(self, other):
0114 return SQLOp("-", self, other)
0115 def __rsub__(self, other):
0116 return SQLOp("-", other, self)
0117 def __mul__(self, other):
0118 return SQLOp("*", self, other)
0119 def __rmul__(self, other):
0120 return SQLOp("*", other, self)
0121 def __div__(self, other):
0122 return SQLOp("/", self, other)
0123 def __rdiv__(self, other):
0124 return SQLOp("/", other, self)
0125 def __pos__(self):
0126 return SQLPrefix("+", self)
0127 def __neg__(self):
0128 return SQLPrefix("-", self)
0129 def __pow__(self, other):
0130 return SQLConstant("POW")(self, other)
0131 def __rpow__(self, other):
0132 return SQLConstant("POW")(other, self)
0133 def __abs__(self):
0134 return SQLConstant("ABS")(self)
0135 def __mod__(self, other):
0136 return SQLModulo(self, other)
0137 def __rmod__(self, other):
0138 return SQLConstant("MOD")(other, self)
0139
0140 def __lt__(self, other):
0141 return SQLOp("<", self, other)
0142 def __le__(self, other):
0143 return SQLOp("<=", self, other)
0144 def __gt__(self, other):
0145 return SQLOp(">", self, other)
0146 def __ge__(self, other):
0147 return SQLOp(">=", self, other)
0148 def __eq__(self, other):
0149 if other is None:
0150 return ISNULL(self)
0151 else:
0152 return SQLOp("=", self, other)
0153 def __ne__(self, other):
0154 if other is None:
0155 return ISNOTNULL(self)
0156 else:
0157 return SQLOp("<>", self, other)
0158
0159 def __and__(self, other):
0160 return SQLOp("AND", self, other)
0161 def __rand__(self, other):
0162 return SQLOp("AND", other, self)
0163 def __or__(self, other):
0164 return SQLOp("OR", self, other)
0165 def __ror__(self, other):
0166 return SQLOp("OR", other, self)
0167 def __invert__(self):
0168 return SQLPrefix("NOT", self)
0169
0170 def __call__(self, *args):
0171 return SQLCall(self, args)
0172
0173 def __repr__(self):
0174 try:
0175 return self.__sqlrepr__(None)
0176 except AssertionError:
0177 return '<%s %s>' % (
0178 self.__class__.__name__, hex(id(self))[2:])
0179
0180 def __str__(self):
0181 return repr(self)
0182
0183 def __cmp__(self, other):
0184 raise VersionError, "Python 2.1+ required"
0185 def __rcmp__(self, other):
0186 raise VersionError, "Python 2.1+ required"
0187
0188 def startswith(self, s):
0189 return STARTSWITH(self, s)
0190 def endswith(self, s):
0191 return ENDSWITH(self, s)
0192 def contains(self, s):
0193 return CONTAINSSTRING(self, s)
0194
0195 def components(self):
0196 return []
0197
0198 def tablesUsed(self, db):
0199 return self.tablesUsedSet(db)
0200 def tablesUsedSet(self, db):
0201 tables = set()
0202 for table in self.tablesUsedImmediate():
0203 if hasattr(table, '__sqlrepr__'):
0204 table = sqlrepr(table, db)
0205 tables.add(table)
0206 for component in self.components():
0207 tables.update(tablesUsedSet(component, db))
0208 return tables
0209 def tablesUsedImmediate(self):
0210 return []
0211
0212
0213
0214
0215
0216def SQLExprConverter(value, db):
0217 return value.__sqlrepr__()
0218
0219registerConverter(SQLExpression, SQLExprConverter)
0220
0221def tablesUsedSet(obj, db):
0222 if hasattr(obj, "tablesUsedSet"):
0223 return obj.tablesUsedSet(db)
0224 else:
0225 return {}
0226
0227operatorMap = {
0228 "+": operator.add,
0229 "/": operator.div,
0230 "-": operator.sub,
0231 "*": operator.mul,
0232 "<": operator.lt,
0233 "<=": operator.le,
0234 "=": operator.eq,
0235 "!=": operator.ne,
0236 ">=": operator.ge,
0237 ">": operator.gt,
0238 "IN": operator.contains,
0239 "IS": operator.eq,
0240 }
0241
0242class SQLOp(SQLExpression):
0243 def __init__(self, op, expr1, expr2):
0244 self.op = op.upper()
0245 self.expr1 = expr1
0246 self.expr2 = expr2
0247 def __sqlrepr__(self, db):
0248 s1 = sqlrepr(self.expr1, db)
0249 s2 = sqlrepr(self.expr2, db)
0250 if s1[0] != '(' and s1 != 'NULL':
0251 s1 = '(' + s1 + ')'
0252 if s2[0] != '(' and s2 != 'NULL':
0253 s2 = '(' + s2 + ')'
0254 return "(%s %s %s)" % (s1, self.op, s2)
0255 def components(self):
0256 return [self.expr1, self.expr2]
0257 def execute(self, executor):
0258 if self.op == "AND":
0259 return execute(self.expr1, executor) and execute(self.expr2, executor)
0261 elif self.op == "OR":
0262 return execute(self.expr1, executor) or execute(self.expr2, executor)
0264 else:
0265 return operatorMap[self.op.upper()](execute(self.expr1, executor),
0266 execute(self.expr2, executor))
0267
0268class SQLModulo(SQLOp):
0269 def __init__(self, expr1, expr2):
0270 SQLOp.__init__(self, '%', expr1, expr2)
0271 def __sqlrepr__(self, db):
0272 if db == 'sqlite':
0273 return SQLOp.__sqlrepr__(self, db)
0274 s1 = sqlrepr(self.expr1, db)
0275 s2 = sqlrepr(self.expr2, db)
0276 return "MOD(%s, %s)" % (s1, s2)
0277
0278registerConverter(SQLOp, SQLExprConverter)
0279registerConverter(SQLModulo, SQLExprConverter)
0280
0281class SQLCall(SQLExpression):
0282 def __init__(self, expr, args):
0283 self.expr = expr
0284 self.args = args
0285 def __sqlrepr__(self, db):
0286 return "%s%s" % (sqlrepr(self.expr, db), sqlrepr(self.args, db))
0287 def components(self):
0288 return [self.expr] + list(self.args)
0289 def execute(self, executor):
0290 raise ValueError, "I don't yet know how to locally execute functions"
0291
0292registerConverter(SQLCall, SQLExprConverter)
0293
0294class SQLPrefix(SQLExpression):
0295 def __init__(self, prefix, expr):
0296 self.prefix = prefix
0297 self.expr = expr
0298 def __sqlrepr__(self, db):
0299 return "%s %s" % (self.prefix, sqlrepr(self.expr, db))
0300 def components(self):
0301 return [self.expr]
0302 def execute(self, executor):
0303 expr = execute(self.expr, executor)
0304 if prefix == "+":
0305 return expr
0306 elif prefix == "-":
0307 return -expr
0308 elif prefix.upper() == "NOT":
0309 return not expr
0310
0311registerConverter(SQLPrefix, SQLExprConverter)
0312
0313class SQLConstant(SQLExpression):
0314 def __init__(self, const):
0315 self.const = const
0316 def __sqlrepr__(self, db):
0317 return self.const
0318 def execute(self, executor):
0319 raise ValueError, "I don't yet know how to execute SQL constants"
0320
0321registerConverter(SQLConstant, SQLExprConverter)
0322
0323class SQLTrueClauseClass(SQLExpression):
0324 def __sqlrepr__(self, db):
0325 return "1 = 1"
0326 def execute(self, executor):
0327 return 1
0328
0329SQLTrueClause = SQLTrueClauseClass()
0330
0331registerConverter(SQLTrueClauseClass, SQLExprConverter)
0332
0333
0334
0335
0336
0337class Field(SQLExpression):
0338 def __init__(self, tableName, fieldName):
0339 self.tableName = tableName
0340 self.fieldName = fieldName
0341 def __sqlrepr__(self, db):
0342 return self.tableName + "." + self.fieldName
0343 def tablesUsedImmediate(self):
0344 return [self.tableName]
0345 def execute(self, executor):
0346 return executor.field(self.tableName, self.fieldName)
0347
0348class SQLObjectField(Field):
0349 def __init__(self, tableName, fieldName, original, soClass, column):
0350 Field.__init__(self, tableName, fieldName)
0351 self.original = original
0352 self.soClass = soClass
0353 self.column = column
0354 def _from_python(self, value):
0355 column = self.column
0356 if not isinstance(value, SQLExpression) and column and column.from_python:
0357 value = column.from_python(value, SQLObjectState(self.soClass))
0358 return value
0359 def __eq__(self, other):
0360 if other is None:
0361 return ISNULL(self)
0362 other = self._from_python(other)
0363 return SQLOp('=', self, other)
0364 def __ne__(self, other):
0365 if other is None:
0366 return ISNOTNULL(self)
0367 other = self._from_python(other)
0368 return SQLOp('<>', self, other)
0369 def startswith(self, s):
0370 s = self._from_python(s)
0371 return STARTSWITH(self, s)
0372 def endswith(self, s):
0373 s = self._from_python(s)
0374 return ENDSWITH(self, s)
0375 def contains(self, s):
0376 s = self._from_python(s)
0377 return CONTAINSSTRING(self, s)
0378
0379registerConverter(SQLObjectField, SQLExprConverter)
0380
0381
0382class Table(SQLExpression):
0383 FieldClass = Field
0384
0385 def __init__(self, tableName):
0386 self.tableName = tableName
0387 def __getattr__(self, attr):
0388 if attr.startswith('__'):
0389 raise AttributeError
0390 return self.FieldClass(self.tableName, attr)
0391 def __sqlrepr__(self, db):
0392 return _str_or_sqlrepr(self.tableName, db)
0393 def execute(self, executor):
0394 raise ValueError, "Tables don't have values"
0395
0396class SQLObjectTable(Table):
0397 FieldClass = SQLObjectField
0398
0399 def __init__(self, soClass):
0400 self.soClass = soClass
0401 assert soClass.sqlmeta.table, (
0402 "Bad table name in class %r: %r"
0403 % (soClass, soClass.sqlmeta.table))
0404 Table.__init__(self, soClass.sqlmeta.table)
0405
0406 def __getattr__(self, attr):
0407 if attr.startswith('__'):
0408 raise AttributeError
0409 if attr == 'id':
0410 return self._getattrFromID(attr)
0411 elif attr in self.soClass.sqlmeta.columns:
0412 column = self.soClass.sqlmeta.columns[attr]
0413 return self._getattrFromColumn(column, attr)
0414 elif attr+'ID' in [k for (k, v) in self.soClass.sqlmeta.columns.items() if v.foreignKey]:
0415 attr += 'ID'
0416 column = self.soClass.sqlmeta.columns[attr]
0417 return self._getattrFromColumn(column, attr)
0418 else:
0419 raise AttributeError("%s instance has no attribute '%s'" % (self.soClass.__name__, attr))
0420
0421 def _getattrFromID(self, attr):
0422 return self.FieldClass(self.tableName, self.soClass.sqlmeta.idName, attr, self.soClass, None)
0423
0424 def _getattrFromColumn(self, column, attr):
0425 return self.FieldClass(self.tableName, column.dbName, attr, self.soClass, column)
0426
0427class SQLObjectTableWithJoins(SQLObjectTable):
0428
0429 def __getattr__(self, attr):
0430 if attr+'ID' in [k for (k, v) in self.soClass.sqlmeta.columns.items() if v.foreignKey]:
0431 column = self.soClass.sqlmeta.columns[attr+'ID']
0432 return self._getattrFromForeignKey(column, attr)
0433 elif attr in [x.joinMethodName for x in self.soClass.sqlmeta.joins]:
0434 join = [x for x in self.soClass.sqlmeta.joins if x.joinMethodName == attr][0]
0435 return self._getattrFromJoin(join, attr)
0436 else:
0437 return SQLObjectTable.__getattr__(self, attr)
0438
0439 def _getattrFromForeignKey(self, column, attr):
0440 ret = getattr(self, column.name) == getattr(self.soClass, '_SO_class_'+column.foreignKey).q.id
0442 return ret
0443
0444 def _getattrFromJoin(self, join, attr):
0445 if hasattr(join, 'otherColumn'):
0446 return AND(join.otherClass.q.id == Field(join.intermediateTable, join.otherColumn),
0447 Field(join.intermediateTable, join.joinColumn) == self.soClass.q.id)
0448 else:
0449 return getattr(join.otherClass.q, join.joinColumn)==self.soClass.q.id
0450
0451class TableSpace:
0452 TableClass = Table
0453
0454 def __getattr__(self, attr):
0455 if attr.startswith('__'):
0456 raise AttributeError
0457 return self.TableClass(attr)
0458
0459class ConstantSpace:
0460 def __getattr__(self, attr):
0461 if attr.startswith('__'):
0462 raise AttributeError
0463 return SQLConstant(attr)
0464
0465
0466
0467
0468
0469
0470class AliasField(Field):
0471 def __init__(self, tableName, fieldName, alias, aliasTable):
0472 Field.__init__(self, tableName, fieldName)
0473 self.alias = alias
0474 self.aliasTable = aliasTable
0475
0476 def __sqlrepr__(self, db):
0477 fieldName = self.fieldName
0478 if isinstance(fieldName, SQLExpression):
0479 fieldName = sqlrepr(fieldName, db)
0480 return self.alias + "." + fieldName
0481
0482 def tablesUsedImmediate(self):
0483 return [self.aliasTable]
0484
0485class AliasTable(Table):
0486 as_string = ''
0487 FieldClass = AliasField
0488
0489 _alias_lock = threading.Lock()
0490 _alias_counter = 0
0491
0492 def __init__(self, table, alias=None):
0493 if hasattr(table, "sqlmeta"):
0494 tableName = SQLConstant(table.sqlmeta.table)
0495 elif isinstance(table, (Select, Union)):
0496 assert alias is not None, "Alias name cannot be constructed from Select instances, please provide 'alias' kw."
0497 tableName = Subquery('', table)
0498 table = None
0499 else:
0500 tableName = SQLConstant(table)
0501 table = None
0502 Table.__init__(self, tableName)
0503 self.table = table
0504 if alias is None:
0505 self._alias_lock.acquire()
0506 try:
0507 AliasTable._alias_counter += 1
0508 alias = "%s_alias%d" % (tableName, AliasTable._alias_counter)
0509 finally:
0510 self._alias_lock.release()
0511 self.alias = alias
0512
0513 def __getattr__(self, attr):
0514 if attr.startswith('__'):
0515 raise AttributeError
0516 if self.table:
0517 attr = getattr(self.table.q, attr).fieldName
0518 return self.FieldClass(self.tableName, attr, self.alias, self)
0519
0520 def __sqlrepr__(self, db):
0521 return "%s %s %s" % (sqlrepr(self.tableName, db), self.as_string, self.alias)
0522
0523class Alias(SQLExpression):
0524 def __init__(self, table, alias=None):
0525 self.q = AliasTable(table, alias)
0526
0527 def __sqlrepr__(self, db):
0528 return sqlrepr(self.q, db)
0529
0530 def components(self):
0531 return [self.q]
0532
0533
0534class Union(SQLExpression):
0535 def __init__(self, *tables):
0536 tabs = []
0537 for t in tables:
0538 if not isinstance(t, SQLExpression) and hasattr(t, 'sqlmeta'):
0539 t = t.sqlmeta.table
0540 if isinstance(t, Alias):
0541 t = t.q
0542 if isinstance(t, Table):
0543 t = t.tableName
0544 if not isinstance(t, SQLExpression):
0545 t = SQLConstant(t)
0546 tabs.append(t)
0547 self.tables = tabs
0548
0549 def __sqlrepr__(self, db):
0550 return " UNION ".join([str(sqlrepr(t, db)) for t in self.tables])
0551
0552
0553
0554
0555
0556class Select(SQLExpression):
0557 def __init__(self, items=NoDefault, where=NoDefault, groupBy=NoDefault,
0558 having=NoDefault, orderBy=NoDefault, limit=NoDefault,
0559 join=NoDefault, lazyColumns=False, distinct=False,
0560 start=0, end=None, reversed=False, forUpdate=False,
0561 clause=NoDefault, staticTables=NoDefault, distinctOn=NoDefault):
0562 self.ops = {}
0563 if not isinstance(items, (list, tuple, types.GeneratorType)):
0564 items = [items]
0565 if clause is NoDefault and where is not NoDefault:
0566 clause = where
0567 if staticTables is NoDefault:
0568 staticTables = []
0569 self.ops['items'] = items
0570 self.ops['clause'] = clause
0571 self.ops['groupBy'] = groupBy
0572 self.ops['having'] = having
0573 self.ops['orderBy'] = orderBy
0574 self.ops['limit'] = limit
0575 self.ops['join'] = join
0576 self.ops['lazyColumns'] = lazyColumns
0577 self.ops['distinct'] = distinct
0578 self.ops['distinctOn'] = distinctOn
0579 self.ops['start'] = start
0580 self.ops['end'] = end
0581 self.ops['reversed'] = reversed
0582 self.ops['forUpdate'] = forUpdate
0583 self.ops['staticTables'] = staticTables
0584
0585 def clone(self, **newOps):
0586 ops = self.ops.copy()
0587 ops.update(newOps)
0588 return self.__class__(**ops)
0589
0590 def newItems(self, items):
0591 return self.clone(items=items)
0592
0593 def newClause(self, new_clause):
0594 return self.clone(clause=new_clause)
0595
0596 def orderBy(self, orderBy):
0597 return self.clone(orderBy=orderBy)
0598
0599 def unlimited(self):
0600 return self.clone(limit=NoDefault, start=0, end=None)
0601
0602 def limit(self, limit):
0603 self.clone(limit=limit)
0604
0605 def lazyColumns(self, value):
0606 return self.clone(lazyColumns=value)
0607
0608 def reversed(self):
0609 return self.clone(reversed=not self.ops.get('reversed', False))
0610
0611 def distinct(self):
0612 return self.clone(distinct=True)
0613
0614 def filter(self, filter_clause):
0615 if filter_clause is None:
0616
0617 return self
0618 clause = self.ops['clause']
0619 if isinstance(clause, basestring):
0620 clause = SQLConstant('(%s)' % clause)
0621
0622 if clause == SQLTrueClause:
0623 newClause = filter_clause
0624 else:
0625 newClause = AND(clause, filter_clause)
0626 return self.newClause(newClause)
0627
0628 def __sqlrepr__(self, db):
0629
0630 select = "SELECT"
0631 if self.ops['distinct']:
0632 select += " DISTINCT"
0633 if self.ops['distinctOn'] is not NoDefault:
0634 select += " ON(%s)" % _str_or_sqlrepr(self.ops['distinctOn'], db)
0635 if not self.ops['lazyColumns']:
0636 select += " %s" % ", ".join([str(_str_or_sqlrepr(v, db)) for v in self.ops['items']])
0637 else:
0638 select += " %s" % _str_or_sqlrepr(self.ops['items'][0], db)
0639
0640 join = []
0641 join_str = ''
0642 if self.ops['join'] is not NoDefault and self.ops['join'] is not None:
0643 _join = self.ops['join']
0644 if isinstance(_join, str):
0645 join_str = " " + _join
0646 elif isinstance(_join, SQLJoin):
0647 join.append(_join)
0648 else:
0649 join.extend(_join)
0650 tables = set()
0651 for x in self.ops['staticTables']:
0652 if isinstance(x, SQLExpression):
0653 x = sqlrepr(x, db)
0654 tables.add(x)
0655 things = list(self.ops['items']) + join
0656 if self.ops['clause'] is not NoDefault:
0657 things.append(self.ops['clause'])
0658 for thing in things:
0659 if isinstance(thing, SQLExpression):
0660 tables.update(tablesUsedSet(thing, db))
0661 for j in join:
0662 t1 = _str_or_sqlrepr(j.table1, db)
0663 if t1 in tables: tables.remove(t1)
0664 t2 = _str_or_sqlrepr(j.table2, db)
0665 if t2 in tables: tables.remove(t2)
0666 if tables:
0667 select += " FROM %s" % ", ".join(tables)
0668 elif join:
0669 select += " FROM"
0670 tablesYet = tables
0671 for j in join:
0672 if tablesYet and j.table1:
0673 sep = ", "
0674 else:
0675 sep = " "
0676 select += sep + sqlrepr(j, db)
0677 tablesYet = True
0678
0679 if join_str:
0680 select += join_str
0681
0682 if self.ops['clause'] is not NoDefault:
0683 select += " WHERE %s" % _str_or_sqlrepr(self.ops['clause'], db)
0684 if self.ops['groupBy'] is not NoDefault:
0685 groupBy = _str_or_sqlrepr(self.ops['groupBy'], db)
0686 if isinstance(self.ops['groupBy'], (list, tuple)):
0687 groupBy = groupBy[1:-1]
0688 select += " GROUP BY %s" % groupBy
0689 if self.ops['having'] is not NoDefault:
0690 select += " HAVING %s" % _str_or_sqlrepr(self.ops['having'], db)
0691 if self.ops['orderBy'] is not NoDefault and self.ops['orderBy'] is not None:
0692 orderBy = self.ops['orderBy']
0693 if self.ops['reversed']:
0694 reverser = DESC
0695 else:
0696 reverser = lambda x: x
0697 if isinstance(orderBy, (list, tuple)):
0698 select += " ORDER BY %s" % ", ".join([_str_or_sqlrepr(reverser(x), db) for x in orderBy])
0699 else:
0700 select += " ORDER BY %s" % _str_or_sqlrepr(reverser(orderBy), db)
0701 start, end = self.ops['start'], self.ops['end']
0702 if self.ops['limit'] is not NoDefault:
0703 end = start + self.ops['limit']
0704 if start or end:
0705 from dbconnection import dbConnectionForScheme
0706 select = dbConnectionForScheme(db)._queryAddLimitOffset(select, start, end)
0707 if self.ops['forUpdate']:
0708 select += " FOR UPDATE"
0709 return select
0710
0711registerConverter(Select, SQLExprConverter)
0712
0713class Insert(SQLExpression):
0714 def __init__(self, table, valueList=None, values=None, template=NoDefault):
0715 self.template = template
0716 self.table = table
0717 if valueList:
0718 if values:
0719 raise TypeError, "You may only give valueList *or* values"
0720 self.valueList = valueList
0721 else:
0722 self.valueList = [values]
0723 def __sqlrepr__(self, db):
0724 if not self.valueList:
0725 return ''
0726 insert = "INSERT INTO %s" % self.table
0727 allowNonDict = True
0728 template = self.template
0729 if (template is NoDefault) and isinstance(self.valueList[0], dict):
0730 template = self.valueList[0].keys()
0731 allowNonDict = False
0732 if template is not NoDefault:
0733 insert += " (%s)" % ", ".join(template)
0734 insert += " VALUES "
0735 listToJoin = []
0736 listToJoin_app = listToJoin.append
0737 for value in self.valueList:
0738 if isinstance(value, dict):
0739 if template is NoDefault:
0740 raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value)
0741 value = dictToList(template, value)
0742 elif not allowNonDict:
0743 raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value)
0744 listToJoin_app("(%s)" % ", ".join([sqlrepr(v, db) for v in value]))
0745 insert = "%s%s" % (insert, ", ".join(listToJoin))
0746 return insert
0747
0748registerConverter(Insert, SQLExprConverter)
0749
0750def dictToList(template, dict):
0751 list = []
0752 for key in template:
0753 list.append(dict[key])
0754 if len(dict.keys()) > len(template):
0755 raise TypeError, "Extra entries in dictionary that aren't asked for in template (template=%s, dict=%s)" % (repr(template), repr(dict))
0756 return list
0757
0758class Update(SQLExpression):
0759 def __init__(self, table, values, template=NoDefault, where=NoDefault):
0760 self.table = table
0761 self.values = values
0762 self.template = template
0763 self.whereClause = where
0764 def __sqlrepr__(self, db):
0765 update = "%s %s" % (self.sqlName(), self.table)
0766 update += " SET"
0767 first = True
0768 if self.template is not NoDefault:
0769 for i in range(len(self.template)):
0770 if first:
0771 first = False
0772 else:
0773 update += ","
0774 update += " %s=%s" % (self.template[i], sqlrepr(self.values[i], db))
0775 else:
0776 for key, value in self.values.items():
0777 if first:
0778 first = False
0779 else:
0780 update += ","
0781 update += " %s=%s" % (key, sqlrepr(value, db))
0782 if self.whereClause is not NoDefault:
0783 update += " WHERE %s" % _str_or_sqlrepr(self.whereClause, db)
0784 return update
0785 def sqlName(self):
0786 return "UPDATE"
0787
0788registerConverter(Update, SQLExprConverter)
0789
0790class Delete(SQLExpression):
0791 """To be safe, this will signal an error if there is no where clause,
0792 unless you pass in where=None to the constructor."""
0793 def __init__(self, table, where=NoDefault):
0794 self.table = table
0795 if where is NoDefault:
0796 raise TypeError, "You must give a where clause or pass in None to indicate no where clause"
0797 self.whereClause = where
0798 def __sqlrepr__(self, db):
0799 whereClause = self.whereClause
0800 if whereClause is None:
0801 return "DELETE FROM %s" % self.table
0802 whereClause = _str_or_sqlrepr(whereClause, db)
0803 return "DELETE FROM %s WHERE %s" % (self.table, whereClause)
0804
0805registerConverter(Delete, SQLExprConverter)
0806
0807class Replace(Update):
0808 def sqlName(self):
0809 return "REPLACE"
0810
0811registerConverter(Replace, SQLExprConverter)
0812
0813
0814
0815
0816
0817class DESC(SQLExpression):
0818
0819 def __init__(self, expr):
0820 self.expr = expr
0821
0822 def __sqlrepr__(self, db):
0823 if isinstance(self.expr, DESC):
0824 return sqlrepr(self.expr.expr, db)
0825 return '%s DESC' % sqlrepr(self.expr, db)
0826
0827def AND(*ops):
0828 if not ops:
0829 return None
0830 op1 = ops[0]
0831 ops = ops[1:]
0832 if ops:
0833 return SQLOp("AND", op1, AND(*ops))
0834 else:
0835 return op1
0836
0837def OR(*ops):
0838 if not ops:
0839 return None
0840 op1 = ops[0]
0841 ops = ops[1:]
0842 if ops:
0843 return SQLOp("OR", op1, OR(*ops))
0844 else:
0845 return op1
0846
0847def NOT(op):
0848 return SQLPrefix("NOT", op)
0849
0850def _IN(item, list):
0851 return SQLOp("IN", item, list)
0852
0853def IN(item, list):
0854 from sresults import SelectResults
0855 if isinstance(list, SelectResults):
0856 query = list.queryForSelect()
0857 query.ops['items'] = [list.sourceClass.q.id]
0858 list = query
0859 if isinstance(list, Select):
0860 return INSubquery(item, list)
0861 else:
0862 return _IN(item, list)
0863
0864def NOTIN(item, list):
0865 if isinstance(list, Select):
0866 return NOTINSubquery(item, list)
0867 else:
0868 return NOT(_IN(item, list))
0869
0870def STARTSWITH(expr, pattern):
0871 return LIKE(expr, _LikeQuoted(pattern) + '%', escape='\\')
0872
0873def ENDSWITH(expr, pattern):
0874 return LIKE(expr, '%' + _LikeQuoted(pattern), escape='\\')
0875
0876def CONTAINSSTRING(expr, pattern):
0877 return LIKE(expr, '%' + _LikeQuoted(pattern) + '%', escape='\\')
0878
0879def ISNULL(expr):
0880 return SQLOp("IS", expr, None)
0881
0882def ISNOTNULL(expr):
0883 return SQLOp("IS NOT", expr, None)
0884
0885class ColumnAS(SQLOp):
0886 ''' Just like SQLOp('AS', expr, name) except without the parentheses '''
0887 def __init__(self, expr, name):
0888 if isinstance(name, basestring):
0889 name = SQLConstant(name)
0890 SQLOp.__init__(self, 'AS', expr, name)
0891 def __sqlrepr__(self, db):
0892 return "%s %s %s" % (sqlrepr(self.expr1, db), self.op, sqlrepr(self.expr2, db))
0893
0894class _LikeQuoted:
0895
0896
0897
0898
0899
0900 def __init__(self, expr):
0901 self.expr = expr
0902 self.prefix = ''
0903 self.postfix = ''
0904
0905 def __radd__(self, s):
0906 self.prefix = s + self.prefix
0907 return self
0908
0909 def __add__(self, s):
0910 self.postfix += s
0911 return self
0912
0913 def __sqlrepr__(self, db):
0914 s = self.expr
0915 if isinstance(s, SQLExpression):
0916 values = []
0917 if self.prefix:
0918 values.append(quote_str(self.prefix, db))
0919 s = _quote_like_special(sqlrepr(s, db), db)
0920 values.append(s)
0921 if self.postfix:
0922 values.append(quote_str(self.postfix, db))
0923 if db == "mysql":
0924 return "CONCAT(%s)" % ", ".join(values)
0925 else:
0926 return " || ".join(values)
0927 elif isinstance(s, basestring):
0928 s = _quote_like_special(unquote_str(sqlrepr(s, db)), db)
0929 return quote_str("%s%s%s" % (self.prefix, s, self.postfix), db)
0930 else:
0931 raise TypeError, "expected str, unicode or SQLExpression, got %s" % type(s)
0932
0933def _quote_like_special(s, db):
0934 if db in ('postgres', 'rdbhost'):
0935 escape = r'\\'
0936 else:
0937 escape = '\\'
0938 s = s.replace('\\', r'\\').replace('%', escape+'%').replace('_', escape+'_')
0939 return s
0940
0941
0942
0943
0944
0945class SQLJoin(SQLExpression):
0946 def __init__(self, table1, table2, op=','):
0947 if hasattr(table1, 'sqlmeta'):
0948 table1 = table1.sqlmeta.table
0949 if hasattr(table2, 'sqlmeta'):
0950 table2 = table2.sqlmeta.table
0951 if isinstance(table1, str):
0952 table1 = SQLConstant(table1)
0953 if isinstance(table2, str):
0954 table2 = SQLConstant(table2)
0955 self.table1 = table1
0956 self.table2 = table2
0957 self.op = op
0958
0959 def __sqlrepr__(self, db):
0960 if self.table1:
0961 return "%s%s %s" % (sqlrepr(self.table1, db), self.op, sqlrepr(self.table2, db))
0962 else:
0963 return "%s %s" % (self.op, sqlrepr(self.table2, db))
0964
0965registerConverter(SQLJoin, SQLExprConverter)
0966
0967def JOIN(table1, table2):
0968 return SQLJoin(table1, table2, " JOIN")
0969
0970def INNERJOIN(table1, table2):
0971 return SQLJoin(table1, table2, " INNER JOIN")
0972
0973def CROSSJOIN(table1, table2):
0974 return SQLJoin(table1, table2, " CROSS JOIN")
0975
0976def STRAIGHTJOIN(table1, table2):
0977 return SQLJoin(table1, table2, " STRAIGHT JOIN")
0978
0979def LEFTJOIN(table1, table2):
0980 return SQLJoin(table1, table2, " LEFT JOIN")
0981
0982def LEFTOUTERJOIN(table1, table2):
0983 return SQLJoin(table1, table2, " LEFT OUTER JOIN")
0984
0985def NATURALJOIN(table1, table2):
0986 return SQLJoin(table1, table2, " NATURAL JOIN")
0987
0988def NATURALLEFTJOIN(table1, table2):
0989 return SQLJoin(table1, table2, " NATURAL LEFT JOIN")
0990
0991def NATURALLEFTOUTERJOIN(table1, table2):
0992 return SQLJoin(table1, table2, " NATURAL LEFT OUTER JOIN")
0993
0994def RIGHTJOIN(table1, table2):
0995 return SQLJoin(table1, table2, " RIGHT JOIN")
0996
0997def RIGHTOUTERJOIN(table1, table2):
0998 return SQLJoin(table1, table2, " RIGHT OUTER JOIN")
0999
1000def NATURALRIGHTJOIN(table1, table2):
1001 return SQLJoin(table1, table2, " NATURAL RIGHT JOIN")
1002
1003def NATURALRIGHTOUTERJOIN(table1, table2):
1004 return SQLJoin(table1, table2, " NATURAL RIGHT OUTER JOIN")
1005
1006def FULLJOIN(table1, table2):
1007 return SQLJoin(table1, table2, " FULL JOIN")
1008
1009def FULLOUTERJOIN(table1, table2):
1010 return SQLJoin(table1, table2, " FULL OUTER JOIN")
1011
1012def NATURALFULLJOIN(table1, table2):
1013 return SQLJoin(table1, table2, " NATURAL FULL JOIN")
1014
1015def NATURALFULLOUTERJOIN(table1, table2):
1016 return SQLJoin(table1, table2, " NATURAL FULL OUTER JOIN")
1017
1018class SQLJoinConditional(SQLJoin):
1019 """Conditional JOIN"""
1020 def __init__(self, table1, table2, op, on_condition=None, using_columns=None):
1021 """For condition you must give on_condition or using_columns but not both
1022
1023 on_condition can be a string or SQLExpression, for example
1024 Table1.q.col1 == Table2.q.col2
1025 using_columns can be a string or a list of columns, e.g.
1026 (Table1.q.col1, Table2.q.col2)
1027 """
1028 if not on_condition and not using_columns:
1029 raise TypeError, "You must give ON condition or USING columns"
1030 if on_condition and using_columns:
1031 raise TypeError, "You must give ON condition or USING columns but not both"
1032 SQLJoin.__init__(self, table1, table2, op)
1033 self.on_condition = on_condition
1034 self.using_columns = using_columns
1035
1036 def __sqlrepr__(self, db):
1037 if self.on_condition:
1038 on_condition = self.on_condition
1039 if hasattr(on_condition, "__sqlrepr__"):
1040 on_condition = sqlrepr(on_condition, db)
1041 join = "%s %s ON %s" % (self.op, sqlrepr(self.table2, db), on_condition)
1042 if self.table1:
1043 join = "%s %s" % (sqlrepr(self.table1, db), join)
1044 return join
1045 elif self.using_columns:
1046 using_columns = []
1047 for col in self.using_columns:
1048 if hasattr(col, "__sqlrepr__"):
1049 col = sqlrepr(col, db)
1050 using_columns.append(col)
1051 using_columns = ", ".join(using_columns)
1052 join = "%s %s USING (%s)" % (self.op, sqlrepr(self.table2, db), using_columns)
1053 if self.table1:
1054 join = "%s %s" % (sqlrepr(self.table1, db), join)
1055 return join
1056 else:
1057 RuntimeError, "Impossible error"
1058
1059registerConverter(SQLJoinConditional, SQLExprConverter)
1060
1061def INNERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1062 return SQLJoinConditional(table1, table2, "INNER JOIN", on_condition, using_columns)
1063
1064def LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None):
1065 return SQLJoinConditional(table1, table2, "LEFT JOIN", on_condition, using_columns)
1066
1067def LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1068 return SQLJoinConditional(table1, table2, "LEFT OUTER JOIN", on_condition, using_columns)
1069
1070def RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None):
1071 return SQLJoinConditional(table1, table2, "RIGHT JOIN", on_condition, using_columns)
1072
1073def RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1074 return SQLJoinConditional(table1, table2, "RIGHT OUTER JOIN", on_condition, using_columns)
1075
1076def FULLJOINConditional(table1, table2, on_condition=None, using_columns=None):
1077 return SQLJoinConditional(table1, table2, "FULL JOIN", on_condition, using_columns)
1078
1079def FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1080 return SQLJoinConditional(table1, table2, "FULL OUTER JOIN", on_condition, using_columns)
1081
1082class SQLJoinOn(SQLJoinConditional):
1083 """Conditional JOIN ON"""
1084 def __init__(self, table1, table2, op, on_condition):
1085 SQLJoinConditional.__init__(self, table1, table2, op, on_condition)
1086
1087registerConverter(SQLJoinOn, SQLExprConverter)
1088
1089class SQLJoinUsing(SQLJoinConditional):
1090 """Conditional JOIN USING"""
1091 def __init__(self, table1, table2, op, using_columns):
1092 SQLJoinConditional.__init__(self, table1, table2, op, None, using_columns)
1093
1094registerConverter(SQLJoinUsing, SQLExprConverter)
1095
1096def INNERJOINOn(table1, table2, on_condition):
1097 return SQLJoinOn(table1, table2, "INNER JOIN", on_condition)
1098
1099def LEFTJOINOn(table1, table2, on_condition):
1100 return SQLJoinOn(table1, table2, "LEFT JOIN", on_condition)
1101
1102def LEFTOUTERJOINOn(table1, table2, on_condition):
1103 return SQLJoinOn(table1, table2, "LEFT OUTER JOIN", on_condition)
1104
1105def RIGHTJOINOn(table1, table2, on_condition):
1106 return SQLJoinOn(table1, table2, "RIGHT JOIN", on_condition)
1107
1108def RIGHTOUTERJOINOn(table1, table2, on_condition):
1109 return SQLJoinOn(table1, table2, "RIGHT OUTER JOIN", on_condition)
1110
1111def FULLJOINOn(table1, table2, on_condition):
1112 return SQLJoinOn(table1, table2, "FULL JOIN", on_condition)
1113
1114def FULLOUTERJOINOn(table1, table2, on_condition):
1115 return SQLJoinOn(table1, table2, "FULL OUTER JOIN", on_condition)
1116
1117def INNERJOINUsing(table1, table2, using_columns):
1118 return SQLJoinUsing(table1, table2, "INNER JOIN", using_columns)
1119
1120def LEFTJOINUsing(table1, table2, using_columns):
1121 return SQLJoinUsing(table1, table2, "LEFT JOIN", using_columns)
1122
1123def LEFTOUTERJOINUsing(table1, table2, using_columns):
1124 return SQLJoinUsing(table1, table2, "LEFT OUTER JOIN", using_columns)
1125
1126def RIGHTJOINUsing(table1, table2, using_columns):
1127 return SQLJoinUsing(table1, table2, "RIGHT JOIN", using_columns)
1128
1129def RIGHTOUTERJOINUsing(table1, table2, using_columns):
1130 return SQLJoinUsing(table1, table2, "RIGHT OUTER JOIN", using_columns)
1131
1132def FULLJOINUsing(table1, table2, using_columns):
1133 return SQLJoinUsing(table1, table2, "FULL JOIN", using_columns)
1134
1135def FULLOUTERJOINUsing(table1, table2, using_columns):
1136 return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
1137
1138
1139
1140
1141
1142
1143class OuterField(Field):
1144 def tablesUsedImmediate(self):
1145 return []
1146
1147class OuterTable(Table):
1148 FieldClass = OuterField
1149
1150 def __init__(self, table):
1151 if hasattr(table, "sqlmeta"):
1152 tableName = table.sqlmeta.table
1153 else:
1154 tableName = table
1155 table = None
1156 Table.__init__(self, tableName)
1157 self.table = table
1158
1159class Outer:
1160 def __init__(self, table):
1161 self.q = OuterTable(table)
1162
1163
1164class LIKE(SQLExpression):
1165 op = "LIKE"
1166
1167 def __init__(self, expr, string, escape=None):
1168 self.expr = expr
1169 self.string = string
1170 self.escape = escape
1171 def __sqlrepr__(self, db):
1172 escape = self.escape
1173 like = "%s %s (%s)" % (sqlrepr(self.expr, db), self.op, sqlrepr(self.string, db))
1174 if escape is None:
1175 return "(%s)" % like
1176 else:
1177 return "(%s ESCAPE %s)" % (like, sqlrepr(escape, db))
1178 def components(self):
1179 return [self.expr, self.string]
1180 def execute(self, executor):
1181 if not hasattr(self, '_regex'):
1182
1183 dest = self.string
1184 dest = dest.replace("%%", "\001")
1185 dest = dest.replace("*", "\002")
1186 dest = dest.replace("%", "*")
1187 dest = dest.replace("\001", "%")
1188 dest = dest.replace("\002", "[*]")
1189 self._regex = re.compile(fnmatch.translate(dest), re.I)
1190 return self._regex.search(execute(self.expr, executor))
1191
1192class RLIKE(LIKE):
1193 op = "RLIKE"
1194
1195 op_db = {
1196 'firebird': 'RLIKE',
1197 'maxdb': 'RLIKE',
1198 'mysql': 'RLIKE',
1199 'postgres': '~',
1200 'rdbhost': '~',
1201 'sqlite': 'REGEXP'
1202 }
1203
1204 def _get_op(self, db):
1205 return self.op_db.get(db, 'LIKE')
1206 def __sqlrepr__(self, db):
1207 return "(%s %s (%s))" % (
1208 sqlrepr(self.expr, db), self._get_op(db), sqlrepr(self.string, db)
1209 )
1210 def execute(self, executor):
1211 self.op = self._get_op(self.db)
1212 return LIKE.execute(self, executor)
1213
1214
1215class INSubquery(SQLExpression):
1216 op = "IN"
1217
1218 def __init__(self, item, subquery):
1219 self.item = item
1220 self.subquery = subquery
1221 def components(self):
1222 return [self.item]
1223 def __sqlrepr__(self, db):
1224 return "%s %s (%s)" % (sqlrepr(self.item, db), self.op, sqlrepr(self.subquery, db))
1225
1226class NOTINSubquery(INSubquery):
1227 op = "NOT IN"
1228
1229
1230class Subquery(SQLExpression):
1231 def __init__(self, op, subquery):
1232 self.op = op
1233 self.subquery = subquery
1234
1235 def __sqlrepr__(self, db):
1236 return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
1237
1238def EXISTS(subquery):
1239 return Subquery("EXISTS", subquery)
1240
1241def NOTEXISTS(subquery):
1242 return Subquery("NOT EXISTS", subquery)
1243
1244def SOME(subquery):
1245 return Subquery("SOME", subquery)
1246
1247def ANY(subquery):
1248 return Subquery("ANY", subquery)
1249
1250def ALL(subquery):
1251 return Subquery("ALL", subquery)
1252
1253
1254
1255class ImportProxyField(SQLObjectField):
1256 def tablesUsedImmediate(self):
1257 return [str(self.tableName)]
1258
1259class ImportProxy(SQLExpression):
1260 '''Class to be used in column definitions that rely on other tables that might
1261 not yet be in a classregistry.
1262 '''
1263 FieldClass = ImportProxyField
1264 def __init__(self, clsName, registry=None):
1265 self.tableName = _DelayClass(self, clsName)
1266 self.sqlmeta = _Delay_proxy(table=_DelayClass(self, clsName))
1267 self.q = self
1268 self.soClass = None
1269 classregistry.registry(registry).addClassCallback(clsName, lambda foreign, me: setattr(me, 'soClass', foreign), self)
1270
1271 def __nonzero__(self):
1272 return True
1273
1274 def __getattr__(self, attr):
1275 if self.soClass is None:
1276 return _Delay(self, attr)
1277 return getattr(self.soClass.q, attr)
1278
1279class _Delay(SQLExpression):
1280 def __init__(self, proxy, attr):
1281 self.attr = attr
1282 self.proxy = proxy
1283
1284 def __sqlrepr__(self, db):
1285 if self.proxy.soClass is None:
1286 return '_DELAYED_' + self.attr
1287 val = self._resolve()
1288 if isinstance(val, SQLExpression):
1289 val = sqlrepr(val, db)
1290 return val
1291
1292 def tablesUsedImmediate(self):
1293 return getattr(self._resolve(), 'tablesUsedImmediate', lambda: [])()
1294
1295 def components(self):
1296 return getattr(self._resolve(), 'components', lambda: [])()
1297
1298 def _resolve(self):
1299 return getattr(self.proxy, self.attr)
1300
1301
1302 def fieldName(self):
1303 class _aliasFieldName(SQLExpression):
1304 def __init__(self, proxy):
1305 self.proxy = proxy
1306 def __sqlrepr__(self, db):
1307 return self.proxy._resolve().fieldName
1308 return _aliasFieldName(self)
1309 fieldName = property(fieldName)
1310
1311class _DelayClass(_Delay):
1312 def _resolve(self):
1313 return self.proxy.soClass.sqlmeta.table
1314
1315class _Delay_proxy(object):
1316 def __init__(self, **kw):
1317 self.__dict__.update(kw)
1318
1319
1320
1321
1322
1323
1324
1325
1326table = TableSpace()
1327const = ConstantSpace()
1328func = const
1329
1330
1331
1332
1333
1334if __name__ == "__main__":
1335 tests = """
1336>>> AND(table.address.name == "Ian Bicking", table.address.zip > 30000)
1337>>> table.address.name
1338>>> AND(LIKE(table.address.name, "this"), IN(table.address.zip, [100, 200, 300]))
1339>>> Select([table.address.name, table.address.state], where=LIKE(table.address.name, "%ian%"))
1340>>> Select([table.user.name], where=AND(table.user.state == table.states.abbrev))
1341>>> Insert(table.address, [{"name": "BOB", "address": "3049 N. 18th St."}, {"name": "TIM", "address": "409 S. 10th St."}])
1342>>> Insert(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1343>>> Delete(table.address, where="BOB"==table.address.name)
1344>>> Update(table.address, {"lastModified": const.NOW()})
1345>>> Replace(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1346"""
1347 for expr in tests.split('\n'):
1348 if not expr.strip(): continue
1349 if expr.startswith('>>> '):
1350 expr = expr[4:]