Python: Using a generator when accessing a SQL source

Question

I'm trying to refactor some code and have come up with this

def get_inpatients():
    """
    Getting all the inpatients currently sitting in A&E
    """
    cnxn = pyodbc.connect(f'DRIVER={DB_DRIVER};SERVER={DB_SERVER};DATABASE={DB_NAME};UID={DB_USER};PWD={DB_PASS}')
    cursor = cnxn.cursor()

    cursor.execute('EXEC spGetInpatients')
    row = cursor.fetchone()
    while row is not None:
        yield row[0]
        row = cursor.fetchone()

In the main file I then do this

for nhs_number in get_inpatients():
    .... # This then goes and grabs details from several APIs meaning 
         # it will be a few seconds for each loop

My question is whether a genertaor is a good choice here. I previously had it so that the function would return a list. Thinking about it now, would this then mean the connection is open for as long as the for loop is running in the main file in which case I am better returning a list?

score 2 · Answer 1 · answered Jan 24 '22 at 09:50

Yes, the connection will remain open. Whether that is a good idea depends on the circumstances. Normally it is a good idea to use the generator because it allows the processing in your application to run concurrently with the fetching of more rows by the database. It also reduces memory consumption and improves CPU cache efficiency in your application. When done right, it also reduces latency which is very user-visible.

But of course you could run into the maximum connection limit sooner. I'd argue that increasing the connection limit is better than artifically making your application perform worse.

Also note that you can have multiple cursors per connection. See for example Max SQL connections with Python and pyodbc on a local database showing as 1

Thanks for that - especially the note about the cursors. The script takes about 30m to complete and I'm hesitant to keep the connection open that long. — pee2pee, Jan 24 '22 at 10:00

raratiru · Answer 2 · 2022-02-15T16:30:03.407

I am adding this answer for 2 reasons.

To point that the cursor is an iterator
To make more clear that the "maximum connection limit" (as per the answer of @Homer512) is a client side setting and not a server side one and defaults to 0 both for the database connection and the queries.

So:

According to pyodbc wiki you can avoid the boilerplate code:

The fetchall() function returns all remaining rows in a list. Bear in mind those rows will all be stored in memory so if there a lot of rows, you may run out of memory. If you are going to process the rows one at a time, you can use the cursor itself as an iterator:
```
for row in cursor.execute("select user_id, user_name from users"):
  print(row.user_id, row.user_name)
```
The connection limit lies in the client side and not the server side.
The comment on that answer reads:

You should clarify what server scoped means. SQL Server has a remote query timeout value that refers to its queries issued on over linked servers, not to queries issued by clients to it. I believe the query timeout is a client property, not a server property. The server runs the query indefinitely. There is such a thing as a query governor for addressing this issue which is disabled by default.

Indeed, the docs verify:

This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. A query will wait until it completes.

Regarding the question if it is safe to keep open a database connection for a long time, I found this old but relevant question which has an extended answer in favor of "yes, if you know what you are doing".

Python: Using a generator when accessing a SQL source

2 Answers2