Python psycopg2.ProgrammingError() Examples

def test_server_txt_version(self, conn_mock):
        """
        simple test for the server_txt_version property
        """
        # Build a server
        server = build_real_server()
        cursor_mock = conn_mock.return_value.cursor.return_value

        # Connection error
        conn_mock.side_effect = PostgresConnectionError
        assert server.postgres.server_txt_version is None

        # Communication error
        conn_mock.side_effect = None
        cursor_mock.execute.side_effect = psycopg2.ProgrammingError
        assert server.postgres.server_txt_version is None

        # Good connection
        cursor_mock.execute.side_effect = None
        cursor_mock.fetchone.return_value = (
            "PostgreSQL 9.4.5 on x86_64-apple-darwin15.0.0, compiled by "
            "Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit",)

        assert server.postgres.server_txt_version == '9.4.5'
        cursor_mock.execute.assert_called_with("SELECT version()") 

def write_files(self, base_path, models, **kwargs):
        for model in models:
            try:
                self.cur.execute("SELECT pk FROM %s", camel_to_snake(model.name))
            except psycopg2.ProgrammingError as e:
                self._report_query_error(e)

            pks = []

            row = self.cur.fetchone()
            while row:
                pks.append(row[0])

                row = self.cur.fetchone()

            for pk in pks:
                path = os.path.join(base_path, "%s.yaml" % pk)

                if not self.override and os.path.isfile(path):
                    continue
                with open(path, "w") as f:
                    yaml.dump(self.to_dict(model, pk), f, default_flow_style=False) 

def test_execute_failing_postgres_based_job(self):
        config_manager = self._setup_config_manager(
            'crontabber.tests.test_crontabber.BrokenPostgresSampleJob|1d'
        )

        with config_manager.context() as config:
            tab = app.CronTabber(config)
            tab.run_all()
            infos = [x[0][0] for x in config.logger.info.call_args_list]
            infos = [x for x in infos if x.startswith('Ran ')]
            ok_('Ran PostgresSampleJob' not in infos)

            information = tab.job_state_database['broken-pg-job']
            ok_(information['last_error'])
            ok_(
                'ProgrammingError' in
                information['last_error']['type']
            ) 

def is_operational_exception(self, msg):
        """return True if a conditional exception is actually an operational
        error. Return False if it's a genuine error that should probably be
        raised and propagate up.

        Some conditional exceptions might be actually be some form of
        operational exception "labelled" wrong by the psycopg2 code error
        handler.
        """
        if msg.pgerror in ('SSL SYSCALL error: EOF detected',):
            # Ideally we'd like to check against msg.pgcode values
            # but certain odd ProgrammingError exceptions don't have
            # pgcodes so we have to rely on reading the pgerror :(
            return True

        # at the of writing, the list of exceptions is short but this would be
        # where you add more as you discover more odd cases of psycopg2

        return False

    #-------------------------------------------------------------------------- 

def test_delete_slot(slot):
    with patch.object(psycopg2.ProgrammingError, 'pgcode',
                      new_callable=PropertyMock,
                      return_value=psycopg2.errorcodes.UNDEFINED_OBJECT):
        pe = psycopg2.ProgrammingError()
        slot._repl_cursor.drop_replication_slot = Mock(side_effect=pe)
        slot.delete_slot()
    slot._repl_cursor.drop_replication_slot.assert_called_with('pg2kinesis')

    with patch.object(psycopg2.ProgrammingError, 'pgcode',
                      new_callable=PropertyMock,
                      return_value=-1):
        pe = psycopg2.ProgrammingError()
        slot._repl_cursor.create_replication_slot = Mock(side_effect=pe)
        with pytest.raises(psycopg2.ProgrammingError) as e_info:
            slot.delete_slot()
            slot._repl_cursor.drop_replication_slot.assert_called_with('pg2kinesis')

            assert e_info.value.pgcode == -1

    slot._repl_cursor.create_replication_slot = Mock(side_effect=Exception)
    with pytest.raises(Exception):
        slot.delete_slot()
        slot._repl_cursor.drop_replication_slot.assert_called_with('pg2kinesis') 

