4

Is it possible to perform asynchronous queries against Microsoft SQL Server from Python (3.4), i.e. in the context of an asyncio event loop?

The following is a skeleton asyncio program, where the (async) SQL query should be fitted into the do_it function:

import asyncio
import contextlib


@asyncio.coroutine
def do_it():
    # TODO: Make an asynchronous MS SQL query, but how??
    fut = asyncio.Future()
    fut.set_result(None)
    return fut


with contextlib.closing(asyncio.SelectorEventLoop()) as loop:
    asyncio.set_event_loop(loop)
    loop.run_until_complete(do_it())

print('Finished')
Improve this question

4 Answers 4

Reset to default
5

For now there is only PostgreSQL native asyncio support via aiopg library.

But you can run synchronous calls to MSSQL by loop.run_in_executor().

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, I'm aware of the threaded executor option. However, I see that for instance mxODBC supports async, so I was hoping I could fit such a component with async support into the asyncio framework.
I wrote aiopg as asyncio wrapper for psycopg2. If you need for asyncio-compatible wrapper for mxODBC you perhaps should to create it yourself (or wait for somebody to get this work implemented).
No, I mean, I don't need an asyncio wrapper. I really just need to know how I may asynchronously query SQL Server, if it is possible at all. The only option I've found so far in this regard is mxODBC Connect, which acts as a proxy server to SQL Server. Unfortunately, Connect is quite costly.
4

The one option I have found so far is the commercial product mxODBC Connect, which unfortunately costs several hundred dollars. By providing a proxy server to SQL Server, it is able to support asynchronous calls. Supposedly the async support is implemented via gevent, so I don't know how well it'll fit into asyncio. From Python's ODBC wiki, mxODBC is the only product listed with async support.

Improve this answer

Comments

1

I would say use the aioodbc ported from pyodbc works great in my current project! :)

Improve this answer

4 Comments

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review
Did you face a problem with a connection staying in the sleeping state for a long time? And if yes, how did you resolve it?
I had connection issues when it come to long queries and mainting connection is things I ran into. I would suggest to incorporate the batch processing and rollback if it fails. So wrapping in a try/catch block. I walso suggessed utilizing the isConnected() then if its disconnected just re connect.
@AiratK be sure to close each individual connection. you should close the cursor then close the connection following each query you perform on the individual threads. cursor.close() conn.close()
0

Update: this answer lost its usefulness because:

  • Of the raise of asynchronous frameworks, which became the de facto standard of Python web application frameworks, and red functions are contagious: it makes no sense to try to revert back to blue.
  • The referred article is updated now by Mike himself, pointing out that now SQLAlchemy supports async DB drivers too – which doesn't invalidate the benchmark results, though, but nobody ran a convincing benchmark on it. asyncpg running at concurrency levels from 20 to 500 threads may be such a benchmark.

Original answer from a younger me:

I hate a "don't do that" answers, but the article by Mike Bayer, the creator of SQLAlchemy makes me think exactly exactly that:

"when it comes to stereotypical database logic, there are no advantages to using it versus a traditional threaded approach, and you can likely expect a small to moderate decrease in performance, not an increase"

Improve this answer

10 Comments

Sometimes the hit to performance is acceptable when the code can be written in a much clearer and simpler way using CPython's asyncio.
I agree that asynchronous code is about the only way to write a clear parallel code in Python, because of GIL.
The (Mike Bayer's) article is based on misunderstanding what concurrency really is. It's true if you fire the async function only once it will be slower. But it you fire lots of async functions esp. in IO context you can discover that you can perfom much more operations in the same time. This is the area where conncurency really shines. Threading is not an option when you have hundreds of simultanous requests.
I doubt Mike Bayer doesn’t understand it. In fact, the benchmark he built does just that. If I get the data correctly (I admit, I didn’t read deeply into the code), threaded SQL client beat async on 350 concurrent threads on an old laptop, and the more powerful CPU got, the better threads performed. Do you have other data to support your quite strong claim?
There is a myth that async code will not help with databases, if you look on techempower benchmarks you can see that rust actix + async postgres is 3x faster than exactly the same codebase with a synchronous ORM. That's the difference between supporting 1 million users per day or 3 million. I hate don't do that answers. Mike bayer, the creator of sqlalchemy, has after 10 years not figured out a way to make his library async.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.