Python sqlalchemy.exc.ResourceClosedError() Examples

def test_used_with_begin_nested(self, session, assert_session):
        @transaction(session)
        def create_user(commit_inner):
            with session.begin_nested():
                user = User(username='test1', email='1@b.com', password='1')
                session.add(user)

            with session.begin_nested():
                user = User(username='test2', email='2@b.com', password='1')
                session.add(user)
            if commit_inner:
                session.commit()

        create_user(commit_inner=False)
        assert len(assert_session.query(User).all()) == 2

        self.clear_user()
        msg = 'This transaction is closed'
        with pytest.raises(ResourceClosedError, match=msg):
            create_user(commit_inner=True)
        assert len(assert_session.query(User).all()) == 0 

def update_repo_directory(ctx, repo_directory):
    """
    Update Facade worker repo cloning directory
    """
    app = ctx.obj

    db = get_db_connection(app)

    updateRepoDirectorySQL = s.sql.text("""
        UPDATE augur_data.settings SET VALUE = :repo_directory WHERE setting='repo_directory';
    """)

    try:
        pd.read_sql(updateRepoDirectorySQL, db, params={'repo_directory': repo_directory})
    except exc.ResourceClosedError as error:
        print(f"Successfully updated the Facade worker repo directory.")
        # pd.read_sql() will throw an AttributeError when it can't sucessfully "fetch" any rows from the result.
        # Since there's no rows to fetch after a successful insert, this is how we know it worked.
        # I know it's weird, sue me (jk please don't)

# get_db_version is a helper function to print_db_version and upgrade_db_version 

def test_connectionless_autoclose_no_metadata(self):
        # TODO: deprecate for 2.0
        result = testing.db.execute(text("update users set user_id=5"))
        connection = result.connection
        assert connection.closed

        assert_raises_message(
            exc.ResourceClosedError,
            "This result object does not return rows.",
            result.fetchone,
        )
        assert_raises_message(
            exc.ResourceClosedError,
            "This result object does not return rows.",
            result.keys,
        ) 

def get_version(sql):
    table_info = "PRAGMA table_info('migrations')"
    from sqlalchemy.exc import ResourceClosedError
    try:
        if not sql.execute(table_info).fetchall():
            return 0
    except ResourceClosedError as e:
        if 'does not return rows' in str(e):
            return 0
    version_query = "SELECT version from migrations"
    version = sql.execute(version_query).fetchone()
    return version and version[0] or 0 

def test_nested_self_raise(self, session, assert_session):
        @transaction(session)
        def create_user():
            user = User(username='test1', email='1@b.com', password='1')
            db.session.add(user)

        @transaction(session)
        def view_func():
            user = User(username='test2', email='2@b.com', password='1')
            db.session.add(user)
            create_user()

        if session.autocommit is False:
            msg = 'This transaction is closed'
            with pytest.raises(ResourceClosedError, match=msg):
                view_func()
        else:
            msg = r'A transaction is already begun.*'
            with pytest.raises(InvalidRequestError, match=msg):
                view_func()
        assert len(assert_session.query(User).all()) == 0 

def test_notautocommit_use_nested_alone_commit_raise(self, db_session):
        @transaction(db_session, nested=True)
        def create_user():
            user = User(username='test1', email='1@b.com', password='1')
            db_session.add(user)
            db_session.commit()

        msg = 'This transaction is closed'
        with pytest.raises(ResourceClosedError, match=msg):
            create_user() 

def test_nested_self_with_nested_arg_is_true_commit_raise(
            self, session, assert_session):
        @transaction(session, nested=True)
        def create_user():
            user = User(username='test1', email='1@b.com', password='1')
            session.add(user)
            session.commit()

        @transaction(session)
        def view_func():
            create_user()

        msg = r'This transaction is closed'
        with pytest.raises(ResourceClosedError, match=msg):
            view_func() 

