Python psycopg2.pool() Examples

def __init__(self, max_conn, expiration, disable_pooling, **kwargs):
        """Initialize the connection pool."""
        self._pool = []
        self._used = {}
        self._rused = {}  # id(conn) -> key map
        self._tused = {}  # last used timestamp
        self._keys = 0
        self._disposed = False
        self.expiration = expiration
        self.max_conn = max_conn
        self._debug = kwargs.get('debug', False)
        self._disable_pooling = disable_pooling  # do not pool database connections
        if self._debug:
            self._debug_fn = self._debug.debug if hasattr(self._debug, 'debug') else self._debug.write
            self._debug_msg_suffix = '\n' if not hasattr(self._debug, 'debug') else ''
        if 'debug' in kwargs:
            del kwargs['debug']
        self._db_config = kwargs
        self._dsn = kwargs.get('dsn', None) 

def _get_conn(self, key=None):
        """Get a free connection and assign it to 'key' if not None."""
        if self._disposed:
            raise PoolError('Connection pool is disposed')

        if self._disable_pooling:
            return self._connect(key)

        if key is None:
            key = self._get_key()
        if key in self._used:
            return self._used[key]

        if self._pool:
            self._used[key] = conn = self._pool.pop()
            self._rused[id(conn)] = key
            self._tused[id(conn)] = time.time()
            return conn
        else:
            if len(self._used) == self.max_conn:
                raise PoolError('Connection pool exhausted')
            return self._connect(key) 

def test_execute_in_pool():
    q = 'SELECT 1'
    with testing.postgresql.Postgresql() as postgresql:
        url = postgresql.url()
        dsn = postgresql.dsn()
        pool = psycopg2.pool.SimpleConnectionPool(1, 1,
                                                  dbname=dsn['database'],
                                                  user=dsn['user'],
                                                  password='',
                                                  host=dsn['host'],
                                                  port=dsn['port'])
        cur = pool.getconn(key=dsn['user']).cursor()
        cur.execute(q)

    subsegment = xray_recorder.current_segment().subsegments[0]
    assert subsegment.name == 'execute'
    sql = subsegment.sql
    assert sql['database_type'] == 'PostgreSQL'
    assert sql['user'] == dsn['user']
    assert sql['url'] == url
    assert sql['database_version'] 

def _connect(self):
        if self.application_name is None:
            st = inspect.stack()
            self.application_name = os.path.basename(st[-1][1])
        conn_args = dict(
            host=self.url.hostname,
            port=self.url.port,
            database=self.url.database,
            user=self.url.username,
            password=self.url.password,
            application_name=self.application_name + "/" + hgvs.__version__,
        )
        if self.pooling:
            _logger.info("Using UTA ThreadedConnectionPool")
            self._pool = psycopg2.pool.ThreadedConnectionPool(
                hgvs.global_config.uta.pool_min, hgvs.global_config.uta.pool_max, **conn_args)
        else:
            self._conn = psycopg2.connect(**conn_args)
            self._conn.autocommit = True

        self._ensure_schema_exists()

        # remap sqlite's ? placeholders to psycopg2's %s
        self._queries = {k: v.replace('?', '%s') for k, v in six.iteritems(self._queries)} 

def _release(self, conn, remove_from_pool=False):
        if not self._disable_pooling and remove_from_pool and conn in self._pool:
            self._pool.remove(conn)
            del self._tused[id(conn)]
        conn.close()
        self._log('Connection closed: %s [pool: %d]' % (conn, len(self._pool))) 

def __init__(self):
		self.__dict__ = self._shared_state

		if not hasattr(self, 'connected'):
			print("Database connection pool init!")
			try:
				self.dbPool = psycopg2.pool.ThreadedConnectionPool(1, self._db_max_connections, dbname=settings.DATABASE_DB_NAME, user=settings.DATABASE_USER,password=settings.DATABASE_PASS)
			except psycopg2.OperationalError:
				self.dbPool = psycopg2.pool.ThreadedConnectionPool(1, self._db_max_connections, host=settings.DATABASE_IP, dbname=settings.DATABASE_DB_NAME, user=settings.DATABASE_USER,password=settings.DATABASE_PASS)

			self.connected = True 

