Python ConnectionPool.runQuery方法代码示例

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runQuery [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 runQuery [as 别名]
class Database():
    """
    HouseAgent database interaction.
    """
    def __init__(self, log, db_location):
        self.log = log

        type = "sqlite"

        self.coordinator = None

        # Note: cp_max=1 is required otherwise undefined behaviour could occur when using yield icw subsequent
        # runQuery or runOperation statements
        if type == "sqlite":
            self.dbpool = ConnectionPool("sqlite3", db_location, check_same_thread=False, cp_max=1)
       
        # Check database schema version and upgrade when required
        self.updatedb('0.1')
             
    def updatedb(self, dbversion):
        '''
        Perform a database schema update when required. 
        '''
        # Note: runInteraction runs all queries defined within the specified function as part of a transaction.
        return self.dbpool.runInteraction(self._updatedb, dbversion)

    def _updatedb(self, txn, dbversion):
        '''
        Check whether a database schema update is required and act accordingly.
        '''
        # Note: Although all queries are run as part of a transaction, a create or drop table statement result in an implicit commit

        # Query the version of the current schema
        try:
            result = txn.execute("SELECT parm_value FROM common WHERE parm = 'schema_version'").fetchall()
        except:
            result = None
            
        if result:
            version = result[0][0]
        else:
            version = '0.0'

        if float(version) > float(dbversion):
            self.log.error("ERROR: The current database schema (%s) is not supported by this version of HouseAgent" % version)
            # Exit HouseAgent
            sys.exit(1)
        
        elif float(version) == float(dbversion):
            self.log.debug("Database schema is up to date")
            return
        
        else:
            self.log.info("Database schema will be updated from %s to %s:" % (version, dbversion))

            # Before we start manipulating the database schema, first make a backup copy of the database
            try:
                shutil.copy(db_location, db_location + datetime.datetime.strftime(datetime.datetime.now(), ".%y%m%d-%H%M%S"))
            except:
                self.log.error("Cannot make a backup copy of the database (%s)", sys.exc_info()[1])
                return

            if version == '0.0':
                try:
                    # Create common table
                    txn.execute("CREATE TABLE IF NOT EXISTS common (parm VARCHAR(16) PRIMARY KEY, parm_value VARCHAR(24) NOT NULL)")
            
                    # Add schema version to database
                    txn.execute("INSERT INTO common (parm, parm_value) VALUES ('schema_version', ?)", [dbversion])

                    # Set primary key of the devices table on address + plugin_id to prevent adding duplicate devices
                    txn.execute("CREATE TEMPORARY TABLE devices_backup(id INTEGER PRIMARY KEY, name VARCHAR(45), address VARCHAR(45) NOT NULL, plugin_id INTEGER NOT NULL, location_id INTEGER)")
                    txn.execute("INSERT INTO devices_backup SELECT id, name, address, plugin_id, location_id FROM devices")
                    txn.execute("DROP TABLE devices")
                    txn.execute("CREATE TABLE devices(id INTEGER PRIMARY KEY, name VARCHAR(45), address VARCHAR(45) NOT NULL, plugin_id INTEGER, location_id INTEGER)")
                    txn.execute("CREATE UNIQUE INDEX device_address ON devices (address, plugin_id)")
                    txn.execute("INSERT INTO devices SELECT id, name, address, plugin_id, location_id FROM devices_backup")
                    txn.execute("DROP TABLE devices_backup")

                    self.log.info("Successfully upgraded database schema")
                except:
                    self.log.error("Database schema upgrade failed (%s)" % sys.exc_info()[1])

    def query_plugin_auth(self, authcode):
        return self.dbpool.runQuery("SELECT authcode, id from plugins WHERE authcode = '%s'" % authcode)

    def check_plugin_auth(self, result):
        if len(result) >= 1:
            return {'registered': True}
        else:
            return {'registered': False}

    def insert_result(self, result):
        return {'received': True}

    def add_event(self, name, enabled, triggers):
        """
        This function adds an event to the database.
        """
        d = self.dbpool.runQuery("INSERT INTO events (name, enabled) VALUES (?, ?)", (name, enabled) )
#.........这里部分代码省略.........

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runQuery [as 别名]
class MDatabase:
    """
    Sqlite database for Marnatarlo
    """
    def __init__(self, dbname):
        self.dbname = dbname
        try:
            fh = open(dbname)
        except IOError as e:
            conn = sqlite3.connect(dbname)
            curs = conn.cursor()
            curs.execute("Create table users (name text unique, password text)")
            curs.execute("Create table stats(name text, played INTEGER, won INTEGER, FOREIGN KEY(name) REFERENCES users(name))")
            conn.commit()
            curs.close()
        self.__dbpool = ConnectionPool('sqlite3', self.dbname)

    def shutdown(self):
        """
            Shutdown function
            It's a required task to shutdown the database connection pool:
                garbage collector doesn't shutdown associated thread
        """
        self.__dbpool.close()

    def returnOk(self, o):
        return True

    def returnFailure(self, o):
        return False

    def returnResult(self, result):
        return result

    def _returnResult(self, deferred, count=None):
        if count:
            return self.__dbpool.fetchmany(count)
        else:
            return self.__dbpool.fetchall()

    def execSql(self, sql, params={}):
        """
        Exec an SQL command, return True or False
      
        @type sql C{str}
        @param sql SQL command
        """
        def run(sql, params):
            return self.__dbpool.runQuery(sql, params)
        d = run(sql, params)
        d.addCallback(self._returnResult)
        d.addErrback(self.returnFailure)
        d.addCallback(self.returnResult)
        return d

    def fetch(self, sql, params={}):
        """
          Exec an SQL command, fetching the rows resulting
      
          @type sql C{str}
          @param sql SQL command
        """
        def run(sql, params):
            return self.__dbpool.runQuery(sql, params)
        d = run(sql, params)
        d.addCallback(self.returnResult)
        d.addErrback(self.returnFailure)
        return d

    def get_stats(self, user):
        query = "SELECT * FROM stats WHERE name=?"
        return self.fetch(query, (user,))

    def user_won(self, user):
        query = "UPDATE stats SET won=won+1 WHERE name=?"
        return self.execSql(query, (user,))

    def user_play(self, user):
        query = "UPDATE stats SET played=played+1 WHERE name=?"
        return self.execSql(query, (user,))

    def save_user(self, user, passwd):
        """
        Save user / password into db
      
        @type user C{str}
        @type password C{str}
        """
        def insert_user(users, user, passwd):
            if len(users) > 0:
                return self.returnFailure(users)
            query = "INSERT INTO users(name, password) VALUES (?, ?)"
            self.execSql(query, (user, passwd,))
            query = "INSERT INTO stats(name, played, won) VALUES (?, 0,0)"
            return self.execSql(query, (user,))

        return self.get_user_login_info(user).addCallback(insert_user, user, passwd)

    def get_user_login_info(self, user):
        """
#.........这里部分代码省略.........

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

#.........这里部分代码省略.........
    @inlineCallbacks
    def _db_remove(self):
        """
        Remove all database information (all the tables)
        """
        yield self._db_remove_data_tables()
        yield self._db_remove_schema()

    def _db_remove_data_tables(self):
        """
        Remove all the data from an older version of the DB.
        """
        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)

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

#.........这里部分代码省略.........
                    txn.execute("CREATE INDEX 'current_values.fk_current_values_history_periods1' \
                                    ON current_values (history_period_id);")
                    txn.execute("CREATE INDEX 'current_values.fk_current_values_history_types1' \
                                    ON current_values (history_type_id);")
                    txn.execute("CREATE INDEX 'current_values.fk_values_devices1' \
                                    ON current_values (device_id);")
                    
                    # fill new current_values table
                    txn.execute("INSERT INTO current_values \
                                SELECT id, name, value, device_id, lastupdate, 1, 1, control_type_id \
                                FROM current_values_tmp;")
                    txn.execute("DROP TABLE current_values_tmp;")

                    # history_values table
                    txn.execute("CREATE TABLE history_values (value_id integer,\
                                value real, created_at datetime, \
                                FOREIGN KEY (value_id) REFERENCES current_values(id));")

                    txn.execute("CREATE INDEX 'history_values.idx_history_values_created_at1' \
                                    ON history_values (created_at);")
                    txn.execute("CREATE INDEX 'history_values.idx_history_values_value_id1' \
                                    ON history_values (value_id);")
                    
                    # Control types fix
                    txn.execute("INSERT into control_types VALUES(0, 'Not controllable');")
                    txn.execute("UPDATE control_types SET name='Switch (On/off)' WHERE id=1;")
                    txn.execute("UPDATE control_types SET name='Thermostat (Setpoint)' WHERE id=2;")

                    self.log.info("Successfully upgraded database schema")
                except:
                    self.log.error("Database schema upgrade failed (%s)" % sys.exc_info()[1])

    def query_plugin_auth(self, authcode):
        return self.dbpool.runQuery("SELECT authcode, id from plugins WHERE authcode = '%s'" % authcode)

    def check_plugin_auth(self, result):
        if len(result) >= 1:
            return {'registered': True}
        else:
            return {'registered': False}

    def insert_result(self, result):
        return {'received': True}

    def add_event(self, name, enabled, triggers):
        """
        This function adds an event to the database.
        """
        d = self.dbpool.runQuery("INSERT INTO events (name, enabled) VALUES (?, ?)", (name, enabled) )
        def event_added(result):
            print "added event"
            return self.dbpool.runQuery("select id from events order by id desc limit 1")      
        
        d.addCallback(event_added)
        def got_id(result):
            event_id = result[0][0]
            
            print "got event_id", result[0][0]
            print "triggers=",triggers
            
            # Add triggers
            deferredlist = []
            
            for trigger in triggers:
                trigger_type_id = trigger["trigger_type"]
                print "trigger", trigger

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runQuery [as 别名]
class IrcBot(irc.IRCClient):


    def __init__(self, *args, **kwargs):
        self.plugins = {'privmsg': [self.url_plugin, self.cmd_plugin],
                   'signedOn': [self.signon_plugin],
                   'joined': [self.joined_plugin]}
        self.init_db()



    @defer.inlineCallbacks
    def init_db(self):
        self.db = ConnectionPool('sqlite3', db_file)
        yield self.db.runQuery('''CREATE TABLE IF NOT EXISTS QUOTES (ID INTEGER PRIMARY KEY,
                                                    NICK TEXT,
                                                    QUOTE TEXT collate nocase,
                                                    QUOTE_DT NUMERIC,
                                                    ADDED_BY TEXT,
                                                    CHANNEL TEXT
                                                    )''')
        yield self.db.runQuery('''CREATE TABLE IF NOT EXISTS URLS (ID INTEGER PRIMARY KEY,
                                                    URL TEXT collate nocase,
                                                    URL_DT NUMERIC,
                                                    ADDED_BY TEXT,
                                                    CHANNEL TEXT
                                                    )''')

    ###############################################################################
    # Misc methods
    #############################################################################

    # Return the name of the method that calls funcname() as a string
    def funcname(self):
        return inspect.stack()[1][3]

    # Helper method that converts seconds to a string
    # in the format of "X days, X hours, X minutes, X seconds
    def get_time(self, sec):
        sec = timedelta(seconds=int(time.time()) - sec)
        dt = datetime(1, 1, 1) + sec
        msg = "%s seconds" % dt.second
        if dt.minute:
            msg = "%s minute(s)," % dt.minute + msg
        if dt.hour:
            msg = "%s hour(s)," % dt.hour + msg
        if dt.day - 1:
            msg = "%s day(s)," % (dt.day - 1) + msg
        return msg

    #############################################################################
    # Plugin code, to be moved to a separate file
    #############################################################################

    def signon_plugin(self):
        self.setNick(self.factory.nickname)
        self.join(self.factory.channel)
        print "Signed on as %s." % (self.nickname,)

    def joined_plugin(self, channel):
        print "Joined %s." % (channel,)
        self.say(channel, 'hi')

    def cmd_plugin(self, user, channel, msg):
        cmd_dict = {'quote':self.quote,
                    'add':self.add_quote}

        if msg[0] == '!':
            cmd = msg.split()[0][1:]
            cmd_dict[cmd](user, channel, msg)

    def url_plugin(self, user, channel, msg):
        #Do not process commands
        if msg[0] == '!':
            return
        for tok in msg.split(' '):
            if urlparse.urlparse(tok).scheme[:4] == 'http':
                user = user.split('!')[0]
                self.store(tok, channel, user)

    @defer.inlineCallbacks
    def quote(self, user, channel, msg):
        toks = msg.split(' ')
        if len(toks) < 2:
            query = '''SELECT id, quote FROM quotes ORDER BY RANDOM() LIMIT 1'''
            quotes = yield self.db.runQuery(query,)
        else:
            query = '''SELECT id, quote from quotes where quote like ?'''
            pattern = '%%%s%%' % ' '.join(toks[1:])
            quotes = yield self.db.runQuery(query, (pattern,))

        if len(quotes):
            if len(quotes) > 1:
                quotes = [random.choice(quotes)]
            msg = '[%s] %s' % (str(quotes[0][0]), str(quotes[0][1]))
            self.say(channel, msg)

    @defer.inlineCallbacks
    def add_quote(self, user, channel, msg):
        print user,channel,msg
#.........这里部分代码省略.........

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runQuery [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