33

I'm under the impression that database calls through SQLAlchemy will block and aren't suitable for use in anything other than synchronous code. Am I correct (I hope I'm not!) or is there a way to configure it to be non-blocking?

Matty
  • 33,203
  • 13
  • 65
  • 93
  • 3
    Short answer, no. It's the database libraries that are blocking, not SQLalchemy itself. Nothing stops you from doing DB stuff in a separate thread, though. – AdamKG Apr 18 '12 at 16:51
  • 2
    @AdamKG This probably should be an answer, not a comment! :-) – Matty Apr 18 '12 at 16:54

3 Answers3

29

You can use SQLA in a non-blocking style using gevent. Here's an example using psycopg2, using psycopg2's coroutine support:

https://bitbucket.org/zzzeek/green_sqla/

I've also heard folks use the same idea with pymysql. As pymysql is in pure Python and uses the sockets library, gevent patches the socket library to be asynchronous.

zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • Excellent! Thank you. Are there any caveats that I should be aware of (that don't appear in the docs) when using it in this way? – Matty Apr 18 '12 at 19:04
  • 1
    not sure. i found it seemed to work better using NullPool, which disables pooling. Otherwise something was making it hang. So maybe proceed with caution to start with.. – zzzeek Apr 18 '12 at 19:30
  • 3
    @zzzeek Your hangs might come from SQLAlchemy's default pool (QueuePool) using non-monkey-patched threading. Either apply gevent's monkey patch or make a green version of QueuePool per the example at https://groups.google.com/forum/#!msg/gevent/533wzrnL0Fs/ijL34u5prYIJ . That fixed the same behavior when I had it. – mikenerone Jul 03 '12 at 21:50
  • how come you didn't need to also patch sqlalchemy/util/queue.py there? also uses threading.RLock. – zzzeek Jul 05 '12 at 14:57
  • @zzzeek the link no longer works... do you still have the example available somewhere else? – carla Oct 22 '20 at 22:13
6

Have a look at Tornado as they've got some neat non-blocking libraries, particularly tornado.gen.

We use that along with Momoko, a non-blocking psycopg wrapper lib for Tornado. It's been great so far. Perhaps the only drawback is you lose all the model object stuff that SQLAlchemy gives you. Performance is unreal though.

kuhnza
  • 731
  • 1
  • 5
  • 15
  • I've been looking at Tornado and I may well go down that path. Maybe I should write some demo code to test this out, but if the psycopg driver supports async database calls (which apparently it does), I suppose it would be possible to do non-blocking DB calls with SQLAlchemy. – Matty Apr 18 '12 at 17:31
  • @Matty Good idea, I'd be keen to hear how you go with it. One of the main issues I imagine you'll hit with SQLAlchemy is not knowing exactly when a blocking call is going to be made. Of course you could always do a deep dive into the code to figure out when by my gut says that's going to be a lot of work. – kuhnza Apr 18 '12 at 17:43
  • @Kahunza After having a quick look at the docs for psycopg2 it appears there's an argument that can be be included in the connection string that's passed to SQLAlchemy's `create_engine()` function. Maybe one of the maintainers will chime in or I'll ask on their list. Cheers! – Matty Apr 18 '12 at 17:52
  • @Matty: Did you ever figure this out? – jawsnnn Feb 11 '21 at 19:51
2

Without the help of greenlet, the answer is no, in the context of asyncio.

However it is possible to use only a part of SQLAlchemy in asyncio. Please find example in the GINO project, where we used only SQLAlchemy core without engine and full execution context to make a simple ORM in asyncio.

Fantix King
  • 1,414
  • 1
  • 14
  • 13