Python sqlalchemy.pool() Examples

def _install_reconnector(self):
        # copied from:
        # http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic
        # and slightly adjusted
        @sqlalchemy.event.listens_for(sqlalchemy.pool.Pool, "checkout")
        def ping_connection(dbapi_connection, connection_record, connection_proxy):
            cursor = dbapi_connection.cursor()
            try:
                cursor.execute("SELECT 1")
            except Exception:
                # dispose the whole pool instead of invalidating one at a time
                connection_proxy._pool.dispose()
                # pool will try connecting again up to three times before giving up
                raise sqlalchemy.exc.DisconnectionError()
            cursor.close() 

def _init_dependencies(cls):
        global sa, pool, types
        if sa is not None:
            return
        try:
            import sqlalchemy as sa
            import sqlalchemy.pool as pool
            from sqlalchemy import types
        except ImportError:
            raise InvalidCacheBackendError("Database cache backend requires "
                                            "the 'sqlalchemy' library") 

def _init_dependencies(cls):
        global sa, pool, types
        if sa is not None:
            return
        try:
            import sqlalchemy as sa
            import sqlalchemy.pool as pool
            from sqlalchemy import types
        except ImportError:
            raise InvalidCacheBackendError("Database cache backend requires "
                                            "the 'sqlalchemy' library") 

def get_engine_with_numpy(db_parameters, user=None, password=None,
                          account=None):
    """
    Creates a connection using the parameters defined in JDBC connect string
    """
    from sqlalchemy import create_engine
    from snowflake.sqlalchemy import URL

    if user is not None:
        db_parameters['user'] = user
    if password is not None:
        db_parameters['password'] = password
    if account is not None:
        db_parameters['account'] = account

    from sqlalchemy.pool import NullPool
    engine = create_engine(URL(
        user=db_parameters['user'],
        password=db_parameters['password'],
        host=db_parameters['host'],
        port=db_parameters['port'],
        database=db_parameters['database'],
        schema=db_parameters['schema'],
        account=db_parameters['account'],
        protocol=db_parameters['protocol'],
        numpy=True,
    ), poolclass=NullPool)

    return engine 

def get_engine_with_numpy(db_parameters, user=None, password=None,
                          account=None):
    """
    Creates a connection using the parameters defined in JDBC connect string
    """
    from sqlalchemy import create_engine
    from snowflake.sqlalchemy import URL

    if user is not None:
        db_parameters['user'] = user
    if password is not None:
        db_parameters['password'] = password
    if account is not None:
        db_parameters['account'] = account

    from sqlalchemy.pool import NullPool
    engine = create_engine(URL(
        user=db_parameters['user'],
        password=db_parameters['password'],
        host=db_parameters['host'],
        port=db_parameters['port'],
        database=db_parameters['database'],
        schema=db_parameters['schema'],
        account=db_parameters['account'],
        protocol=db_parameters['protocol'],
        numpy=True,
    ), poolclass=NullPool)

    return engine 

def _init_dependencies(cls):
        global sa, pool, types
        if sa is not None:
            return
        try:
            import sqlalchemy as sa
            import sqlalchemy.pool as pool
            from sqlalchemy import types
        except ImportError:
            raise InvalidCacheBackendError("Database cache backend requires "
                                            "the 'sqlalchemy' library") 

def __call__(self):

        """This method will activate the class and start the analysis of the input file."""

        # NOTE: Pool, Process and Manager must NOT become instance attributes!
        # Otherwise it will raise all sorts of mistakes

        self.logger.debug("Source: %s",
                          self.json_conf["pick"]["output_format"]["source"])
        if self.json_conf["db_settings"]["dbtype"] == "sqlite":
            self.queue_pool = sqlalchemy.pool.QueuePool(
                self.db_connection,
                pool_size=self.procs,
                max_overflow=0)

        try:
            self._parse_and_submit_input()
        except UnsortedInput as _:
            self.logger.error(
                "The input files were not properly sorted! Please run prepare and retry.")
        except Exception:
            self.__cleanup()
            raise

        self.__cleanup()
        self.main_logger.info("Finished analysis of %s", self.input_file)

        sys.exit(0)
# pylint: enable=too-many-instance-attributes 

def apply_driver_hacks(self, app, info, options):
        """This method is called before engine creation and used to inject
        driver specific hacks into the options.  The `options` parameter is
        a dictionary of keyword arguments that will then be used to call
        the :func:`sqlalchemy.create_engine` function.

        The default implementation provides some saner defaults for things
        like pool sizes for MySQL and sqlite.  Also it injects the setting of
        `SQLALCHEMY_NATIVE_UNICODE`.
        """
        if info.drivername.startswith('mysql'):
            info.query.setdefault('charset', 'utf8')
            if info.drivername != 'mysql+gaerdbms':
                options.setdefault('pool_size', 10)
                options.setdefault('pool_recycle', 7200)
        elif info.drivername == 'sqlite':
            pool_size = options.get('pool_size')
            detected_in_memory = False
            # we go to memory and the pool size was explicitly set to 0
            # which is fail.  Let the user know that
            if info.database in (None, '', ':memory:'):
                detected_in_memory = True
                from sqlalchemy.pool import StaticPool
                options['poolclass'] = StaticPool
                if 'connect_args' not in options:
                    options['connect_args'] = {}
                options['connect_args']['check_same_thread'] = False

                if pool_size == 0:
                    raise RuntimeError('SQLite in memory database with an '
                                       'empty queue not possible due to data '
                                       'loss.')
            # if pool size is None or explicitly set to 0 we assume the
            # user did not want a queue for this sqlite connection and
            # hook in the null pool.
            elif not pool_size:
                from sqlalchemy.pool import NullPool
                options['poolclass'] = NullPool

            # if it's not an in memory database we make the path absolute.
            if not detected_in_memory:
                info.database = os.path.join(app.root_path, info.database)

        unu = app.config['SQLALCHEMY_NATIVE_UNICODE']
        if unu is None:
            unu = self.use_native_unicode
        if not unu:
            options['use_native_unicode'] = False 

