Code Monkey home page Code Monkey logo

aurora-data-api's Introduction

aurora-data-api - A Python DB-API 2.0 client for the AWS Aurora Serverless v1 and v2 Data API

Installation

pip install aurora-data-api

Prerequisites

  • Set up an AWS Aurora Serverless v2 cluster and enable Data API access for it (Aurora Serverless v1 is also supported). If you have previously set up an Aurora Serverless cluster, you can enable Data API with the following AWS CLI command:

    aws rds modify-db-cluster --db-cluster-identifier DB_CLUSTER_NAME --enable-http-endpoint --apply-immediately

  • Save the database credentials in AWS Secrets Manager using a format expected by the Data API (a JSON object with the keys username and password):

    aws secretsmanager create-secret --name rds-db-credentials/MY_DB
aws secretsmanager put-secret-value --secret-id rds-db-credentials/MY_DB --secret-string "$(jq -n '.username=env.PGUSER | .password=env.PGPASSWORD')"

  • Configure your AWS command line credentials using standard AWS conventions. You can verify that everything works correctly by running a test query via the AWS CLI:

    aws rds-data execute-statement --resource-arn RESOURCE_ARN --secret-arn SECRET_ARN --sql "select * from pg_catalog.pg_tables"
    • Here, RESOURCE_ARN refers to the Aurora RDS database ARN, which can be found in the AWS RDS Console (click on your database, then "Configuration") or in the CLI by running aws rds describe-db-clusters. SECRET_ARN refers to the AWS Secrets Manager secret created above.
    • When running deployed code (on an EC2 instance, ECS/EKS container, or Lambda), you can use the managed IAM policy AmazonRDSDataFullAccess to grant your IAM role permissions to access the RDS Data API (while this policy is convenient for testing, we recommend that you create your own scoped down least-privilege policy for production applications).

Usage

Use this module as you would use any DB-API compatible driver module. The aurora_data_api.connect() method is the standard main entry point, and accepts two implementation-specific keyword arguments:

  • aurora_cluster_arn (also referred to as resourceArn in the Data API documentation)
    • If not given as a keyword argument, this can also be specified using the AURORA_CLUSTER_ARN environment variable
  • secret_arn (the database credentials secret)
    • If not given as a keyword argument, this can also be specified using the AURORA_SECRET_ARN environment variable
import aurora_data_api

cluster_arn = "arn:aws:rds:us-east-1:123456789012:cluster:my-aurora-serverless-cluster"
secret_arn = "arn:aws:secretsmanager:us-east-1:123456789012:secret:rds-db-credentials/MY_DB"
with aurora_data_api.connect(aurora_cluster_arn=cluster_arn, secret_arn=secret_arn, database="my_db") as conn:
    with conn.cursor() as cursor:
        cursor.execute("select * from pg_catalog.pg_tables")
        print(cursor.fetchall())

The cursor supports iteration (and automatically wraps the query in a server-side cursor and paginates it if required):

with conn.cursor() as cursor:
    for row in cursor.execute("select * from pg_catalog.pg_tables"):
        print(row)

Motivation

The RDS Data API is the link between the AWS Lambda serverless environment and the sophisticated features provided by PostgreSQL and MySQL. The Data API tunnels SQL over HTTP, which has advantages in the context of AWS Lambda:

  • It eliminates the need to open database ports to the AWS Lambda public IP address pool
  • It uses stateless HTTP connections instead of stateful internal TCP connection pools used by most database drivers (the stateful pools become invalid after going through AWS Lambda freeze-thaw cycles, causing connection errors and burdening the database server with abandoned invalid connections)
  • It uses AWS role-based authentication, eliminating the need for the Lambda to handle database credentials directly

Links

Bugs

Please report bugs, issues, feature requests, etc. on GitHub.

License

Licensed under the terms of the Apache License, Version 2.0.

https://travis-ci.org/chanzuckerberg/aurora-data-api.png https://codecov.io/github/chanzuckerberg/aurora-data-api/coverage.svg?branch=master

aurora-data-api's People

Contributors

4word avatar dnk8n avatar healarconr avatar joyofhex avatar kislyuk avatar mat100payette avatar naturalethic avatar ororog avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Watchers

avatar avatar avatar avatar avatar avatar avatar avatar avatar

Forkers

senterallc hyuma corwintanner ororog vkelk olinger dnk8n alydh jetbridge predictionmachine vfigueiro zacharyspector 4word duncankoss carmeraco hizvi rgustafson-ie wls-jd jurasofish atreides-ai nsmithuk fenestron achint08 jshhrrsn ivarmedi fall1ngstar hang15 joyofhex healarconr antonm00715 jsharpe preset-io mat100payette kyruski ajeetkumarsingh321 andy-goellner bruceadams yuvaljacob duygu-edb arpitjain799

aurora-data-api's Issues

Using Tuples in cursor.excecutemany does not work with aurora_data_api.connect()

I have noticed that when using the aurora_data_api, when using the cursor.executemany method for a MySQL DB, I cannot use a list of Tuples, only a list of dicts is processed correctly, while the "official" MySQL Python docs give an example of using a Tuple and not a dict.

