diff options
Diffstat (limited to 'lib/sqlalchemy/sql.py')
| -rw-r--r-- | lib/sqlalchemy/sql.py | 252 |
1 files changed, 176 insertions, 76 deletions
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 2c133f6c1..643f9e3d3 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -16,9 +16,8 @@ # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. -"""base sql module used by all sql implementations. defines abstract units which construct -expression trees that generate into text strings + bind parameters. -""" +"""defines the base components of SQL expression trees.""" + import sqlalchemy.schema as schema import sqlalchemy.util as util import string @@ -26,28 +25,74 @@ import string __ALL__ = ['textclause', 'select', 'join', 'and_', 'or_', 'union', 'desc', 'asc', 'outerjoin', 'alias', 'subquery', 'bindparam', 'sequence'] def desc(column): + """returns a descending ORDER BY clause element""" return CompoundClause(None, column, "DESC") def asc(column): + """returns an ascending ORDER BY clause element""" return CompoundClause(None, column, "ASC") -def outerjoin(left, right, onclause, **params): - return Join(left, right, onclause, isouter = True, **params) +def outerjoin(left, right, onclause, **kwargs): + """returns an OUTER JOIN clause element, given the left and right hand expressions, + as well as the ON condition's expression. When chaining joins together, the previous JOIN + expression should be specified as the left side of this JOIN expression.""" + return Join(left, right, onclause, isouter = True, **kwargs) + +def join(left, right, onclause, **kwargs): + """returns a JOIN clause element (regular inner join), given the left and right hand expressions, + as well as the ON condition's expression. When chaining joins together, the previous JOIN + expression should be specified as the left side of this JOIN expression.""" + return Join(left, right, onclause, **kwargs) + +def select(columns, whereclause = None, from_obj = [], **kwargs): + """returns a SELECT clause element, given a list of columns and/or selectable items to select + columns from, an optional expression for the WHERE clause, an optional list of "FROM" objects + to select from, and additional parameters.""" + return Select(columns, whereclause = whereclause, from_obj = from_obj, **kwargs) + +def insert(table, values = None, **kwargs): + """returns an INSERT clause element. -def join(left, right, onclause, **params): - return Join(left, right, onclause, **params) - -def select(columns, whereclause = None, from_obj = [], **params): - return Select(columns, whereclause = whereclause, from_obj = from_obj, **params) - -def insert(table, values = None, **params): - return Insert(table, values, **params) + 'table' is the table to be inserted into. + 'values' is a dictionary which specifies the column specifications of the INSERT, and is optional. + If left as None, the + column specifications are determined from the bind parameters used during the compile phase of the + INSERT statement. If the bind parameters also are None during the compile phase, then the column + specifications will be generated from the full list of table columns. + + If both 'values' and compile-time bind parameters are present, the compile-time bind parameters + override the information specified within 'values' on a per-key basis. + + The keys within 'values' can be either Column objects or their string identifiers. + Each key may reference one of: a literal data value (i.e. string, number, etc.), a Column object, + or a SELECT statement. If a SELECT statement is specified which references this INSERT statement's + table, the statement will be correlated against the INSERT statement. + """ + return Insert(table, values, **kwargs) -def update(table, whereclause = None, values = None, **params): - return Update(table, whereclause, values, **params) +def update(table, whereclause = None, values = None, **kwargs): + """returns an UPDATE clause element. + + 'table' is the table to be updated. + 'whereclause' is a ClauseElement describing the WHERE condition of the UPDATE statement. + 'values' is a dictionary which specifies the SET conditions of the UPDATE, and is optional. + If left as None, the + SET conditions are determined from the bind parameters used during the compile phase of the + UPDATE statement. If the bind parameters also are None during the compile phase, then the SET + conditions will be generated from the full list of table columns. + + If both 'values' and compile-time bind parameters are present, the compile-time bind parameters + override the information specified within 'values' on a per-key basis. + + The keys within 'values' can be either Column objects or their string identifiers. + Each key may reference one of: a literal data value (i.e. string, number, etc.), a Column object, + or a SELECT statement. If a SELECT statement is specified which references this UPDATE statement's + table, the statement will be correlated against the UPDATE statement. + """ + return Update(table, whereclause, values, **kwargs) -def delete(table, whereclause = None, **params): - return Delete(table, whereclause, **params) +def delete(table, whereclause = None, **kwargs): + return Delete(table, whereclause, **kwargs) def and_(*clauses): return _compound_clause('AND', *clauses) @@ -55,7 +100,7 @@ def and_(*clauses): def or_(*clauses): clause = _compound_clause('OR', *clauses) return clause - + def union(*selects, **params): return _compound_select('UNION', *selects, **params) @@ -73,23 +118,25 @@ def textclause(text): def sequence(): return Sequence() - + def _compound_clause(keyword, *clauses): return CompoundClause(keyword, *clauses) def _compound_select(keyword, *selects, **params): - if len(selects) == 0: return None - + if len(selects) == 0: + return None s = selects[0] for n in selects[1:]: s.append_clause(keyword, n) - + if params.get('order_by', None) is not None: s.order_by(*params['order_by']) return s class ClauseVisitor(schema.SchemaVisitor): + """builds upon SchemaVisitor to define the visiting of SQL statement elements in + addition to Schema elements.""" def visit_columnclause(self, column):pass def visit_fromclause(self, fromclause):pass def visit_bindparam(self, bindparam):pass @@ -101,13 +148,23 @@ class ClauseVisitor(schema.SchemaVisitor): def visit_join(self, join):pass class Compiled(ClauseVisitor): - pass - + """represents a compiled SQL expression. the __str__ method of the Compiled object + should produce the actual text of the statement. Compiled objects are specific to the database + library that created them, and also may or may not be specific to the columns referenced + within a particular set of bind parameters. In no case should the Compiled object be dependent + on the actual values of those bind parameters, even though it may reference those values + as defaults.""" + def __str__(self): + raise NotImplementedError() + def get_params(self, **params): + """returns the bind params for this compiled object, with values overridden by + those given in the **params dictionary""" + raise NotImplementedError() + class ClauseElement(object): - """base class for elements of a generated SQL statement. + """base class for elements of a programmatically constructed SQL expression. - includes a parameter hash to store bind parameter key/value pairs, - as well as a list of 'from objects' which collects items to be placed + includes a list of 'from objects' which collects items to be placed in the FROM clause of a SQL statement. when many ClauseElements are attached together, the from objects and bind @@ -115,26 +172,46 @@ class ClauseElement(object): """ def hash_key(self): + """returns a string that uniquely identifies the concept this ClauseElement represents. + + two ClauseElements can have the same value for hash_key() iff they both correspond to the + exact same generated SQL. This allows the hash_key() values of a collection of ClauseElements + to be constructed into a larger identifying string for the purpose of caching a SQL expression. + + Note that since ClauseElements may be mutable, the hash_key() value is subject to change + if the underlying structure of the ClauseElement changes.""" raise NotImplementedError(repr(self)) def _get_from_objects(self): raise NotImplementedError(repr(self)) def accept_visitor(self, visitor): raise NotImplementedError(repr(self)) - def compile(self, engine, bindparams = None): - return engine.compile(self, bindparams = bindparams) - def copy_structure(self): - """allows the copying of a statement's containers, so that a modified statement - can be produced without affecting the original. containing clauseelements, - like Select, Join, CompoundClause, BinaryClause, etc., should produce a copy of - themselves, whereas "leaf-node" clauseelements should return themselves.""" + def copy_container(self): + """should return a copy of this ClauseElement, iff this ClauseElement contains other + ClauseElements. Otherwise, it should be left alone to return self. This is used to create + copies of expression trees that still reference the same "leaf nodes". The new structure + can then be restructured without affecting the original.""" return self - + def _engine(self): + """should return a SQLEngine instance that is associated with this expression tree. + this engine is usually attached to one of the underlying Table objects within the expression.""" raise NotImplementedError("Object %s has no built-in SQLEngine." % repr(self)) - + + def compile(self, engine, bindparams = None): + """compiles this SQL expression using its underlying SQLEngine to produce + a Compiled object. The actual SQL statement is the Compiled object's string representation. + bindparams is an optional dictionary representing the bind parameters to be used with + the statement. Currently, only the compilations of INSERT and UPDATE statements + use the bind parameters, in order to determine which + table columns should be used in the statement.""" + return engine.compile(self, bindparams = bindparams) + def execute(self, **params): + """compiles and executes this SQL expression using its underlying SQLEngine. + the given **params are used as bind parameters when compiling and executing the expression. + the DBAPI cursor object is returned.""" e = self._engine() c = self.compile(e, bindparams = params) # TODO: do pre-execute right here, for sequences, if the compiled object @@ -142,13 +219,14 @@ class ClauseElement(object): return e.execute(str(c), c.get_params(), echo = getattr(self, 'echo', None)) def result(self, **params): + """the same as execute(), except a RowProxy object is returned instead of a DBAPI cursor.""" e = self._engine() c = self.compile(e, bindparams = params) - return e.result(str(c), c.binds) - + return e.result(str(c), c.get_params(), echo = getattr(self, 'echo', None)) + class ColumnClause(ClauseElement): - """represents a column clause element in a SQL statement.""" - + """represents a textual column clause in a SQL statement.""" + def __init__(self, text, selectable): self.text = text self.table = selectable @@ -165,7 +243,7 @@ class ColumnClause(ClauseElement): def hash_key(self): return "ColumnClause(%s, %s)" % (self.text, self.table.hash_key()) - + def _get_from_objects(self): return [] @@ -236,8 +314,8 @@ class CompoundClause(ClauseElement): if c is None: continue self.append(c) - def copy_structure(self): - clauses = [clause.copy_structure() for clause in self.clauses] + def copy_container(self): + clauses = [clause.copy_container() for clause in self.clauses] return CompoundClause(self.operator, *clauses) def append(self, clause): @@ -279,8 +357,8 @@ class BinaryClause(ClauseElement): self.operator = operator self.parens = False - def copy_structure(self): - return BinaryClause(self.left.copy_structure(), self.right.copy_structure(), self.operator) + def copy_container(self): + return BinaryClause(self.left.copy_container(), self.right.copy_container(), self.operator) def _get_from_objects(self): return self.left._get_from_objects() + self.right._get_from_objects() @@ -394,7 +472,7 @@ class ColumnSelectable(Selectable): self.label = self.column.name self.fullname = self.column.name - def copy_structure(self): + def copy_container(self): return self.column def _get_from_objects(self): @@ -594,29 +672,59 @@ class Select(Selectable): class UpdateBase(ClauseElement): + """forms the base for INSERT, UPDATE, and DELETE statements. + Deals with the special needs of INSERT and UPDATE parameter lists - + these statements have two separate lists of parameters, those + defined when the statement is constructed, and those specified at compile time.""" + + def _process_colparams(self, parameters): + if parameters is None: + return None + + for key in parameters.keys(): + value = parameters[key] + if isinstance(value, Select): + value.append_from(FromClause(from_key=self.table.id)) + elif not isinstance(value, schema.Column) and not isinstance(value, ClauseElement): + try: + col = self.table.c[key] + parameters[key] = bindparam(col.name, value) + except KeyError: + del parameters[key] + + return parameters + def get_colparams(self, parameters): - values = [] + # case one: no parameters in the statement, no parameters in the + # compiled params - just return binds for all the table columns + if parameters is None and self.parameters is None: + return [(c, bindparam(c.name)) for c in self.table.columns] + # if we have statement parameters - set defaults in the + # compiled params if parameters is None: - parameters = self.parameters + parameters = {} - if parameters is None: - for c in self.table.columns: - values.append((c, bindparam(c.name))) - else: - d = {} - for key, value in parameters.iteritems(): - if isinstance(key, schema.Column): - d[key] = value - else: - d[self.table.columns[str(key)]] = value - - for c in self.table.columns: - if d.has_key(c): - value = d[c] - if not isinstance(value, BindParamClause): - value = bindparam(c.name, value) - values.append((c, value)) + if self.parameters is not None: + for k, v in self.parameters.iteritems(): + parameters.setdefault(k, v) + + # now go thru compiled params, get the Column object for each key + d = {} + for key, value in parameters.iteritems(): + if isinstance(key, schema.Column): + d[key] = value + else: + d[self.table.columns[str(key)]] = value + + # create a list of column assignment clauses as tuples + values = [] + for c in self.table.columns: + if d.has_key(c): + value = d[c] + if isinstance(value, str): + value = bindparam(c.name, value) + values.append((c, value)) return values def _engine(self): @@ -635,7 +743,7 @@ class Insert(UpdateBase): def __init__(self, table, parameters = None, **params): self.table = table self.select = None - self.parameters = parameters + self.parameters = self._process_colparams(parameters) self.engine = self.table._engine() def accept_visitor(self, visitor): @@ -650,41 +758,33 @@ class Insert(UpdateBase): def compile(self, engine = None, bindparams = None): if engine is None: engine = self.engine - if engine is None: raise "no engine supplied, and no engine could be located within the clauses!" - return engine.compile(self, bindparams) class Update(UpdateBase): def __init__(self, table, whereclause, parameters = None, **params): self.table = table self.whereclause = whereclause - - self.parameters = parameters + self.parameters = self._process_colparams(parameters) self.engine = self.table._engine() - def accept_visitor(self, visitor): if self.whereclause is not None: self.whereclause.accept_visitor(visitor) - visitor.visit_update(self) class Delete(UpdateBase): def __init__(self, table, whereclause, **params): self.table = table self.whereclause = whereclause - self.engine = self.table._engine() - def accept_visitor(self, visitor): if self.whereclause is not None: self.whereclause.accept_visitor(visitor) - visitor.visit_delete(self) - + class Sequence(BindParamClause): def __init__(self): BindParamClause.__init__(self, 'sequence') |