Use sqlAlchemy instead of psycopg2

[MathiasDrapier]

Hello,

I have a flask + socketio + postgresql server, I am using psycopg2 to send requests to my database.
At the beginning I was often confronted with the following error:

psycopg2.ProgrammingError: execute cannot be used while an asynchronous query is underway

I understood that this error occurs because of eventlet, 2 greenlet tries to use the connection to the database at the same time, which is not possible. I have corrected this problem using the eventlet semaphore. When calling a route from my API, it waits for a connection to the database to be available before executing a request.
It works well but causes slowness when my API is under a heavy workload.

By going through the discussions I understand that sqlAlchemy manages this concurrency problem natively.

But I don't want to use sqlAlchemy's ORM features, I would like still to execute sql queries like: "SELECT * FROM ...".
Is it possible to set up sqlAlchemy only to manage the connection to the database and manage the concurrency while keeping the requests in sql form? If so, do you have an example?

[miguelgrinberg]

I don't have a lot of experience using SQLAlchemy in the way you describe, but it is a fully supported way to use the library, so I don't see any problems on that part of your plans.

The problem that I see is that you have an assumption that SQLAlchemy can magically handle concurrency, which is not true. Regardless of the method you use to access the database, you will need to create multiple connections if you intend to have concurrent queries. From your description it seems you are creating a single connection and then you share it. You will have the same error with SQLAlchemy if you use this same design with it.

So I think you have more a design problem than a library limitation problem. You should be able to do what you want with psycopg2 as well.

[MathiasDrapier]

Thank you for your answers @miguelgrinberg . I have researched and people advise opening and maintaining one connexion by coroutine, is it possible to know which coroutine is used when calling a flask route?

[miguelgrinberg]

I'm sorry but I think you are making this more complicated that it needs to be. Your database supports a maximum number of connections. I asked you what this number is and you did not reply. This is important, you can't just create connections left and right, the database always enforces a limit. If this number is too low for your needs, then you have to reconfigure your Postgres to support a larger number of connections. Which brings me to the fact that you can't just say I want a connection per coroutine. You have to have a maximum. That's how the database works.

So once you decide on a maximum, and configure the database to support it, all you need to do is configure your pool of connections with the same maximum. And I recommend that you switch to the pool implementations in SQLAlchemy or psycopg2, as they are more efficient than your solution that creates all connections up front.

[MathiasDrapier]

Sorry @miguelgrinberg indeed I answered next to your question.
I have configured my database to accept up to 1000 connections.
I tried using a psycopg2 connection pooler like that :

class Db():
    postgreSQL_pool = None

    def __init__(self):
        self.postgreSQL_pool = psycopg2.pool.ThreadedConnectionPool(500,1000,
                                                                    user=dbUser,
                                                                    password=dbPassword,
                                                                    host=dbHost,
                                                                    port=dbPort,
                                                                    database=dbDatabase
                                                                    )

    def getConn(self):
        conn = self.postgreSQL_pool.getconn()
        cursor = conn.cursor()
        return conn, cursor

    def putConn(self, conn, cursor):
        cursor.close()
        self.postgreSQL_pool.putconn(conn)


    def executeRequestWithResult(self, request, cursor, retryCounter=0):
        try:
            cursor.execute(request)
            response = cursor.fetchall()
            retryCounter = 0
            return response
        except:
            cursor.execute("SET search_path TO blabla;")
            if retryCounter > 3:
                traceback.print_exc()
                raise error("Reconnect retryCounter > 3")
            else:
                retryCounter += 1
                return self.executeRequestWithResult(request, cursor, retryCounter)

But I get this error, that's why I developed my own connection pool. I thought that the one of psycopg2 was not available for eventlet.

Traceback (most recent call last):
  File "C:/Users/Mathias/Documents/Smarthome/smarthome-backend/src/main.py", line 13, in <module>
    socketio.run(app, host='::', port=port)
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\flask_socketio\__init__.py", line 631, in run
    run_server()
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\flask_socketio\__init__.py", line 625, in run_server
    eventlet.wsgi.server(eventlet_socket, app,
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\eventlet\wsgi.py", line 998, in server
    pool.waitall()
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\eventlet\greenpool.py", line 126, in waitall
    self.no_coros_running.wait()
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\eventlet\event.py", line 125, in wait
    result = hub.switch()
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\eventlet\hubs\hub.py", line 313, in switch
    return self.greenlet.switch()
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\eventlet\hubs\hub.py", line 365, in run
    self.wait(sleep_time)
  File "C:\Users\Mathias\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\eventlet\hubs\selects.py", line 42, in wait
    r, w, er = select.select(reader_fds, writer_fds, all_fds, seconds)
ValueError: too many file descriptors in select()

[miguelgrinberg]

This error is very common on Windows and has nothing to do with eventlet. The select() function in Windows has a maximum of 64 file descriptors and cannot be changed easily. If possible, you should try running your application on WSL which would give you the more sane Linux maximum, which I believe is 1024. If that is not an option then sadly you will not be able to use eventlet or any other library that manages a high number of file descriptors with the select() function.

[MathiasDrapier]

Ah ok thank you very much I will test this on linux!