When I try to use a list of Tuples with cursor.excecutemany, like seen below, I get an error message regarding Tuples:

            INSERT INTO aurora_data_api_test
            (name, doc, num, ts)
            VALUES (%s, %s, %s, %s)
            """
            
            data = [(f"name-{i}", f"doc-{i}", "5.0", "2020-09-17 13:49:32.780180") for i in range(2048)]
            
            response = cursor.executemany(sql, data)

[ERROR] AttributeError: 'tuple' object has no attribute 'items'

I believe that it is related to this part in your init.py file, which expects to get a dict:

def _format_parameter_set(self, parameters):
      return [self.prepare_param(k, v) for k, v in parameters.items()]

Can you please help me with understanding this gap? The only way that it seems to work with auroro_data_api, is if I do this instead:

sql = """
            INSERT INTO aurora_data_api_test
            (name, doc, num, ts)
            VALUES (:name, :doc, :num, :ts)
            """

            response = cursor.executemany(sql, [
                {
                    "name": f"name-{i}",
                    "doc": f"doc-{i}",
                    "num": "5.0",
                    "ts": "2020-09-17 13:49:32.780180"
                } for i in range(2048)]
            )

If I run the code above and use my local docker image with the latest MySQL image, I get this error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':name, :doc, :num, :ts)' at line 3")

Pagination throws an error against Auora Serverless V1 running MySQL 5.7

At the current time this is more of a question if this should work.

Our situation is we have an AWS Auora v1 running as serverless running MySQL 5.7.

We have a large dataset that exceeds being queried in one call. I have seen in the docs for this library that there is some kind of pagination using server side cursors.

Stepping into the code the method "_start_paginated_query" throws an error here: "self._client.execute_statement(**execute_statement_args)" line 196.

{BadRequestException}An error occurred (BadRequestException) when calling the ExecuteStatement operation: Transaction AQ1a0rmK5xPx2DUvqTwbMHbNhwxGqF5Jz7hBXu732zAwepruvVqGRNLm0DkYoXHF0NX79RCmPvRFySf6NXhXMx9wLihYoIIpswCqo11yQiBoK+UBjxtHWzjMatnv/SYhWxHuZwiLKxwU78KIK7LFCwyEnpO1D9qcfntuyC95WC3+RD/sHww8uMGqQKKHbR9siS1c89xZZ2ga is not found: transaction has been aborted due to an error from a previous call

So I was just wondering if this is even supposed to work with this type of server configuration.

Thanks, I really like this library.

Async options?

From what I can tell, this does not seem to by asynchronous.

If that's inaccurate, can someone point me at the "how to implement async" docs?

If that's accurate, would the project be interested in help implementing async?

image

How to avoid using transactions / Long running processes are using transactions that have expired

I've been encountering an issue where I have a long-running process that primarily only reads data and is constantly returning (BadRequestException) when calling the ExecuteStatement operation: Transaction is not found.

According to AWS: A transaction times out if there are no calls that use its transaction ID in three minutes (ref)

An example to reproduce this issue:

with aurora_data_api.connect(aurora_cluster_arn=cluster_arn, secret_arn=secret_arn, database=database) as conn:
    with conn.cursor() as cursor:
        while True:
            cursor.execute("select * from pg_catalog.pg_tables")
            print(cursor.fetchall())
            time.sleep(3 * 60 + 1)

This problem stems from the following code:

    def cursor(self):
        if self._transaction_id is None:
            res = self._client.begin_transaction(database=self._dbname,
                                                 resourceArn=self._aurora_cluster_arn,
                                                 # schema="string", TODO
                                                 secretArn=self._secret_arn)
            self._transaction_id = res["transactionId"]
        cursor = AuroraDataAPICursor(client=self._client,
                                     dbname=self._dbname,
                                     aurora_cluster_arn=self._aurora_cluster_arn,
                                     secret_arn=self._secret_arn,
                                     transaction_id=self._transaction_id)

Things to note:

  • I have not started any transaction and have had no requirement or need to call .commit or .rollback as I have made no changes to the database through any UPDATE or DELETE queries
  • Transactions are deemed as optional in the rds-data API:

As per https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html

--transaction-id (optional) – The identifier of a transaction that was started using the begin-transaction CLI command. Specify the transaction ID of the transaction that you want to include the SQL statement in.

According to this information, there is no need to actually create and use transactions to perform database queries.

Suggestion:

  • Update def cursor() to NOT automatically create a transaction if one doesn't currently exist
  • Introduce a begin method that starts a new transaction
  • Users can opt to perform their database work in transactions or not.

Do my suggestions seem to be valid? I'm happy to (try to) contribute this change back.

v0.3.0 fails on aurora postgresql json columns

Hey there,

When upgrading to 0.3.0 (and sqlalchemy-aurora-data-api 0.3.1) from 0.2.7, loading json columns starts to fail with "the JSON object must be str, bytes or bytearray, not dict".

Versions:

  • Python: 3.7.10
  • aurora-data-api: 0.3.0
  • sqlalchemy-aurora-data-api: 0.3.1
  • AWS Aurora Serverless Data API for postgresql

Digging in, it seems that aurora-data-api 0.3.0 is now returning json data as a dictionary and version 0.2.7 returned a string.

The following line in init. _render_value(): see line on github converts the json string to a dict with the code json.loads(scalar_value).

Then, sqlalchemy sql.sqltypes.py tries to re-load the data as a dictionary in a result_processor() method: see method on github, but you can't run json.loads on a dict or you get the above error.

Using the previous version for now, but wanted to submit this issue to log it.

Also, I notice that there is a related issue in the sqlalchemy-aurora-data-api project, but I think the issue belongs in the aurora-data-api project. (sqlalchemy-aurora-data-api issue)

Thanks!

Transaction error in Execute StatementDatabaseError

Hello,

Firstly, many thanks for the excellent contribution 👍

I'm seeing unpredictable transaction errors arising from the aurora_data_api during our commits and queries.

DatabaseError: (aurora_data_api.exceptions.DatabaseError) An error occurred (BadRequestException) when calling the ExecuteStatement operation: Transaction [...] is not found

What I've tried:

  1. handling the DatabaseError in my application and trying to force the commit to execute in a new transaction as follows:
from aurora_data_api.exceptions import DatabaseError
from botocore.exceptions import ClientError

def handle_invalid_transaction_id(func):
    retries = 3

    @wraps(func)
    def inner(*args, **kwargs):
        for i in range(retries):
            try:
                return func(*args, **kwargs)
            except (DatabaseError, ClientError):
                if i != retries:
                    # The aim here is to try and force a new transaction 
                    # If an error occurs and retry
                    db.session.close()
                else:
                    raise

    return inner

And then in my models doing something like this:

class MyModel(db.Model):
    @classmethod
    @handle_invalid_transaction_id
    def create(cls, **kwargs):
        instance = cls(**kwargs)
        db.session.add(instance)
        db.session.commit()
        db.session.close()
        return kwargs

Unfortunately no joy here.

  1. Turning off sleeps in Aurora-serverless

I thought perhaps the issue was due to the instance hibernating mid-transaction and so experimented with turning off the sleep as per this blog. However, sadly the issue still persists :(

  1. I've attempted to fine to the commit with no_autoflush, but again I'm hitting the issue.

N.b. there's also a SO for this issue here

I don't know if this is a user error or an issue with the driver, but any help would be greatly appreciated as this is blocking a deployment.

Many thanks

NoRegionError: You must specify a region.

Hi,

I get this error when I try to run your sample code specifying my cluser_arn and secret_arn:

NoRegionError: You must specify a region.

The connect function -

with aurora_data_api.connect(
aurora_cluster_arn=cluster_arn,
secret_arn=secret_arn,
database=database) as conn:

does not allow specifying a region_name parameter like boto3.client does.

Database response exceeded size limit

👋 @kislyuk! I love the library! Pretty cool how quickly you were able to add support for Aurora Data API 😄

I'm working with some geospatial data that can get fairly large. Unfortunately that means my queries can overflow the boto3 ExecuteStatement size limit:

botocore.errorfactory.BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: Database response exceeded size limit
As you can imagine, limiting the number of returned records (probably via server-side cursor) would help here.

I'm new to sqlalchemy but it looks like some other database APIs have support for server-side cursors (see stream_results and max_row_buffer for psycopg2 execution options).

Just curious if you have thoughts on:

  • how this driver could add support for server-side cursors
  • other ideas to limit the response size from Data API to combat the "response exceeded size limit" error

In the meantime, I think I'll poke around the psycopg2 driver for inspiration. Thanks again for publishing this!

Documentation link not working

https://aurora-data-api.readthedocs.io/en/latest/

        \          SORRY            /
         \                         /
          \    This page does     /
           ]   not exist yet.    [    ,'|
           ]                     [   /  |
           ]___               ___[ ,'   |
           ]  ]\             /[  [ |:   |
           ]  ] \           / [  [ |:   |
           ]  ]  ]         [  [  [ |:   |
           ]  ]  ]__     __[  [  [ |:   |
           ]  ]  ] ]\ _ /[ [  [  [ |:   |
           ]  ]  ] ] (#) [ [  [  [ :===='
           ]  ]  ]_].nHn.[_[  [  [
           ]  ]  ]  HHHHH. [  [  [
           ]  ] /   `HH("N  \ [  [
           ]__]/     HHH  "  \[__[
           ]         NNN         [
           ]         N/"         [
           ]         N H         [
          /          N            \
         /           q,            \
        /                           \

Problem with expired transaction IDs when rolling back transactions

I was getting Boto errors in the logs of my Lambda's about expired transaction IDs when aurora_data_api tries to rollback a transaction.

AWS docs say that: "If the transaction ID has expired, the transaction was rolled back automatically. In this case, an aws rds-data rollback-transaction command that specifies the expired transaction ID returns an error."

So I tried catching that exception and ignoring it in aurora_data_api ... because presumably the transaction has already been rolled back automatically. I did this by adding a try/except around the rollback_transaction() call here:

aurora-data-api/aurora_data_api/__init__.py

Line 63 in 509cefe

self._client.rollback_transaction(resourceArn=self._aurora_cluster_arn, 

try:
    self._client.rollback_transaction(
        resourceArn=self._aurora_cluster_arn,
        secretArn=self._secret_arn,
        transactionId=self._transaction_id,
    )
except (BotoCoreError, ClientError) as exception:
    logger.warning(
        "Transaction ID has expired - ignoring rollback request. ",
        extra={
            "transaction_id": self._transaction_id,
            "exception": str(exception),
        },
    )

And then in execute(), I had to add another elif after this one:

aurora-data-api/aurora_data_api/__init__.py

Line 227 in 509cefe

elif "Database returned more than the allowed response size limit" in str(e): 

elif re.search(r"Transaction \S+ is not found", str(e)) or (
    "Invalid transaction ID" in str(e)
):
    self._transaction_id = None
    self.execute(operation, parameters=parameters)

This seems to work, and I no longer get those errors, but I'm not at all sure it's a good (or even correct) solution. Or what the actual root of the problem is. I still get those warning logs I've added (Transaction ID has expired...) several times a day.

Wait for paused database to launch

If you send a query over rds-data using boto3 on a paused Aurora Serverless database, an error will be returned:

BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: Communications link failure

It only takes a few seconds for the database to launch after this first failed query response. So it would be a nice feature if this package intercepts the Communications link failure and retries every second. You can simply probe the status by sending a dummy SQL statement, for example SELECT 1 would already suffice. This user on stackoverflow proposed a similar solution.

Would it be possible to include this functionality in the package?

Improper handling of BadRequestException

Hi there, I am seeing an unhandled exception ValueError: 0 is not a valid MySQLErrorCodes when I attempt to INSERT a duplicate item into one of my tables:

In [19]: save_model_raise(data, model)
---------------------------------------------------------------------------
BadRequestException                       Traceback (most recent call last)
~/src/my-repo/.venv/lib/python3.8/site-packages/aurora_data_api/__init__.py in execute(self, operation, parameters)
    219         try:
--> 220             res = self._client.execute_statement(**execute_statement_args)
    221             if "columnMetadata" in res:

~/src/my-repo/.venv/lib/python3.8/site-packages/botocore/client.py in _api_call(self, *args, **kwargs)
    385             # The "self" in this scope is referring to the BaseClient.
--> 386             return self._make_api_call(operation_name, kwargs)
    387

~/src/my-repo/.venv/lib/python3.8/site-packages/botocore/client.py in _make_api_call(self, operation_name, api_params)
    704             error_class = self.exceptions.from_code(error_code)
--> 705             raise error_class(parsed_response, operation_name)
    706         else:

BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: Database error code: 0. Message: already exists.

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
<ipython-input-19-f64886eff39c> in <module>
----> 1 save_model_raise(t, taxonomies)

<ipython-input-2-c7088ae28687> in save_model_raise(model, table)
      7         with connection.begin():
      8             # Raise if duplicate
----> 9             return connection.execute(table.insert().values(**model.dict()))
     10             

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   1261             )
   1262         else:
-> 1263             return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
   1264
   1265     def _execute_function(self, func, multiparams, params, execution_options):

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    321     ):
    322         if _force or self.supports_execution:
--> 323             return connection._execute_clauseelement(
    324                 self, multiparams, params, execution_options
    325             )

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params, execution_options)
   1450             linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1451         )
-> 1452         ret = self._execute_context(
   1453             dialect,
   1454             dialect.execution_ctx_cls._init_compiled,

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1812
   1813         except BaseException as e:
-> 1814             self._handle_dbapi_exception(
   1815                 e, statement, parameters, cursor, context
   1816             )

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1997                 )
   1998             else:
-> 1999                 util.raise_(exc_info[1], with_traceback=exc_info[2])
   2000
   2001         finally:

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1769                             break
   1770                 if not evt_handled:
-> 1771                     self.dialect.do_execute(
   1772                         cursor, statement, parameters, context
   1773                     )

~/src/my-repo/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    715
    716     def do_execute(self, cursor, statement, parameters, context=None):
--> 717         cursor.execute(statement, parameters)
    718
    719     def do_execute_no_params(self, cursor, statement, context=None):

~/src/my-repo/.venv/lib/python3.8/site-packages/aurora_data_api/__init__.py in execute(self, operation, parameters)
    228                 self._start_paginated_query(execute_statement_args, records_per_page=max(1, self.arraysize // 2))
    229             else:
--> 230                 raise self._get_database_error(e) from e
    231         self._iterator = iter(self)
    232

~/src/my-repo/.venv/lib/python3.8/site-packages/aurora_data_api/__init__.py in _get_database_error(self, origin_error)
    206         if getattr(origin_error, "response", {}).get("Error", {}).get("Message", "").startswith("Database error code"):
    207             code, msg = (s.split(": ", 1)[1] for s in origin_error.response["Error"]["Message"].split(". ", 1))
--> 208             return DatabaseError(MySQLErrorCodes(int(code)), msg)
    209         else:
    210             return DatabaseError(origin_error)

~/.pyenv/versions/3.8.5/lib/python3.8/enum.py in __call__(cls, value, names, module, qualname, type, start)
    307         """
    308         if names is None:  # simple value lookup
