Python sqlalchemy.exc.DBAPIError() Examples

def get_logbook(self, book_uuid, lazy=False):
        try:
            logbooks = self._tables.logbooks
            with contextlib.closing(self._engine.connect()) as conn:
                q = (sql.select([logbooks]).
                     where(logbooks.c.uuid == book_uuid))
                row = conn.execute(q).first()
                if not row:
                    raise exc.NotFound("No logbook found with"
                                       " uuid '%s'" % book_uuid)
                book = self._converter.convert_book(row)
                if not lazy:
                    self._converter.populate_book(conn, book)
                return book
        except sa_exc.DBAPIError:
            exc.raise_with_cause(exc.StorageFailure,
                                 "Failed getting logbook '%s'" % book_uuid) 

def test_statement_error_w_code(self):
        try:
            raise sa_exceptions.DBAPIError.instance(
                "select * from table",
                [{"x": 1}],
                sa_exceptions.InvalidRequestError("hello", code="abcd"),
                DatabaseError,
            )
        except sa_exceptions.StatementError as err:
            eq_(
                str(err),
                "(sqlalchemy.exc.InvalidRequestError) hello\n"
                "[SQL: select * from table]\n"
                "[parameters: [{'x': 1}]]\n"
                "(Background on this error at: http://sqlalche.me/e/%s/abcd)"
                % sa_exceptions._version_token,
            )
            eq_(err.args, ("(sqlalchemy.exc.InvalidRequestError) hello",)) 

def test_empty_insert_pk2_fv(self):
        assert_raises(
            exc.DBAPIError,
            self._test_empty_insert,
            Table(
                "b",
                MetaData(testing.db),
                Column(
                    "x",
                    Integer,
                    primary_key=True,
                    server_default=FetchedValue(),
                ),
                Column(
                    "y",
                    Integer,
                    primary_key=True,
                    server_default=FetchedValue(),
                ),
            ),
        ) 

def test_tostring_with_newlines(self):
        try:
            raise sa_exceptions.DBAPIError.instance(
                "this is a message\nthis is the next line\nthe last line",
                None,
                OperationalError(),
                DatabaseError,
            )
        except sa_exceptions.DBAPIError as exc:
            eq_(
                str(exc),
                "(test.base.test_except.OperationalError) \n"
                "[SQL: this is a message\nthis is the next line\n"
                "the last line]\n"
                "(Background on this error at: http://sqlalche.me/e/%s/e3q8)"
                % sa_exceptions._version_token,
            ) 

def test_empty_insert_pk3_fv(self):
        assert_raises(
            exc.DBAPIError,
            self._test_empty_insert,
            Table(
                "c",
                MetaData(testing.db),
                Column(
                    "x",
                    Integer,
                    primary_key=True,
                    server_default=FetchedValue(),
                ),
                Column("y", Integer, DefaultClause("123"), primary_key=True),
            ),
        ) 

def test_savepoint_release_fails_flat(self):
        with testing.db.connect() as connection:
            t1 = connection.begin()

            s1 = connection.begin_nested()

            # force the "commit" of the savepoint that occurs
            # when the "with" block fails, e.g.
            # the RELEASE, to fail, because the savepoint is already
            # released.
            connection.dialect.do_release_savepoint(connection, s1._savepoint)

            assert_raises_message(
                exc.DBAPIError, r".*SQL\:.*RELEASE SAVEPOINT", s1.commit
            )

            assert not s1.is_active
            s1.rollback()  # no error.  prior to 1.4 this would try to rollback

            t1.commit()  # no error 

def test_pyodbc_version_fallback(self):
        dialect = pyodbc.MSDialect_pyodbc()
        dialect.dbapi = Mock()

        for vers, expected in [
            ("11.0.9216.62", (11, 0, 9216, 62)),
            ("notsqlserver.11.foo.0.9216.BAR.62", (11, 0, 9216, 62)),
            ("Not SQL Server Version 10.5", (5,)),
        ]:
            conn = Mock(
                exec_driver_sql=Mock(
                    return_value=Mock(
                        scalar=Mock(
                            side_effect=exc.DBAPIError("stmt", "params", None)
                        )
                    )
                ),
                connection=Mock(getinfo=Mock(return_value=vers)),
            )

            eq_(dialect._get_server_version_info(conn), expected) 

