Python adbapi.ConnectionPool类代码示例

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)

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

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)

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) )

 def test_startedClose(self):
     """
     If L{ConnectionPool.close} is called after it has been started, but
     not by its shutdown trigger, the shutdown trigger is cancelled.
     """
     reactor = EventReactor(True)
     pool = ConnectionPool('twisted.test.test_adbapi', cp_reactor=reactor)
     # There should be a shutdown trigger waiting.
     self.assertEquals(reactor.triggers, [('during', 'shutdown', pool.finalClose)])
     pool.close()
     # But not anymore.
     self.assertFalse(reactor.triggers)

 def test_unstartedClose(self):
     """
     If L{ConnectionPool.close} is called without L{ConnectionPool.start}
     having been called, the pool's startup event is cancelled.
     """
     reactor = EventReactor(False)
     pool = ConnectionPool('twisted.test.test_adbapi', cp_reactor=reactor)
     # There should be a startup trigger waiting.
     self.assertEquals(reactor.triggers, [('after', 'startup', pool._start)])
     pool.close()
     # But not anymore.
     self.assertFalse(reactor.triggers)

    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 open(self):
        """
        Access the underlying database.
        @return: a db2 connection object for this index's underlying data store.
        """
        if not self.initialized:

            self.pool = ConnectionPool(self.dbapiName, *self.dbapiArgs, **self.dbapikwargs)

            # sqlite3 is not thread safe which means we have to close the sqlite3 connections in the same thread that
            # opened them. We need a special thread pool class that has a thread worker function that does a close
            # when a thread is closed.
            if self.dbapiName == "sqlite3":
                self.pool.threadpool.stop()
                self.pool.threadpool = ConnectionClosingThreadPool(1, 1)
                self.pool.threadpool.start()
                self.pool.threadpool.pool = self.pool

            #
            # Set up the schema
            #
            # Create CALDAV table if needed

            try:
                test = (yield self._test_schema_table())
                if test:
                    version = (yield self._db_value_for_sql("select VALUE from CALDAV where KEY = 'SCHEMA_VERSION'"))
                    dbtype = (yield self._db_value_for_sql("select VALUE from CALDAV where KEY = 'TYPE'"))

                    if (version != self._db_version()) or (dbtype != self._db_type()):

                        if dbtype != self._db_type():
                            log.error("Database %s has different type (%s vs. %s)"
                                      % (self.dbID, dbtype, self._db_type()))

                            # Delete this index and start over
                            yield self._db_remove()
                            yield self._db_init()

                        elif version != self._db_version():
                            log.error("Database %s has different schema (v.%s vs. v.%s)"
                                      % (self.dbID, version, self._db_version()))

                            # Upgrade the DB
                            yield self._db_upgrade(version)

                else:
                    yield self._db_init()
                self.initialized = True
            except:
                # Clean up upon error so we don't end up leaking threads
                self.pool.close()
                self.pool = None
                raise

 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 __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 _runInteraction(self, interaction, *args, **kw):
		try:
			return ConnectionPool._runInteraction(self, interaction, *args, **kw)
		except MySQLdb.OperationalError as e:
			if e[0] not in (2006, 2013, 1213):
				raise
			# 2006 MySQL server has gone away
			# 2013 Lost connection to MySQL server
			# 1213 Deadlock found when trying to get lock; try restarting transaction
			log.msg("%s got error %s, retrying operation" % (self.__class__.__name__, e))
			conn = self.connections.get(self.threadID())
			self.disconnect(conn)
			# try the interaction again
			return ConnectionPool._runInteraction(self, interaction, *args, **kw)
		except MySQLdb.InterfaceError as e:
			if e[0] not in (0,):
				raise
			# 0 Interface error (conn gone away or closed)
			log.msg("%s got error %s, retrying operation" % (self.__class__.__name__, e))
			conn = self.connections.get(self.threadID())
			self.disconnect(conn)
			# try the interaction again
			return ConnectionPool._runInteraction(self, interaction, *args, **kw)

 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
                                                 )''')

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) )
#.........这里部分代码省略.........

class AbstractADBAPIDatabase(object):
    """
    A generic SQL database.
    """

    def __init__(self, dbID, dbapiName, dbapiArgs, persistent, **kwargs):
        """
        
        @param persistent: C{True} if the data in the DB must be perserved during upgrades,
            C{False} if the DB data can be re-created from an external source.
        @type persistent: bool
        """
        self.dbID = dbID
        self.dbapiName = dbapiName
        self.dbapiArgs = dbapiArgs
        self.dbapikwargs = kwargs

        self.persistent = persistent
        
        self.initialized = False

    def __repr__(self):
        return "<%s %r>" % (self.__class__.__name__, self.pool)

    @inlineCallbacks
    def open(self):
        """
        Access the underlying database.
        @return: a db2 connection object for this index's underlying data store.
        """
        if not self.initialized:

            self.pool = ConnectionPool(self.dbapiName, *self.dbapiArgs, **self.dbapikwargs)
            
            # sqlite3 is not thread safe which means we have to close the sqlite3 connections in the same thread that
            # opened them. We need a special thread pool class that has a thread worker function that does a close
            # when a thread is closed.
            if self.dbapiName == "sqlite3":
                self.pool.threadpool.stop()
                self.pool.threadpool = ConnectionClosingThreadPool(1, 1)
                self.pool.threadpool.start()
                self.pool.threadpool.pool = self.pool

            #
            # Set up the schema
            #
            # Create CALDAV table if needed

            test = (yield self._test_schema_table())
            if test:
                version = (yield self._db_value_for_sql("select VALUE from CALDAV where KEY = 'SCHEMA_VERSION'"))
                dbtype = (yield self._db_value_for_sql("select VALUE from CALDAV where KEY = 'TYPE'"))

                if (version != self._db_version()) or (dbtype != self._db_type()):

                    if dbtype != self._db_type():
                        log.err("Database %s has different type (%s vs. %s)"
                                % (self.dbID, dbtype, self._db_type()))

                        # Delete this index and start over
                        yield self._db_remove()
                        yield self._db_init()

                    elif version != self._db_version():
                        log.err("Database %s has different schema (v.%s vs. v.%s)"
                                % (self.dbID, version, self._db_version()))
                        
                        # Upgrade the DB
                        yield self._db_upgrade(version)

            else:
                yield self._db_init()
            self.initialized = True

    def close(self):
        
        if self.initialized:
            self.pool.close()
            self.pool = None
            self.initialized = False

    @inlineCallbacks
    def clean(self):
        
        if not self.initialized:
            yield self.open()

        yield self._db_empty_data_tables()

    @inlineCallbacks
    def execute(self, sql, *query_params):
        
        if not self.initialized:
            yield self.open()

        yield self._db_execute(sql, *query_params)

    @inlineCallbacks
    def executescript(self, script):
        
#.........这里部分代码省略.........

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