koxudaxi / py-data-api Goto Github PK

View Code? Open in Web Editor NEW

40.0 4.0 9.0 2.77 MB

A user-friendly client for AWS Aurora Serverless's Data API

Home Page: https://koxudaxi.github.io/py-data-api

License: MIT License

Python 99.16% Shell 0.74% Batchfile 0.10%

python python3 dataapi data-api aurora aws serverless client sqlalchemy sql

py-data-api's Introduction

py-data-api - Data API Client for Python

py-data-api is a client for Data API of Aurora Serverless. Also, the package includes SQLAlchemy Dialects and DB API 2.0 Client.

Features

SQLAlchemy Dialects
DB API 2.0 compatible client PEP 249

Support Database Engines

MySQL
PostgreSQL

What's AWS Aurora Serverless's Data API?

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

This project is an experimental phase.

Warning: Some interface will be changed.

How to install

pydataapi requires Python 3.6.1 or later

$ pip install pydataapi

Example

from typing import List

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

from pydataapi import DataAPI, Result


class Pets(declarative_base()):
    __tablename__ = 'pets'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255, collation='utf8_unicode_ci'), default=None)


database: str = 'test'
resource_arn: str = 'arn:aws:rds:us-east-1:123456789012:cluster:serverless-test-1'
secret_arn: str = 'arn:aws:secretsmanager:us-east-1:123456789012:secret:serverless-test1'

def example_driver_for_sqlalchemy():
    from sqlalchemy.engine import create_engine
    engine = create_engine(
        'mysql+pydataapi://',
        connect_args={
            'resource_arn': 'arn:aws:rds:us-east-1:123456789012:cluster:dummy',
            'secret_arn': 'arn:aws:secretsmanager:us-east-1:123456789012:secret:dummy',
            'database': 'test'}
    )

    result = engine.execute("select * from pets")
    print(result.fetchall())

def example_simple_execute():
    data_api = DataAPI(resource_arn=resource_arn, secret_arn=secret_arn, database=database)
    result: Result = data_api.execute('show tables')
    print(result.scalar())
    # Pets

Contributing to pydataapi

We are waiting for your contributions to pydataapi.

How to contribute

https://koxudaxi.github.io/py-data-api/contributing

Related projects

local-data-api

DataAPI Server for local

https://github.com/koxudaxi/local-data-api

PyPi

https://pypi.org/project/pydataapi

Source Code

https://github.com/koxudaxi/py-data-api

Documentation

https://koxudaxi.github.io/py-data-api

License

py-data-api is released under the MIT License. http://www.opensource.org/licenses/mit-license

py-data-api's People

Contributors

Stargazers

Watchers

Forkers

rubyj leafeanking acute0203 hyuma zacharyspector vijay-singh amaizr craigmulligan creatorrr cazoo-uk

py-data-api's Issues

Use names instead of ARN

For providing DB cluster ARN and secret ARN, I would like to be able to provide the name instead.

aws_args = {'resource_name': 'lambda_function_name',
    'secret_name': 'secret_name',
    'database': 'database'}

Maybe we can allow either name or ARN? Boto3 has this ability!
https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/rds-data.html

Is there a way to use the feature of datapi continueAfterTimeout

https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/rds-data.html#RDSDataService.Client.execute_statement

Even though I am using SQLAlchemy session.execute(query), is there a way to enable continueAfterTimeOut?

the original post was created by @Rubyj

optionally disable forced transactions when creating a cursor

Is your feature request related to a problem? Please describe.
In some cases (e.g. paginating a select) its useful to not force a transaction on each exec statement call which according to doc can be done by just excluding transaction id. Currently whenever we execute a statement w/o a transaction one is created for us.

Describe the solution you'd like
Add e.g. a connection arg that controls this automatic transaction behavior, e.g.

sqlalchemy.create_engine(
    'postgresql+pydataapi://',
    connect_args={
        'resource_arn': 'my_cluster_arn',
        'secret_arn': 'my_secret_arn',
        'database': 'my_db',
        'auto_transaction': False,  # defaults to True
    },
)

Describe alternatives you've considered
Patching py-data-api at runtime:

def pydataapi_cursor(self: pydataapi.dbapi.Connection) -> pydataapi.dbapi.Cursor:
    # https://github.com/koxudaxi/py-data-api/blob/7f004883ff6c0ea3ca9285e69c71d07f45518693/pydataapi/dbapi.py#L135-L136
    cursor = pydataapi.dbapi.Cursor(self._data_api)
    self.cursors.append(cursor)
    return cursor

