Skip to content

Commit b63588e

Browse files
peeyushguptapeeyushgupta
committed
WL7292: Add cursors returning dict and namedtuple as rows
We implement new cursor classes returning rows as dictionaries or named tuples and are available as both buffered and non-buffered. The new classes in mysql.connector.cursor are: * MySQLCursorDict * MySQLCursorBufferedDict * MySQLCursorNamedTuple * MySQLCursorBufferedNamedTuple A raw version for the above classes is not available. The cursor()-method of MySQLConnection has two new arguments dictionary and named_tuple which can be set to True, for example, to instantiate a buffered cursor returning rows as dict-objects: cur = cnx.cursor(dictionary=True, buffered=True)
1 parent 17e4e95 commit b63588e

File tree

4 files changed

+354
-15
lines changed

4 files changed

+354
-15
lines changed

‎lib/mysql/connector/connection.py

Lines changed: 39 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,9 @@
4040
from .utils import int4store
4141
from .cursor import (
4242
CursorBase, MySQLCursor, MySQLCursorRaw,
43-
MySQLCursorBuffered, MySQLCursorBufferedRaw, MySQLCursorPrepared)
43+
MySQLCursorBuffered, MySQLCursorBufferedRaw, MySQLCursorPrepared,
44+
MySQLCursorDict, MySQLCursorBufferedDict, MySQLCursorNamedTuple,
45+
MySQLCursorBufferedNamedTuple)
4446
from .authentication import get_auth_plugin
4547
from .catch23 import PY2, isstr
4648

@@ -1318,17 +1320,25 @@ def _get_raise_on_warnings(self):
13181320
doc="Toggle whether to raise on warnings "\
13191321
"(implies retrieving warnings).")
13201322

1321-
def cursor(self, buffered=None, raw=None, prepared=None, cursor_class=None):
1323+
def cursor(self, buffered=None, raw=None, prepared=None, cursor_class=None,
1324+
dictionary=None, named_tuple=None):
13221325
"""Instantiates and returns a cursor
13231326
13241327
By default, MySQLCursor is returned. Depending on the options
1325-
while connecting, a buffered and/or raw cursor instantiated
1326-
instead.
1328+
while connecting, a buffered and/or raw cursor is instantiated
1329+
instead. Also depending upon the cursor options, rows can be
1330+
returned as dictionary or named tuple.
1331+
1332+
Dictionary and namedtuple based cursors are available with buffered
1333+
output but not raw.
13271334
13281335
It is possible to also give a custom cursor through the
13291336
cursor_class parameter, but it needs to be a subclass of
13301337
mysql.connector.cursor.CursorBase.
13311338
1339+
Raises ProgrammingError when cursor_class is not a subclass of
1340+
CursorBase. Raises ValueError when cursor is not available.
1341+
13321342
Returns a cursor-object
13331343
"""
13341344
if self._unread_result is True:
@@ -1340,8 +1350,6 @@ def cursor(self, buffered=None, raw=None, prepared=None, cursor_class=None):
13401350
raise errors.ProgrammingError(
13411351
"Cursor class needs be to subclass of cursor.CursorBase")
13421352
return (cursor_class)(self)
1343-
if prepared is True:
1344-
return MySQLCursorPrepared(self)
13451353

13461354
buffered = buffered or self._buffered
13471355
raw = raw or self._raw
@@ -1351,14 +1359,31 @@ def cursor(self, buffered=None, raw=None, prepared=None, cursor_class=None):
13511359
cursor_type |= 1
13521360
if raw is True:
13531361
cursor_type |= 2
1354-
1355-
types = (
1356-
MySQLCursor, # 0
1357-
MySQLCursorBuffered,
1358-
MySQLCursorRaw,
1359-
MySQLCursorBufferedRaw,
1360-
)
1361-
return (types[cursor_type])(self)
1362+
if dictionary is True:
1363+
cursor_type |= 4
1364+
if named_tuple is True:
1365+
cursor_type |= 8
1366+
if prepared is True:
1367+
cursor_type |= 16
1368+
1369+
types = {
1370+
0: MySQLCursor, # 0
1371+
1: MySQLCursorBuffered,
1372+
2: MySQLCursorRaw,
1373+
3: MySQLCursorBufferedRaw,
1374+
4: MySQLCursorDict,
1375+
5: MySQLCursorBufferedDict,
1376+
8: MySQLCursorNamedTuple,
1377+
9: MySQLCursorBufferedNamedTuple,
1378+
16: MySQLCursorPrepared
1379+
}
1380+
try:
1381+
return (types[cursor_type])(self)
1382+
except KeyError:
1383+
args = ('buffered', 'raw', 'dictionary', 'named_tuple', 'prepared')
1384+
raise ValueError('Cursor not available with given criteria: ' +
1385+
', '.join([args[i] for i in range(5)
1386+
if cursor_type & (1 << i) != 0]))
13621387