def __get_previously_aggregated__(self,workflow_id):
        """
        get the list of all previously aggregated subjects - so we when upserting new results
        we know which subjects we are updating and which we are inserting (i.e. no previously existing results)
        :param workflow_id:
        :return:
        """

        try:
            postgres_cursor = self.postgres_writeable_session.cursor()
            postgres_cursor.execute("select subject_id from aggregations where workflow_id = " + str(workflow_id))
            previously_aggregated = [i[0] for i in postgres_cursor.fetchall()]
        except psycopg2.ProgrammingError as e:
            # again, not sure why there would be an error - but in this case just to be certain
            # assume that there are no pre-existing aggregation results
            print(e)
            self.postgres_session.rollback()
            previously_aggregated = []

        return previously_aggregated 

def __cleanup_schema(self):
        with psycopg2.connect(os.environ['GREASE_TEST_DSN_ORIGINAL']) as conn:
            conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
            with conn.cursor() as cursor:
                cursor.execute("""
                  SELECT  
                    pg_terminate_backend(pid) 
                    FROM pg_stat_activity 
                    WHERE datname='test_data'
                """)
                try:
                    cursor.execute("""
                        DROP DATABASE test_data;
                    """)
                except psycopg2.ProgrammingError as e:
                    print("Schema Does Not Exist: {0}".format(e.pgerror))
        os.environ['GREASE_TEST_DSN'] = os.environ['GREASE_TEST_DSN_ORIGINAL'] 

def test_execute_failing_postgres_transaction_managed_job(self):
        config_manager = self._setup_config_manager(
            'crontabber.tests.test_crontabber.'
            'BrokenPostgresTransactionManagedSampleJob|1d'
        )

        with config_manager.context() as config:
            tab = app.CronTabber(config)
            tab.run_all()
            infos = [x[0][0] for x in config.logger.info.call_args_list]
            infos = [x for x in infos if x.startswith('Ran ')]
            ok_('Ran PostgresTransactionSampleJob' not in infos)

            information = \
                tab.job_state_database['broken-transaction-managed-pg-job']
            ok_(information['last_error'])
            ok_(
                'ProgrammingError' in
                information['last_error']['type']
            ) 

def __init__(self, tableName = None, load_database = True, *args, **kwargs):


		# Create the table twice, because horrible.
		if tableName:
			self.tableName = self.tableName+"_"+tableName
		super().__init__(*args, **kwargs)

		try:
			with self.transaction() as cur:
				cur.execute("BEGIN;")
				self.log.info("Clearing table")
				cur.execute('DELETE FROM {table};'.format(table=self.tableName))
				self.log.info("cleared")
				cur.execute("COMMIT;")

		except psycopg2.Error:
			traceback.print_exc()


		try:
			cur.execute('''CREATE        INDEX {table}_phash_bk_tree_index    ON {table} USING spgist ( phash bktree_ops );'''.format(table=self.tableName))
		except psycopg2.ProgrammingError:
			traceback.print_exc()
			print("Failed to create index?") 

def connect(self):
        """
        Connect to the PostgreSQL server. It reuses an existing connection.
        """
        if self._check_connection():
            return self._conn

        self._conn = super(PostgreSQLConnection, self).connect()
        server_version = self._conn.server_version
        use_app_name = 'application_name' in self.conn_parameters
        if server_version >= 90000 and not use_app_name:
            try:
                cur = self._conn.cursor()
                # Do not use parameter substitution with SET
                cur.execute('SET application_name TO %s' %
                            self.application_name)
                cur.close()
            # If psycopg2 fails to set the application name,
            # raise the appropriate exception
            except psycopg2.ProgrammingError as e:
                raise PostgresAppNameError(force_str(e).strip())
        return self._conn 

def _get_last_dominant_index(self, _product, _tradingday):
        try:
            self.dominant_index_cur.execute(
                "SELECT closeprice FROM {} WHERE tradingday<'{}' "
                "ORDER BY tradingday DESC LIMIT 1".format(
                    _product, _tradingday
                )
            )
            for d in self.dominant_index_cur:
                return d[0]
        except psycopg2.ProgrammingError as e:
            logging.warning(e)
            assert e.pgcode == '42P01'
            self.dominant_index_con.rollback()
            self._create_dominant_index_table(_product)
            return None 

