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
3
View Complete Implementation : database_test.py
Copyright BSD 3-Clause "New" or "Revised" License
Author : CloverHealth
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
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
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
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
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
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
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)