Python ConnectionPool.runInteraction方法代码示例

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runInteraction [as 别名]
def getReadyPostgres(connstr):
    pool = ConnectionPool('psycopg2', connstr)
    def i1(c):
        try:
            c.execute('''create table sticky (
                    id serial primary key,
                    board_id text,
                    updated timestamp default current_timestamp,
                    note text,
                    x integer,
                    y integer)''')
        except Exception as e:
            log.err(e)
    def i2(c):
        try:
            c.execute('''create table image (
                id serial primary key,
                board_id text,
                updated timestamp default current_timestamp,
                data bytea
            )''')
            c.execute('''create unique index image_board_id on image(board_id)''')
        except Exception as e:
            log.err(e)
    d = pool.runInteraction(i1)
    d.addCallback(lambda x: pool.runInteraction(i2))
    return d.addCallbacks((lambda x:pool), log.err)

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runInteraction [as 别名]
class ChannelLogger(object):
    implements(IDBLogger)

    def __init__(self, dbfile, **kw):
        # XXX Ignore thread warnings from sqlite3.  Should be OK.
        # http://twistedmatrix.com/trac/ticket/3629
        kw.setdefault("check_same_thread", False)

        from twisted.enterprise.adbapi import ConnectionPool
        type = 'sqlite3'
        self.dbfile = dbfile
        self.dbconn = ConnectionPool(type, dbfile, **kw)
        self.table = 'channels'
        self.initialize_db()

    def initialize_db(self):
        return self.dbconn.runInteraction(self._initialize_db, self.table)

    @staticmethod
    def _initialize_db(tx, table):
        tx.execute('CREATE TABLE IF NOT EXISTS {0} ('
                   'id INTEGER PRIMARY KEY AUTOINCREMENT,'
                   'timestamp INTEGER,'
                   'channel TEXT,'
                   'nick TEXT,'
                   'msg TEXT )'.format(table))

    def log(self, who, chan, msg):
        return self.dbconn.runInteraction(self._log, who, chan, msg, self.table)

    @staticmethod
    def _log(tx, who, chan, msg, table):
        now = int(time.time())
        stmt = 'INSERT INTO {0}(timestamp,nick,channel,msg) VALUES(?,?,?,?)'
        tx.execute(stmt.format(table), (now, who, chan, msg) )

# 需要导入模块: from twisted.enterprise.adbapi import ConnectionPool [as 别名]
# 或者: from twisted.enterprise.adbapi.ConnectionPool import runInteraction [as 别名]
def getReadySqlite(connstr):
    pool = ConnectionPool('pysqlite2.dbapi2', connstr,
                          cp_min=1, cp_max=1)
    def interaction(c):
        try:
            c.execute('''create table sticky (
                    id integer primary key,
                    board_id text,
                    updated timestamp default current_timestamp,
                    note text,
                    x integer,
                    y integer)''')
        except Exception as e:
            log.err(e)
        try:
            c.execute('''create table image (
                id integer primary key,
                board_id text,
                updated timestamp default current_timestamp,
                data blob
            )''')
            c.execute('''create unique index image_board_id on image(board_id)''')
        except Exception as e:
            log.err(e)
    return pool.runInteraction(interaction).addCallbacks((lambda x:pool), log.err)

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

        type = "sqlite"

        self.coordinator = None
        self.histcollector = None
        self._db_location = db_location

        # 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.2')
             
    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(self._db_location, self._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])

            elif version == '0.1':
                # update DB schema version to '0.2'
                try:
                    # update common table
                    txn.execute("UPDATE common SET parm_value=0.2 WHERE parm='schema_version';")

                    # history_periods table
                    txn.execute("CREATE TABLE history_periods(id integer PRIMARY KEY AUTOINCREMENT NOT NULL,\
                                name varchar(20), secs integer NOT NULL, sysflag CHAR(1) NOT NULL DEFAULT '0');")
                    
                    # default values for history_periods table
                    txn.execute("INSERT INTO history_periods VALUES(1,'Disabled',0,'1');")
                    txn.execute("INSERT INTO history_periods VALUES(2,'5 min',300,'1');")
                    txn.execute("INSERT INTO history_periods VALUES(3,'15 min',900,'1');")
                    txn.execute("INSERT INTO history_periods VALUES(4,'30 min',1800,'1');")
#.........这里部分代码省略.........

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

#.........这里部分代码省略.........
			if self.debug:
				qlog = self._log_preparedsql(query, params)
				log.msg('%s failed executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.INFO, spider=spider)
		#	error.raiseException() # keep bubbling

		def on_operationalerror(error, query, params):
			error.trap(self.dbapi.OperationalError)
			e = error.getErrorMessage()
			self.stats.inc_value('sqlmagic/error_operational')
			if self.debug:
				qlog = self._log_preparedsql(query, params)
				log.msg('%s failed executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING, spider=spider)
		#	error.raiseException() # keep bubbling

		def on_seriouserror(error, query, params):
			error.trap(self.dbapi.ProgrammingError, self.dbapi.InterfaceError)
			e = error.getErrorMessage()
			self.stats.inc_value('sqlmagic/error_connection')
			if self.debug:
				qlog = self._log_preparedsql(query, params)
				log.msg('%s FAILED executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING, spider=spider)
			error.raiseException() # keep bubbling
			return error

		def update(error, query, params):
			error.trap(self.dbapi.IntegrityError)
			if error.value[0] != 1062: # Duplicate key
				error.raiseException() # keep bubbling
			#e = error.getErrorMessage()
			#if self.debug:
			#	qlog = self._log_preparedsql(query, params)
			#	log.msg('%s got error %s - trying update' % (self.__class__.__name__, e), level=log.DEBUG, spider=spider)
			self.stats.inc_value('sqlmagic/sqlop_update_after_insert_tries')
			d = self.__dbpool.runInteraction(self.transaction, query, params, item, spider)
			d.addCallback(on_update, query, params)
			return d

		# try insert
		query, params = _sql_format(self.queries['insert'], item, paramstyle=self.paramstyle, identifier=self.identifier)
		#query, params = _sql_format(self.queries['upsert'], item, paramstyle=self.paramstyle, identifier=self.identifier)
		deferred = self.__dbpool.runInteraction(self.transaction, query, params, item, spider)
		deferred.addCallback(on_insert, query, params)
		deferred.addErrback(on_seriouserror, query, params)
		deferred.addErrback(on_operationalerror, query, params)
		#deferred.addErrback(on_integrityerror, query, params) # ignore failing inserts before update
		# on failure, update
		query, params = _sql_format(self.queries['update'], item, paramstyle=self.paramstyle, identifier=self.identifier)
		deferred.addErrback(update, query, params)
		deferred.addErrback(on_seriouserror, query, params)
		deferred.addErrback(on_operationalerror, query, params)
		deferred.addErrback(on_integrityerror, query, params)
		deferred.addErrback(self._database_error, item, spider)

	#	deferred = self.insert_or_update((query,params), (update, uparams), item, spider)

		self.stats.inc_value('sqlmagic/total_items_returned')
		return deferred

	def transaction(self, txn, query, params, item, spider):
		self.stats.inc_value('sqlmagic/sqlop_transact_%s' % query[:6].lower())
		txn.execute(query, params)

	"""
	def xtransaction(self, txn, query, params, item, spider):
		# primary key check
		query, params = _sql_format(self.queries['select'], item, paramstyle=self.paramstyle, identifier=self.identifier)