Parameter usability and data API deployment

[kislyuk]

http https://query.dev.data.humancellatlas.org/v1/query query="SELECT * FROM FILES LIMIT %(l)s" params:='{"l": "1"}'

This query works, and uses the "pyformat" parameter style required by psycopg2 starting with 4a16ed7.

When we switch to the Aurora Data API, we will need to switch back to the "named" parameter style still deployed on prod (because sqlalchemy also uses the "named" parameter style):

http https://query.data.humancellatlas.org/v1/query query="SELECT * FROM FILES LIMIT :l" params:='{"l": "1"}'

This will fix the issue where this query broke in the query builder after 4a16ed7, which will be an unambiguous improvement: the "named" paramstyle follows the principle of least surprise and doesn't require you to escape % characters.

Switching to the Aurora Data API will have another positive side effect: it will make error messages better when parameters are improperly formatted.

When passing the query through sqlalchemy, a parameter mismatch error looked like this:

>http https://query.data.humancellatlas.org/v1/query query="SELECT * FROM FILES LIMIT :l" params:='{"z": "1"}'
HTTP/1.1 500 Internal Server Error
Access-Control-Allow-Headers: Authorization,Content-Type,X-Amz-Date,X-Amz-Security-Token,X-Api-Key
Access-Control-Allow-Origin: *
Connection: keep-alive
Content-Length: 3935
Content-Type: text/plain
Date: Fri, 25 Oct 2019 00:27:08 GMT
Via: 1.1 f1234553b388306d833e1a4591227882.cloudfront.net (CloudFront)
X-Amz-Cf-Id: ZvDi03JyPTaIjRM9B8drHHXNzHHp172M6VpyO9cm4fFWwwwXA93Xqw==
X-Amz-Cf-Pop: SFO5-C1
X-Amzn-Trace-Id: Root=1-5db2415c-6b812950a9620b3658a4c812;Sampled=0
X-Cache: Error from cloudfront
x-amz-apigw-id: CF8mcHtqoAMFeUQ=
x-amzn-RequestId: 545ed7d2-061b-4723-9ec2-880ca1be5557

Traceback (most recent call last):
  File "/var/task/sqlalchemy/engine/base.py", line 1179, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/var/task/sqlalchemy/engine/default.py", line 683, in _init_compiled
    for grp, m in enumerate(parameters)
  File "/var/task/sqlalchemy/engine/default.py", line 683, in <listcomp>
    for grp, m in enumerate(parameters)
  File "/var/task/sqlalchemy/sql/compiler.py", line 665, in construct_params
    code="cd3x",
