Python pyodbc.Error() Examples

def _sql(client, sql, may_fail=False, fatal_error=False):
    try:
        opts.log.debug('executing SQL: %s', sql)
        client.query(sql)
        return True
    except pyodbc.Error as e:
        if may_fail:
            pass
        else:
            opts.log.critical(str(e))
            opts.log.exception(e)
            if not opts.log.isEnabledFor(logging.DEBUG):
                opts.log.error('SQL was: %s', sql)
            if fatal_error:
                sys.exit(1)
    return False 

def test_odbc_connection_setup_timeout_unknown_error(self, mocked_pyodbc_connect):
        print("Test setup odbc connection set timeout pyodbc.Error pass")

        class PostgresODBCConnection_customexception(object):

            @property
            def timeout(self):
                return 1

            @timeout.setter
            def timeout(self, value):
                raise pyodbc.Error('HY000',
                                   "[HY000] Driver doesn't support this")

        db_connection = PostgresODBCConnection_customexception()
        mocked_pyodbc_connect.return_value = db_connection

        try:
            odbc_utils.OdbcConnection(self.fake_sql_connection_string, True, 10, 'HY000')
            assert False
        except Exception:
            assert True 

def test_op_error_release(loop, pool_maker, pg_server_local):
    pool = await pool_maker(loop, dsn=pg_server_local['dsn'], autocommit=True)

    async with pool.acquire() as conn:
        async def execute():
            start = time.time()

            while time.time() - start < 20:
                await conn.execute('SELECT 1; SELECT pg_sleep(1);')

        async def _kill_conn():
            await asyncio.sleep(2)
            await pg_server_local['container'].kill()
            await pg_server_local['container'].delete(v=True, force=True)
            pg_server_local['container'] = None

        result = await asyncio.gather(
            _kill_conn(), execute(), return_exceptions=True)
        exc = result[1]
        assert isinstance(exc, Error)

    assert 9 == pool.freesize
    assert not pool._used 

def execute(self, sql, *args):
        """Create a new Cursor object, call its execute method, and return it.

        See Cursor.execute for more details.This is a convenience method
        that is not part of the DB API.  Since a new Cursor is allocated
        by each call, this should not be used if more than one SQL
        statement needs to be executed.

        :param sql: str, formatted sql statement
        :param args: tuple, arguments for construction of sql statement
        """
        try:
            _cursor = await self._execute(self._conn.execute, sql, *args)
            connection = self
            cursor = Cursor(_cursor, connection, echo=self._echo)
            return cursor
        except pyodbc.Error as e:
            if _is_conn_close_error(e):
                await self.close()
            raise 

def _run_operation(self, func, *args, **kwargs):
        # execute func in thread pool of attached to cursor connection
        if not self._conn:
            raise pyodbc.OperationalError('Cursor is closed.')

        try:
            result = await self._conn._execute(func, *args, **kwargs)
            return result
        except pyodbc.Error as e:
            if self._conn and _is_conn_close_error(e):
                await self._conn.close()
            raise 

def test_odbc_connection_setup_timeout_pyodbc_error_pass(self, mocked_pyodbc_connect):
        print("Test setup odbc connection set timeout pyodbc.Error pass")

        mocked_pyodbc_connect.return_value = PostgresODBCConnection()

        try:
            odbc_utils.OdbcConnection(self.fake_sql_connection_string, True, 10, 'HY000')
            assert True
        except Exception:
            assert False 

def timeout(self, value):
        raise pyodbc.Error('HY000', "[HY000] Couldn't set unsupported connect attribute 113 (216) (SQLSetConnectAttr)") 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(getSafeExString(msg)) 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(msg[1]) 

def close(self):
        """Close the cursor now (rather than whenever __del__ is called).

        The cursor will be unusable from this point forward; an Error
        (or subclass) exception will be raised if any operation is attempted
        with the cursor.
        """
        if self._conn is None:
            return
        await self._run_operation(self._impl.close)
        self._conn = None 

