Python sqlalchemy.exc 模块，ProgrammingError() 实例源码

def on_post(self, req, resp, *args, **kwargs):
        data = self.deserialize(req.context['doc'] if 'doc' in req.context else None)
        data, errors = self.clean(data)
        if errors:
            result = {'errors': errors}
            status_code = falcon.HTTP_BAD_REQUEST
            self.render_response(result, req, resp, status_code)
            return

        try:
            with self.session_scope(self.db_engine) as db_session:
                result = self.create(req, resp, data, db_session=db_session)
        except IntegrityError:
            raise HTTPConflict('Conflict', 'Unique constraint violated')
        except ProgrammingError as err:
            # Cases such as unallowed NULL value should have been checked before we got here (e.g. validate against
            # schema using falconjsonio) - therefore assume this is a UNIQUE constraint violation
            if len(err.orig.args) > 1 and err.orig.args[1] == self.VIOLATION_UNIQUE:
                raise HTTPConflict('Conflict', 'Unique constraint violated')
            raise
        status_code = falcon.HTTP_CREATED

        self.render_response(result, req, resp, status_code)

def on_put(self, req, resp, *args, **kwargs):
        status_code = falcon.HTTP_OK
        try:
            with self.session_scope(self.db_engine) as db_session:
                obj = self.get_object(req, resp, kwargs, for_update=True, db_session=db_session)

                data = self.deserialize(req.context['doc'] if 'doc' in req.context else None)
                data, errors = self.clean(data)
                if errors:
                    result = {'errors': errors}
                    status_code = falcon.HTTP_BAD_REQUEST
                else:
                    result = self.update(req, resp, data, obj, db_session)
        except (IntegrityError, ProgrammingError) as err:
            # Cases such as unallowed NULL value should have been checked before we got here (e.g. validate against
            # schema using falconjsonio) - therefore assume this is a UNIQUE constraint violation
            if isinstance(err, IntegrityError) or err.orig.args[1] == self.VIOLATION_FOREIGN_KEY:
                raise HTTPConflict('Conflict', 'Unique constraint violated')
            else:
                raise

        self.render_response(result, req, resp, status_code)

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

        traces = self.tracer.writer.pop_traces()
        # trace composition
        eq_(len(traces), 1)
        eq_(len(traces[0]), 1)
        span = traces[0][0]
        # span fields
        eq_(span.name, '{}.query'.format(self.VENDOR))
        eq_(span.service, self.SERVICE)
        eq_(span.resource, 'SELECT * FROM a_wrong_table')
        eq_(span.get_tag('sql.db'), self.SQL_DB)
        ok_(span.get_tag('sql.rows') is None)
        self.check_meta(span)
        eq_(span.span_type, 'sql')
        ok_(span.duration > 0)
        # check the error
        eq_(span.error, 1)
        eq_(span.get_tag('error.type'), 'mysql.connector.errors.ProgrammingError')
        ok_("Table 'test.a_wrong_table' doesn't exist" in span.get_tag('error.msg'))
        ok_("Table 'test.a_wrong_table' doesn't exist" in span.get_tag('error.stack'))

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

        traces = self.tracer.writer.pop_traces()
        # trace composition
        eq_(len(traces), 1)
        eq_(len(traces[0]), 1)
        span = traces[0][0]
        # span fields
        eq_(span.name, '{}.query'.format(self.VENDOR))
        eq_(span.service, self.SERVICE)
        eq_(span.resource, 'SELECT * FROM a_wrong_table')
        eq_(span.get_tag('sql.db'), self.SQL_DB)
        ok_(span.get_tag('sql.rows') is None)
        self.check_meta(span)
        eq_(span.span_type, 'sql')
        ok_(span.duration > 0)
        # check the error
        eq_(span.error, 1)
        ok_('relation "a_wrong_table" does not exist' in span.get_tag('error.msg'))
        ok_('ProgrammingError' in span.get_tag('error.type'))
        ok_('ProgrammingError: relation "a_wrong_table" does not exist' in span.get_tag('error.stack'))