--> 309             return cls.__new__(cls, value)
    310         # otherwise, functional API: we're creating a new Enum type
    311         return cls._create_(value, names, module=module, qualname=qualname, type=type, start=start)

~/.pyenv/versions/3.8.5/lib/python3.8/enum.py in __new__(cls, value)
    598                         )
    599             exc.__context__ = ve_exc
--> 600             raise exc
    601
    602     def _generate_next_value_(name, start, count, last_values):

~/.pyenv/versions/3.8.5/lib/python3.8/enum.py in __new__(cls, value)
    582         try:
    583             exc = None
--> 584             result = cls._missing_(value)
    585         except Exception as e:
    586             exc = e

~/.pyenv/versions/3.8.5/lib/python3.8/enum.py in _missing_(cls, value)
    611     @classmethod
    612     def _missing_(cls, value):
--> 613         raise ValueError("%r is not a valid %s" % (value, cls.__name__))
    614
    615     def __repr__(self):

ValueError: 0 is not a valid MySQLErrorCodes

I believe the problematic line is here. The error code the API returns is 0, but that method is only equipped to handle MySQL error codes.

In case it's relevant, I am using the sqlalchemy-aurora-data-api library, but I don't think that would impact the error code the API returns.

Thanks for considering! Please let me know if I am mistaken and this is the expected behavior.