pydataapi.dbapi.Connection.cursor = pydataapi_cursor

sqlalchemy.exc.StatementError: (builtins.AttributeError) type object 'Connection' has no attribute 'Binary'

Hi, I'm trying to use sqlalchemy.utils.PasswordType and running into the error below:

sqlalchemy.exc.StatementError: (builtins.AttributeError) type object 'Connection' has no attribute 'Binary'

Versions:

SQLAlchemy==1.3.24
SQLAlchemy-Utils==0.37.6
pydataapi==0.4.22

It appears some other dialect implementations have bumped into this too: python-gino/gino#225

Thanks,
Craig

Support SQLAlchemy >= 1.4 by default

As the SQLAlchemy docs state SQLAlchemy < 1.4 is legacy (see here), it would be awesome to have 1.4 support.

Publish Lambda Layers

We want pydataapi as published lambda layers.
Also, CI should publish it.

Motivation

The package usually is used on aws lambda.
However, there is a limitation on package size.
Layers can resolve the problem.
We should supply the layer for everyone.

Related issues

#33

Bump pydantic

Is your feature request related to a problem? Please describe.
Need write access to this repo so I can push the branch and create the PR

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Request: Create a new release reflecting latest commit.

I kindly request to create a new release using latest commit.
Current release invokes tons of warnings from pydantic so...

Thank you in advance.

No able to read 2d array column from RDS using py-data-api

Describe the bug
Not able to get 2d array column values from RDS using py-data-api

To Reproduce
step 1: create the table in RDS aurora,

CREATE TABLE person(
pkey int,
col1 text [],
col2 text[][]
);

INSERT INTO person( pkey, col1,col2) VALUES( 1, '{'abc','def','xyz' }','{ {'a','b','c'}, {'d','e','f'} }' );
INSERT INTO person( pkey, col1,col2) VALUES( 2, '{'abc','def','xyz' }','{ {'a','b','c'}, {'d','e','f'} }' );
INSERT INTO person( pkey, col1,col2) VALUES( 3, '{'abc','def','xyz' }','{ {'a','b','c'}, {'d','e','f'} }' );

Step 2: try to query the table using py-data-api from aws lambda

query : select pkey, col1, col2 from person;

Step 3: below is the error i am getting

2023-01-23T19:07:06.994+05:30 executing the query

2023-01-23T19:07:07.216+05:30 ERROR : 'arrayValue'

2023-01-23T19:07:07.217+05:30 Traceback (most recent call last):

2023-01-23T19:07:07.217+05:30 File "/var/task/task_report_analysis.py", line 41, in handler

2023-01-23T19:07:07.217+05:30 results = get_data_from_rds(query_util.query, db_handler)

2023-01-23T19:07:07.217+05:30 File "/var/task/task_report_analysis.py", line 79, in get_data_from_rds

2023-01-23T19:07:07.217+05:30 result = db_handler.execute(query)

2023-01-23T19:07:07.217+05:30 File "/opt/python/lib/python3.8/site-packages/pydataapi/pydataapi.py", line 492, in execute

2023-01-23T19:07:07.217+05:30 return Result(response)

2023-01-23T19:07:07.217+05:30 File "/opt/python/lib/python3.8/site-packages/pydataapi/pydataapi.py", line 253, in init