def register_hstore_handler(connection, **kwargs):
    if connection.vendor != 'postgresql':
        return

    try:
        if six.PY2:
            register_hstore(connection.connection, globally=True, unicode=True)
        else:
            register_hstore(connection.connection, globally=True)
    except ProgrammingError:
        # Hstore is not available on the database.
        #
        # If someone tries to create an hstore field it will error there.
        # This is necessary as someone may be using PSQL without extensions
        # installed but be using other features of contrib.postgres.
        #
        # This is also needed in order to create the connection in order to
        # install the hstore extension.
        pass 

def register_hstore_handler(connection, **kwargs):
    if connection.vendor != 'postgresql':
        return

    try:
        if six.PY2:
            register_hstore(connection.connection, globally=True, unicode=True)
        else:
            register_hstore(connection.connection, globally=True)
    except ProgrammingError:
        # Hstore is not available on the database.
        #
        # If someone tries to create an hstore field it will error there.
        # This is necessary as someone may be using PSQL without extensions
        # installed but be using other features of contrib.postgres.
        #
        # This is also needed in order to create the connection in order to
        # install the hstore extension.
        pass 

def view_definition(self, spec):
        """Returns the SQL defining views described by `spec`"""

        template = "CREATE OR REPLACE {6} VIEW {0}.{1} AS \n{3}"
        # 2: relkind, v or m (materialized)
        # 4: reloptions, null
        # 5: checkoption: local or cascaded
        with self.conn.cursor() as cur:
            sql = self.view_definition_query
            _logger.debug("View Definition Query. sql: %r\nspec: %r", sql, spec)
            try:
                cur.execute(sql, (spec,))
            except psycopg2.ProgrammingError:
                raise RuntimeError("View {} does not exist.".format(spec))
            result = cur.fetchone()
            view_type = "MATERIALIZED" if result[2] == "m" else ""
            return template.format(*result + (view_type,)) 

def register_json_typecasters(conn, loads_fn):
    """Set the function for converting JSON data for a connection.

    Use the supplied function to decode JSON data returned from the database
    via the given connection. The function should accept a single argument of
    the data as a string encoded in the database's character encoding.
    psycopg2's default handler for JSON data is json.loads.
    http://initd.org/psycopg/docs/extras.html#json-adaptation

    This function attempts to register the typecaster for both JSON and JSONB
    types.

    Returns a set that is a subset of {'json', 'jsonb'} indicating which types
    (if any) were successfully registered.
    """
    available = set()

    for name in ["json", "jsonb"]:
        try:
            psycopg2.extras.register_json(conn, loads=loads_fn, name=name)
            available.add(name)
        except psycopg2.ProgrammingError:
            pass

    return available 

def test_cannot_drop_geo(cursor, user):
    """Flowapi cannot drop geography tables."""
    with pytest.raises(pg.ProgrammingError):
        cursor.execute("DROP TABLE geography.admin0") 

def execute(self, query):
        retVal = False

        try:
            self.cursor.execute(query)
            retVal = True
        except (psycopg2.OperationalError, psycopg2.ProgrammingError), msg:
            logger.warn(("(remote) %s" % msg).strip()) 

def test_cannot_drop_cache_dependencies_table(cursor):
    """Role cannot drop cache metadata"""
    with pytest.raises(pg.ProgrammingError):
        cursor.execute("DROP TABLE cache.dependencies") 

def maybe_create_tables(db):
    try:
        with (await db.cursor()) as cur:
            await cur.execute("SELECT COUNT(*) FROM entries LIMIT 1")
            await cur.fetchone()
    except psycopg2.ProgrammingError:
        with open("schema.sql") as f:
            schema = f.read()
        with (await db.cursor()) as cur:
            await cur.execute(schema) 

def test_cannot_drop_cache_tables(cursor):
    """Flowapi cannot drop cache tables"""
    with pytest.raises(pg.ProgrammingError):
        cursor.execute("DROP TABLE cache.blah") 

def test_cannot_select_cache_metadata_table(cursor, user):
    """Flowapi cannot read cache metadata"""
    with pytest.raises(pg.ProgrammingError):
        cursor.execute("SELECT * FROM cache.cached") 