def _create_schema(self):
        log.info("Creating schema.")

        if self._schema:
            tmp_Base = declarative_base()
            d = _init_daos(tmp_Base, DObject())
            for t in tmp_Base.metadata.tables.values():
                t.schema = self.schema

            try:
                tmp_Base.metadata.create_all(self.engine)
            except ProgrammingError:
                with self.open_session() as sess:
                    st = "CREATE SCHEMA {}".format(self.schema)
                    sess.execute(st)
                    sess.commit()

                tmp_Base.metadata.create_all(self.engine)
        else:
            Base.metadata.create_all(self.engine)

def _displayMetaInfo(self):
        display_list = DisplayList()

        def mkkey(k):
            return Style.bright(Fg.blue(str(k)))

        def mkval(v):
            return str(v)

        display_list.add(mkkey("Version"), mkval(version))
        display_list.add(mkkey("Database URL"),
                         mkval(safeDbUrl(self.config.db_url)))
        try:
            meta = self.db_session.query(Meta).one()
        except (ProgrammingError, OperationalError) as ex:
            print("\nError querying metadata. Database may not be "
                  "initialized: %s" % str(ex), file=sys.stderr)
            return 1

        display_list.add(mkkey("Database version"), mkval(meta.version))
        display_list.add(mkkey("Last sync"), mkval(meta.last_sync or "Never"))
        display_list.add(mkkey("Configuration files "),
                         mkval(", ".join(self.args.config.input_filenames)))
        display_list.print("{k} {delim} {v}", delim=Style.bright(":"))

def _select_from_materialized_view(view, n_days=None):
    if n_days is None:
        sql = text("SELECT * FROM %s" % view)
        options = {}
    else:
        sql = text("""SELECT COUNT(user_id)
                   FROM %s
                   WHERE n_days=:n_days""" % view)
        options = dict(n_days=n_days)
    try:
        session = db.slave_session
        return session.execute(sql, options)
    except ProgrammingError:
        db.slave_session.rollback()
        raise

def refresh_materialized_view(db, view):
    try:
        sql = text('REFRESH MATERIALIZED VIEW CONCURRENTLY %s' % view)
        db.session.execute(sql)
        db.session.commit()
        return "Materialized view refreshed"
    except ProgrammingError:
        sql = text('REFRESH MATERIALIZED VIEW %s' % view)
        db.session.rollback()
        db.session.execute(sql)
        db.session.commit()
        return "Materialized view refreshed"

def get_leaderboard(n, user_id=None, window=0):
    """Return the top n users with their rank."""
    try:
        return gl(top_users=n, user_id=user_id, window=window)
    except ProgrammingError:
        db.session.rollback()
        lb()
        return gl(top_users=n, user_id=user_id, window=window)

def test_materialized_view_refreshed(self, db_mock, exists_mock):
        """Test JOB leaderboard materialized view is refreshed."""
        result = MagicMock()
        result.exists = True
        results = [result]
        exists_mock.return_value = True
        db_mock.slave_session.execute.side_effect = results
        db_mock.session.execute.side_effect = [ProgrammingError('foo',
                                                                'bar',
                                                                'bar'),
                                               True]
        res = leaderboard()
        assert db_mock.session.execute.called
        assert res == 'Materialized view refreshed'

def run_sql(db, q):
    q = q.strip()
    if not q:
        return
    start = time.time()
    try:
        con = db.engine.connect()
        trans = con.begin()
        con.execute(q)
        trans.commit()
    except exc.ProgrammingError as e:
        pass
    finally:
        con.close()

def on_delete(self, req, resp, *args, **kwargs):
        try:
            with self.session_scope(self.db_engine) as db_session:
                obj = self.get_object(req, resp, kwargs, for_update=True, db_session=db_session)

                self.delete(req, resp, obj, db_session)
        except (IntegrityError, ProgrammingError) as err:
            # This should only be caused by foreign key constraint being violated
            if isinstance(err, IntegrityError) or err.orig.args[1] == self.VIOLATION_FOREIGN_KEY:
                raise HTTPConflict('Conflict', 'Other content links to this')
            else:
                raise

        self.render_response({}, req, resp)