def run(self):
        while not self.__stopped:
            # Initialization phase
            if not self.is_connected():
                while not self.__stopped and \
                        not self.__init_connection() and \
                        self.__config["connection"].get("reconnect", self.DEFAULT_RECONNECT_STATE):
                    reconnect_period = self.__config["connection"].get("reconnectPeriod", self.DEFAULT_RECONNECT_PERIOD)
                    log.info("[%s] Will reconnect to database in %d second(s)", self.get_name(), reconnect_period)
                    sleep(reconnect_period)

                if not self.is_connected():
                    log.error("[%s] Cannot connect to database so exit from main loop", self.get_name())
                    break

                if not self.__init_iterator():
                    log.error("[%s] Cannot init database iterator so exit from main loop", self.get_name())
                    break

            # Polling phase
            try:
                self.__poll()
                if not self.__stopped:
                    polling_period = self.__config["polling"].get("period", self.DEFAULT_POLL_PERIOD)
                    log.debug("[%s] Next polling iteration will be in %d second(s)", self.get_name(), polling_period)
                    sleep(polling_period)
            except pyodbc.Warning as w:
                log.warning("[%s] Warning while polling database: %s", self.get_name(), str(w))
            except pyodbc.Error as e:
                log.error("[%s] Error while polling database: %s", self.get_name(), str(e))
                self.__close()

        self.__close()
        self.__stopped = False
        log.info("[%s] Stopped", self.get_name()) 

def _is_conn_close_error(e):
    if not isinstance(e, Error) or len(e.args) < 2:
        return False

    sqlstate, msg = e.args[0], e.args[1]
    if sqlstate not in _CONN_CLOSE_ERRORS:
        return False

    check_msg = _CONN_CLOSE_ERRORS[sqlstate]
    if not check_msg:
        return True

    return msg.startswith(check_msg) 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(msg[1]) 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(msg[1]) 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(msg[1]) 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(msg[1]) 

def retry_on_lock(f):
    """
    Continue to retry transaction while "lock" is in the error message.

    This should include deadlocks and lock wait timeouts.

    Usage:

        @retry_on_lock
        def insert_data(data_tuple):
            query = "INSERT INTO example VALUES (?, ?, ?);"
            with DatabaseConnection('DSN=example_dsn;') as cursor:
                cursor.execute(query, data_tuple)
                cursor.commit()

    :param f: function to wrap
    :return:
    """
    def inner(*args, **kwargs):
        for i in range(5):
            try:
                return f(*args, **kwargs)
            except pyodbc.Error as e1:
                logger.error(e1)
                if 'lock' in e1.args[1].lower():
                    time.sleep(retry_timeout)
                    continue
                raise
        else:
            raise Exception('max retries exceeded when attempting to write to database.')
    return inner 

def __init_iterator(self):
        save_iterator = self.DEFAULT_SAVE_ITERATOR
        if "persistent" not in self.__config["polling"]["iterator"]:
            self.__config["polling"]["iterator"]["persistent"] = save_iterator
        else:
            save_iterator = self.__config["polling"]["iterator"]["persistent"]

        log.info("[%s] Iterator saving %s", self.get_name(), "enabled" if save_iterator else "disabled")

        if save_iterator and self.__load_iterator_config():
            log.info("[%s] Init iterator from file '%s': column=%s, start_value=%s",
                     self.get_name(), self.__iterator_file_name,
                     self.__iterator["name"], self.__iterator["value"])
            return True

        self.__iterator = {"name": self.__config["polling"]["iterator"]["column"],
                           "total": 0}

        if "value" in self.__config["polling"]["iterator"]:
            self.__iterator["value"] = self.__config["polling"]["iterator"]["value"]
            log.info("[%s] Init iterator from configuration: column=%s, start_value=%s",
                     self.get_name(), self.__iterator["name"], self.__iterator["value"])
        elif "query" in self.__config["polling"]["iterator"]:
            try:
                self.__iterator["value"] = \
                    self.__cursor.execute(self.__config["polling"]["iterator"]["query"]).fetchone()[0]
                log.info("[%s] Init iterator from database: column=%s, start_value=%s",
                         self.get_name(), self.__iterator["name"], self.__iterator["value"])
            except pyodbc.Warning as w:
                log.warning("[%s] Warning on init iterator from database: %s", self.get_name(), str(w))
            except pyodbc.Error as e:
                log.error("[%s] Failed to init iterator from database: %s", self.get_name(), str(e))
        else:
            log.error("[%s] Failed to init iterator: value/query param is absent", self.get_name())

        return "value" in self.__iterator 