13631388
def start_transaction(self, consistent_snapshot=False,
13641389
isolation_level=None, readonly=None):

‎lib/mysql/connector/cursor.py

Lines changed: 147 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,8 +24,9 @@
2424
"""Cursor classes
2525
"""
2626

27-
import weakref
27+
from collections import namedtuple
2828
import re
29+
import weakref
2930

3031
from . import errors
3132

@@ -46,6 +47,8 @@
4647
RE_SQL_FIND_PARAM = re.compile(
4748
b'''%s(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)''')
4849

50+
_ERR_NO_RESULT_TO_FETCH = "No result set to fetch from."
51+
4952

5053
class _ParamSubstitutor(object):
5154
"""
@@ -1124,3 +1127,146 @@ def fetchall(self):
11241127
self._rowcount = len(rows)
11251128
self._handle_eof(eof)
11261129
return rows
1130+
1131+
1132+
class MySQLCursorDict(MySQLCursor):
1133+
"""
1134+
Cursor fetching rows as dictionaries.
1135+
1136+
The fetch methods of this class will return dictionaries instead of tuples.
1137+
Each row is a dictionary that looks like:
1138+
row = {
1139+
"col1": value1,
1140+
"col2": value2
1141+
}
1142+
"""
1143+
def _row_to_python(self, rowdata, desc=None):
1144+
"""Convert a MySQL text result row to Python types
1145+
1146+
Returns a dictionary.
1147+
"""
1148+
row = self._connection.converter.row_to_python(rowdata, desc)
1149+
if row:
1150+
return dict(zip(self.column_names, row))
1151+
return None
1152+
1153+
def fetchone(self):
1154+
"""Returns next row of a query result set
1155+
"""
1156+
row = self._fetch_row()
1157+
if row:
1158+
return self._row_to_python(row, self.description)
1159+
return None
1160+
1161+
def fetchall(self):
1162+
"""Returns all rows of a query result set
1163+
"""
1164+
if not self._have_unread_result():
1165+
raise errors.InterfaceError(_ERR_NO_RESULT_TO_FETCH)
1166+
(rows, eof) = self._connection.get_rows()
1167+
if self._nextrow[0]:
1168+
rows.insert(0, self._nextrow[0])
1169+
res = [self._row_to_python(row, self.description)
1170+
for row in rows]
1171+
self._handle_eof(eof)
1172+
rowcount = len(rows)
1173+
if rowcount >= 0 and self._rowcount == -1:
1174+
self._rowcount = 0
1175+
self._rowcount += rowcount
1176+
return res
1177+
1178+
1179+
class MySQLCursorNamedTuple(MySQLCursor):
1180+
"""
1181+
Cursor fetching rows as named tuple.
1182+
1183+
The fetch methods of this class will return namedtuples instead of tuples.
1184+
Each row is returned as a namedtuple and the values can be accessed as:
1185+
row.col1, row.col2
1186+
"""
1187+
def _row_to_python(self, rowdata, desc=None):
1188+
"""Convert a MySQL text result row to Python types
1189+
1190+
Returns a named tuple.
1191+
"""
1192+
row = self._connection.converter.row_to_python(rowdata, desc)
1193+
if row:
1194+
# pylint: disable=W0201
1195+
self.named_tuple = namedtuple('Row', self.column_names)
1196+
# pylint: enable=W0201
1197+
return self.named_tuple(*row)
1198+
1199+
def fetchone(self):
1200+
"""Returns next row of a query result set
1201+
"""
1202+
row = self._fetch_row()
1203+
if row:
1204+
return self._row_to_python(row, self.description)
1205+
return None
1206+
1207+
def fetchall(self):
1208+
"""Returns all rows of a query result set
1209+
"""
1210+
if not self._have_unread_result():
1211+
raise errors.InterfaceError(_ERR_NO_RESULT_TO_FETCH)
1212+
(rows, eof) = self._connection.get_rows()
1213+
if self._nextrow[0]:
1214+
rows.insert(0, self._nextrow[0])
1215+
res = [self._row_to_python(row, self.description)
1216+
for row in rows]
1217+
self._handle_eof(eof)
1218+
rowcount = len(rows)
1219+
if rowcount >= 0 and self._rowcount == -1:
1220+
self._rowcount = 0
1221+
self._rowcount += rowcount
1222+
return res
1223+
1224+
1225+
class MySQLCursorBufferedDict(MySQLCursorDict, MySQLCursorBuffered):
1226+
"""
1227+
Buffered Cursor fetching rows as dictionaries.
1228+
"""
1229+
def fetchone(self):
1230+
"""Returns next row of a query result set
1231+
"""
1232+
row = self._fetch_row()
1233+
if row:
1234+
return self._row_to_python(row, self.description)
1235+
return None
1236+
1237+
def fetchall(self):
1238+
"""Returns all rows of a query result set
1239+
"""
1240+
if self._rows is None:
1241+
raise errors.InterfaceError(_ERR_NO_RESULT_TO_FETCH)
1242+
res = []
1243+
for row in self._rows[self._next_row:]:
1244+
res.append(self._row_to_python(
1245+
row, self.description))
1246+
self._next_row = len(self._rows)
1247+
return res
1248+
1249+
1250+
class MySQLCursorBufferedNamedTuple(MySQLCursorNamedTuple, MySQLCursorBuffered):
1251+
"""
1252+
Buffered Cursor fetching rows as named tuple.
1253+
"""
1254+
def fetchone(self):
1255+
"""Returns next row of a query result set
1256+
"""
1257+
row = self._fetch_row()
1258+
if row:
1259+
return self._row_to_python(row, self.description)
1260+
return None
1261+
1262+
def fetchall(self):
1263+
"""Returns all rows of a query result set
1264+
"""
1265+
if self._rows is None:
1266+
raise errors.InterfaceError(_ERR_NO_RESULT_TO_FETCH)
1267+
res = []
1268+
for row in self._rows[self._next_row:]:
1269+
res.append(self._row_to_python(
1270+
row, self.description))
1271+
self._next_row = len(self._rows)
1272+
return res

‎tests/test_connection.py

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1524,10 +1524,24 @@ def __init__(self, cnx=None):
15241524
({'buffered': True}, cursor.MySQLCursorBuffered),
15251525
({'raw': True}, cursor.MySQLCursorRaw),
15261526
({'buffered': True, 'raw': True}, cursor.MySQLCursorBufferedRaw),
1527+
({'prepared': True}, cursor.MySQLCursorPrepared),
1528+
({'dictionary': True}, cursor.MySQLCursorDict),
1529+
({'named_tuple': True}, cursor.MySQLCursorNamedTuple),
1530+
({'dictionary': True, 'buffered': True},
1531+
cursor.MySQLCursorBufferedDict),
1532+
({'named_tuple': True, 'buffered': True},
1533+
cursor.MySQLCursorBufferedNamedTuple)
15271534
]
15281535
for kwargs, exp in cases:
15291536
self.assertTrue(isinstance(self.cnx.cursor(**kwargs), exp))
15301537

1538+
self.assertRaises(ValueError, self.cnx.cursor, prepared=True,
1539+
buffered=True)
1540+
self.assertRaises(ValueError, self.cnx.cursor, dictionary=True,
1541+
raw=True)
1542+
self.assertRaises(ValueError, self.cnx.cursor, named_tuple=True,
1543+
raw=True)
1544+
15311545
# Test when connection is closed
15321546
self.cnx.close()
15331547
self.assertRaises(errors.OperationalError, self.cnx.cursor)

0 commit comments

Comments
 (0)