sqlalchemy.exc.InvalidRequestError: A value is required for bind parameter 'l' (Background on this error at: http://sqlalche.me/e/cd3x)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/var/task/chalice/app.py", line 842, in _get_view_function_response
    response = view_function(**function_args)
  File "/var/task/dcpquery/api/__init__.py", line 83, in dispatch
    flask_res = self.connexion_app.app.full_dispatch_request()
  File "/var/task/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/var/task/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/var/task/flask/_compat.py", line 35, in reraise
    raise value
  File "/var/task/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/var/task/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/var/task/connexion/decorators/decorator.py", line 48, in wrapper
    response = function(request)
  File "/var/task/connexion/decorators/uri_parsing.py", line 143, in wrapper
    response = function(request)
  File "/var/task/connexion/decorators/validation.py", line 172, in wrapper
    response = function(request)
  File "/var/task/connexion/decorators/response.py", line 109, in wrapper
    response = function(request)
  File "/var/task/connexion/decorators/parameter.py", line 126, in wrapper
    return function(**kwargs)
  File "/var/task/dcpquery/api/query.py", line 20, in post
    for row in run_query(query, params):
  File "/var/task/dcpquery/db/__init__.py", line 168, in run_query
    cursor = config.db_session.execute(query, params=params)
  File "/var/task/sqlalchemy/orm/session.py", line 1268, in execute
    clause, params or {}
  File "/var/task/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/var/task/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/var/task/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/var/task/sqlalchemy/engine/base.py", line 1182, in _execute_context
    e, util.text_type(statement), parameters, None, None
  File "/var/task/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/var/task/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/var/task/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/var/task/sqlalchemy/engine/base.py", line 1179, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/var/task/sqlalchemy/engine/default.py", line 683, in _init_compiled
    for grp, m in enumerate(parameters)
  File "/var/task/sqlalchemy/engine/default.py", line 683, in <listcomp>
    for grp, m in enumerate(parameters)
  File "/var/task/sqlalchemy/sql/compiler.py", line 665, in construct_params
    code="cd3x",
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'l'
[SQL: SELECT * FROM FILES LIMIT %(l)s]
[parameters: [{'z': '1'}]]
(Background on this error at: http://sqlalche.me/e/cd3x)

Currently, the error looks like this:

>http https://query.dev.data.humancellatlas.org/v1/query query="SELECT * FROM FILES LIMIT %(l)s" params:='{"z": "1"}'
HTTP/1.1 500 Internal Server Error
Access-Control-Allow-Headers: Authorization,Content-Type,X-Amz-Date,X-Amz-Security-Token,X-Api-Key
Access-Control-Allow-Origin: *
Connection: keep-alive
Content-Encoding: gzip
Content-Length: 644
Content-Type: application/problem+json
Date: Fri, 25 Oct 2019 00:28:18 GMT
Via: 1.1 01a455b735347358143739c76de1ab1c.cloudfront.net (CloudFront)
X-Amz-Cf-Id: JeIvvy2AiU6JSvG4FFJnstAQjTFaAyVcEEMfRpZNDqX1ZA_SPf8Rdw==
X-Amz-Cf-Pop: SFO5-C1
X-Amzn-Trace-Id: Root=1-5db241a2-ba3d2e32aafd5db08eacbb28;Sampled=0
X-Cache: Error from cloudfront
x-amz-apigw-id: CF8xWEGmoAMFqjw=
x-amzn-RequestId: 73430843-e516-4b33-b96d-9658c903fa98

{
    "detail": "Traceback (most recent call last):\n  File \"/var/task/flask/app.py\", line 1949, in full_dispatch_request\n    rv = self.dispatch_request()\n  File \"/var/task/flask/app.py\", line 1935, in dispatch_request\n    return self.view_functions[rule.endpoint](**req.view_args)\n  File \"/var/task/connexion/decorators/decorator.py\", line 48, in wrapper\n    response = function(request)\n  File \"/var/task/connexion/decorators/uri_parsing.py\", line 143, in wrapper\n    response = function(request)\n  File \"/var/task/connexion/decorators/validation.py\", line 172, in wrapper\n    response = function(request)\n  File \"/var/task/connexion/decorators/response.py\", line 109, in wrapper\n    response = function(request)\n  File \"/var/task/connexion/decorators/parameter.py\", line 126, in wrapper\n    return function(**kwargs)\n  File \"/var/task/dcpquery/api/query.py\", line 20, in post\n    for row in run_query(query, params):\n  File \"/var/task/dcpquery/db/__init__.py\", line 201, in run_query\n    cursor = config.db.execute(query, params)\n  File \"/var/task/sqlalchemy/engine/base.py\", line 2166, in execute\n    return connection.execute(statement, *multiparams, **params)\n  File \"/var/task/sqlalchemy/engine/base.py\", line 982, in execute\n    return self._execute_text(object_, multiparams, params)\n  File \"/var/task/sqlalchemy/engine/base.py\", line 1155, in _execute_text\n    parameters,\n  File \"/var/task/sqlalchemy/engine/base.py\", line 1248, in _execute_context\n    e, statement, parameters, cursor, context\n  File \"/var/task/sqlalchemy/engine/base.py\", line 1468, in _handle_dbapi_exception\n    util.reraise(*exc_info)\n  File \"/var/task/sqlalchemy/util/compat.py\", line 154, in reraise\n    raise value\n  File \"/var/task/sqlalchemy/engine/base.py\", line 1244, in _execute_context\n    cursor, statement, parameters, context\n  File \"/var/task/sqlalchemy/engine/default.py\", line 550, in do_execute\n    cursor.execute(statement, parameters)\nKeyError: 'l'\n",
    "status": 500,
    "title": "KeyError('l')",
    "type": "about:blank"
}

Using the Aurora Data API, the error will look like this:

aws rds-data execute-statement --resource-arn arn:aws:rds:us-east-1:861229788715:cluster:database-1 --secret-arn arn:aws:secretsmanager:us-east-1:861229788715:secret:akislyuk-s2 --sql "select * from files limit :s"

An error occurred (BadRequestException) when calling the ExecuteStatement operation: Cannot find parameter: s