def __init_connection(self):
        try:
            log.debug("[%s] Opening connection to database", self.get_name())
            connection_config = self.__config["connection"]
            self.__connection = pyodbc.connect(connection_config["str"], **connection_config.get("attributes", {}))
            if connection_config.get("encoding", ""):
                log.info("[%s] Setting encoding to %s", self.get_name(), connection_config["encoding"])
                self.__connection.setencoding(connection_config["encoding"])

            decoding_config = connection_config.get("decoding")
            if decoding_config is not None:
                if isinstance(decoding_config, dict):
                    if decoding_config.get("char", ""):
                        log.info("[%s] Setting SQL_CHAR decoding to %s", self.get_name(), decoding_config["char"])
                        self.__connection.setdecoding(pyodbc.SQL_CHAR, decoding_config["char"])
                    if decoding_config.get("wchar", ""):
                        log.info("[%s] Setting SQL_WCHAR decoding to %s", self.get_name(), decoding_config["wchar"])
                        self.__connection.setdecoding(pyodbc.SQL_WCHAR, decoding_config["wchar"])
                    if decoding_config.get("metadata", ""):
                        log.info("[%s] Setting SQL_WMETADATA decoding to %s",
                                 self.get_name(), decoding_config["metadata"])
                        self.__connection.setdecoding(pyodbc.SQL_WMETADATA, decoding_config["metadata"])
                else:
                    log.warning("[%s] Unknown decoding configuration %s. Read data may be misdecoded", self.get_name(),
                                decoding_config)

            self.__cursor = self.__connection.cursor()
            log.info("[%s] Connection to database opened, attributes %s",
                     self.get_name(), connection_config.get("attributes", {}))
        except pyodbc.Error as e:
            log.error("[%s] Failed to connect to database: %s", self.get_name(), str(e))
            self.__close()

        return self.is_connected() 

def _pg_server_helper(host, docker, session_id):
    pg_tag = '9.5'

    await docker.pull('postgres:{}'.format(pg_tag))
    container = await docker.containers.create_or_replace(
        name=f'aioodbc-test-server-{pg_tag}-{session_id}',
        config={
            'Image': f'postgres:{pg_tag}',
            'AttachStdout': False,
            'AttachStderr': False,
            'HostConfig': {
                'PublishAllPorts': True,
            },
        }
    )
    await container.start()
    container_port = await container.port(5432)
    port = container_port[0]['HostPort']

    pg_params = {
        'database': 'postgres',
        'user': 'postgres',
        'password': 'mysecretpassword',
        'host': host,
        'port': port,
    }

    start = time.time()
    dsn = create_pg_dsn(pg_params)
    last_error = None
    container_info = {
        'port': port,
        'pg_params': pg_params,
        'container': container,
        'dsn': dsn,
    }
    try:
        while (time.time() - start) < 40:
            try:
                conn = pyodbc.connect(dsn)
                cur = conn.execute("SELECT 1;")
                cur.close()
                conn.close()
                break
            except pyodbc.Error as e:
                last_error = e
                await asyncio.sleep(random.uniform(0.1, 1))
        else:
            pytest.fail("Cannot start postgres server: {}".format(last_error))

        yield container_info
    finally:
        container = container_info['container']
        if container:
            await container.kill()
            await container.delete(v=True, force=True) 

