From 252d692c34f728e3db2f403d596fe0a6ea57bdd4 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 28 Sep 2006 01:46:16 +0000 Subject: [PATCH] - pool will auto-close open cursors, or can be configured to raise an error instead - consolidated scalar() calls into ResultProxy scalar(), fixed ResultProxy scalar() to apply typing rules - general de-cruftification of ClauseElement/Compiled (yes i know theres crufty things everywhere) --- lib/sqlalchemy/engine/base.py | 21 +++--- lib/sqlalchemy/pool.py | 14 +++- lib/sqlalchemy/sql.py | 119 ++++++++++++++-------------------- 3 files changed, 69 insertions(+), 85 deletions(-) diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py index 9cb589e2ee..8dc96daba7 100644 --- a/lib/sqlalchemy/engine/base.py +++ b/lib/sqlalchemy/engine/base.py @@ -110,7 +110,13 @@ class Dialect(sql.AbstractDialect): raise NotImplementedError() def do_execute(self, cursor, statement, parameters): raise NotImplementedError() + def compile(self, clauseelement, parameters=None): + """compile the given ClauseElement using this Dialect. + a convenience method which simply flips around the compile() call + on ClauseElement.""" + return clauseelement.compile(dialect=self, parameters=parameters) + class ExecutionContext(object): """a messenger object for a Dialect that corresponds to a single execution. The Dialect should provide an ExecutionContext via the create_execution_context() method. @@ -233,15 +239,7 @@ class Connection(Connectable): self.__connection = None del self.__connection def scalar(self, object, parameters=None, **kwargs): - result = self.execute(object, parameters, **kwargs) - row = result.fetchone() - try: - if row is not None: - return row[0] - else: - return None - finally: - result.close() + return self.execute(object, parameters, **kwargs).scalar() def execute(self, object, *multiparams, **params): return Connection.executors[type(object).__mro__[-2]](self, object, *multiparams, **params) def execute_default(self, default, **kwargs): @@ -466,8 +464,7 @@ class ComposedSQLEngine(sql.Engine, Connectable): return connection.execute(statement, *multiparams, **params) def scalar(self, statement, *multiparams, **params): - connection = self.contextual_connect(close_with_result=True) - return connection.scalar(statement, *multiparams, **params) + return self.execute(statement, *multiparams, **params).scalar() def execute_compiled(self, compiled, *multiparams, **params): connection = self.contextual_connect(close_with_result=True) @@ -658,7 +655,7 @@ class ResultProxy: if row is not None: if self.__echo: self.engine.logger.debug("Row " + repr(row)) - return row[0] + return RowProxy(self, row)[0] else: return None finally: diff --git a/lib/sqlalchemy/pool.py b/lib/sqlalchemy/pool.py index 2b9ecb2505..7bebbded89 100644 --- a/lib/sqlalchemy/pool.py +++ b/lib/sqlalchemy/pool.py @@ -75,12 +75,14 @@ def clear_managers(): proxies.clear() class Pool(object): - def __init__(self, creator, recycle=-1, echo=None, use_threadlocal = True): + def __init__(self, creator, recycle=-1, echo=None, use_threadlocal = True, auto_close_cursors=True, disallow_open_cursors=False): self.logger = logging.instance_logger(self) self._threadconns = weakref.WeakValueDictionary() self._creator = creator self._recycle = recycle self._use_threadlocal = use_threadlocal + self.auto_close_cursors = auto_close_cursors + self.disallow_open_cursors = disallow_open_cursors self.echo = echo echo = logging.echo_property() @@ -206,8 +208,14 @@ class _ConnectionFairy(object): def __del__(self): self._close() def _close(self): -# if self.cursors is not None and len(self.cursors): -# raise exceptions.InvalidRequestError("This connection still has %d open cursors" % len(self.cursors)) + if self.cursors is not None: + # cursors should be closed before connection is returned to the pool. some dbapis like + # mysql have real issues if they are not. + if self.__pool.auto_close_cursors: + self.close_open_cursors() + elif self.__pool.disallow_open_cursors: + if len(self.cursors): + raise exceptions.InvalidRequestError("This connection still has %d open cursors" % len(self.cursors)) if self.connection is not None: try: self.connection.rollback() diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 1a732742d3..d332a2907e 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -13,29 +13,41 @@ types = __import__('types') __all__ = ['text', 'table', 'column', 'func', 'select', 'update', 'insert', 'delete', 'join', 'and_', 'or_', 'not_', 'between_', 'case', 'cast', 'union', 'union_all', 'null', 'desc', 'asc', 'outerjoin', 'alias', 'subquery', 'literal', 'bindparam', 'exists', 'extract'] def desc(column): - """returns a descending ORDER BY clause element, e.g.: + """return a descending ORDER BY clause element, e.g.: order_by = [desc(table1.mycol)] """ return CompoundClause(None, column, "DESC") def asc(column): - """returns an ascending ORDER BY clause element, e.g.: + """return an ascending ORDER BY clause element, e.g.: order_by = [asc(table1.mycol)] """ return CompoundClause(None, column, "ASC") def outerjoin(left, right, onclause=None, **kwargs): - """returns an OUTER JOIN clause element, given the left and right hand expressions, - as well as the ON condition's expression. To chain joins together, use the resulting + """return an OUTER JOIN clause element. + + left - the left side of the join + right - the right side of the join + onclause - optional criterion for the ON clause, + is derived from foreign key relationships otherwise + + To chain joins together, use the resulting Join object's "join()" or "outerjoin()" methods.""" return Join(left, right, onclause, isouter = True, **kwargs) def join(left, right, onclause=None, **kwargs): - """returns a JOIN clause element (regular inner join), given the left and right - hand expressions, as well as the ON condition's expression. To chain joins - together, use the resulting Join object's "join()" or "outerjoin()" methods.""" + """return a JOIN clause element (regular inner join). + + left - the left side of the join + right - the right side of the join + onclause - optional criterion for the ON clause, + is derived from foreign key relationships otherwise + + To chain joins together, use the resulting Join object's + "join()" or "outerjoin()" methods.""" return Join(left, right, onclause, **kwargs) def select(columns=None, whereclause = None, from_obj = [], **kwargs): @@ -340,7 +352,7 @@ class Compiled(ClauseVisitor): reference those values as defaults.""" def __init__(self, dialect, statement, parameters, engine=None): - """constructs a new Compiled object. + """construct a new Compiled object. statement - ClauseElement to be compiled @@ -358,6 +370,10 @@ class Compiled(ClauseVisitor): self.statement = statement self.parameters = parameters self.engine = engine + + def compile(self): + self.statement.accept_visitor(self) + self.after_compile() def __str__(self): """returns the string text of the generated SQL statement.""" @@ -373,44 +389,17 @@ class Compiled(ClauseVisitor): """ raise NotImplementedError() - def compile(self): - self.statement.accept_visitor(self) - self.after_compile() - def execute(self, *multiparams, **params): - """executes this compiled object using the AbstractEngine it is bound to.""" + """execute this compiled object.""" e = self.engine if e is None: raise exceptions.InvalidRequestError("This Compiled object is not bound to any engine.") return e.execute_compiled(self, *multiparams, **params) def scalar(self, *multiparams, **params): - """executes this compiled object via the execute() method, then - returns the first column of the first row. Useful for executing functions, - sequences, rowcounts, etc.""" - # we are still going off the assumption that fetching only the first row - # in a result set is not performance-wise any different than specifying limit=1 - # else we'd have to construct a copy of the select() object with the limit - # installed (else if we change the existing select, not threadsafe) - r = self.execute(*multiparams, **params) - row = r.fetchone() - try: - if row is not None: - return row[0] - else: - return None - finally: - r.close() - -class Executor(object): - """context-sensitive executor for the using() function.""" - def __init__(self, clauseelement, abstractengine=None): - self.engine=abstractengine - self.clauseelement = clauseelement - def execute(self, *multiparams, **params): - return self.clauseelement.execute_using(self.engine) - def scalar(self, *multiparams, **params): - return self.clauseelement.scalar_using(self.engine) + """execute this compiled object and return the result's scalar value.""" + return self.execute(*multiparams, **params).scalar() + class ClauseElement(object): """base class for elements of a programmatically constructed SQL expression.""" @@ -465,22 +454,20 @@ class ClauseElement(object): engine = property(lambda s: s._find_engine(), doc="attempts to locate a Engine within this ClauseElement structure, or returns None if none found.") - def using(self, abstractengine): - return Executor(self, abstractengine) - - def execute_using(self, engine, *multiparams, **params): - compile_params = self._conv_params(*multiparams, **params) - return self.compile(engine=engine, parameters=compile_params).execute(*multiparams, **params) - def scalar_using(self, engine, *multiparams, **params): - compile_params = self._conv_params(*multiparams, **params) - return self.compile(engine=engine, parameters=compile_params).scalar(*multiparams, **params) - def _conv_params(self, *multiparams, **params): + def execute(self, *multiparams, **params): + """compile and execute this ClauseElement.""" if len(multiparams): - return multiparams[0] + compile_params = multiparams[0] else: - return params + compile_params = params + return self.compile(engine=self.engine, parameters=compile_params).execute(*multiparams, **params) + + def scalar(self, *multiparams, **params): + """compile and execute this ClauseElement, returning the result's scalar representation.""" + return self.execute(*multiparams, **params).scalar() + def compile(self, engine=None, parameters=None, compiler=None, dialect=None): - """compiles this SQL expression. + """compile this SQL expression. Uses the given Compiler, or the given AbstractDialect or Engine to create a Compiler. If no compiler arguments are given, tries to use the underlying Engine this ClauseElement is bound @@ -494,7 +481,6 @@ class ClauseElement(object): and INSERT statements the bind parameters that are present determine the SET and VALUES clause of those statements. """ - if (isinstance(parameters, list) or isinstance(parameters, tuple)): parameters = parameters[0] @@ -514,13 +500,6 @@ class ClauseElement(object): def __str__(self): return str(self.compile()) - - def execute(self, *multiparams, **params): - return self.execute_using(self.engine, *multiparams, **params) - - def scalar(self, *multiparams, **params): - return self.scalar_using(self.engine, *multiparams, **params) - def __and__(self, other): return and_(self, other) def __or__(self, other): @@ -745,8 +724,14 @@ class FromClause(Selectable): oid_column = property(_get_oid_column) def _export_columns(self): - """this method is called the first time any of the "exported attrbutes" are called. it receives from the Selectable - a list of all columns to be exported and creates "proxy" columns for each one.""" + """initialize column collections. + + the collections include the primary key, foreign keys, list of all columns, as well as + the "_orig_cols" collection which is a dictionary used to match Table-bound columns + to proxied columns in this FromClause. The columns in each collection are "proxied" from + the columns returned by the _exportable_columns method, where a "proxied" column maintains + most or all of the properties of its original column, except its parent Selectable is this FromClause. + """ if hasattr(self, '_columns'): # TODO: put a mutex here ? this is a key place for threading probs return @@ -798,7 +783,7 @@ class BindParamClause(ClauseElement, CompareMixin): # return self.obj._make_proxy(selectable, name=self.name) class TypeClause(ClauseElement): - """handles a type keyword in a SQL statement""" + """handles a type keyword in a SQL statement. used by the Case statement.""" def __init__(self, type): self.type = type def accept_visitor(self, visitor): @@ -810,13 +795,7 @@ class TextClause(ClauseElement): """represents literal a SQL text fragment. public constructor is the text() function. - TextClauses, since they can be anything, have no comparison operators or - typing information. - - A single literal value within a compiled SQL statement is more useful - being specified as a bind parameter via the bindparam() method, - since it provides more information about what it is, including an optional - type, as well as providing comparison operations.""" + """ def __init__(self, text = "", engine=None, bindparams=None, typemap=None): self.parens = False self._engine = engine -- 2.47.2