Automatic close of ResultProxy (using "with" statement)

[Martin Stettner]

I'm using SqlAlchemy core to access my database. I'm just wondering if there's a specific reason why the ResultProxy class doesn't implement the __enter__ and __exit__ methods for automatically closing the results of a query using Python's with statement. This would make the following code possible

stmt = select(mytable)

with engine.execute(stmt) as result:

# do something with result...

pass

#result (and possibly connection) gets automatically closed

For now, I'm helping myself with a AutoClose class:

class AutoClose:

def __init__(self, close_object):

self.close_object = close_object

def __enter__(self):

return self.close_object

def __exit__(self, type, value, traceback):

self.close_object.close()

which allows me to do something like

stmt = select(mytable)

with AutoClose(engine.execute(stmt)) as result:

# do something with result...

pass

#result (and possibly connection) gets automatically closed

But I must admit I see no reason why the with-Protocol shouldn't be implemented in ResultProxy itself.

[Michael Bayer]

the only time a result proxy doesn't close automatically is if it a. has result rows present and b. you don't fetch them all (where if you need only the first row, then call first()).    for the context manager pattern it's typically used at the connection/transaction level like this:

with engine.connect() as conn:

    result = conn.execute()

    result = conn.execute()

    # etc.

now it's true that ResultProxy refers to a cursor which has a close() method of its own, but in my experience it's more important to frame the operation around a connection.   

When using connectionless execution as in engine.execute(), then the ResultProxy does refer to a Connection that it will release as well.   

I'm a little concerned that adding a context to resultproxy itself complicates the API with too many choices, which is mostly because I'd have preferred if engine never had an execute() method in the first place, it's better to work with a connection directly.   But it does, so perhaps this should be added.