def filters(dbname, exception_type, regex):
    """Mark a function as receiving a filtered exception.

    :param dbname: string database name, e.g. 'mysql'
    :param exception_type: a SQLAlchemy database exception class, which
     extends from :class:`sqlalchemy.exc.DBAPIError`.
    :param regex: a string, or a tuple of strings, that will be processed
     as matching regular expressions.

    """
    def _receive(fn):
        _registry[dbname][exception_type].extend(
            (fn, re.compile(reg))
            for reg in
            ((regex,) if not isinstance(regex, tuple) else regex)
        )
        return fn
    return _receive


# NOTE(zzzeek) - for Postgresql, catch both OperationalError, as the
# actual error is
# psycopg2.extensions.TransactionRollbackError(OperationalError),
# as well as sqlalchemy.exc.DBAPIError, as SQLAlchemy will reraise it
# as this until issue #3075 is fixed. 

def test_invalidate_conn_w_contextmanager_disconnect(self):
        # test [ticket:3803] change maintains old behavior

        pool = self.db.pool

        conn = self.db.connect()
        self.dbapi.shutdown("execute")

        def go():
            with conn.begin():
                conn.execute(select([1]))

        assert_raises(exc.DBAPIError, go)  # wraps a MockDisconnect

        assert conn.invalidated

        ne_(pool._invalidate_time, 0)  # pool is invalidated

        conn.execute(select([1]))
        assert not conn.invalidated 

def test_savepoint_rollback_fails_flat(self, local_connection):
        connection = local_connection
        t1 = connection.begin()

        s1 = connection.begin_nested()

        # force the "commit" of the savepoint that occurs
        # when the "with" block fails, e.g.
        # the RELEASE, to fail, because the savepoint is already
        # released.
        connection.dialect.do_release_savepoint(connection, s1._savepoint)

        assert_raises_message(
            exc.DBAPIError, r".*SQL\:.*ROLLBACK TO SAVEPOINT", s1.rollback
        )

        assert not s1.is_active

        with testing.expect_warnings("nested transaction already"):
            s1.rollback()  # no error (though it warns)

        t1.commit()  # no error 

def test_conn_reusable(self):
        conn = self.db.connect()

        conn.execute(select([1]))

        eq_(self.dbapi.connect.mock_calls, [self.mock_connect])

        self.dbapi.shutdown()

        assert_raises(tsa.exc.DBAPIError, conn.execute, select([1]))

        assert not conn.closed
        assert conn.invalidated

        eq_([c.close.mock_calls for c in self.dbapi.connections], [[call()]])

        # test reconnects
        conn.execute(select([1]))
        assert not conn.invalidated

        eq_(
            [c.close.mock_calls for c in self.dbapi.connections],
            [[call()], []],
        ) 

def test_standalone_orphans(self):
        if self.redefine_colprop:
            person_attribute_name = "person_name"
        else:
            person_attribute_name = "name"

        session = Session()

        daboss = Boss(
            status="BBB",
            manager_name="boss",
            golf_swing="fore",
            **{person_attribute_name: "daboss"}
        )
        session.add(daboss)
        assert_raises(sa_exc.DBAPIError, session.flush) 

def test_invalidated_close(self):
        conn = self.db.connect()

        self.dbapi.shutdown()

        assert_raises(tsa.exc.DBAPIError, conn.execute, select([1]))

        conn.close()
        assert conn.closed
        assert not conn.invalidated
        assert_raises_message(
            tsa.exc.ResourceClosedError,
            "This Connection is closed",
            conn.execute,
            select([1]),
        ) 

def test_report_primary_error_when_rollback_fails(self):
        User, users = self.classes.User, self.tables.users

        mapper(User, users)

        session = Session(testing.db)

        with expect_warnings(".*during handling of a previous exception.*"):
            session.begin_nested()
            savepoint = session.connection()._nested_transaction._savepoint

            # force the savepoint to disappear
            session.connection().dialect.do_release_savepoint(
                session.connection(), savepoint
            )

            # now do a broken flush
            session.add_all([User(id=1), User(id=1)])

            assert_raises_message(
                sa_exc.DBAPIError, "ROLLBACK TO SAVEPOINT ", session.flush
            ) 

def test_contextmanager_nested_rollback(self):
        users, User = self.tables.users, self.classes.User

        mapper(User, users)

        sess = Session()

        def go():
            with sess.begin_nested():
                sess.add(User())  # name can't be null
                sess.flush()

        # and not InvalidRequestError
        assert_raises(sa_exc.DBAPIError, go)

        with sess.begin_nested():
            sess.add(User(name="u1"))

        eq_(sess.query(User).count(), 1) 