def get_connection(self):
        """Get a connection to this Database. Connections are retrieved from a
        pool.
        """
        if not self.open:
            raise exc.ResourceClosedError('Database closed.')

        return Connection(self._engine.connect()) 

def do_exec(args):
            msg, sql = args
            with log_duration(msg):
                with engine.begin() as trans:
                    res = trans.execute(sql)
                    try:
                        logger.info(f"SQL result", job=msg, result=res.fetchall())
                    except ResourceClosedError:
                        pass  # Nothing to do here
                    except Exception as exc:
                        logger.error("Hit an issue.", exc=exc)
                        raise exc 

def do_exec(args):
            msg, sql = args
            with log_duration(msg):
                started = datetime.datetime.now()
                with engine.begin() as trans:
                    res = trans.execute(sql)
                    try:
                        logger.info(f"Ran", job=msg, result=res.fetchall())
                    except ResourceClosedError:
                        pass  # Nothing to do here 

def validate(self, max_retries=0):
        """Performs basic **connection** validation of a sqlalchemy engine."""

        def _retry_on_exception(exc):
            LOG.warning("Engine connection (validate) failed due to '%s'", exc)
            if isinstance(exc, sa_exc.OperationalError) and \
               _is_db_connection_error(six.text_type(exc.args[0])):
                # We may be able to fix this by retrying...
                return True
            if isinstance(exc, (sa_exc.TimeoutError,
                                sa_exc.ResourceClosedError,
                                sa_exc.DisconnectionError)):
                # We may be able to fix this by retrying...
                return True
            # Other failures we likely can't fix by retrying...
            return False

        @tenacity.retry(
            stop=tenacity.stop_after_attempt(max(0, int(max_retries))),
            wait=tenacity.wait_exponential(),
            reraise=True,
            retry=tenacity.retry_if_exception(_retry_on_exception)
        )
        def _try_connect(engine):
            # See if we can make a connection happen.
            #
            # NOTE(harlowja): note that even though we are connecting
            # once it does not mean that we will be able to connect in
            # the future, so this is more of a sanity test and is not
            # complete connection insurance.
            with contextlib.closing(engine.connect()):
                pass

        _try_connect(self._engine) 

def test_graceful_fetch_on_non_rows(self):
        """test that calling fetchone() etc. on a result that doesn't
        return rows fails gracefully.

        """

        # these proxies don't work with no cursor.description present.
        # so they don't apply to this test at the moment.
        # result.FullyBufferedCursorResult,
        # result.BufferedRowCursorResult,
        # result.BufferedColumnCursorResult

        users = self.tables.users

        conn = testing.db.connect()
        for meth in [
            lambda r: r.fetchone(),
            lambda r: r.fetchall(),
            lambda r: r.first(),
            lambda r: r.scalar(),
            lambda r: r.fetchmany(),
            lambda r: r._getter("user"),
            lambda r: r.keys(),
            lambda r: r.columns("user"),
            lambda r: r.cursor_strategy.fetchone(r, r.cursor),
        ]:
            trans = conn.begin()
            result = conn.execute(users.insert(), user_id=1)
            assert_raises_message(
                exc.ResourceClosedError,
                "This result object does not return rows. "
                "It has been closed automatically.",
                meth,
                result,
            )
            trans.rollback() 

def test_fetchone_til_end(self, connection):
        result = connection.exec_driver_sql("select * from users")
        eq_(result.fetchone(), None)
        eq_(result.fetchone(), None)
        eq_(result.fetchone(), None)
        result.close()
        assert_raises_message(
            exc.ResourceClosedError,
            "This result object is closed.",
            result.fetchone,
        ) 

def _assert_result_closed(self, r):
        assert_raises_message(
            sa_exc.ResourceClosedError, "object is closed", r.fetchone
        )

        assert_raises_message(
            sa_exc.ResourceClosedError, "object is closed", r.fetchmany, 2
        )

        assert_raises_message(
            sa_exc.ResourceClosedError, "object is closed", r.fetchall
        ) 