def to_dict(self, model, pk):
        try:
            self.cur.execute(
                "SELECT %s FROM %s WHERE pk = %s",
                (','.join(model.field_names), camel_to_snake(model.name), pk),
            )
        except psycopg2.ProgrammingError as e:
            self._report_query_error(e)

        row = self.cur.fetchone()

        return {name.replace('_', '-'):col for name, col in zip(model.field_names, row)} 

def register_type_handlers(connection, **kwargs):
    if connection.vendor != 'postgresql':
        return

    try:
        if six.PY2:
            register_hstore(connection.connection, globally=True, unicode=True)
        else:
            register_hstore(connection.connection, globally=True)
    except ProgrammingError:
        # Hstore is not available on the database.
        #
        # If someone tries to create an hstore field it will error there.
        # This is necessary as someone may be using PSQL without extensions
        # installed but be using other features of contrib.postgres.
        #
        # This is also needed in order to create the connection in order to
        # install the hstore extension.
        pass

    try:
        with connection.cursor() as cursor:
            # Retrieve oids of citext arrays.
            cursor.execute("SELECT typarray FROM pg_type WHERE typname = 'citext'")
            oids = tuple(row[0] for row in cursor)
        array_type = psycopg2.extensions.new_array_type(oids, 'citext[]', psycopg2.STRING)
        psycopg2.extensions.register_type(array_type, None)
    except ProgrammingError:
        # citext is not available on the database.
        #
        # The same comments in the except block of the above call to
        # register_hstore() also apply here.
        pass 

def test_fetch_remote_status(self, conn_mock):
        """
        simple test for the fetch_remote_status method
        """
        # Build a server
        server = build_real_server(
            main_conf={
                'streaming_archiver': True,
                'streaming_conninfo': 'dummy=param'})

        # Too old PostgreSQL
        conn_mock.return_value.server_version = 90100
        result = server.streaming.fetch_remote_status()
        assert result["streaming_supported"] is False
        assert result['streaming'] is None

        # Working streaming connection
        conn_mock.return_value.server_version = 90300
        cursor_mock = conn_mock.return_value.cursor.return_value
        cursor_mock.fetchone.return_value = ('12345', 1, 'DE/ADBEEF')
        result = server.streaming.fetch_remote_status()
        cursor_mock.execute.assert_called_with("IDENTIFY_SYSTEM")
        assert result["streaming_supported"] is True
        assert result['streaming'] is True

        # Working non-streaming connection
        conn_mock.reset_mock()
        cursor_mock.execute.side_effect = psycopg2.ProgrammingError
        result = server.streaming.fetch_remote_status()
        cursor_mock.execute.assert_called_with("IDENTIFY_SYSTEM")
        assert result["streaming_supported"] is True
        assert result['streaming'] is False

        # Connection failed
        server.streaming.close()
        conn_mock.reset_mock()
        conn_mock.side_effect = psycopg2.DatabaseError
        result = server.streaming.fetch_remote_status()
        assert result["streaming_supported"] is None
        assert result['streaming'] is None 

def test_current_xlog_file_name(self, is_in_recovery_mock, conn_mock):
        """
        simple test for current_xlog property
        """
        # Build a server
        server = build_real_server()
        conn_mock.return_value.server_version = 90300
        cursor_mock = conn_mock.return_value.cursor.return_value

        timestamp = datetime.datetime(2016, 3, 30, 17, 4, 20, 271376)
        cursor_mock.fetchone.return_value = dict(
            location='0/35000528',
            file_name='000000010000000000000035',
            file_offset=1320,
            timestamp=timestamp,
        )

        # Special way to mock a property
        is_in_recovery_mock.return_value = False
        assert server.postgres.current_xlog_file_name == (
            '000000010000000000000035')

        # Reset mock
        conn_mock.reset_mock()

        # Test error management
        cursor_mock.execute.side_effect = PostgresConnectionError
        assert server.postgres.current_xlog_file_name is None

        cursor_mock.execute.side_effect = psycopg2.ProgrammingError
        assert server.postgres.current_xlog_file_name is None 