2023-01-23T19:07:07.217+05:30 self._rows = [

2023-01-23T19:07:07.217+05:30 File "/opt/python/lib/python3.8/site-packages/pydataapi/pydataapi.py", line 254, in

2023-01-23T19:07:07.217+05:30 [_get_value_from_row(column) for column in row]

2023-01-23T19:07:07.217+05:30 File "/opt/python/lib/python3.8/site-packages/pydataapi/pydataapi.py", line 254, in

2023-01-23T19:07:07.217+05:30 [_get_value_from_row(column) for column in row]

2023-01-23T19:07:07.217+05:30 File "/opt/python/lib/python3.8/site-packages/pydataapi/pydataapi.py", line 141, in _get_value_from_row

2023-01-23T19:07:07.217+05:30 return [

2023-01-23T19:07:07.217+05:30 File "/opt/python/lib/python3.8/site-packages/pydataapi/pydataapi.py", line 142, in

2023-01-23T19:07:07.217+05:30 tuple(nested_value[ARRAY_VALUE].values())[0] # type: ignore

Reduce the size of dependency packages

This issue is created to research and the size of dependency packages.
@Rubyj said these packages waste a lof of size.
The problem may touch the problem to over 50MB lambda package.

Package Size

$ pip install pydataapi 
$ du -d 1 -m venv/lib/python3.7/site-packages/  |sort -n |tail -n7
2	venv/lib/python3.7/site-packages/pip
3	venv/lib/python3.7/site-packages/setuptools
4	venv/lib/python3.7/site-packages/docutils
10	venv/lib/python3.7/site-packages/sqlalchemy
19	venv/lib/python3.7/site-packages/pydantic
43	venv/lib/python3.7/site-packages/botocore
84	venv/lib/python3.7/site-packages/

Postgres migrations support

@koxudaxi

Do you think this plug-in could be used to support database migrations using alembic/sqlalchemy?

Support JSONB

We should support JSONB for PostgreSQL

Related issues

#68

Support a URI attribute from created engine

Is your feature request related to a problem? Please describe.
SQL Alchemy allows users to grab the URI of the sql engine (sql_engine.url) for example. Some packages require the URI instead of an engine object.

Describe the solution you'd like
I would like the engine created to have a URI attribute that returns the string representation of the connection

Describe alternatives you've considered
No way to get past this as far as I can tell.

Issue with regex for catching whether table exists

Describe the bug
The has_table function here uses a regular expression on the error message to catch if a table does not exist. However, for me, the error message also includes the connection number at the start. As an example:

(conn=23) Table 'users.user' doesn't exist

As a result, the regular expression does not match and the exception is not caught. We should update the regular expression to handle this, and I'll be putting up a PR to do so. This will enable us to support Metadata.create_all functionality in SQLAlchemy.

BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: ERROR: syntax error at or near "like" Position: 16

Creating a table through SQLAlchemy with the following code:
`
meta = MetaData()

Table(
"pets", meta,
Column("id",Integer, primary_key=True, autoincrement=True),
Column("name",String(255), default=None)
)

meta.create_all(engine)
`

I get the give error....

Also with:
Pets.__table__.create(engine)

Is not implemented yet?

TIMESTAMP isn't supported by the Postgres+dataapi dialect

Describe the bug
Timestamp columns aren't supported.
When you are trying to save such filed you'll have an exception:

column "****" is of type timestamp without time zone but expression is of type character varying

To Reproduce
you have a table like this:

create table users (
    id serial primary key,
    username VARCHAR(100) not null,
    seen_at TIMESTAMP null
);

you have an entity class like this:

class UserEntity(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String)
    seen_at = Column(DateTime)

and the execution plan:

user = UserEntity(
    username="user@name", 
    seen_at=datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
)

session.add(user)
session.commit()

I had spent the time to investigate the issue and what I've found:
the generated options to the client.execute_statement are

{
    .....
    'sql': 'INSERT INTO users (username, seen_at) VALUES (:username, :seen_at) RETURNING users.id', 
    'parameters': [{
        'name': 'username', 
        'value': {
            'stringValue': 'novachok'
        }
    }, {
        'name': 'seen_at', 
        'value': {
            'stringValue': '2020-02-14 12:46:46.901717'
        }
    }]
}

however, must contains the typeHint in it:

{
    .....
    'sql': 'INSERT INTO users (username, seen_at) VALUES (:username, :seen_at) RETURNING users.id', 
    'parameters': [{
        'name': 'username', 
        'value': {
            'stringValue': 'novachok'
        }
    }, {
        'name': 'seen_at', 
        'typeHint': 'TIMESTAMP'
        'value': {
            'stringValue': '2020-02-14 12:46:46.901717'
        }
    }]
}

Change execute() response

execute method returns these types Union[List[Result], List[Row], List[RowDict]]

However, IDE autocompletion is not user-friendly.

I thought the problem.
I have some plan.

separate method for each return value
change the order return types for improving autocompletion
create special response object for the method.

Now, This project is an experimental phase.
I want to change to a more useful interface.
All suggestions are most welcome.

Add support for datetime.datetime columns

Is your feature request related to a problem? Please describe.
I'm testing this driver, and so far it looks good, except for one thing.

I get this error when trying to insert a record:
Exception: unsupported type <class 'datetime.datetime'>: 2019-11-15 18:49:39.737104

Describe the solution you'd like
I don't know if I am doing something wrong, but it seems that this datatype is not yet supported.

Additional context
I am using Flask-SQLAlchemy==2.4.1

Thanks for your work on this package, I really apreciate it.

Chalice does not detect permissions when using pydatapi

Usually chalice will automatically detect permissions the lambda function needs based on what is used in the code. For example, when we get a secret from SecretsManager, chalice would give that permission to the IAM role for lambda. When I use pydataapi I do not seem to get these permissions, like access to secrets, added to my lambda function IAM role when I chalice deploy.

Is this a pydataapi bug or a chalice bug.

Drop a feature supporting sqlalchemey object

Now, py-data-api runs as a sqlalchemy dialect.
But, The library treats sqlalchemy objects like Select, Insert, etc...
The feature wastes a lot of processes on issue SQL.
And, it makes code complex.

Support custom data type conversion in query

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

First, thanks for making pydataapi, it helps using Data-Api a lot!.
With it, I can define Table like below:

machines = Table('machines', metaData, Column('ip', IPAddressType())
My IPAddressType can convert between VarBinary(16) and ip_address.IPAddress.
I can see the conversion from IPAddress to VarBinary(16) working in insertion.
But the results are still in bytes when selecting from machines table.

Describe the solution you'd like
A clear and concise description of what you want to happen.

I hope execute(sql) when it is a sql object, it can help convert the custom data type back before returning the results.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Error when creating second alembic migration

After successfuly creating an initial database with alembic and sqlalchemy I am having trouble adding a column in a second migration.

When I try to create the second migration (the database already exists from the first migration) I get the following error:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 'variant_annotation_id_seq' as owned by integer column 'variant_annotation(id)', assuming SERIAL and omitting
Traceback (most recent call last):
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/config.py", line 573, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/config.py", line 567, in main
    self.run_cmd(cfg, options)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/config.py", line 547, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/command.py", line 214, in revision
    script_directory.run_env()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 173, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 85, in <module>
    run_migrations_online()
  File "alembic/env.py", line 79, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 507, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/command.py", line 190, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/api.py", line 433, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/api.py", line 473, in _run_environment
    autogen_context, migration_script
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 51, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 83, in _autogen_for_tables
    autogen_context,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/alembic/autogenerate/compare.py", line 195, in _compare_tables
    inspector.reflecttable(t, None)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 667, in reflecttable
    reflection_options,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 846, in _reflect_fk
    **reflection_options
  File "<string>", line 2, in __new__
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 496, in __new__
    metadata._remove_table(name, schema)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 491, in __new__
    table._init(name, metadata, *args, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 585, in _init
    resolve_fks=resolve_fks,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 609, in _autoload
    _extend_on=_extend_on,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1604, in run_callable
    return callable_(self, *args, **kwargs)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 431, in reflecttable
    table, include_columns, exclude_columns, resolve_fks, **opts
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 687, in reflecttable
    reflection_options,
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 942, in _reflect_unique_constraints
    constraints = self.get_unique_constraints(table_name, schema)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 520, in get_unique_constraints
    self.bind, table_name, schema, info_cache=self.info_cache, **kw
  File "<string>", line 2, in get_unique_constraints
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 56, in cache
    ret = fn(self, con, *args, **kw)
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3401, in get_unique_constraints
    for name, uc in uniques.items()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3401, in <listcomp>
    for name, uc in uniques.items()
  File "/c/Users/rjacobs/git/vcf-parser-lambda/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3400, in <listcomp>
    {"name": name, "column_names": [uc["cols"][i] for i in uc["key"]]}
KeyError: 'longValues'

Any idea @koxudaxi ? It seems like the unique id constraint is causing issues and preventing me from making second migration.

koxudaxi / py-data-api Goto Github PK

py-data-api's Introduction

py-data-api - Data API Client for Python

Features

Support Database Engines

What's AWS Aurora Serverless's Data API?

This project is an experimental phase.

How to install

Example

Contributing to pydataapi

How to contribute

Related projects

local-data-api

PyPi

Source Code

Documentation

License

py-data-api's People

Contributors

Stargazers

Watchers

Forkers

py-data-api's Issues

Motivation

Related issues

Package Size

Related issues

Recommend Projects

Recommend Topics

Recommend Org