search

crate / crate-python

Python DB API client library for CrateDB, using HTTP.
https://cratedb.com/docs/python/
Apache License 2.0
78 stars 31 forks source link

[DRAFT] SA20: Add compatibility adapters for psycopg3 and asyncpg dialects #532

Closed amotl closed 11 months ago

amotl commented 1 year ago

About

The idea is to unlock the CrateDB dialect for SQLAlchemy with the psycopg3 and asyncpg drivers by introducing corresponding crate+psycopg://, crate+asyncpg://, and crate+urllib3:// dialect identifiers.

The asynchronous variant of psycopg is also supported and will be automatically selected when using create_async_engine() instead of create_engine().

Installation

git clone https://github.com/crate/crate-python --branch=amo/postgresql-async
cd crate-python
python3 -m venv .venv
source .venv/bin/activate
pip install --editable='.[postgresql,sqlalchemy]'

Examples

Those example programs exercise basic conversations using the specified drivers.

python examples/sync_table.py urllib3 psycopg
python examples/async_table.py psycopg asyncpg
python examples/async_streaming.py psycopg asyncpg

References

amotl commented 1 year ago

Backlog

1. Missing test cases

C'est la vie. Please use the provided example programs until we have them.

2. Still using PGDDLCompiler

There is more work to do, to make the hybrid dialect use the CrateDDLCompiler instead of the PGDDLCompiler. Otherwise, when defining a column with autoincrement=True, it will obviously croak, because it will render the SERIAL type, which CrateDB does not understand.

sa.Column("x", sa.Integer, primary_key=True, autoincrement=True)
CREATE TABLE testdrive (
    x SERIAL NOT NULL,
    y INT,
    PRIMARY KEY (x)
)
sqlalchemy.exc.InternalError: (psycopg.errors.InternalError_) Cannot find data type: serial

3. Error Cannot find portal: c_10479c0a0_1 with psycopg3

This flaw has been observed with the psycopg driver in asynchronous mode when using result streaming based on server-side cursors. It can be exercised using the async_streaming.py example program.

python examples/async_streaming.py psycopg

The same program works fine when using the asyncpg driver.

python examples/async_streaming.py asyncpg

Introduction

https://github.com/crate/crate-python/blob/387b19f9664ac8e78e898a37aa81dd18a38ee542/examples/async_streaming.py#L2-L15

Bug

https://github.com/crate/crate-python/blob/387b19f9664ac8e78e898a37aa81dd18a38ee542/examples/async_streaming.py#L42-L47

References

amotl commented 11 months ago

This patch has been replaced by two others.

/cc @SStorm, @surister