def execute(engine, file_path):
    with open(file_path) as f:
        for parsed_statement in parsestream(f):
            statement = stringtype(parsed_statement).strip()
            if statement == '':
                continue
            if log is not None:
                log.info("Running: {statement}".format(statement=statement))
            try:
                engine.execute(statement)
            except ProgrammingError as e:
                if log is not None:
                    log.warning(e.message)
                else:
                    raise e

def __init__(self, conf_file, logging_level, group):

        # Load the default conf and store it globally
        sg.config = ConfigParser.RawConfigParser()
        with codecs.open(conf_file, 'r', sg.DEFAULT_CHARSET) as fp:
            sg.config.readfp(fp)

        # Set up the logger and store it globally
        logger_file = sg.config.get(sg.CONF_LOG_SECTION, sg.CONF_LOG_FILE)
        logger_file_max_size = sg.config.get(sg.CONF_LOG_SECTION, sg.CONF_LOG_FILE_MAX_SIZE)
        logger_formatter = sg.config.get(sg.CONF_LOG_SECTION, sg.CONF_LOG_FORMATTER)
        sg.createDirName(logger_file);
        log_file = RotatingFileHandler(logger_file, 'a', logger_file_max_size, 1)
        log_file.setLevel(logging_level)
        log_file.setFormatter(logging.Formatter(logger_formatter))
        sg.logger = logging.getLogger()
        sg.logger.setLevel(logging.DEBUG)
        sg.logger.addHandler(log_file)

        # Set up the database connection and store it globally
        sg.db =  SQLHelper()

        # Load the stored configuration
        try:
            confs = sg.db.session.query(CONF).filter(CONF.group_id == None, CONF.section == sg.CONF_INSTANCE_SECTION).all()
            for conf in confs:
                sg.config.set(sg.CONF_INSTANCE_SECTION, conf.key, conf.value)
            sg.logger.info('Loaded stored configurations for instance!')
        except (NoResultFound, ProgrammingError) as e:
            sg.logger.warning('No configurations for instance!')

    # Mail walker

def _sql_cmd(client, sql):
    try:
        return client(sql)
    except ProgrammingError as err:
        return str(err), 400
    except OperationalError as err:
        return str(err), 400
    except Exception as err:
        return str(err), 400

def drop_table(self):
        """Delete an existing table."""

        try:
            self.query("DROP TABLE {}".format(self.table))
        except ProgrammingError:
            pass

def test_create_table_with_default_options(self):
        """Ensure the table is correctly created with the default schema."""

        tracker = pawprint.Tracker(db=db, table=table)

        # The table shouldn't exist. Assert it's correct created.
        assert tracker.create_table() == None

        # Try creating it again. This should raise an error.
        with pytest.raises(ProgrammingError):
            tracker.create_table()

        # Assert the table is empty when created
        assert pd.io.sql.execute("SELECT COUNT(*) FROM {}".format(table), db).fetchall() == [(0,)]

        # Ensure its schema is correct
        schema = pd.io.sql.execute(
            "SELECT column_name, data_type, character_maximum_length "
            "FROM INFORMATION_SCHEMA.COLUMNS "
            "WHERE table_name = '{}'".format(table),
            db).fetchall()
        assert schema == [(u"id", u"integer", None),
                          (u"timestamp", u"timestamp without time zone", None),
                          (u"user_id", u"text", None),
                          (u"event", u"text", None),
                          (u"metadata", u"jsonb", None)]

def test_drop_table(self):
        """Ensure that tables are deleted successfully."""

        tracker = pawprint.Tracker(db=db, table=table)
        tracker.create_table()

        with pytest.raises(ProgrammingError):
            tracker.create_table()

        tracker.drop_table()
        tracker.create_table()

