I am currently building a REST API using Python and Flask. Most endpoints require some type of interaction with a SQL database (SQL Server, using pyodbc). What is the best way of going around this. Should I open a single connection (e.g. in a singleton class) and try and reuse it as much as possible? Should I use different connections? If so, at what granularity? One connection per endpoint? One connection per query? What role does connection pooling play in all of this and how do I make use of it? Do I have to worry about concurrent requests?

Proper way of using db connections? (Python + pyodbc)

I am currently building a REST API using Python and Flask. Most endpoints require some type of interaction with a SQL database (SQL Server, using pyodbc). What is the best way of going around this.

Should I open a single connection (e.g. in a singleton class) and try and reuse it as much as possible?
Should I use different connections? If so, at what granularity? One connection per endpoint? One connection per query?
What role does connection pooling play in all of this and how do I make use of it?
Do I have to worry about concurrent requests?

Proper way of using db connections? (Python + pyodbc)

0 Answers