sqlalchemy.exc.DataError - python examples

Here are the examples of the python api sqlalchemy.exc.DataError taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

7 Examples 7

3 View Complete Implementation : database_test.py
Copyright BSD 3-Clause "New" or "Revised" License
Author : CloverHealth
def test_create_extension_no_injection(clean_db):
    """Verify that install_extension is invulnerable to SQL injection."""
    # Try installing an extension that will result in the following query:
    #   CREATE EXTENSION "uuid-ossp"; DROP TABLE IF EXISTS test CASCADE; --"
    malicious_extension = 'uuid-ossp"; DROP TABLE IF EXISTS test CASCADE; --'

    # If we quoted this right then an exception should've been raised because
    # 'uuid-ossp"; DROP TABLE test CASCADE; --' is not a valid extension name.
    # If no exception is raised then the DROP succeeded.
    with pytest.raises(sqla_exc.DataError):
        clean_db.install_extension(malicious_extension)

3 View Complete Implementation : util.py
Copyright MIT License
Author : ourresearch
def safe_commit(db):
    try:
        db.session.commit()
        return True
    except (KeyboardInterrupt, SystemExit):
        # let these ones through, don't save anything to db
        raise
    except sqlalchemy.exc.DataError:
        db.session.rollback()
        print u"sqlalchemy.exc.DataError on commit.  rolling back."
    except Exception:
        db.session.rollback()
        print u"generic exception in commit.  rolling back."
        logging.exception("commit error")
    return False

0 View Complete Implementation : db.py
Copyright GNU General Public License v3.0
Author : italia
def upsert_data(context, data_dict):
    '''insert all data from records'''
    if not data_dict.get('records'):
        return

    method = data_dict.get('method', _UPSERT)

    fields = _get_fields(context, data_dict)
    field_names = _pluck('id', fields)
    records = data_dict['records']
    sql_columns = ", ".join(['"%s"' % name.replace(
        '%', '%%') for name in field_names] + ['"_full_text"'])

    if method == _INSERT:
        rows = []
        for num, record in enumerate(records):
            _validate_record(record, num, field_names)

            row = []
            for field in fields:
                value = record.get(field['id'])
                if value and field['type'].lower() == 'nested':
                    ## a tuple with an empty second value
                    value = (json.dumps(value), '')
                row.append(value)
            row.append(_to_full_text(fields, record))
            rows.append(row)

        sql_string = u'''INSERT INTO "{res_id}" ({columns})
            VALUES ({values}, to_tsvector(%s));'''.format(
            res_id=data_dict['resource_id'],
            columns=sql_columns,
            values=', '.join(['%s' for field in field_names])
        )

        try:
            context['connection'].execute(sql_string, rows)
        except sqlalchemy.exc.DataError as err:
            raise InvalidDataError(
                toolkit._("The data was invalid (for example: a numeric value "
                          "is out of range or was inserted into a text field)."
                          ))

    elif method in [_UPDATE, _UPSERT]:
        unique_keys = _get_unique_key(context, data_dict)
        if len(unique_keys) < 1:
            raise ValidationError({
                'table': [u'table does not have a unique key defined']
            })

        for num, record in enumerate(records):
            # all key columns have to be defined
            missing_fields = [field for field in unique_keys
                              if field not in record]
            if missing_fields:
                raise ValidationError({
                    'key': [u'''fields "{fields}" are missing
                        but needed as key'''.format(
                            fields=', '.join(missing_fields))]
                })

            for field in fields:
                value = record.get(field['id'])
                if value is not None and field['type'].lower() == 'nested':
                    ## a tuple with an empty second value
                    record[field['id']] = (json.dumps(value), '')

            non_existing_filed_names = [field for field in record
                                        if field not in field_names]
            if non_existing_filed_names:
                raise ValidationError({
                    'fields': [u'fields "{0}" do not exist'.format(
                        ', '.join(non_existing_filed_names))]
                })

            unique_values = [record[key] for key in unique_keys]

            used_fields = [field for field in fields
                           if field['id'] in record]

            used_field_names = _pluck('id', used_fields)

            used_values = [record[field] for field in used_field_names]

            full_text = _to_full_text(fields, record)

            if method == _UPDATE:
                sql_string = u'''
                    UPDATE "{res_id}"
                    SET ({columns}, "_full_text") = ({values}, to_tsvector(%s))
                    WHERE ({primary_key}) = ({primary_value});
                '''.format(
                    res_id=data_dict['resource_id'],
                    columns=u', '.join(
                        [u'"{0}"'.format(field)
                         for field in used_field_names]),
                    values=u', '.join(
                        ['%s' for _ in used_field_names]),
                    primary_key=u','.join(
                        [u'"{0}"'.format(part) for part in unique_keys]),
                    primary_value=u','.join(["%s"] * len(unique_keys))
                )
                results = context['connection'].execute(
                    sql_string, used_values + [full_text] + unique_values)

                # validate that exactly one row has been updated
                if results.rowcount != 1:
                    raise ValidationError({
                        'key': [u'key "{0}" not found'.format(unique_values)]
                    })

            elif method == _UPSERT:
                sql_string = u'''
                    UPDATE "{res_id}"
                    SET ({columns}, "_full_text") = ({values}, to_tsvector(%s))
                    WHERE ({primary_key}) = ({primary_value});
                    INSERT INTO "{res_id}" ({columns}, "_full_text")
                           SELECT {values}, to_tsvector(%s)
                           WHERE NOT EXISTS (SELECT 1 FROM "{res_id}"
                                    WHERE ({primary_key}) = ({primary_value}));
                '''.format(
                    res_id=data_dict['resource_id'],
                    columns=u', '.join([u'"{0}"'.format(field)
                                        for field in used_field_names]),
                    values=u', '.join(['%s::nested'
                                       if field['type'] == 'nested' else '%s'
                                       for field in used_fields]),
                    primary_key=u','.join([u'"{0}"'.format(part)
                                           for part in unique_keys]),
                    primary_value=u','.join(["%s"] * len(unique_keys))
                )
                context['connection'].execute(
                    sql_string,
                    (used_values + [full_text] + unique_values) * 2)

