Python MySQLdb "with" syntax and DictCursor

Question

I'm trying to use a DictCursor with a with block. I would think that by using:

with MySQLdb.connect(...) as c:

c would be a connection object, because that's what connect() returns. But alas, it is not! All of a sudden, c is a cursor! While this is usually convenient, I really like using a DictCursor - is this not designed that way at all? Enclosing the DictCursor as the "scoped object" causes an error (__exit__ wasn't defined)

It appears the `with` statement is not supported: see [this answer](http://stackoverflow.com/a/11751859/707650). — , Feb 14 '13 at 17:22

score 2 · Accepted Answer · answered Apr 30 '16 at 02:31

According to the definition of the Connection in MySQLdb here line 254 of connections.py, Connection.cursor() will return an instance of Connection.cursorclass:

def cursor(self, cursorclass=None):
    """
    Create a cursor on which queries may be performed. The
    optional cursorclass parameter is used to create the
    Cursor. By default, self.cursorclass=cursors.Cursor is
    used.
    """
    return (cursorclass or self.cursorclass)(self)

so, I guess if you initialize the connection with the argument "cursorclass", and set it to MySQLdb.cursors.DictCursor, like:

dbconn = MySQLdb.connect(cursorclass=MySQLdb.cursors.DictCursor)

when it comes

def __enter__(self):
    if self.get_autocommit():
        self.query("BEGIN")
    return self.cursor()

it will return a dict cursor.

thikonom · Answer 2 · 2013-02-14T17:37:05.600

1

c is a cursor because that's what is returned from the __enter__ method of the context manager.
If you browse Mysqldb's source code, you'll be able to see in line 245 of connections.py:

def __enter__(self): return self.cursor()

As for DictCursor, it does not support context management.

edited Feb 14 '13 at 17:37

answered Feb 14 '13 at 17:24

thikonom

4,219
3
26
30

I'm a little confused - doesn't `with MySQLdb.connect(...) as c:` work? Or have I missed something? At least it doesn't raise an exception! – Hut8 Feb 14 '13 at 17:25
yeah, it works but as i said it is designed to return a cursor. – thikonom Feb 14 '13 at 17:40

score 0 · Answer 3 · answered Sep 13 '16 at 09:36

My fix for raw sql with sqlalchemy

if self.engine.url.drivername == 'mysql+pymysql':
    from pymysql.cursors import SSDictCursor
    connection = self.engine.raw_connection()
    cursor = connection.cursor(cursor=SSDictCursor)
elif self.engine.url.drivername == 'mysql+mysqldb':
    import MySQLdb.cursors
    connection = self.engine.raw_connection()
    cursor = connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)
else:
    connection = self.engine.raw_connection()
    cursor = connection.cursor()

or use when create connection

connect_args=dict(cursorclass=MySQLdb.cursors.DictCursor)

Python MySQLdb "with" syntax and DictCursor

3 Answers3