def apply(self, callback, context):
        # Test if the original callback accepts a 'conn' keyword.
        # Ignore it if it does not need a database connection.
        args = inspect.getargspec(context['callback'])[0]
        if 'conn' not in args:
            return callback

        def wrapper(*args, **kwargs):
            for _ in range(0, 3):
                # Connect to the database
                conn = None
                try:
                    conn = self.pool.getconn()
                except HTTPResponse, e:
                    raise HTTPError(500, "Database Error", e)

                # Add the connection handle as a keyword argument.
                kwargs['conn'] = conn

                try:
                    rv = callback(*args, **kwargs)
                    return rv
                except HTTPError, e:
                    raise
                except HTTPResponse, e:
                    raise 

def __init__(self):
        self.pool = SimpleConnectionPool(1, 10,
                                         dbname=os.environ['INFRABOX_DATABASE_DB'],
                                         user=os.environ['INFRABOX_DATABASE_USER'],
                                         password=os.environ['INFRABOX_DATABASE_PASSWORD'],
                                         host=os.environ['INFRABOX_DATABASE_HOST'],
                                         port=os.environ['INFRABOX_DATABASE_PORT']) 

def apply(self, callback, context):
        # Test if the original callback accepts a 'conn' keyword.
        # Ignore it if it does not need a database connection.
        args = inspect.getargspec(context['callback'])[0]
        if 'conn' not in args:
            return callback

        def wrapper(*args, **kwargs):
            for _ in range(0, 3):
                # Connect to the database
                conn = None
                try:
                    conn = self.pool.getconn()
                except HTTPResponse, e:
                    raise HTTPError(500, "Database Error", e)

                # Add the connection handle as a keyword argument.
                kwargs['conn'] = conn

                try:
                    rv = callback(*args, **kwargs)
                    return rv
                except HTTPError, e:
                    raise
                except HTTPResponse, e:
                    raise 

def __init__(self):
        self.pool = SimpleConnectionPool(1, 10,
                                         dbname=os.environ['INFRABOX_DATABASE_DB'],
                                         user=os.environ['INFRABOX_DATABASE_USER'],
                                         password=os.environ['INFRABOX_DATABASE_PASSWORD'],
                                         host=os.environ['INFRABOX_DATABASE_HOST'],
                                         port=os.environ['INFRABOX_DATABASE_PORT']) 

def get_db_connection():
    """
    psycopg2 connection context manager.
    Fetch a connection from the connection pool and release it.
    """
    try:
        connection = pool.getconn()
        yield connection
    finally:
        pool.putconn(connection) 

def _connect(self):
        if self.application_name is None:
            st = inspect.stack()
            self.application_name = os.path.basename(st[-1][1])
        conn_args = dict(
            host=self.url.hostname,
            port=self.url.port,
            database=self.url.database,
            user=self.url.username,
            password=self.url.password,
            application_name=self.application_name + "/" + hgvs.__version__,
        )
        if self.pooling:
            _logger.info("Using UTA ThreadedConnectionPool")
            self._pool = psycopg2.pool.ThreadedConnectionPool(
                hgvs.global_config.uta.pool_min, hgvs.global_config.uta.pool_max, **conn_args)
        else:
            self._conn = psycopg2.connect(**conn_args)
            self._conn.autocommit = True
            with self._get_cursor() as cur:
                self._set_search_path(cur)

        self._ensure_schema_exists()

        # remap sqlite's ? placeholders to psycopg2's %s
        self._queries = {k: v.replace('?', '%s') for k, v in six.iteritems(self._queries)} 

def close(self):
        if self.pooling:
            _logger.warning("Closing pool; future mapping and validation will fail.")
            self._pool.closeall()
        else:
            _logger.warning("Closing connection; future mapping and validation will fail.")
            if self._conn is not None:
                self._conn.close() 

def _purge_expired_connections(self):
        if self._disable_pooling:
            return

        now = time.time()
        expiry_list = []
        for item in self._pool:
            conn_time = self._tused[id(item)]
            elapsed = now - conn_time
            if elapsed >= self.expiration:
                expiry_list.append(item)

        self._log('Purging... [pool: %d, expired: %d]' % (len(self._pool), len(expiry_list)))
        for item in expiry_list:
            self._release(item, True) 