0 View Complete Implementation : test_db.py
Copyright GNU General Public License v3.0
Author : italia
@mock.patch("ckanext.datastore.db._get_fields")
def test_upsert_with_insert_method_and_invalid_data(
        mock_get_fields_function):
    """upsert_data() should raise InvalidDataError if given invalid data.

    If the type of a field is numeric and upsert_data() is given a whitespace
    value like "   ", it should raise DataError.

    In this case we're testing with "method": "insert" in the data_dict.

    """
    mock_connection = mock.Mock()
    mock_connection.execute.side_effect = sqlalchemy.exc.DataError(
        "statement", "params", "orig", connection_invalidated=False)

    context = {
        "connection": mock_connection,
    }
    data_dict = {
        "fields": [{"id": "value", "type": "numeric"}],
        "records": [
            {"value": 0},
            {"value": 1},
            {"value": 2},
            {"value": 3},
            {"value": "   "},  # Invalid numeric value.
            {"value": 5},
            {"value": 6},
            {"value": 7},
        ],
        "method": "insert",
        "resource_id": "fake-resource-id",
    }

    mock_get_fields_function.return_value = data_dict["fields"]

    nose.tools.astert_raises(
        db.InvalidDataError, db.upsert_data, context, data_dict)

0 View Complete Implementation : projectuserrole.py
Copyright Apache License 2.0
Author : molior-dbs
async def upsert_project_user_role(request):
    """
    Set/update a user role for a project.

    ---
    description: Set or update role for user_id on project_id
    tags:
      - Project UserRole
    produces:
      - application/json
    parameters:
      - name: project_id
        description: id of the project
        in: path
        required: true
        type: integer
      - name: user_id
        description: id of the user
        in: path
        required: true
        type: integer
      - name: role
        description: role to set
        in: query
        required: false
        type: string
    responses:
      "200":
        description: Return a dict with result
        schema:
          type: object
          properties:
            result:
              type: string
      "400":
        description: Invalid input where given
    """

    project_id = request.match_info["project_id"]
    user_id = request.match_info["user_id"]

    params = await request.json()
    user_role = params.get("role", "member")

    try:
        project_id = int(project_id)
    except (ValueError, TypeError):
        return web.Response(status=400, text="Incorrect project_id")
    try:
        user_id = int(user_id)
    except (ValueError, TypeError):
        return web.Response(status=400, text="Incorrect user_id")

    project = request.cirrina.db_session.query(Project).filter_by(id=project_id).first()
    if not project:
        return web.Response(status=404, text="Project not found")

    user = request.cirrina.db_session.query(User).filter_by(id=user_id).first()
    if not user:
        return web.Response(status=404, text="User not found")

    rolerec = (
        request.cirrina.db_session.query(UserRole)
        .filter_by(project_id=project.id, user_id=user.id)
        .first()
    )

    if rolerec:
        rolerec.role = user_role
    else:
        request.cirrina.db_session.add(
            UserRole(user_id=user_id, project_id=project_id, role=user_role)
        )
    data = {
        "result": "{u} is now {r} on {p}".format(
            u=user.username, r=user_role, p=project.name
        )
    }

    try:
        request.cirrina.db_session.commit()
    except sqlalchemy.exc.DataError:
        request.cirrina.db_session.rollback()
        return web.Response(status=500, text="Database error")

    # TODO : change to a multicast group
    await app.websocket_broadcast(
        {
            "event": Event.changed.value,
            "subject": Subject.userrole.value,
            "changed": {"id": user_id, "project_id": project_id, "role": user_role},
        }
    )

    return web.json_response(data)