def validate_session():
    """ Validate ORM Session """
    worker_precheck = conf.getboolean('core', 'worker_precheck', fallback=False)
    if not worker_precheck:
        return True
    else:
        check_session = sessionmaker(bind=engine)
        session = check_session()
        try:
            session.execute("select 1")  # pylint: disable=no-member
            conn_status = True
        except exc.DBAPIError as err:
            log.error(err)
            conn_status = False
        session.close()  # pylint: disable=no-member
        return conn_status 

def test_contextmanager_rollback(self):
        users, User = self.tables.users, self.classes.User

        mapper(User, users)

        sess = Session(autocommit=True)

        def go():
            with sess.begin():
                sess.add(User())  # name can't be null

        assert_raises(sa_exc.DBAPIError, go)

        eq_(sess.query(User).count(), 0)

        with sess.begin():
            sess.add(User(name="u1"))
        eq_(sess.query(User).count(), 1) 

def _sqlite_json(self, config):
        if not against(config, "sqlite >= 3.9"):
            return False
        else:
            with config.db.connect() as conn:
                try:
                    return (
                        conn.exec_driver_sql(
                            """select json_extract('{"foo": "bar"}', """
                            """'$."foo"')"""
                        ).scalar()
                        == "bar"
                    )
                except exc.DBAPIError:
                    return False 

def my_view(request):
    try:
        one = DBSession.query(MyModel).filter(MyModel.name == 'one').first()
    except DBAPIError:
        return Response(conn_err_msg, content_type='text/plain', status_int=500)
    return {'one': one, 'project': 'MyProject'} 

def _assert_invalidated(fn, *args):
    try:
        fn(*args)
        assert False
    except tsa.exc.DBAPIError as e:
        if not e.connection_invalidated:
            raise 

def test_standalone_orphan(self):
        Order = self.classes.Order

        sess = Session()
        o5 = Order(description="order 5")
        sess.add(o5)
        assert_raises(sa_exc.DBAPIError, sess.flush) 

def test_orphan_message(self):
        class Base(fixtures.BasicEntity):
            pass

        class SubClass(Base):
            pass

        class Parent(fixtures.BasicEntity):
            pass

        mapper(
            Base,
            single,
            polymorphic_on=single.c.type,
            polymorphic_identity="base",
        )
        mapper(SubClass, inherits=Base, polymorphic_identity="sub")
        mapper(
            Parent,
            parent,
            properties={
                "related": relationship(Base, cascade="all, delete-orphan")
            },
        )

        sess = create_session()
        s1 = SubClass(data="s1")
        sess.add(s1)
        assert_raises(sa_exc.DBAPIError, sess.flush) 

def test_versioncheck_for_update(self):
        """query.with_lockmode performs a 'version check' on an already loaded
        instance"""

        Foo = self.classes.Foo

        s1 = self._fixture()
        f1s1 = Foo(value="f1 value")
        s1.add(f1s1)
        s1.commit()

        s2 = create_session(autocommit=False)
        f1s2 = s2.query(Foo).get(f1s1.id)
        # not sure if I like this API
        s2.refresh(f1s2, with_for_update=True)
        f1s2.value = "f1 new value"

        assert_raises(
            exc.DBAPIError, s1.refresh, f1s1, with_for_update={"nowait": True}
        )
        s1.rollback()

        with conditional_sane_rowcount_warnings(update=True):
            s2.commit()
        s1.refresh(f1s1, with_for_update={"nowait": True})
        assert f1s1.version_id == f1s2.version_id 

def setup_class(cls):
        con = testing.db.connect()
        for ddl in [
            'CREATE SCHEMA "SomeSchema"',
            "CREATE DOMAIN testdomain INTEGER NOT NULL DEFAULT 42",
            "CREATE DOMAIN test_schema.testdomain INTEGER DEFAULT 0",
            "CREATE TYPE testtype AS ENUM ('test')",
            "CREATE DOMAIN enumdomain AS testtype",
            "CREATE DOMAIN arraydomain AS INTEGER[]",
            'CREATE DOMAIN "SomeSchema"."Quoted.Domain" INTEGER DEFAULT 0',
        ]:
            try:
                con.exec_driver_sql(ddl)
            except exc.DBAPIError as e:
                if "already exists" not in str(e):
                    raise e
        con.exec_driver_sql(
            "CREATE TABLE testtable (question integer, answer " "testdomain)"
        )
        con.exec_driver_sql(
            "CREATE TABLE test_schema.testtable(question "
            "integer, answer test_schema.testdomain, anything "
            "integer)"
        )
        con.exec_driver_sql(
            "CREATE TABLE crosschema (question integer, answer "
            "test_schema.testdomain)"
        )

        con.exec_driver_sql(
            "CREATE TABLE enum_test (id integer, data enumdomain)"
        )

        con.exec_driver_sql(
            "CREATE TABLE array_test (id integer, data arraydomain)"
        )

        con.exec_driver_sql(
            "CREATE TABLE quote_test "
            '(id integer, data "SomeSchema"."Quoted.Domain")'
        ) 

