- Notifications
You must be signed in to change notification settings - Fork2
Dialect: Add support forasyncpg andpsycopg3 drivers#11
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.
Already on GitHub?Sign in to your account
base:main
Are you sure you want to change the base?
Uh oh!
There was an error while loading.Please reload this page.
Conversation
20ab16e toe1929cbComparetests/engine_test.py Outdated
| deftest_engine_sync_vanilla(): | ||
| """ | ||
| crate:// -- Verify connectivity and data transport with vanilla HTTP-based driver. | ||
| """ | ||
| engine=sa.create_engine("crate://crate@localhost:4200/",echo=True) | ||
| assertisinstance(engine,sa.engine.Engine) | ||
| withengine.connect()asconnection: | ||
| result=connection.execute(QUERY) | ||
| assertresult.mappings().fetchone()== {'mountain':'Acherkogel','coordinates': [10.95667,47.18917]} | ||
| deftest_engine_sync_urllib3(): | ||
| """ | ||
| crate+urllib3:// -- Verify connectivity and data transport *explicitly* selecting the HTTP driver. | ||
| """ | ||
| engine=sa.create_engine("crate+urllib3://crate@localhost:4200/",isolation_level="AUTOCOMMIT",echo=True) | ||
| assertisinstance(engine,sa.engine.Engine) | ||
| withengine.connect()asconnection: | ||
| result=connection.execute(QUERY) | ||
| assertresult.mappings().fetchone()== {'mountain':'Acherkogel','coordinates': [10.95667,47.18917]} |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
This is the reference record representation.
{'mountain':'Acherkogel','coordinates': [10.95667,47.18917]}tests/engine_test.py Outdated
| deftest_engine_sync_psycopg(): | ||
| """ | ||
| crate+psycopg:// -- Verify connectivity and data transport using the psycopg driver (version 3). | ||
| """ | ||
| engine=sa.create_engine("crate+psycopg://crate@localhost:5432/",isolation_level="AUTOCOMMIT",echo=True) | ||
| assertisinstance(engine,sa.engine.Engine) | ||
| withengine.connect()asconnection: | ||
| result=connection.execute(QUERY) | ||
| assertresult.mappings().fetchone()== {'mountain':'Acherkogel','coordinates':'(10.95667,47.18917)'} | ||
| @pytest.mark.asyncio | ||
| asyncdeftest_engine_async_psycopg(): | ||
| """ | ||
| crate+psycopg:// -- Verify connectivity and data transport using the psycopg driver (version 3). | ||
| This time, in asynchronous mode. | ||
| """ | ||
| engine=create_async_engine("crate+psycopg://crate@localhost:5432/",isolation_level="AUTOCOMMIT",echo=True) | ||
| assertisinstance(engine,AsyncEngine) | ||
| asyncwithengine.begin()asconn: | ||
| result=awaitconn.execute(QUERY) | ||
| assertresult.mappings().fetchone()== {'mountain':'Acherkogel','coordinates':'(10.95667,47.18917)'} |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
When usingpsycopg, there are deviations, which clearly need to be addressed. By chance, you might have seen this already,@SStorm? Or did you only use asyncpg?
{'mountain':'Acherkogel','coordinates':'(10.95667,47.18917)'}tests/engine_test.py Outdated
| @pytest.mark.asyncio | ||
| asyncdeftest_engine_async_asyncpg(): | ||
| """ | ||
| crate+asyncpg:// -- Verify connectivity and data transport using the asyncpg driver. | ||
| This exclusively uses asynchronous mode. | ||
| """ | ||
| fromasyncpg.pgproto.typesimportPoint | ||
| engine=create_async_engine("crate+asyncpg://crate@localhost:5432/",isolation_level="AUTOCOMMIT",echo=True) | ||
| assertisinstance(engine,AsyncEngine) | ||
| asyncwithengine.begin()asconn: | ||
| result=awaitconn.execute(QUERY) | ||
| assertresult.mappings().fetchone()== {'mountain':'Acherkogel','coordinates':Point(10.95667,47.18917)} |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
When usingasyncpg, the response record is not wrong, but its shape is different, as it provides a dedicatedPoint type for representing coordinates.
{'mountain': 'Acherkogel', 'coordinates': Point(10.95667, 47.18917)}psycopg andasyncpg driversasyncpg andpsycopg driversasyncpg andpsycopg driversasyncpg andpsycopg3 driversasyncpg andpsycopg3 driversasyncpg andpsycopg3 drivers6ac0a22 tod2c7613Compare5c906ad to474f658Compare| When using the PostgreSQL protocol with drivers `psycopg` or `asyncpg`, | ||
| the paramstyle is not `qmark`, but `pyformat`. |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
I am not quite following. I assume we would want to use server side binding (i.e. qmark)?
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
This is probably the reason for needing the workaround at all: Because PostgreSQL drivers psycopg and asyncpg, or the SA dialect, usepyformat, but CrateDB usesqmark, we may need to adjust, iirc.
At least, the patch in its current shape needs it. Maybe there are alternatives to implement it, possibly even easier ones. We will be happy to learn about them.
08adb59 to5953f14CompareThis introduces the `crate+psycopg://`, `crate+asyncpg://`, and`crate+urllib3://` dialect identifiers. The asynchronous variant of`psycopg` is also supported.
Uh oh!
There was an error while loading.Please reload this page.
About
This patch adds support forasyncpg andpsycopg3 drivers, by introducing the
crate+asyncpg://andcrate+psycopg://dialect identifiers.It also adds the
crate+urllib3://dialect identifier, for explicitly addressing theurllib3-based transport, in case there might be other HTTP-based transport adapters in the future, using libraries likeaiohttp,httpx, orniquests.1Notes
The asynchronous variant of
psycopgis also supported and will be automatically selected when usingcreate_async_engine()instead ofcreate_engine(), so it doesn't have a dedicated dialect identifier.All of this will only work with SQLAlchemy 2.x.
Installation
pip install 'sqlalchemy-cratedb[all] @ git+https://github.com/crate/sqlalchemy-cratedb@amo/postgresql-async'References
Backlog
psycopg, see below.asyncpgandpsycopg3cratedb-examples#201pandas.read_sql()with both urllib3 vs. psycopg3 cratedb-examples#651Footnotes
Picked up from another discussion athttps://github.com/panodata/grafana-client/issues/134.↩