def update_row(table_name, uids, keys, values, nickname, _tn):
    """
    Updates the data in a specific row of an table

    :param table_name: Name of the table
    :param uids: Array with uids
    :param keys: Array with keys
    :param values: Array with values
    :param nickname: Current nickname of the user
    :param _tn: Translator
    :return: Empty string or error message
    """
    if not is_user_admin(nickname):
        return _tn.get(_.noRights)

    if not table_name.lower() in table_mapper:
        return _tn.get(_.internalKeyError)

    table = table_mapper[table_name.lower()]['table']
    try:
        update_dict, success = __update_row_dict(table, values, keys, _tn)
        if not success:
            return update_dict  # update_dict is a string
    except ProgrammingError as e:
        logger('AdminLib', 'update_row ProgrammingError in __update_row_dict', str(e))
        return 'SQLAlchemy ProgrammingError: ' + str(e)

    try:
        __update_row(table, table_name, uids, update_dict)

    except IntegrityError as e:
        logger('AdminLib', 'update_row IntegrityError', str(e))
        return 'SQLAlchemy IntegrityError: ' + str(e)
    except ProgrammingError as e:
        logger('AdminLib', 'update_row ProgrammingError', str(e))
        return 'SQLAlchemy ProgrammingError: ' + str(e)

    DBDiscussionSession.flush()
    transaction.commit()
    return ''

def delete_row(table_name, uids, nickname, _tn):
    """
    Deletes a row in a table

    :param table_name: Name of the table
    :param uids: Array with uids
    :param nickname: Current nickname of the user
    :param _tn: Translator
    :return: Empty string or error message
    """
    logger('AdminLib', 'delete_row', table_name + ' ' + str(uids) + ' ' + nickname)
    if not is_user_admin(nickname):
        return _tn.get(_.noRights)

    if not table_name.lower() in table_mapper:
        return _tn.get(_.internalKeyError)

    table = table_mapper[table_name.lower()]['table']
    try:
        # check if there is a table, where uid is not the PK!
        if table_name.lower() == 'settings':
            uid = DBDiscussionSession.query(User).filter_by(nickname=uids[0]).first().uid
            DBDiscussionSession.query(table).filter_by(author_uid=uid).delete()
        elif table_name.lower() == 'premise':
            DBDiscussionSession.query(table).filter(Premise.premisesgroup_uid == uids[0],
                                                    Premise.statement_uid == uids[1]).delete()
        else:
            DBDiscussionSession.query(table).filter_by(uid=uids[0]).delete()

    except IntegrityError as e:
        logger('AdminLib', 'delete_row IntegrityError', str(e))
        return 'SQLAlchemy IntegrityError: ' + str(e)
    except ProgrammingError as e:
        logger('AdminLib', 'delete_row ProgrammingError', str(e))
        return 'SQLAlchemy ProgrammingError: ' + str(e)

    DBDiscussionSession.flush()
    transaction.commit()
    return ''

def create_user(database_uri, user, password):
    engine = create_engine(database_uri)

    conn = engine.connect()

    try:
        conn.execute("CREATE USER {} WITH PASSWORD '{}';".format(user, password))
    except ProgrammingError:
        logger.error('SQL error creating user')
    except Exception:
        logger.exception('Error creating user')

    try:
        conn.execute(
            ('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES,'
             ' TRIGGER ON TABLES TO {user};').format(user=user)
        )
        conn.execute(
            ('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO {user};')
            .format(user=user)
        )

        conn.execute(
            ('GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public '
             'TO {};').format(user)
        )
        conn.execute(
            ('GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO {};').format(user)
        )
    except ProgrammingError as e:
        logger.error('SQL error assigning permissions: {}'.format(e))
    except Exception:
        logger.exception('Error assigning permissions')

def _format_sql(self, sql, write_pk_field):
        last_pk = 0
        try:
            rows = self.write_db.query("SELECT MAX({}) max FROM {};".format(
                write_pk_field, self.write_table_name
            ))
        except (OperationalError, ProgrammingError):
            pass
        else:
            last_pk = next(rows)['max'] or last_pk
        return self.read_db.query(sql.format(last_pk))

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 detect_version(conn):
    """
    Detect the version of the database. This is typically done by reading the
    contents of the ``configuration`` table, but before that was added we can
    guess a couple of versions based on what tables exist (or don't). Returns
    ``None`` if the database appears uninitialized, and raises
    :exc:`RuntimeError` is the version is so ancient we can't do anything with
    it.
    """
    try:
        with conn.begin():
            db_version = conn.scalar(text(
                "SELECT version FROM configuration"))
    except exc.ProgrammingError:
        with conn.begin():
            packages_exists = bool(conn.scalar(text(
                "SELECT 1 FROM pg_catalog.pg_tables "
                "WHERE schemaname = 'public' AND tablename = 'packages'")))
        with conn.begin():
            statistics_exists = bool(conn.scalar(text(
                "SELECT 1 FROM pg_catalog.pg_views "
                "WHERE schemaname = 'public' AND viewname = 'statistics'")))
        with conn.begin():
            files_exists = bool(conn.scalar(text(
                "SELECT 1 FROM pg_catalog.pg_tables "
                "WHERE schemaname = 'public' AND tablename = 'files'")))
        if not packages_exists:
            # Database is uninitialized
            return None
        elif not files_exists:
            # Database is too ancient to upgrade
            raise RuntimeError("Database version older than 0.4; cannot upgrade")
        elif not statistics_exists:
            return "0.4"
        else:
            return "0.5"
    else:
        return db_version

