native asynchronous driver support

[dpnova]

I'm starting this as a place to have the conversation about a txpostgres implementation of this. I'm willing to work on it as I'm starting a new project that would benefit from it.

Having checked the source so far, I think the majority of the work would be abstracting the deferToThread stuff into a less thread-y naming convention and then hooking up the txpostgres connection objects.

the txpostgres pool is pretty limited compared to the sqla ones, so there may be a little bit of integration worth doing there too.

Anyone have any other thoughts?

[alex]

So the trick is figuring out how sqlalchemy itself uses the underlying connection. My inclination is that we'd have to replace more layers, and probably copy-paste-and-edit more code.

I'm definitely in favor of this, but I think it's non-trivial.

[iffy]

Would the dialect level be the right place? https://bitbucket.org/zzzeek/sqlalchemy/src/2b1c3d8821b3b375a6148d583f05bd65810e3faf/README.dialects.rst?at=master

[alex]

Maybe. It's all about the relationship between all the pieces, and being able to propogate deferreds correctly.

[oberstet]

+1 for a txpostgres based alchimia.

[dpnova]

I've looked into this a little bit more. Working backwards from the raw cursor level:

1. The engine will need to return a deferred from the execute_* methods: lib/sqlalchemy/engine/default.py
2. To handle this, the engine will also need to expect deferreds from these methods in lib/sqlalchemy/engine/base.py _execute_context - most likely an ExecuteContext subclass that has a deferred attribute, or maybe monkey patching the existing EC class.
3. In turn this must bubble up through the _execute_compiled and friends methods to the actual execute method.

Note, this is only to execute a query via the engine.execute method. There's a whole family of things that potentially wouldnt even get touched in a first implementation of this, off the top of my head, connection and introspection would fall in the basket since they seem to skip the regular execution flow.

Finally there would need to be some changes or subclassing done in txpostgres to give better access to the raw dbapi connection and cursor instances.

This raises the question, is building this on txpostgres even the right approach? The core of txpostgres is quite small, perhaps it would be easier to create something inspired by it with an api friendlier to sqlalchemy.

Anyway... monday morning musings :)

[alex]

txpostgres might not be the perfect approach, but I think the analysis of sqlalchemy that you did will apply, whatever connection library you use, because at the core you need to take a bunch of code that executes synchronously, and make it be written in terms of deffereds.

On Sun, Aug 24, 2014 at 6:08 PM, David Novakovic [email protected] wrote:

I've looked into this a little bit more. Working backwards from the raw cursor level:

1. The engine will need to return a deferred from the execute_* methods: lib/sqlalchemy/engine/default.py
2. To handle this, the engine will also need to expect deferreds from these methods in lib/sqlalchemy/engine/base.py _execute_context - most likely an ExecuteContext subclass that has a deferred attribute, or maybe monkey patching the existing EC class.
3. In turn this must bubble up through the _execute_compiled and friends methods to the actual execute method.

Note, this is only to execute a query via the engine.execute method. There's a whole family of things that potentially wouldnt even get touched in a first implementation of this, off the top of my head, connection and introspection would fall in the basket since they seem to skip the regular execution flow.

Finally there would need to be some changes or subclassing done in txpostgres to give better access to the raw dbapi connection and cursor instances.

This raises the question, is building this on txpostgres even the right approach? The core of txpostgres is quite small, perhaps it would be easier to create something inspired by it with an api friendlier to sqlalchemy.

Anyway... monday morning musings :)

— Reply to this email directly or view it on GitHub https://github.com/alex/alchimia/issues/9#issuecomment-53218711.

"I disapprove of what you say, but I will defend to the death your right to say it." -- Evelyn Beatrice Hall (summarizing Voltaire) "The people's good is the highest law." -- Cicero GPG Key fingerprint: 125F 5C67 DFE9 4084

[taion]

:+1: This would be really cool to have.

[glyph]

Changing the name on this issue because although txpostgres may well satisfy the goal, so might https://magicstack.github.io/asyncpg/ or https://pypi.python.org/pypi/aiomysql/ or http://aiopg.readthedocs.io/en/stable/ - alchimia should probably be able to work with more than one of these (especially since many async-plus-sqlalchemy things are so database-specific right now)

[glyph]

Also, I should note: now that the main entrypoint into the library is wrap_engine we can have a clean break for native-async stuff and just have an async_engine function that doesn't need to concern itself with doing anything with an underlying sqlalchemy Engine object, since satisfying the same interface as TwistedEngine may not in fact require such an object.

[graingert]

@aiopg does exactly that: https://aiopg.readthedocs.io/en/stable/sa.html

[graingert]

it looks like the engine code is mostly copied, with yield from added where needed

[dpnova]

I haven't used aiopg but FWIW asyncpg is great.

[glyph]

@dpnova @graingert Anyone feel like wrapping one of these up into a PR? :)