Python ConnectionPool.runOperation方法代码示例

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runOperation [as 别名]
class ConnectionPool(object):
    """
    Wrapper for twisted.enterprise.adbapi.ConnectionPool to use with tornado.
    """

    def __init__(self, *args, **kwargs):
        self._pool = TxConnectionPool(*args, **kwargs)

    def run_query(self, *args, **kwargs):
        return self._defer_to_future(self._pool.runQuery(*args, **kwargs))

    def run_operation(self, *args, **kwargs):
        return self._defer_to_future(self._pool.runOperation(*args, **kwargs))

    def run_interaction(self, *args, **kwargs):
        return self._defer_to_future(self._pool.runInteraction(*args, **kwargs))

    def close(self):
        self._pool.close()

    @staticmethod
    def _defer_to_future(defer):
        future = TracebackFuture()
        defer.addCallbacks(
            future.set_result,
            lambda failure: future.set_exc_info(
                (failure.type, failure.value, failure.tb)))
        return future

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runOperation [as 别名]

#.........这里部分代码省略.........
        """
        raise NotImplementedError("Each database must remove its own tables.")

    @inlineCallbacks
    def _db_remove_schema(self):
        """
        Remove the stored schema version table.
        """
        yield self._db_execute("drop table if exists CALDAV")

    @inlineCallbacks
    def _db_all_values_for_sql(self, sql, *query_params):
        """
        Execute an SQL query and obtain the resulting values.
        @param sql: the SQL query to execute.
        @param query_params: parameters to C{sql}.
        @return: an interable of values in the first column of each row
            resulting from executing C{sql} with C{query_params}.
        @raise AssertionError: if the query yields multiple columns.
        """
        
        sql = self._prepare_statement(sql)
        results = (yield self.pool.runQuery(sql, *query_params))
        returnValue(tuple(results))

    @inlineCallbacks
    def _db_values_for_sql(self, sql, *query_params):
        """
        Execute an SQL query and obtain the resulting values.

        @param sql: the SQL query to execute.
        @param query_params: parameters to C{sql}.
        @return: an interable of values in the first column of each row
            resulting from executing C{sql} with C{query_params}.
        @raise AssertionError: if the query yields multiple columns.
        """
        
        sql = self._prepare_statement(sql)
        results = (yield self.pool.runQuery(sql, *query_params))
        returnValue(tuple([row[0] for row in results]))

    @inlineCallbacks
    def _db_value_for_sql(self, sql, *query_params):
        """
        Execute an SQL query and obtain a single value.

        @param sql: the SQL query to execute.
        @param query_params: parameters to C{sql}.
        @return: the value resulting from the executing C{sql} with
            C{query_params}.
        @raise AssertionError: if the query yields multiple rows or columns.
        """
        value = None
        for row in (yield self._db_values_for_sql(sql, *query_params)):
            assert value is None, "Multiple values in DB for %s %s" % (sql, query_params)
            value = row
        returnValue(value)

    def _db_execute(self, sql, *query_params):
        """
        Execute an SQL operation that returns None.

        @param sql: the SQL query to execute.
        @param query_params: parameters to C{sql}.
        @return: an iterable of tuples for each row resulting from executing
            C{sql} with C{query_params}.
        """
        
        sql = self._prepare_statement(sql)
        return self.pool.runOperation(sql, *query_params)

    """
    Since different databases support different types of columns and modifiers on those we need to
    have an "abstract" way of specifying columns in our code and then map the abstract specifiers to
    the underlying DB's allowed types.
    
    Types we can use are:
    
    integer
    text
    text(n)
    date
    serial
    
    The " unique" modifier can be appended to any of those.
    """
    def _map_column_types(self, type):
        raise NotImplementedError
        
    def _create_table(self, name, columns, ifnotexists=False):
        raise NotImplementedError

    def _test_table(self, name):
        raise NotImplementedError

    def _create_index(self, name, ontable, columns, ifnotexists=False):
        raise NotImplementedError

    def _prepare_statement(self, sql):
        raise NotImplementedError

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runOperation [as 别名]

#.........这里部分代码省略.........
		query, params = _sql_format(self.queries['select'], item, paramstyle=self.paramstyle, identifier=self.identifier)
		txn.execute(query, params)
		result = txn.fetchone()
		if result:
			log.msg("Item already in db: (id) %s item:\n%r" % (result['id'], item), level=log.WARNING)

		query, params = _sql_format(self.queries['insert'], item, paramstyle=self.paramstyle, identifier=self.identifier)
		# transaction in thread
		qlog = self._log_preparedsql(query, params)
		try:
			txn.execute(query, params)
		except self.dbapi.IntegrityError as e:
			#spider.log('%s FAILED executing: %s' % (self.__class__.__name__, qlog), level=log.DEBUG)
			query, params = _sql_format(self.queries['update'], item, paramstyle=self.paramstyle, identifier=self.identifier)
			qlog = self._log_preparedsql(query, params)
			try:
				#spider.log('%s executing: %s' % (self.__class__.__name__, qlog), level=log.DEBUG)
				txn.execute(query, params)
			except self.dbapi.OperationalError as e:
				# retrying in new transaction
			#	spider.log('%s errored. Retrying.\nError: %s\nQuery: %s' % (self.__class__.__name__, e, qlog), level=log.WARNING)
			#	self._spool.append((query, params, item))
			#except Exception as e:
				if self.debug:
					spider.log('%s FAILED executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING)
				raise
			finally:
				if self.debug:
					spider.log('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG)
		except self.dbapi.OperationalError as e:
			# also try again
			if self.debug:
				spider.log('%s failed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG)
			raise
		finally:
			if self.debug:
				spider.log('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG)
	"""

	def _log_preparedsql(self, query, params):
		"""Simulate escaped query for log"""
		for p in params:
			query = re.sub('(\\'+self.paramstyle+r'\d?)', '"%s"' % p, query, count=1)
		return query

	def _database_error(self, e, item, spider=None):
		"""Log exceptions."""
		if spider:
			log.err(e, spider=spider)
		else:
			log.err(e)

	def query(self, sql):
		# run a query in the connection pool
		# parameters for prepared statements must be passed as 'sql=(query, params)'
		# (possible use-case from inside spider code)
		'''Spider Example: build start requests from database results

		from scrapy.exceptions import CloseSpider, NotConfigured
		from ..pipelines.sqlmagic import SQLMagicPipeline

		class MySpider(Spider):
			def spider_opened(self, spider):
				try:
					self.db = SQLMagicPipeline(self.settings.get('SQLMAGIC_DATABASE'))
				except NotConfigured:
					raise CloseSpider('Could not get database settings.')

			@defer.inlineCallbacks
			def db_queries(self, response):
				query = """CALL procedure ()"""
				result = yield self.db.query(query)

				# build requests
				requests = []
				for value in result:
					r = yield self.build_request_fromdb(response, value)
					requests.append(r)

				# queue them
				defer.returnValue(requests)

			def start_requests(self):
				yield Request(self.start_urls[0], callback=self.database_queries)

			def build_request_fromdb(self, response, db):
				# custom logic to convert db result into a request
				r = Request(response.url)
				r.callback = self.parse
				return r
		'''
		if query[:6].lower() in ('select',):
			deferred = self.__dbpool.runQuery(sql)
		if query[:4].lower() in ('call',):
			# potential fail: procedure must run a SELECT for this,
			# otherwise it should do runOperation
			deferred = self.__dbpool.runQuery(sql)
		else:
			deferred = self.__dbpool.runOperation(sql)
		return deferred