def dashboard():
    """Show PYBOSSA Dashboard."""
    try:
        if request.args.get('refresh') == '1':
            db_jobs = get_dashboard_jobs()
            for j in db_jobs:
                DASHBOARD_QUEUE.enqueue(j['name'])
            msg = gettext('Dashboard jobs enqueued,'
                          ' refresh page in a few minutes')
            flash(msg)
        active_users_last_week = dashb.format_users_week()
        active_anon_last_week = dashb.format_anon_week()
        draft_projects_last_week = dashb.format_draft_projects()
        published_projects_last_week = dashb.format_published_projects()
        update_projects_last_week = dashb.format_update_projects()
        new_tasks_week = dashb.format_new_tasks()
        new_task_runs_week = dashb.format_new_task_runs()
        new_users_week = dashb.format_new_users()
        returning_users_week = dashb.format_returning_users()
        update_feed = get_update_feed()

        response = dict(
            template='admin/dashboard.html',
            title=gettext('Dashboard'),
            active_users_last_week=active_users_last_week,
            active_anon_last_week=active_anon_last_week,
            draft_projects_last_week=draft_projects_last_week,
            published_projects_last_week=published_projects_last_week,
            update_projects_last_week=update_projects_last_week,
            new_tasks_week=new_tasks_week,
            new_task_runs_week=new_task_runs_week,
            new_users_week=new_users_week,
            returning_users_week=returning_users_week,
            update_feed=update_feed,
            wait=False)
        return handle_content_type(response)
    except ProgrammingError as e:
        response = dict(template='admin/dashboard.html',
                        title=gettext('Dashboard'),
                        wait=True)
        return handle_content_type(response)
    except Exception as e:  # pragma: no cover
        current_app.logger.error(e)
        return abort(500)

def fx_sess(fx_engine):
    metadata = Base.metadata
    foreign_key_turn_off = {
        'mysql': 'SET FOREIGN_KEY_CHECKS=0;',
        'postgresql': 'SET CONSTRAINTS ALL DEFERRED;',
        'sqlite': 'PRAGMA foreign_keys = OFF;',
    }
    foreign_key_turn_on = {
        'mysql': 'SET FOREIGN_KEY_CHECKS=1;',
        'postgresql': 'SET CONSTRAINTS ALL IMMEDIATE;',
        'sqlite': 'PRAGMA foreign_keys = ON;',
    }
    truncate_query = {
        'mysql': 'TRUNCATE TABLE {};',
        'postgresql': 'TRUNCATE TABLE {} RESTART IDENTITY CASCADE;',
        'sqlite': 'DELETE FROM {};',
    }
    error = False
    with fx_engine.begin() as conn:
        try:
            conn.execute(foreign_key_turn_off[fx_engine.name])
        except ProgrammingError:
            error = True

        for table in reversed(metadata.sorted_tables):
            try:
                conn.execute(truncate_query[fx_engine.name].format(table.name))
            except ProgrammingError:
                error = True

        try:
            conn.execute(foreign_key_turn_on[fx_engine.name])
        except ProgrammingError:
            error = True

    if error:
        metadata = Base.metadata
        metadata.drop_all(bind=fx_engine)
        metadata.create_all(bind=fx_engine)

    sess = Session(bind=fx_engine)
    yield sess
    sess.rollback()