Parameter validation failed: Invalid type for parameter parameters[4].value.stringValue, value: 1942-02-19, type: <class 'datetime.date'>, valid types: <class 'str'>

Code is here: https://github.com/jetbridge/sls-flask/blob/master/TEMPLATE/db/fixtures.py and https://github.com/jetbridge/sls-flask/blob/master/TEMPLATE/model/user.py

Trying to insert a row into serverless postgres that includes a date column.

Traceback (most recent call last):
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/bin/flask", line 10, in <module>
    sys.exit(main())
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/flask/cli.py", line 966, in main
    cli.main(prog_name="python -m flask" if as_module else None)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/flask/cli.py", line 426, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/Users/cyber/dev/jb/slsflask/TEMPLATE/commands.py", line 19, in seed_db_cmd
    seed_db()
  File "/Users/cyber/dev/jb/slsflask/TEMPLATE/db/fixtures.py", line 30, in seed_db
    db.session.commit()
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/aws_xray_sdk/ext/sqlalchemy/util/decorators.py", line 61, in wrapper
    res = func(*args, **kw)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1027, in commit
    self.transaction.commit()
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 494, in commit
    self._prepare_impl()
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 473, in _prepare_impl
    self.session.flush()
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/aws_xray_sdk/ext/sqlalchemy/util/decorators.py", line 61, in wrapper
    res = func(*args, **kw)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2470, in flush
    self._flush(objects)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2608, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2568, in _flush
    flush_context.execute()
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1137, in _emit_insert_statements
    statement, params
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    distilled_params,
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1478, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/aurora_data_api/__init__.py", line 197, in execute
    res = self._client.execute_statement(**execute_statement_args)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/botocore/client.py", line 357, in _api_call
    return self._make_api_call(operation_name, kwargs)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/botocore/client.py", line 634, in _make_api_call
    api_params, operation_model, context=request_context)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/botocore/client.py", line 682, in _convert_to_request_dict
    api_params, operation_model)
  File "/Users/cyber/.virtualenvs/slsflask-IkGl8I07/lib/python3.7/site-packages/botocore/validate.py", line 297, in serialize_to_request
    raise ParamValidationError(report=report.generate_report())