def mysql_server(loop, host, docker, session_id):
    mysql_tag = '5.7'
    await docker.pull('mysql:{}'.format(mysql_tag))
    container = await docker.containers.create_or_replace(
        name=f'aioodbc-test-server-{mysql_tag}-{session_id}',
        config={
            'Image': 'mysql:{}'.format(mysql_tag),
            'AttachStdout': False,
            'AttachStderr': False,
            'Env': ['MYSQL_USER=aioodbc',
                    'MYSQL_PASSWORD=mysecretpassword',
                    'MYSQL_DATABASE=aioodbc',
                    'MYSQL_ROOT_PASSWORD=mysecretpassword'],
            'HostConfig': {
                'PublishAllPorts': True,
            },
        }
    )
    await container.start()
    port = (await container.port(3306))[0]['HostPort']
    mysql_params = {
        'database': 'aioodbc',
        'user': 'aioodbc',
        'password': 'mysecretpassword',
        'host': host,
        'port': port
    }
    dsn = create_mysql_dsn(mysql_params)
    start = time.time()
    try:
        last_error = None
        while (time.time() - start) < 30:
            try:
                conn = pyodbc.connect(dsn)
                cur = conn.execute("SELECT 1;")
                cur.close()
                conn.close()
                break
            except pyodbc.Error as e:
                last_error = e
                await asyncio.sleep(random.uniform(0.1, 1))
        else:
            pytest.fail("Cannot start mysql server: {}".format(last_error))

        container_info = {
            'port': port,
            'mysql_params': mysql_params,
        }

        yield container_info
    finally:
        await container.kill()
        await container.delete(v=True, force=True) 

def setup_odbc_connection(sql_connection_string, sql_autocommit, sql_query_timeout):
        """
        Setup ODBC connection to a SQL server using connection string obtained from the config file.
        Set autocommit and query timeout values based on the information in config file.
        :param sql_connection_string: config setting
        :param sql_autocommit: config setting
        :param sql_query_timeout: config setting
        :return: pyodbc Connection
        """
        # ODBC connection pooling is turned ON by default.
        # Not all database drivers close connections on db_connection.close() to save round trips to the server.
        # Pooling should be set to False to close connection on db_connection.close().
        pyodbc.pooling = False

        # This fixes incorrect locale setting issue that causes
        # pyodbc.connect to abort on macOS with ODBC Driver 17 for SQL Server (msodbcsql17) working in Python 3.6.
        import locale
        locale.setlocale(locale.LC_ALL, "")

        try:
            db_connection = pyodbc.connect(sql_connection_string)

            # As per the Python DB API, the default value is False
            if sql_autocommit:
                db_connection.autocommit = True

            if sql_query_timeout:
                # Some ODBC drivers do not implement the connection timeout and will throw pyodbc.Error while trying
                # to set it.
                #
                # The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT.
                #
                # SQL_ATTR_CONNECTION_TIMEOUT appears not be supported by the psqlodbc driver (PostgreSQL).
                # Psqlodbc throws a general error 'HY000' for which no implementation-specific SQLSTATE was defined:
                # ('HY000', u"[HY000] Couldn't set unsupported connect attribute 113 (216) (SQLSetConnectAttr)")
                #
                # Oracle11g driver (Oracle database) also appears not support timeout and throws an error:
                # ('HYC00', u'[HYC00] [Oracle][ODBC]Optional feature not implemented ....(0) (SQLSetConnectAttr)')))
                #
                # Try to catch a pyodbc.Error, log it as warning and pass.
                try:
                    # Query statement timeout defaults to 0, which means "no timeout"
                    db_connection.timeout = sql_query_timeout

                except pyodbc.Error as e:
                    sql_state = e.args[0]
                    error_message = e.args[1]
                    LOG.warning("ODBC driver does not implement the connection timeout attribute. "
                                "Error code: %s - %s", sql_state, error_message)
                    pass

        except Exception as e:
            raise Exception("Could not setup the ODBC connection, Exception %s", e)

        return db_connection