Can I use pymysql.connect() with "with" statement?

Question

The following is listed as example in pymysql:

conn = pymysql.connect(...)
with conn.cursor() as cursor:
    cursor.execute(...)
    ...
conn.close()

Can I use the following instead, or will this leave a lingering connection? (it executes successfully)

import pymysql
with pymysql.connect(...) as cursor:
    cursor.execute('show tables')

(python 3, latest pymysql)

Answer 1

This does not look safe, if you look here, the __enter__ and __exit__ functions are what are called in a with clause. For the pymysql connection they look like this:

def __enter__(self):
    """Context manager that returns a Cursor"""
    return self.cursor()

def __exit__(self, exc, value, traceback):
    """On successful exit, commit. On exception, rollback"""
    if exc:
        self.rollback()
    else:
        self.commit()

So it doesn't look like the exit clause closes the connection, which means it would be lingering. I'm not sure why they did it this way. You could make your own wrappers that do this though.

You could recycle a connection by creating multiple cursors with it (the source for cursors is here) the cursor methods look like this:

def __enter__(self):
    return self

def __exit__(self, *exc_info):
    del exc_info
    self.close()

So they do close themselves. You could create a single connection and reuse it with multiple cursors in with clauses.

If you want to hide the logic of closing connections behind a with clause, e.g. a context manager, a simple way to do it would be like this:

from contextlib import contextmanager
import pymysql


@contextmanager
def get_connection(*args, **kwargs):
    connection = pymysql.connect(*args, **kwargs)
    try:
        yield connection
    finally:
        connection.close()

You could then use that context manager like this:

with get_connection(...) as con:
    with con.cursor() as cursor:
        cursor.execute(...)

Answer 2

As it was pointed out, the Cursor takes care of itself, but all the Connection's support for context manager was removed completely just a few days ago, so the only option now is to write yours:

https://github.com/PyMySQL/PyMySQL/pull/763

https://github.com/PyMySQL/PyMySQL/issues/446