botocore.exceptions.ParamValidationError: Parameter validation failed:
Invalid type for parameter parameters[4].value.stringValue, value: 1942-02-19, type: <class 'datetime.date'>, valid types: <class 'str'>

Race condition getting boto3.client("rds-data")

This bit of code is not thread safe.

        if rds_data_client is None:
            self._client = boto3.client("rds-data")

The problem here is that getting a boto3 client is not thread safe. Using a single boto3 client is thread safe.

I can hit this issue consistently with multi-threaded use of SQLAlchemy when SQLAlchemy tries to get two database connections concurrently.

Once I figured out what was happening, I realized that I can pass in rds_data_client to avoid this race.

The problem can (and probably should) be avoided within this driver by assuring that the rds-data client is only gotten once, globally, across multiple instances of AuroraDataAPIClient.

Here are the three (similar) relevant stack traces (with my code at the top of the stack snipped off):

    with engine().connect() as connection:
  File \"/var/task/sqlalchemy/engine/base.py\", line 3204, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File \"/var/task/sqlalchemy/engine/base.py\", line 96, in __init__
    else engine.raw_connection()
  File \"/var/task/sqlalchemy/engine/base.py\", line 3283, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File \"/var/task/sqlalchemy/engine/base.py\", line 3250, in _wrap_pool_connect
    return fn()
  File \"/var/task/sqlalchemy/pool/base.py\", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File \"/var/task/sqlalchemy/pool/base.py\", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File \"/var/task/sqlalchemy/pool/base.py\", line 476, in checkout
    rec = pool._do_get()
  File \"/var/task/sqlalchemy/pool/impl.py\", line 146, in _do_get
    self._dec_overflow()
  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 70, in __exit__
    compat.raise_(
  File \"/var/task/sqlalchemy/util/compat.py\", line 207, in raise_
    raise exception
  File \"/var/task/sqlalchemy/pool/impl.py\", line 143, in _do_get
    return self._create_connection()
  File \"/var/task/sqlalchemy/pool/base.py\", line 256, in _create_connection
    return _ConnectionRecord(self)
  File \"/var/task/sqlalchemy/pool/base.py\", line 371, in __init__
    self.__connect()
  File \"/var/task/sqlalchemy/pool/base.py\", line 666, in __connect
    pool.logger.debug(\"Error on connect(): %s\", e)
  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 70, in __exit__
    compat.raise_(
  File \"/var/task/sqlalchemy/util/compat.py\", line 207, in raise_
    raise exception
  File \"/var/task/sqlalchemy/pool/base.py\", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File \"/var/task/sqlalchemy/engine/create.py\", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File \"/var/task/sqlalchemy/engine/default.py\", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File \"/var/task/aurora_data_api/__init__.py\", line 404, in connect
    return AuroraDataAPIClient(dbname=database, aurora_cluster_arn=aurora_cluster_arn,
  File \"/var/task/aurora_data_api/__init__.py\", line 45, in __init__
    self._client = boto3.client(\"rds-data\")
  File \"/var/task/boto3/__init__.py\", line 93, in client
    return _get_default_session().client(*args, **kwargs)
  File \"/var/task/boto3/session.py\", line 270, in client
    return self._session.create_client(
  File \"/var/task/botocore/session.py\", line 855, in create_client
    credentials = self.get_credentials()
  File \"/var/task/botocore/session.py\", line 457, in get_credentials
    self._credentials = self._components.get_component(
  File \"/var/task/botocore/session.py\", line 995, in get_component
    del self._deferred[name]
KeyError: 'credential_provider'

    with engine().connect() as connection:
  File \"/var/task/sqlalchemy/engine/base.py\", line 3204, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File \"/var/task/sqlalchemy/engine/base.py\", line 96, in __init__
    else engine.raw_connection()
  File \"/var/task/sqlalchemy/engine/base.py\", line 3283, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File \"/var/task/sqlalchemy/engine/base.py\", line 3250, in _wrap_pool_connect
    return fn()
  File \"/var/task/sqlalchemy/pool/base.py\", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File \"/var/task/sqlalchemy/pool/base.py\", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File \"/var/task/sqlalchemy/pool/base.py\", line 476, in checkout
    rec = pool._do_get()
  File \"/var/task/sqlalchemy/pool/impl.py\", line 146, in _do_get
    self._dec_overflow()
  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 70, in __exit__
    compat.raise_(
  File \"/var/task/sqlalchemy/util/compat.py\", line 207, in raise_
    raise exception
  File \"/var/task/sqlalchemy/pool/impl.py\", line 143, in _do_get
    return self._create_connection()
  File \"/var/task/sqlalchemy/pool/base.py\", line 256, in _create_connection
    return _ConnectionRecord(self)
  File \"/var/task/sqlalchemy/pool/base.py\", line 371, in __init__
    self.__connect()
  File \"/var/task/sqlalchemy/pool/base.py\", line 666, in __connect
    pool.logger.debug(\"Error on connect(): %s\", e)
  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 70, in __exit__
    compat.raise_(
  File \"/var/task/sqlalchemy/util/compat.py\", line 207, in raise_
    raise exception
  File \"/var/task/sqlalchemy/pool/base.py\", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File \"/var/task/sqlalchemy/engine/create.py\", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File \"/var/task/sqlalchemy/engine/default.py\", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File \"/var/task/aurora_data_api/__init__.py\", line 404, in connect
    return AuroraDataAPIClient(dbname=database, aurora_cluster_arn=aurora_cluster_arn,
  File \"/var/task/aurora_data_api/__init__.py\", line 45, in __init__
    self._client = boto3.client(\"rds-data\")
  File \"/var/task/boto3/__init__.py\", line 93, in client
    return _get_default_session().client(*args, **kwargs)
  File \"/var/task/boto3/session.py\", line 270, in client
    return self._session.create_client(
  File \"/var/task/botocore/session.py\", line 859, in create_client
    defaults_mode = self._resolve_defaults_mode(config, config_store)
  File \"/var/task/botocore/session.py\", line 908, in _resolve_defaults_mode
    default_config_resolver = self._get_internal_component(
  File \"/var/task/botocore/session.py\", line 729, in _get_internal_component
    return self._internal_components.get_component(name)
  File \"/var/task/botocore/session.py\", line 995, in get_component
    del self._deferred[name]
KeyError: 'default_config_resolver'

    with engine().connect() as connection:
  File \"/var/task/sqlalchemy/engine/base.py\", line 3204, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File \"/var/task/sqlalchemy/engine/base.py\", line 96, in __init__
    else engine.raw_connection()
  File \"/var/task/sqlalchemy/engine/base.py\", line 3283, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File \"/var/task/sqlalchemy/engine/base.py\", line 3250, in _wrap_pool_connect
    return fn()
  File \"/var/task/sqlalchemy/pool/base.py\", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File \"/var/task/sqlalchemy/pool/base.py\", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File \"/var/task/sqlalchemy/pool/base.py\", line 476, in checkout
    rec = pool._do_get()
  File \"/var/task/sqlalchemy/pool/impl.py\", line 146, in _do_get
    self._dec_overflow()
  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 70, in __exit__
    compat.raise_(
  File \"/var/task/sqlalchemy/util/compat.py\", line 207, in raise_
    raise exception
  File \"/var/task/sqlalchemy/pool/impl.py\", line 143, in _do_get
    return self._create_connection()
  File \"/var/task/sqlalchemy/pool/base.py\", line 256, in _create_connection
    return _ConnectionRecord(self)
  File \"/var/task/sqlalchemy/pool/base.py\", line 371, in __init__
    self.__connect()
  File \"/var/task/sqlalchemy/pool/base.py\", line 666, in __connect
    pool.logger.debug(\"Error on connect(): %s\", e)
  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 70, in __exit__
    compat.raise_(
  File \"/var/task/sqlalchemy/util/compat.py\", line 207, in raise_
    raise exception
  File \"/var/task/sqlalchemy/pool/base.py\", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File \"/var/task/sqlalchemy/engine/create.py\", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File \"/var/task/sqlalchemy/engine/default.py\", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File \"/var/task/aurora_data_api/__init__.py\", line 404, in connect
    return AuroraDataAPIClient(dbname=database, aurora_cluster_arn=aurora_cluster_arn,
  File \"/var/task/aurora_data_api/__init__.py\", line 45, in __init__
    self._client = boto3.client(\"rds-data\")
  File \"/var/task/boto3/__init__.py\", line 93, in client
    return _get_default_session().client(*args, **kwargs)
  File \"/var/task/boto3/session.py\", line 270, in client
    return self._session.create_client(
  File \"/var/task/botocore/session.py\", line 856, in create_client
    endpoint_resolver = self._get_internal_component('endpoint_resolver')
  File \"/var/task/botocore/session.py\", line 729, in _get_internal_component
    return self._internal_components.get_component(name)
  File \"/var/task/botocore/session.py\", line 995, in get_component
    del self._deferred[name]
KeyError: 'endpoint_resolver'

A plan for asynchronous driver

Hi there,

SQLAlchemy 1.4 is releasing soon. Along with new features, it offers an async extension, which yields a perfect combination for async web frameworks.

I wonder if there a plan for making aurora-data-api asynchronous?

That's a difficult problem since there's no async support from the official boto3 and botocore libraries.

On the other hand, there are aiobotocore and aioboto3 from the community.

Proposal

Perhaps, downgrading boto3 to botocore in this lib would be a good first step.

The next one would be adding an async version of the driver, so that there are 2 drivers:

  1. sync driver based on botocore
  2. async driver based on aiobotocore(the dependency shall be available in extra requirements)

What do you think? @kislyuk

Example on Insert Query with SQL parameterized Statement

Hey there @kislyuk ,
I was using your library and I really liked it and you have done an amazing job by building it. Thank you for that,

The reason I created this issue is because, I was trying to run an insert query with Sql parameterized statement instead of an f string. But I was not able to find any example on this topic in this library docs.

So, any example in the readme about insert query using Sql parameterized statement to avoid sql injection will be helpful.

I am using Aurora Mysql and aws python Lambda.
Regards.

Previous ALTER statement doesn't persist within next aurora_data_api context manager instance

First off thank you for creating an awesome tool for interacting with aurora databases!

Problem

Setting the current user's search path with an aurora data API connection and cursor instance doesn't persist within a separate downstream connection and cursor instance.

The following snippet show this problem:

import aurora_data_api

with aurora_data_api.connect(
    aurora_cluster_arn=cluster_arn,
    secret_arn=secret_arn,
    database=db,
    continue_after_timeout=True,
) as conn:
    with conn.cursor() as cur:
        cur.execute("ALTER ROLE CURRENT_USER SET search_path to bar")

with aurora_data_api.connect(
    aurora_cluster_arn=cluster_arn,
    secret_arn=secret_arn,
    database=db
) as conn:
    with conn.cursor() as cur:
        cur.execute("show search_path")

        print(cur.fetchone())

The print statement outputs the default search path and not the expected bar search path.

The RDS cluster is using a serverless Aurora PostgreSQL engine.

Attempts

  • I thought the problem may be a race condition where the show search_path statement is run before the ALTER ROLE CURRENT_USER SET search_path to bar statement is applied to the database. To check this, I successfully ran the ALTER ROLE statement, waited ~30 minutes, and ran the subsequent show search path statement. The search path is still not the expected value.

IndexError: list index out of range

I've started getting a strange error back from aurora-data-api. I'd be grateful to know if anyone else is seeing it.

It seems that when a table not found exception is thrown, aurora-data-api is no longer catching it as expected.

So it is the case that Table 'xxx.alembic_version' does not exist. What would typically then happen in this situation is that aurora-data-api catches the error and it raises back up to sqlalchemy. However that no longer seems to be the case.

DEBUG [aurora_data_api] execute 'DESCRIBE `alembic_version`'
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/aurora_data_api/__init__.py", line 222, in execute
    res = self._client.execute_statement(**execute_statement_args)
  File "/usr/local/lib/python3.8/site-packages/botocore/client.py", line 357, in _api_call
    return self._make_api_call(operation_name, kwargs)
  File "/usr/local/lib/python3.8/site-packages/botocore/client.py", line 676, in _make_api_call
    raise error_class(parsed_response, operation_name)
botocore.errorfactory.BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: Database error code: 1146. Message: Table 'xxx.alembic_version' doesn't exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 581, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 575, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 552, in run_cmd
    fn(
  File "/usr/local/lib/python3.8/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 87, in <module>
    run_migrations_online()
  File "alembic/env.py", line 81, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 498, in run_migrations
    heads = self.get_current_heads()
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 435, in get_current_heads
    if not self._has_version_table():
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 447, in _has_version_table
    return sqla_compat._connectable_has_table(
  File "/usr/local/lib/python3.8/site-packages/alembic/util/sqla_compat.py", line 61, in _connectable_has_table
    return connectable.dialect.has_table(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py", line 2603, in has_table
    rs = connection.execution_options(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1003, in execute
    return self._execute_text(object_, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1172, in _execute_text
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1514, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/aurora_data_api/__init__.py", line 232, in execute
    raise self._get_database_error(e) from e
  File "/usr/local/lib/python3.8/site-packages/aurora_data_api/__init__.py", line 209, in _get_database_error
    err_info = self._render_response(err_res)["records"][-1]
IndexError: list index out of range

It looks like to determine what the error was, it runs:

err_res = self._client.execute_statement(**self._prepare_execute_args("SHOW ERRORS"))

However the records array returned from SHOW ERRORS doesn't contain any errors.

{'ResponseMetadata': {'HTTPHeaders': {'content-length': '41',
                                      'content-type': 'application/json',
                                      'date': 'Thu, 29 Oct 2020 18:55:20 GMT',
                                      'x-amzn-requestid': 'e2178ed8-43c4-48f7-b401-46f2cfc57615'},
                      'HTTPStatusCode': 200,
                      'RequestId': 'e2178ed8-43c4-48f7-b401-46f2cfc57615',
                      'RetryAttempts': 0},
 'numberOfRecordsUpdated': 0,
 'records': []}

Then thus we get an IndexError.

Support continueAfterTimeout

I would like to suggest to add support for the continueAfterTimeout parameter of the ExecuteStatement action. The same documentation suggests to use this parameter for DDL statements:

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures.

Maybe it can be an additional argument for the aurora_data_api.connect() function.

MySQL Support

Does this repo currently support mysql? I'm using this repo in conjunction with sqlalchemy-aurora-data-api and am using serverless Aurora MySQL

When I try to create tables with
Base.metadata.create_all(bind=engine)

This fails to create tables because it runs the statement [SQL: DESCRIBE "users"], where users is the name of one of my tables. I'm not sure if this is known, and if so, if there's a fix.

column [column name] is of type jsonb but expression is of type character varying

I'm using the sqlalchemy ORM on a Postgres db with some jsonb columns. This driver works perfectly for querying data from the database, but fails on data insertion:

botocore.errorfactory.BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: ERROR: column "properties" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

The underlying class and query:

class FeatureCollection(Base):
    __tablename__ = 'feature_collection'
    id = Column(Integer, primary_key=True)
    properties = Column(JSONB)
    ...

INSERT INTO feature_collection (properties) VALUES (:properties)

I'm able to make the equivalent insert using boto3's execute_statement as long as I include a ::jsonb type cast.

INSERT INTO feature_collection (properties) VALUES (:properties::jsonb)

Not sure if there's something here that can be leveraged:
https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/json.py

Table column names for SQL queries with JOINS

Hello,

In the ColumnDescription returned inside the cursor.description, it feels like there is some useful metadata missing.
Could we have the table from which the column is from ?

For instance, when doing :

cursor.execute("SELECT a.description AS foo, b.description AS bar FROM a LEFT JOIN b ON a.id=b.id")

cursor.description contains the column names, but not their alias. In this case, it would return ['description', 'description'], when one could expect ['foo', 'bar'] so it is impossible to differentiate ambiguous columns, to assign them to the right elements in the result.

I think this method in __init__.py is the one which needs enhancement :

def _set_description(self, column_metadata):
      # see https://www.postgresql.org/docs/9.5/datatype.html
      self.description = []
      for column in column_metadata:
          col_desc = ColumnDescription(name=column["name"], type_code=self._pg_type_map.get(column["typeName"].lower(), str))
          self.description.append(col_desc)

The column_metadata should contain a tableName field, according to the boto3 docs, see the response syntax in here. Could it be added to the ColumnDescription object ?

Would it be possible to implement this feature ? Is there another way I am missing ?

Thanks in advance !

Django support?

Wondering if this will work with Django. Any documentation there would be helpful.

New PyPi release?

There are quite a few new features that would be super useful if included by default when installing from pip. Could you guys push a new version to PyPi?

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.