def test_explode_in_initializer_disconnect(self):
        engine = engines.testing_engine()

        def broken_initialize(connection):
            connection.exec_driver_sql("select fake_stuff from _fake_table")

        engine.dialect.initialize = broken_initialize

        def is_disconnect(e, conn, cursor):
            return True

        engine.dialect.is_disconnect = is_disconnect

        # invalidate() also doesn't screw up
        assert_raises(exc.DBAPIError, engine.connect) 

def test_explode_in_initializer(self):
        engine = engines.testing_engine()

        def broken_initialize(connection):
            connection.exec_driver_sql("select fake_stuff from _fake_table")

        engine.dialect.initialize = broken_initialize

        # raises a DBAPIError, not an AttributeError
        assert_raises(exc.DBAPIError, engine.connect) 

def test_noreconnect_rollback_plus_closewresult(self):
        conn = self.db.connect(close_with_result=True)

        self.dbapi.shutdown("rollback_no_disconnect")

        # raises error
        with expect_warnings(
            "An exception has occurred during handling .*"
            "something broke on execute but we didn't lose the connection",
            py2konly=True,
        ):
            assert_raises_message(
                tsa.exc.DBAPIError,
                "something broke on rollback but we didn't "
                "lose the connection",
                conn.execute,
                select([1]),
            )

        assert conn.closed
        assert not conn.invalidated

        assert_raises_message(
            tsa.exc.ResourceClosedError,
            "This Connection is closed",
            conn.execute,
            select([1]),
        ) 

def test_check_disconnect_no_cursor(self):
        conn = self.db.connect()
        result = conn.execute(select([1]))
        result.cursor.close()
        conn.close()

        assert_raises_message(
            tsa.exc.DBAPIError, "cursor closed", list, result
        ) 

def test_reconnect_on_reentrant_plus_closewresult(self):
        conn = self.db.connect(close_with_result=True)

        self.dbapi.shutdown("rollback")

        # raises error
        with expect_warnings(
            "An exception has occurred during handling .*"
            "something broke on execute but we didn't lose the connection",
            py2konly=True,
        ):
            assert_raises_message(
                tsa.exc.DBAPIError,
                "Lost the DB connection on rollback",
                conn.execute,
                select([1]),
            )

        assert conn.closed
        assert not conn.invalidated

        assert_raises_message(
            tsa.exc.ResourceClosedError,
            "This Connection is closed",
            conn.execute,
            select([1]),
        ) 

def pessimistic_connection_handling(some_engine: Engine) -> None:
    @event.listens_for(some_engine, "engine_connect")
    def ping_connection(  # pylint: disable=unused-variable
        connection: Connection, branch: bool
    ) -> None:
        if branch:
            # 'branch' refers to a sub-connection of a connection,
            # we don't want to bother pinging on these.
            return

        # turn off 'close with result'.  This flag is only used with
        # 'connectionless' execution, otherwise will be False in any case
        save_should_close_with_result = connection.should_close_with_result
        connection.should_close_with_result = False

        try:
            # run a SELECT 1.   use a core select() so that
            # the SELECT of a scalar value without a table is
            # appropriately formatted for the backend
            connection.scalar(select([1]))
        except exc.DBAPIError as err:
            # catch SQLAlchemy's DBAPIError, which is a wrapper
            # for the DBAPI's exception.  It includes a .connection_invalidated
            # attribute which specifies if this connection is a 'disconnect'
            # condition, which is based on inspection of the original exception
            # by the dialect in use.
            if err.connection_invalidated:
                # run the same SELECT again - the connection will re-validate
                # itself and establish a new connection.  The disconnect detection
                # here also causes the whole connection pool to be invalidated
                # so that all stale connections are discarded.
                connection.scalar(select([1]))
            else:
                raise
        finally:
            # restore 'close with result'
            connection.should_close_with_result = save_should_close_with_result