0 View Complete Implementation : projectuserrole.py
Copyright Apache License 2.0
Author : molior-dbs
async def remove_project_user(request):
    """
    Remove a user role for a project.

    ---
    description: Remove role for user_id from project_id
    tags:
      - Project UserRole
    produces:
      - application/json
    parameters:
      - name: project_id
        description: id of the project
        in: path
        required: true
        type: integer
      - name: user_id
        description: id of the user
        in: path
        required: true
        type: integer
    responses:
      "200":
        description: Return a dict with result
        schema:
          type: object
          properties:
            result:
              type: string
      "400":
        description: Invalid input where given
    """
    project_id = request.match_info["project_id"]
    user_id = request.match_info["user_id"]
    try:
        project_id = int(project_id)
    except (ValueError, TypeError):
        return web.Response(status=400, text="Incorrect project_id")
    try:
        user_id = int(user_id)
    except (ValueError, TypeError):
        return web.Response(status=400, text="Incorrect user_id")

    project = request.cirrina.db_session.query(Project).filter_by(id=project_id).first()
    if not project:
        return web.Response(status=404, text="Project not found")

    user = request.cirrina.db_session.query(User).filter_by(id=user_id).first()
    if not user:
        return web.Response(status=404, text="User not found")

    rolerec = (
        request.cirrina.db_session.query(UserRole)
        .filter_by(project_id=project.id, user_id=user.id)
        .first()
    )

    if not rolerec:
        return web.Response(status=400, text="No role to delete")

    (
        request.cirrina.db_session.query(UserRole)
        .filter_by(project_id=project.id, user_id=user.id)
        .delete()
    )
    try:
        request.cirrina.db_session.commit()
    except sqlalchemy.exc.DataError:
        request.cirrina.db_session.rollback()
        return web.Response(status=500, text="Database error")
    data = {"result": "{u} is removed from {p}".format(u=user.username, p=project.name)}

    # TODO : change to a multicast group
    await app.websocket_broadcast(
        {
            "event": Event.removed.value,
            "subject": Subject.userrole.value,
            "changed": {"id": user_id, "project_id": project_id},
        }
    )

    return web.json_response(data)

0 View Complete Implementation : user.py
Copyright Apache License 2.0
Author : molior-dbs
async def put_user_byid(request):
    """
    Update a user by id (not yet implemented).

    ---
    description: Change a user
    tags:
      - Users
    parameters:
      - name: user_id
        description: id of the user
        in: path
        required: true
        type: integer
      - name: is_admin
        description: set admin or not
        in: query
        required: false
        type: boolean
    responses:
      "200":
        description: Sucess
        schema:
          type: object
          properties:
            result:
              type: string
      "204":
        description: Nothing to change
      "400":
        description: Invalid parameter
      "404":
        description: User not found
      "500":
        description: Database problem
    """
    user_id = request.match_info["user_id"]
    try:
        user_id = int(user_id)
    except (ValueError, TypeError):
        return web.Response(text="Incorrect value for user_id", status=400)

    user = request.cirrina.db_session.query(User).filter_by(id=user_id).first()
    if not user:
        return web.Response(status=404, text="User not found")

    if user.username == "admin":
        return web.Response(status=400, text="Cannot change admin")

    is_admin = request.GET.getone("is_admin", None)  # str "true" or "flase"
    if not is_admin:  # if None
        return web.Response(text="Nothing to change", status=204)

    if is_admin.lower() == "true":
        user.is_admin = True
        data = {"result": "{u} is now admin ".format(u=user.username)}
    elif is_admin.lower() == "false":
        user.is_admin = False
        data = {"result": "{u} is no longer admin ".format(u=user.username)}

    try:
        request.cirrina.db_session.commit()  # pylint: disable=no-member
    except sqlalchemy.exc.DataError:
        request.cirrina.db_session.rollback()  # pylint: disable=no-member
        return web.Response(status=500, text="Database error")

    # TODO : change to a multicast group
    await app.websocket_broadcast(
        {
            "event": Event.changed.value,
            "subject": Subject.user.value,
            "changed": {"id": user_id, "is_admin": user.is_admin},
        }
    )

    return web.json_response(data)