def test_is_in_recovery(self, conn_mock):
        """
        simple test for is_in_recovery property
        """
        # Build a server
        server = build_real_server()
        cursor_mock = conn_mock.return_value.cursor.return_value

        # Too old
        conn_mock.return_value.server_version = 80400
        assert not server.postgres.is_in_recovery

        # In recovery
        conn_mock.return_value.server_version = 90100
        cursor_mock.fetchone.return_value = [True]
        assert server.postgres.is_in_recovery
        cursor_mock.execute.assert_called_once_with(
            "SELECT pg_is_in_recovery()")

        # Not in recovery
        cursor_mock.fetchone.return_value = [False]
        assert not server.postgres.is_in_recovery

        # Reset mock
        conn_mock.reset_mock()

        # Test error management
        cursor_mock.execute.side_effect = PostgresConnectionError
        assert server.postgres.is_in_recovery is None

        cursor_mock.execute.side_effect = psycopg2.ProgrammingError
        assert server.postgres.is_in_recovery is None 

def __init__(self, pgcode=None, pgerror=None):
        # pgcode and pgerror are read only attributes and the ProgrammingError
        # class is written in native code. The only way to set these attribute
        # is to use the private method '__setstate__', which is also native
        self.__setstate__({
            'pgcode': pgcode,
            'pgerror': pgerror
        })


# noinspection PyMethodMayBeStatic 

def test_upgrade_single(self, db_conn, setup_teardown):
        """ Test with only single upgrade script. """
        conn = db_conn

        try:
            current_version = int(_fetch_version(conn))
        except psycopg2.ProgrammingError:
            pytest.fail("Version cannot be fetched.")

        test_version = current_version + 1
        script_location = _create_script(test_version, TEST_SCRIPT_1)

        # Reparse the connection string from the fixture, because
        # upgrade script does upgrading with own connection & cursor
        db_host, db_user, db_name, db_port = _parse_dsn(conn.dsn)

        os.environ["POSTGRESQL_DATABASE"] = db_name
        os.environ["POSTGRESQL_USER"] = db_user
        os.environ["POSTGRESQL_HOST"] = db_host
        os.environ["POSTGRESQL_PORT"] = db_port

        from database import upgrade

        os.environ["DB_UPGRADE_SCRIPTS_DIR"] = DB_UPDATES_FIXTURE_PATH

        upgrade.main()

        new_version = _fetch_version(conn)
        assert new_version == test_version

        logs = _fetch_version_logs(conn)
        log = logs.pop()

        assert log[VERSION] == test_version
        assert log[STATUS] == "complete"
        assert log[SCRIPT] == script_location.split("/")[-1]
        assert log[RETURNCODE] == 0 

def test_upgrade_empty(self, db_conn, setup_teardown):
        """ Test with 0 upgrade tests. """
        conn = db_conn

        try:
            current_version = int(_fetch_version(conn))
        except psycopg2.ProgrammingError:
            pytest.fail("Version cannot be fetched.")

        # Reparse the connection string from the fixture, because
        # upgrade script does upgrading with own connection & cursor
        db_host, db_user, db_name, db_port = _parse_dsn(conn.dsn)

        os.environ["POSTGRESQL_DATABASE"] = db_name
        os.environ["POSTGRESQL_USER"] = db_user
        os.environ["POSTGRESQL_HOST"] = db_host
        os.environ["POSTGRESQL_PORT"] = db_port

        from database import upgrade

        os.environ["DB_UPGRADE_SCRIPTS_DIR"] = DB_UPDATES_FIXTURE_PATH
        upgrade.main()

        new_version = _fetch_version(conn)
        assert new_version == current_version

        logs = _fetch_version_logs(conn)
        assert not logs 

def _get_current_db_version(conn):
        try:
            with conn.cursor() as cur:
                cur.execute('select version from db_version where name = %s',
                            (SCHEMA_VER_NAME,))
                result = cur.fetchone()
            if not result:
                raise Exception("db_version table exists, but no row with name '%s'" % SCHEMA_VER_NAME)
            db_version = result[0]
        except psycopg2.ProgrammingError:
            db_version = 0
        finally:
            conn.commit()
        return db_version