def test_closed_status_check(self):
        sess = create_session()
        trans = sess.begin()
        trans.rollback()
        assert_raises_message(
            sa_exc.ResourceClosedError,
            "This transaction is closed",
            trans.rollback,
        )
        assert_raises_message(
            sa_exc.ResourceClosedError,
            "This transaction is closed",
            trans.commit,
        ) 

def add_repos(ctx, filename):
    """
    Add repositories to Augur's database
    """
    app = ctx.obj

    db = get_db_connection(app)

    df = pd.read_sql(s.sql.text("SELECT repo_group_id FROM augur_data.repo_groups"), db)
    repo_group_IDs = df['repo_group_id'].values.tolist()

    insertSQL = s.sql.text("""
        INSERT INTO augur_data.repo(repo_group_id, repo_git, repo_status, 
        tool_source, tool_version, data_source, data_collection_date) 
        VALUES (:repo_group_id, :repo_git, 'New', 'CLI', 1.0, 'Git', CURRENT_TIMESTAMP)
    """)

    with open(filename) as upload_repos_file:
        data = csv.reader(upload_repos_file, delimiter=',')
        for row in data:
            print(f"Trying repo with Git URL `{row[1]}` to repo group {row[0]}...\n")
            try:
                if int(row[0]) in repo_group_IDs:
                    pd.read_sql(insertSQL, db, params={'repo_group_id': int(row[0]), 'repo_git': row[1]})
                else:
                    print(f"Invalid repo group id specified for {row[1]}, skipping.")
            except exc.ResourceClosedError as error:
                print(f"Successfully inserted {row[1]}.")
                # pd.read_sql() will throw an AttributeError when it can't sucessfully "fetch" any rows from the result.
                # Since there's no rows to fetch after a successful insert, this is how we know it worked.
                # I know it's weird 

def add_repo_groups(ctx, filename):
    """
    Create new repo groups in Augur's database
    """
    app = ctx.obj

    db = get_db_connection(app)

    df = pd.read_sql(s.sql.text("SELECT repo_group_id FROM augur_data.repo_groups"), db)
    repo_group_IDs = df['repo_group_id'].values.tolist()

    insertSQL = s.sql.text("""
    INSERT INTO "augur_data"."repo_groups"("repo_group_id", "rg_name", "rg_description", "rg_website", "rg_recache", "rg_last_modified", "rg_type", "tool_source", "tool_version", "data_source", "data_collection_date") VALUES (:repo_group_id, :repo_group_name, '', '', 0, CURRENT_TIMESTAMP, 'Unknown', 'Loaded by user', '1.0', 'Git', CURRENT_TIMESTAMP);
    """)

    with open(filename) as create_repo_groups_file:
        data = csv.reader(create_repo_groups_file, delimiter=',')
        for row in data:
            print(f"Trying repo group with name {row[1]} and ID {row[0]}...")
            try:
                if int(row[0]) not in repo_group_IDs:
                    repo_group_IDs.append(int(row[0]))
                    pd.read_sql(insertSQL, db, params={'repo_group_id': int(row[0]), 'repo_group_name': row[1]})
                else:
                    print(f"Repo group with ID {row[1]} for repo group {row[1]} already exists, skipping...")
            except exc.ResourceClosedError as error:
                print(f"Successfully inserted {row[1]}.\n")
                # pd.read_sql() will throw an AttributeError when it can't sucessfully "fetch" any rows from the result.
                # Since there's no rows to fetch after a successful insert, this is how we know it worked.
                # I know it's weird, sue me (jk please don't) 

def test_used_with_commit_raised(self, session, assert_session):
        @transaction(session)
        def create_user():
            user = User(username='test1', email='1@b.com', password='1')
            session.add(user)
            session.commit()

        msg = 'This transaction is closed'
        with pytest.raises(ResourceClosedError, match=msg):
            create_user()
        assert assert_session.query(User).all() == []