Python sqlalchemy.exc.ProgrammingError() Examples

def restore(self, snapshot):
        for table in snapshot.tables:
            click.echo("Restoring database %s" % table.table_name)
            if not self.operations.database_exists(
                table.get_table_name('slave')
            ):
                click.echo(
                    "Database %s does not exist."
                    % table.get_table_name('slave')
                )
                sys.exit(1)
            try:
                self.operations.remove_database(table.table_name)
            except ProgrammingError:
                logger.warn('Database %s does not exist.' % table.table_name)
            self.operations.rename_database(
                table.get_table_name('slave'),
                table.table_name
            )
        snapshot.worker_pid = 1
        self.db.session.commit()

        self.start_background_slave_copy(snapshot) 

def upgrade():
    try:
        op.create_table('passwordreset',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('recoverycode', sa.Unicode(length=255), nullable=False),
        sa.Column('username', sa.Unicode(length=64), nullable=False),
        sa.Column('realm', sa.Unicode(length=64), nullable=False),
        sa.Column('resolver', sa.Unicode(length=64), nullable=True),
        sa.Column('email', sa.Unicode(length=255), nullable=True),
        sa.Column('timestamp', sa.DateTime(), nullable=True),
        sa.Column('expiration', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index(op.f('ix_passwordreset_realm'), 'passwordreset',
                        ['realm'], unique=False)
        op.create_index(op.f('ix_passwordreset_username'), 'passwordreset',
                        ['username'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table passwordreset already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'passwordreset'")
        print (exx) 

def upgrade():
    try:
        op.add_column('token', sa.Column('revoked', sa.Boolean(),
                                         default=False))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column revoked already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'revoked' to table 'token'")
        print (exx)

    try:
        op.add_column('token', sa.Column('locked', sa.Boolean(),
                                         default=False))
    except (OperationalError, ProgrammingError)  as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column locked already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'locked' to table 'token'")
        print (exx) 

def upgrade():
    try:
        op.create_table('eventhandlercondition',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('eventhandler_id', sa.Integer(), nullable=True),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.Unicode(length=2000), nullable=True),
        sa.Column('comparator', sa.Unicode(length=255), nullable=True),
        sa.ForeignKeyConstraint(['eventhandler_id'], ['eventhandler.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('eventhandler_id', 'Key', name='ehcix_1')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table eventhandlercondition already exists.")
        else:
            print("Table already exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table eventhandlercondition")
        print (exx) 

def test_bad_sql_logged_and_raised(caplog):
    """SQL failures during a store should be logged, and raised."""

    class BadQuery(Query):
        def _make_query(self):
            return "THIS IS NOT VALID SQL"

        @property
        def column_names(self):
            return []

    with pytest.raises(ProgrammingError):
        fut = BadQuery().store()
        exec = fut.exception()
        raise exec
    assert "Error executing SQL" in caplog.messages[-1] 

def upgrade():
    try:
        op.create_table('radiusserver',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('identifier', sa.Unicode(length=255), nullable=False),
        sa.Column('server', sa.Unicode(length=255), nullable=False),
        sa.Column('port', sa.Integer(), nullable=True),
        sa.Column('secret', sa.Unicode(length=255), nullable=True),
        sa.Column('description', sa.Unicode(length=2000), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('identifier')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table 'radiusserver' already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'radiusserver'")
        print (exx) 

def remove_snapshot(self, snapshot):
        for table in snapshot.tables:
            try:
                self.operations.remove_database(
                    table.get_table_name('master')
                )
            except ProgrammingError:
                pass
            try:
                self.operations.remove_database(
                    table.get_table_name('slave')
                )
            except ProgrammingError:
                pass
            self.db.session.delete(table)
        self.db.session.delete(snapshot)
        self.db.session.commit() 

def test_errors_while_doing_db(clean_before_and_after):
    with get_sa_session() as session:
        try:
            PullRequestModel.get_or_create(
                pr_id="nope",
                namespace="",
                repo_name=False,
                project_url="https://github.com/the-namespace/the-repo",
            )
        except ProgrammingError:
            pass
        assert len(session.query(PullRequestModel).all()) == 0
        PullRequestModel.get_or_create(
            pr_id=111,
            namespace="asd",
            repo_name="qwe",
            project_url="https://github.com/asd/qwe",
        )
        assert len(session.query(PullRequestModel).all()) == 1


# return all builds in table 

def fetch_backend(key):
    prefix = ioconf.get("prefix", "")
    key = prefix + key
    backend = ioconf.get("backend")
    path = ioconf.get("path")
    if backend == "csv":
        key = key + ".csv"

    try:
        if backend == "csv":
            df0 = pd.read_csv(os.path.join(path, key))
        elif backend == "sql":
            df0 = pd.read_sql(key, path)
        else:
            raise ValueError("no %s option for backend" % backend)

        return df0

    except (FileNotFoundError, exc.ProgrammingError, KeyError):
        return None 

def _fetch_sql(self, path):
        """
        fetch the information and pricetable from sql, not recommend to use manually,
        just set the fetch label to be true when init the object

        :param path:  engine object from sqlalchemy
        """
        try:
            content = pd.read_sql("xa" + self.code, path)
            pricetable = content.iloc[1:]
            commentl = [float(com) for com in pricetable.comment]
            self.price = pricetable[["date", "netvalue", "totvalue"]]
            self.price["comment"] = commentl
            self.name = json.loads(content.iloc[0].comment)["name"]
        except exc.ProgrammingError as e:
            # print('no saved copy of %s' % self.code)
            raise e 

def test_engine_execute_errors(self):
        # ensures that SQL errors are reported
        with pytest.raises(ProgrammingError):
            with self.connection() as conn:
                conn.execute("SELECT * FROM a_wrong_table").fetchall()

        spans = self.memory_exporter.get_finished_spans()
        self.assertEqual(len(spans), 1)
        span = spans[0]
        # span fields
        self.assertEqual(span.name, "{}.query".format(self.VENDOR))
        self.assertEqual(span.attributes.get("service"), self.SERVICE)
        self.assertEqual(
            span.attributes.get(_STMT), "SELECT * FROM a_wrong_table"
        )
        self.assertEqual(span.attributes.get(_DB), self.SQL_DB)
        self.assertIsNone(span.attributes.get(_ROWS))
        self.check_meta(span)
        self.assertTrue(span.end_time - span.start_time > 0)
        # check the error
        self.assertIs(
            span.status.canonical_code,
            trace.status.StatusCanonicalCode.UNKNOWN,
        )
        self.assertIn("a_wrong_table", span.status.description) 

def test_engine_execute_errors(self):
        # ensures that SQL errors are reported
        with pytest.raises(ProgrammingError):
            with self.connection() as conn:
                conn.execute("SELECT * FROM a_wrong_table").fetchall()

        spans = self.memory_exporter.get_finished_spans()
        self.assertEqual(len(spans), 1)
        span = spans[0]
        # span fields
        self.assertEqual(span.name, "{}.query".format(self.VENDOR))
        self.assertEqual(span.attributes.get("service"), self.SERVICE)
        self.assertEqual(
            span.attributes.get(_STMT), "SELECT * FROM a_wrong_table"
        )
        self.assertEqual(span.attributes.get(_DB), self.SQL_DB)
        self.assertIsNone(span.attributes.get(_ROWS))
        self.check_meta(span)
        self.assertTrue(span.end_time - span.start_time > 0)
        # check the error
        self.assertIs(
            span.status.canonical_code,
            trace.status.StatusCanonicalCode.UNKNOWN,
        )
        self.assertIn("a_wrong_table", span.status.description) 

def create_foreign_key_constraint(entity_relation):
    """
    Creates an FK constraint in the database.
    :param entity_relation: EntityRelation object
    :type entity_relation: EntityRelation
    :return: True if the foreign key was successfully created, else False.
    :rtype: bool
    """
    fk_cons = fk_constraint_from_er(entity_relation)

    if fk_cons is None:
        LOGGER.debug('Foreign key constraint object could not be created.')

        return False

    # Catch exception of foreign key already exists
    try:
        fk_cons.create()

        return True

    except ProgrammingError as pe:
        LOGGER.debug('Creation of foreign key constraint failed - %s',
                     unicode(pe))

        return False 

def test_on_conflict_do_update_exotic_targets_five(self):
        users = self.tables.users_xtra

        with testing.db.connect() as conn:
            self._exotic_targets_fixture(conn)
            # try bogus index
            i = insert(users)
            i = i.on_conflict_do_update(
                index_elements=self.bogus_index.columns,
                index_where=self.bogus_index.dialect_options["postgresql"][
                    "where"
                ],
                set_=dict(
                    name=i.excluded.name, login_email=i.excluded.login_email
                ),
            )

            assert_raises(
                exc.ProgrammingError,
                conn.execute,
                i,
                dict(
                    id=1,
                    name="namebogus",
                    login_email="bogus@gmail.com",
                    lets_index_this="bogus",
                ),
            ) 

def drop_foreign_key_constraint(entity_relation):
    """
    Drops an FK constraint in the database.
    :param entity_relation: EntityRelation object
    :type entity_relation: EntityRelation
    :return: True if the foreign key was successfully dropped, else False.
    :rtype: bool
    """
    fk_cons = fk_constraint_from_er(entity_relation)

    if fk_cons is None:
        LOGGER.debug('Foreign key constraint object could not be created.')

        return False

    '''
    Catch and log exception if the constraint does not exist mostly due to
    related cascades(s) having removed the constraint.
    '''
    try:
        fk_cons.drop(cascade=True)

        return True

    except ProgrammingError as pe:
        LOGGER.debug('Drop of foreign key constraint failed - %s',
                     unicode(pe))

        return False 

def _query_generic(self, sql_query, order_by_dict=None, results_transformator=None):
        final_sql_query = sql_query

        if order_by_dict is not None:
            outer_sql = """
                select {data_fields}
                from {order_by} s left outer join (
                    {base_sql}
                ) u
                using (eid)
                order by s.index asc
            """.format(
                order_by=order_by_dict['table'],
                base_sql=sql_query,
                data_fields=order_by_dict['columns_select']
            )

            final_sql_query = outer_sql

        logger.debug(final_sql_query)

        try:
            results_iterator = pd.read_sql(
                final_sql_query, self._get_db_engine(), index_col='eid', chunksize=self.sql_chunksize
            )
        except ProgrammingError as e:
            raise UkbRestSQLExecutionError(str(e))

        if self.sql_chunksize is None:
            results_iterator = iter([results_iterator])

        for chunk in results_iterator:
            if results_transformator is not None:
                chunk = results_transformator(chunk)

            yield chunk 

def createdb(dbname):
    try:
        conn.execute('CREATE DATABASE %s' % (dbname))
    except ProgrammingError as exc:
        if 'already exists' not in str(exc).lower():
            print(exc)
            exit() 

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        # Hack for use custom schema for Alembic version.
        connection.execute(
            """CREATE SCHEMA IF NOT EXISTS "%s";"""
            % version_table_schema
        )
        try:
            # To remove on temBoard 8.
            connection.execute(
                """ALTER TABLE public.alembic_version SET SCHEMA "%s";"""
                % version_table_schema
            )
            logger.debug("Moved Alembic version table to application schema.")
        except ProgrammingError:
            pass

        context.configure(
            connection=connection, target_metadata=target_metadata,
            version_table_schema=version_table_schema,
        )
        with context.begin_transaction():
            context.run_migrations() 

def get_all_profit():
        from psi.app.reports.sqls import ALL_SALES_PROFIT_SQL
        from psi.app.utils import db_util
        try:
            total = db_util.get_result_raw_sql(ALL_SALES_PROFIT_SQL)
            return total[0]
        except ProgrammingError:
            return 0 

def freeze_export(result, fileobj):
    try:
        query = result
        serializer = JSONSerializer(query, fileobj)
        serializer.serialize()
    except (OperationalError, ProgrammingError) as e:
        raise OperationalError("Invalid query: %s" % e) 

def setup_class(cls):
        con = testing.db.connect()
        try:
            con.exec_driver_sql(
                "CREATE DOMAIN int_domain AS INTEGER DEFAULT " "42 NOT NULL"
            )
            con.exec_driver_sql("CREATE DOMAIN str_domain AS VARCHAR(255)")
            con.exec_driver_sql(
                "CREATE DOMAIN rem_domain AS BLOB SUB_TYPE TEXT"
            )
            con.exec_driver_sql(
                "CREATE DOMAIN img_domain AS BLOB SUB_TYPE " "BINARY"
            )
        except ProgrammingError as e:
            if "attempt to store duplicate value" not in str(e):
                raise e
        con.exec_driver_sql("""CREATE GENERATOR gen_testtable_id""")
        con.exec_driver_sql(
            """CREATE TABLE testtable (question int_domain,
                                   answer str_domain DEFAULT 'no answer',
                                   remark rem_domain DEFAULT '',
                                   photo img_domain,
                                   d date,
                                   t time,
                                   dt timestamp,
                                   redundant str_domain DEFAULT NULL)"""
        )
        con.exec_driver_sql(
            "ALTER TABLE testtable "
            "ADD CONSTRAINT testtable_pk PRIMARY KEY "
            "(question)"
        )
        con.exec_driver_sql(
            "CREATE TRIGGER testtable_autoid FOR testtable "
            "   ACTIVE BEFORE INSERT AS"
            "   BEGIN"
            "     IF (NEW.question IS NULL) THEN"
            "       NEW.question = gen_id(gen_testtable_id, 1);"
            "   END"
        ) 

def verify_db_ready(session):
    ready = True
    try:
        from scoring_engine.models.user import User
        session.query(User).get(1)
    except (OperationalError, ProgrammingError):
        ready = False
    return ready 

def test_autocommit_isolation_level(self):
        c = testing.db.connect().execution_options(
            isolation_level="AUTOCOMMIT"
        )
        # If we're really in autocommit mode then we'll get an error saying
        # that the prepared transaction doesn't exist. Otherwise, we'd
        # get an error saying that the command can't be run within a
        # transaction.
        assert_raises_message(
            exc.ProgrammingError,
            'prepared transaction with identifier "gilberte" does not exist',
            c.exec_driver_sql,
            "commit prepared 'gilberte'",
        ) 

def test_foreignkey_missing_insert(self):
        Table("t1", self.metadata, Column("id", Integer, primary_key=True))
        t2 = Table(
            "t2",
            self.metadata,
            Column("id", Integer, ForeignKey("t1.id"), primary_key=True),
        )
        self.metadata.create_all()

        # want to ensure that "null value in column "id" violates not-
        # null constraint" is raised (IntegrityError on psycoopg2, but
        # ProgrammingError on pg8000), and not "ProgrammingError:
        # (ProgrammingError) relationship "t2_id_seq" does not exist".
        # the latter corresponds to autoincrement behavior, which is not
        # the case here due to the foreign key.

        for eng in [
            engines.testing_engine(options={"implicit_returning": False}),
            engines.testing_engine(options={"implicit_returning": True}),
        ]:
            with expect_warnings(
                ".*has no Python-side or server-side default.*"
            ):
                assert_raises(
                    (exc.IntegrityError, exc.ProgrammingError),
                    eng.execute,
                    t2.insert(),
                ) 

def test_check_constraint(self, connection):
        assert_raises(
            (
                exc.IntegrityError,
                exc.ProgrammingError,
                exc.OperationalError,
                # PyMySQL raising InternalError until
                # https://github.com/PyMySQL/PyMySQL/issues/607 is resolved
                exc.InternalError,
            ),
            connection.exec_driver_sql,
            "insert into non_native_enum_table "
            "(id, someenum) values(1, 'four')",
        ) 

def test_session_lock_exception_propagation(self):
        with self.assertRaises(ProgrammingError):
            with pg_session_lock(self.s1.db, LOCK_REPO_RESOLVER, 1, block=False):
                # Execute some invalid statement
                self.s1.db.execute("ALTER TABLE asdf RENAME TO sdfjk")
        # Try if it's unlocked
        with pg_session_lock(self.s2.db, LOCK_REPO_RESOLVER, 1, block=False):
            pass 

def is_unique_violation(ex):
    """
    Is the exception an indication of a unique constraint violation or other

    :param ex: Exception object
    :return: Boolean
    """
    ret = False
    pgcode = _get_pgcode_from_ex(ex)
    if pgcode and pgcode == PG_UNIQUE_CONSTRAINT_VIOLATION_CODE:
        ret = True
    return ret
    #return isinstance(ex, ProgrammingError) and hasattr(ex, 'orig') and str(ex.orig.args[0]) == 'ERROR' and str(ex.orig.args[2]) == PG_UNIQUE_CONSTRAINT_VIOLATION_CODE 

def is_lock_acquisition_error(ex):
    """
    Is the exception an indication of a failure to get a row lock.

    :param ex: Exception object
    :return: Boolean
    """
    ret = False
    pgcode = _get_pgcode_from_ex(ex)
    if pgcode and pgcode == PG_COULD_NOT_GET_ROWLOCK_CODE:
        ret = True
    return ret
    #return isinstance(ex, ProgrammingError) and hasattr(ex, 'orig') and str(ex.orig.args[0]) == 'ERROR' and str(ex.orig.args[2]) == PG_COULD_NOT_GET_ROWLOCK_CODE 

def is_table_not_found(ex):
    ret = False
    pgcode = _get_pgcode_from_ex(ex)
    if pgcode and pgcode == PG_RELATION_NOT_FOUND_CODE:
        ret = True
    return ret
    #return isinstance(ex, ProgrammingError) and hasattr(ex, 'orig') and str(ex.orig.args[0]) == 'ERROR' and (str(ex.orig.args[2]) == PG_RELATION_NOT_FOUND_CODE or str(ex.orig.args[2]) == PG_RELATION_NOT_FOUND_CODE) 

def updateFornecedor(self):

        try:

            # Abrindo Sessao
            conecta = Conexao()
            sessao = conecta.Session()

            # Selecionando id
            query = sessao.query(Fornecedor).get(self.id)

            # Novos Valores
            query.nome_fantasia = self.nomeFantasia
            query.razao_social = self.razaoSocial
            query.cnpj = self.cnpj
            query.insc_estadual = self.inscEstadual
            query.telefone = self.telefone
            query.email = self.email
            query.site = self.site
            query.obs = self.obs
            query.cep = self.cep
            query.endereco = self.endereco
            query.numero = self.numero
            query.bairro = self.bairro
            query.cidade = self.cidade
            query.estado = self.estado

            # Executando a query
            sessao.commit()

            # Fechando a Conexao
            sessao.close()

        except ProgrammingError as err:
            print(err)

        pass

    # Selecionar Fornecedor por Id