def apply_driver_hacks(self, app, info, options):
        """This method is called before engine creation and used to inject
        driver specific hacks into the options.  The `options` parameter is
        a dictionary of keyword arguments that will then be used to call
        the :func:`sqlalchemy.create_engine` function.

        The default implementation provides some saner defaults for things
        like pool sizes for MySQL and sqlite.  Also it injects the setting of
        `SQLALCHEMY_NATIVE_UNICODE`.
        """
        if info.drivername.startswith('mysql'):
            info.query.setdefault('charset', 'utf8')
            if info.drivername != 'mysql+gaerdbms':
                options.setdefault('pool_size', 10)
                options.setdefault('pool_recycle', 7200)
        elif info.drivername == 'sqlite':
            pool_size = options.get('pool_size')
            detected_in_memory = False
            if info.database in (None, '', ':memory:'):
                detected_in_memory = True
                from sqlalchemy.pool import StaticPool
                options['poolclass'] = StaticPool
                if 'connect_args' not in options:
                    options['connect_args'] = {}
                options['connect_args']['check_same_thread'] = False

                # we go to memory and the pool size was explicitly set
                # to 0 which is fail.  Let the user know that
                if pool_size == 0:
                    raise RuntimeError('SQLite in memory database with an '
                                       'empty queue not possible due to data '
                                       'loss.')
            # if pool size is None or explicitly set to 0 we assume the
            # user did not want a queue for this sqlite connection and
            # hook in the null pool.
            elif not pool_size:
                from sqlalchemy.pool import NullPool
                options['poolclass'] = NullPool

            # if it's not an in memory database we make the path absolute.
            if not detected_in_memory:
                info.database = os.path.join(app.root_path, info.database)

        unu = app.config['SQLALCHEMY_NATIVE_UNICODE']
        if unu is None:
            unu = self.use_native_unicode
        if not unu:
            options['use_native_unicode'] = False 

def __init__(self, url, name='connection', watermark=True, **kwargs):
        if not sqlalchemy:
            raise lore.env.ModuleNotFoundError('No module named sqlalchemy. Please add it to requirements.txt.')

        parsed = lore.env.parse_url(url)
        self.adapter = parsed.scheme

        if self.adapter == 'postgres':
            require(lore.dependencies.POSTGRES)
        if self.adapter == 'snowflake':
            require(lore.dependencies.SNOWFLAKE)
            if 'numpy' not in parsed.query:
                logger.error('You should add `?numpy=True` query param to your snowflake connection url to ensure proper compatibility')

        for int_value in ['pool_size', 'pool_recycle', 'max_overflow']:
            if int_value in kwargs:
                kwargs[int_value] = int(kwargs[int_value])
        if 'poolclass' in kwargs:
            kwargs['poolclass'] = getattr(sqlalchemy.pool, kwargs['poolclass'])
        if '__name__' in kwargs:
            del kwargs['__name__']
        if 'echo' not in kwargs:
            kwargs['echo'] = False
        logger.info("Creating engine: %s %s" % (url, kwargs))
        self._engine = sqlalchemy.create_engine(url, **kwargs).execution_options(autocommit=True)
        self._metadata = None
        self.name = name
        self.url = url
        self._transactions = []
        self.__thread_local = threading.local()

        @event.listens_for(self._engine, "before_cursor_execute", retval=True)
        def comment_sql_calls(conn, cursor, statement, parameters, context, executemany):
            conn.info.setdefault('query_start_time', []).append(datetime.now())
            if watermark:
                stack = inspect.stack()[1:-1]
                if sys.version_info.major == 3:
                    stack = [(x.filename, x.lineno, x.function) for x in stack]
                else:
                    stack = [(x[1], x[2], x[3]) for x in stack]

                paths = [x[0] for x in stack]
                origin = next((x for x in paths if x.startswith(lore.env.ROOT)), None)
                if origin is None:
                    origin = next((x for x in paths if 'sqlalchemy' not in x), None)
                if origin is None:
                    origin = paths[0]
                caller = next(x for x in stack if x[0] == origin)

                statement = "/* %s | %s:%d in %s */\n" % (lore.env.APP, caller[0], caller[1], caller[2]) + statement
            return statement, parameters

        @event.listens_for(self._engine, "after_cursor_execute")
        def time_sql_calls(conn, cursor, statement, parameters, context, executemany):
            total = datetime.now() - conn.info['query_start_time'].pop(-1)
            logger.info("SQL: %s" % total)

        @event.listens_for(self._engine, "connect")
        def receive_connect(dbapi_connection, connection_record):
            if hasattr(dbapi_connection, 'get_dsn_parameters'):
                logger.info("connect: %s" % dbapi_connection.get_dsn_parameters())