def connect(db_url=None,
            pooling=hgvs.global_config.uta.pooling,
            application_name=None,
            mode=None,
            cache=None):
    """Connect to a UTA database instance and return a UTA interface instance.

    :param db_url: URL for database connection
    :type db_url: string
    :param pooling: whether to use connection pooling (postgresql only)
    :type pooling: bool
    :param application_name: log application name in connection (useful for debugging; PostgreSQL only)
    :type application_name: str

    When called with an explicit db_url argument, that db_url is used for connecting.

    When called without an explicit argument, the function default is
    determined by the environment variable UTA_DB_URL if it exists, or
    hgvs.datainterface.uta.public_db_url otherwise.

    >>> hdp = connect()
    >>> hdp.schema_version()
    '1.1'

    The format of the db_url is driver://user:pass@host/database/schema (the same
    as that used by SQLAlchemy).  Examples:

    A remote public postgresql database:
        postgresql://anonymous:anonymous@uta.biocommons.org/uta/uta_20170707'

    A local postgresql database:
        postgresql://localhost/uta_dev/uta_20170707

    For postgresql db_urls, pooling=True causes connect to use a
    psycopg2.pool.ThreadedConnectionPool.
    """

    _logger.debug('connecting to ' + str(db_url) + '...')

    if db_url is None:
        db_url = _get_uta_db_url()

    url = _parse_url(db_url)
    if url.scheme == 'sqlite':
        conn = UTA_sqlite(url, mode, cache)
    elif url.scheme == 'postgresql':
        conn = UTA_postgresql(
            url=url, pooling=pooling, application_name=application_name, mode=mode, cache=cache)
    else:
        # fell through connection scheme cases
        raise RuntimeError("{url.scheme} in {url} is not currently supported".format(url=url))
    _logger.info('connected to ' + str(db_url) + '...')
    return conn 

def connect(db_url=None,
            pooling=hgvs.global_config.uta.pooling,
            application_name=None,
            mode=None,
            cache=None):
    """Connect to a uta/ncbi database instance.

    :param db_url: URL for database connection
    :type db_url: string
    :param pooling: whether to use connection pooling (postgresql only)
    :type pooling: bool
    :param application_name: log application name in connection (useful for debugging; PostgreSQL only)
    :type application_name: str

    When called with an explicit db_url argument, that db_url is used for connecting.

    When called without an explicit argument, the function default is
    determined by the environment variable UTA_DB_URL if it exists, or
    hgvs.datainterface.uta.public_db_url otherwise.

    >>> hdp = connect()
    >>> hdp.schema_version()
    '1.1'

    The format of the db_url is driver://user:pass@host/database (the same
    as that used by SQLAlchemy).  Examples:

    A remote public postgresql database:
        postgresql://anonymous:anonymous@uta.biocommons.org/uta'

    A local postgresql database:
        postgresql://localhost/uta

    A local SQLite database:
      sqlite:////tmp/uta-0.0.6.db

    For postgresql db_urls, pooling=True causes connect to use a
    psycopg2.pool.ThreadedConnectionPool.
    """

    _logger.debug('connecting to ' + str(db_url) + '...')

    if db_url is None:
        db_url = _get_ncbi_db_url()

    url = _parse_url(db_url)
    if url.scheme == 'postgresql':
        conn = NCBI_postgresql(
            url=url, pooling=pooling, application_name=application_name, mode=mode, cache=cache)
    else:
        # fell through connection scheme cases
        raise RuntimeError("{url.scheme} in {url} is not currently supported".format(url=url))
    _logger.info('connected to ' + str(db_url) + '...')
    return conn 

def _put_conn(self, conn, key=None, close=False, fail_silently=False):
        """Stow away a connection."""
        if self._disable_pooling:
            self._release(conn)
            return

        self._log('Putting away %s%s' % (conn, ' key=' + key if key else ''))
        if self._disposed:
            if fail_silently:
                return
            raise PoolError('Connection pool is disposed')

        if key is None:
            key = self._rused.get(id(conn))
        if not key:
            raise PoolError('Trying to put un-keyed connection')

        if len(self._pool) < self.max_conn and not close:
            # Return the connection into a consistent state before putting
            # it back into the pool
            if not conn.closed:
                status = conn.get_transaction_status()
                if status == _ext.TRANSACTION_STATUS_UNKNOWN:
                    # server connection lost
                    self._log('Connection lost. Closing %s' % conn)
                    self._release(conn.close)
                elif status != _ext.TRANSACTION_STATUS_IDLE:
                    # connection in error or in transaction
                    self._log('Connection is in transaction. Rolling back %s' % conn)
                    conn.rollback()
                    self._pool.append(conn)
                else:
                    # regular idle connection
                    self._pool.append(conn)
                    # If the connection is closed, we just discard it.
        else:
            self._log('Closing (pool exhausted or explicit close requested) %s' % conn)
            self._release(conn)

        self._purge_expired_connections()

        # here we check for the presence of key because it can happen that a
        # thread tries to put back a connection after a call to close
        if not self._disposed or key in self._used:
            del self._used[key]
            del self._rused[id(conn)]