Skip to content

Commit 4851913

Browse files
oscpacheoscpache
committed
BUG#23339387: Add MySQLCursorPreparedDict option
This patch integrates a new MySQLCursorPreparedDict class option that is a blend of features from MySQLCursorDict and MySQLCursorPrepared. The MySQLCursorPreparedDict class is similar to MySQLCursorPrepared, the difference is that the former returns a fetched row as a dictionary where column names are used as keys while the latter returns a row as a traditional record (tuple). To call out for MySQLCursorPreparedDict you should set flags `prepared` and `dictionary`. Thanks, Luke Weber for the contribution. Change-Id: Iee40b5241c235d7ab3998325be4cf7823dad480f
1 parent ab27bcb commit 4851913

File tree

7 files changed

+255
-35
lines changed

7 files changed

+255
-35
lines changed

‎CHANGES.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@ v8.0.32
2929
- BUG#27426532: Reduce callproc roundtrip time
3030
- BUG#24364556: Improve warning behavior
3131
- BUG#23342572: Allow dictionaries as parameters in prepared statements
32+
- BUG#23339387: Add MySQLCursorPreparedDict option
3233

3334
v8.0.31
3435
=======

‎lib/mysql/connector/connection.py

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@
7878
MySQLCursorDict,
7979
MySQLCursorNamedTuple,
8080
MySQLCursorPrepared,
81+
MySQLCursorPreparedDict,
8182
MySQLCursorRaw,
8283
)
8384
from .errors import (
@@ -1435,6 +1436,7 @@ def cursor(
14351436
8: MySQLCursorNamedTuple,
14361437
9: MySQLCursorBufferedNamedTuple,
14371438
16: MySQLCursorPrepared,
1439+
20: MySQLCursorPreparedDict,
14381440
}
14391441
try:
14401442
return (types[cursor_type])(self)

‎lib/mysql/connector/connection_cext.py

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,6 +74,7 @@
7474
CMySQLCursorDict,
7575
CMySQLCursorNamedTuple,
7676
CMySQLCursorPrepared,
77+
CMySQLCursorPreparedDict,
7778
CMySQLCursorRaw,
7879
)
7980
except ImportError as exc:
@@ -704,6 +705,7 @@ def cursor(
704705
8: CMySQLCursorNamedTuple,
705706
9: CMySQLCursorBufferedNamedTuple,
706707
16: CMySQLCursorPrepared,
708+
20: CMySQLCursorPreparedDict,
707709
}
708710
try:
709711
return (types[cursor_type])(self)

‎lib/mysql/connector/cursor.py

Lines changed: 48 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1489,47 +1489,28 @@ def _row_to_python(
14891489
14901490
Returns a dictionary.
14911491
"""
1492-
row = rowdata
1493-
1494-
if row:
1495-
return dict(zip(self.column_names, row))
1496-
1497-
return None
1492+
return dict(zip(self.column_names, rowdata)) if rowdata else None
14981493

14991494
def fetchone(self) -> Optional[Dict[str, ToPythonOutputTypes]]:
15001495
"""Return next row of a query result set.
15011496
15021497
Returns:
1503-
tuple or None: A row from query result set.
1498+
dict or None: A dict from query result set.
15041499
"""
1505-
self._check_executed()
1506-
row = self._fetch_row()
1507-
if row:
1508-
return self._row_to_python(row, self.description)
1509-
return None
1500+
return self._row_to_python(super().fetchone(), self.description)
15101501

15111502
def fetchall(self) -> List[Optional[Dict[str, ToPythonOutputTypes]]]:
15121503
"""Return all rows of a query result set.
15131504
15141505
Returns:
1515-
list: A list of tuples with all rows of a query result set.
1506+
list: A list of dictionaries with all rows of a query
1507+
result set where column names are used as keys.
15161508
"""
1517-
self._check_executed()
1518-
if not self._have_unread_result():
1519-
return []
1520-
1521-
(rows, eof) = self._connection.get_rows()
1522-
if self._nextrow[0]:
1523-
rows.insert(0, self._nextrow[0])
1524-
res = []
1525-
for row in rows:
1526-
res.append(self._row_to_python(row, self.description))
1527-
self._handle_eof(eof)
1528-
rowcount = len(rows)
1529-
if rowcount >= 0 and self._rowcount == -1:
1530-
self._rowcount = 0
1531-
self._rowcount += rowcount
1532-
return res
1509+
return [
1510+
self._row_to_python(row, self.description)
1511+
for row in super().fetchall()
1512+
if row
1513+
]
15331514

15341515

15351516
class MySQLCursorNamedTuple(MySQLCursor):
@@ -1661,3 +1642,41 @@ def fetchall(self) -> List[Optional[RowType]]:
16611642
res.append(self._row_to_python(row, self.description))
16621643
self._next_row = len(self._rows)
16631644
return res
1645+
1646+
1647+
class MySQLCursorPreparedDict(MySQLCursorDict, MySQLCursorPrepared): # type: ignore[misc]
1648+
"""
1649+
This class is a blend of features from MySQLCursorDict and MySQLCursorPrepared
1650+
1651+
Multiple inheritance in python is allowed but care must be taken
1652+
when assuming methods resolution. In the case of multiple
1653+
inheritance, a given attribute is first searched in the current
1654+
class if it's not found then it's searched in the parent classes.
1655+
The parent classes are searched in a left-right fashion and each
1656+
class is searched once.
1657+
Based on python's attribute resolution, in this case, attributes
1658+
are searched as follows:
1659+
1. MySQLCursorPreparedDict (current class)
1660+
2. MySQLCursorDict (left parent class)
1661+
3. MySQLCursorPrepared (right parent class)
1662+
4. MySQLCursor (base class)
1663+
"""
1664+
1665+
def fetchmany(
1666+
self, size: Optional[int] = None
1667+
) -> List[Dict[str, ToPythonOutputTypes]]:
1668+
"""Return the next set of rows of a query result set.
1669+
1670+
When no more rows are available, it returns an empty list.
1671+
The number of rows returned can be specified using the size argument,
1672+
which defaults to one.
1673+
1674+
Returns:
1675+
list: The next set of rows of a query result set represented
1676+
as a list of dictionaries where column names are used as keys.
1677+
"""
1678+
return [
1679+
self._row_to_python(row, self.description)
1680+
for row in super().fetchmany(size=size)
1681+
if row
1682+
]

‎lib/mysql/connector/cursor_cext.py

Lines changed: 24 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -886,12 +886,10 @@ def fetchone(self) -> Optional[Dict[str, ToPythonOutputTypes]]:
886886
"""Return next row of a query result set.
887887
888888
Returns:
889-
tuple or None: A row from query result set.
889+
dict or None: A dict from query result set.
890890
"""
891891
row = super().fetchone()
892-
if row:
893-
return dict(zip(self.column_names, row))
894-
return None
892+
return dict(zip(self.column_names, row)) if row else None
895893

896894
def fetchmany(self, size: int = 1) -> List[Dict[str, ToPythonOutputTypes]]:
897895
"""Return the next set of rows of a query result set.
@@ -901,7 +899,8 @@ def fetchmany(self, size: int = 1) -> List[Dict[str, ToPythonOutputTypes]]:
901899
which defaults to one.
902900
903901
Returns:
904-
list: The next set of rows of a query result set.
902+
list: The next set of rows of a query result set represented
903+
as a list of dictionaries where column names are used as keys.
905904
"""
906905
res = super().fetchmany(size=size)
907906
return [dict(zip(self.column_names, row)) for row in res]
@@ -910,7 +909,8 @@ def fetchall(self) -> List[Dict[str, ToPythonOutputTypes]]:
910909
"""Return all rows of a query result set.
911910
912911
Returns:
913-
list: A list of tuples with all rows of a query result set.
912+
list: A list of dictionaries with all rows of a query
913+
result set where column names are used as keys.
914914
"""
915915
res = super().fetchall()
916916
return [dict(zip(self.column_names, row)) for row in res]
@@ -1260,3 +1260,21 @@ def fetchall(self) -> List[RowType]:
12601260
self._rowcount += len(rows[0])
12611261
self._handle_eof()
12621262
return rows[0]
1263+
1264+
1265+
class CMySQLCursorPreparedDict(CMySQLCursorDict, CMySQLCursorPrepared): # type: ignore[misc]
1266+
"""This class is a blend of features from CMySQLCursorDict and CMySQLCursorPrepared
1267+
1268+
Multiple inheritance in python is allowed but care must be taken
1269+
when assuming methods resolution. In the case of multiple
1270+
inheritance, a given attribute is first searched in the current
1271+
class if it's not found then it's searched in the parent classes.
1272+
The parent classes are searched in a left-right fashion and each
1273+
class is searched once.
1274+
Based on python's attribute resolution, in this case, attributes
1275+
are searched as follows:
1276+
1. CMySQLCursorPreparedDict (current class)
1277+
2. CMySQLCursorDict (left parent class)
1278+
3. CMySQLCursorPrepared (right parent class)
1279+
4. CMySQLCursor (base class)
1280+
"""

‎tests/cext/test_cext_cursor.py

Lines changed: 115 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,7 @@
5454
CMySQLCursor,
5555
CMySQLCursorBuffered,
5656
CMySQLCursorPrepared,
57+
CMySQLCursorPreparedDict,
5758
CMySQLCursorRaw,
5859
)
5960

@@ -1032,3 +1033,117 @@ def test_executemany(self):
10321033
self.assertEqual(len(rows), 4)
10331034
self.assertEqual(rows[0][1:], self.exp)
10341035
self.assertEqual(rows[1][1:], self.exp)
1036+
1037+
1038+
class CMySQLCursorPreparedDictTests(CMySQLCursorPreparedTests):
1039+
1040+
tbl = "prep_dict_stmt"
1041+
1042+
column_names = (
1043+
"my_null",
1044+
"my_bit",
1045+
"my_tinyint",
1046+
"my_smallint",
1047+
"my_mediumint",
1048+
"my_int",
1049+
"my_bigint",
1050+
"my_decimal",
1051+
"my_float",
1052+
"my_double",
1053+
"my_date",
1054+
"my_time",
1055+
"my_datetime",
1056+
"my_year",
1057+
"my_char",
1058+
"my_varchar",
1059+
"my_enum",
1060+
"my_geometry",
1061+
"my_blob",
1062+
)
1063+
1064+
def setUp(self):
1065+
config = tests.get_mysql_config()
1066+
self.cnx = CMySQLConnection(**config)
1067+
self.cur = self.cnx.cursor(prepared=True, dictionary=True)
1068+
self.cur.execute(self.create_table_stmt.format(self.tbl))
1069+
1070+
def tearDown(self):
1071+
self.cur.execute("DROP TABLE IF EXISTS {0}".format(self.tbl))
1072+
self.cur.close()
1073+
self.cnx.close()
1074+
1075+
def test___init__(self):
1076+
self.assertIsInstance(self.cur, CMySQLCursorPreparedDict)
1077+
1078+
def test_close(self):
1079+
cur = self.cnx.cursor(prepared=True, dictionary=True)
1080+
self.assertEqual(None, cur._stmt)
1081+
cur.close()
1082+
1083+
def test_fetchone(self):
1084+
self.cur.execute(self.insert_stmt.format(self.tbl), self.data)
1085+
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
1086+
row = self.cur.fetchone()
1087+
del row["id"]
1088+
self.assertEqual(row, dict(zip(self.column_names, self.exp)))
1089+
row = self.cur.fetchone()
1090+
self.assertIsNone(row)
1091+
1092+
def test_fetchall(self):
1093+
self.cur.execute(self.insert_stmt.format(self.tbl), self.data)
1094+
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
1095+
rows = self.cur.fetchall()
1096+
self.assertEqual(len(rows), 1)
1097+
del rows[0]["id"]
1098+
self.assertEqual(rows[0], dict(zip(self.column_names, self.exp)))
1099+
1100+
def test_fetchmany(self):
1101+
data = [self.data[:], self.data[:], self.data[:]]
1102+
self.cur.executemany(self.insert_stmt.format(self.tbl), data)
1103+
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
1104+
rows = self.cur.fetchmany(size=2)
1105+
del rows[0]["id"]
1106+
del rows[1]["id"]
1107+
self.assertEqual(len(rows), 2)
1108+
self.assertEqual(rows[0], dict(zip(self.column_names, self.exp)))
1109+
self.assertEqual(rows[1], dict(zip(self.column_names, self.exp)))
1110+
1111+
rows = self.cur.fetchmany(1)
1112+
del rows[0]["id"]
1113+
self.assertEqual(len(rows), 1)
1114+
self.assertEqual(rows[0], dict(zip(self.column_names, self.exp)))
1115+
1116+
def test_execute(self):
1117+
# Use dict as placeholders
1118+
data_dict = dict(zip(self.insert_columns, self.data))
1119+
1120+
self.cur.execute(self.insert_dict_stmt.format(self.tbl), data_dict)
1121+
self.cur.execute(f"SELECT * FROM {self.tbl}")
1122+
rows = self.cur.fetchall()
1123+
del rows[0]["id"]
1124+
self.assertEqual(len(rows), 1)
1125+
self.assertEqual(rows[0], dict(zip(self.column_names, self.exp)))
1126+
1127+
def test_executemany(self):
1128+
data = [self.data[:], self.data[:]]
1129+
self.cur.executemany(self.insert_stmt.format(self.tbl), data)
1130+
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
1131+
rows = self.cur.fetchall()
1132+
del rows[0]["id"]
1133+
del rows[1]["id"]
1134+
self.assertEqual(len(rows), 2)
1135+
self.assertEqual(rows[0], dict(zip(self.column_names, self.exp)))
1136+
self.assertEqual(rows[1], dict(zip(self.column_names, self.exp)))
1137+
1138+
# Use dict as placeholders
1139+
data_dict = dict(zip(self.insert_columns, self.data))
1140+
data = [data_dict, copy.deepcopy(data_dict)]
1141+
1142+
self.cur.executemany(self.insert_dict_stmt.format(self.tbl), data)
1143+
self.cur.execute(f"SELECT * FROM {self.tbl}")
1144+
rows = self.cur.fetchall()
1145+
del rows[0]["id"]
1146+
del rows[1]["id"]
1147+
self.assertEqual(len(rows), 4)
1148+
self.assertEqual(rows[0], dict(zip(self.column_names, self.exp)))
1149+
self.assertEqual(rows[1], dict(zip(self.column_names, self.exp)))

‎tests/test_cursor.py

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1612,3 +1612,66 @@ def test_fetchall(self):
16121612
self.assertEqual(exp.id, row[0].id)
16131613
self.assertEqual(exp.name, row[0].name)
16141614
self.assertEqual(exp.city, row[0].city)
1615+
1616+
1617+
class MySQLCursorPreparedDictTests(tests.TestsCursor):
1618+
1619+
table_name = "MySQLCursorPreparedDictTests"
1620+
column_names = ("id", "name", "city")
1621+
data = [
1622+
(1, "Mr A", "mexico"),
1623+
(2, "Mr B", "portugal"),
1624+
(3, "Mr C", "poland"),
1625+
(4, "Mr D", "usa"),
1626+
]
1627+
1628+
def setUp(self):
1629+
config = tests.get_mysql_config()
1630+
self.cnx = connection.MySQLConnection(**config)
1631+
self.cur = self.cnx.cursor(prepared=True, dictionary=True)
1632+
self.cur.execute(f"DROP TABLE IF EXISTS {self.table_name}")
1633+
self.cur.execute(
1634+
f"CREATE TABLE {self.table_name}(id INT(10) PRIMARY KEY, name "
1635+
"VARCHAR(20), city VARCHAR(20))"
1636+
)
1637+
1638+
def tearDown(self):
1639+
self.cur.execute(f"DROP TABLE IF EXISTS {self.table_name}")
1640+
self.cur.close()
1641+
self.cnx.close()
1642+
1643+
def test_fetchone(self):
1644+
self.cur.execute(
1645+
f"INSERT INTO {self.table_name} VALUES(%s, %s, %s)",
1646+
self.data[0],
1647+
)
1648+
1649+
self.cur.execute(f"SELECT * FROM {self.table_name}")
1650+
exp = dict(zip(self.column_names, self.data[0]))
1651+
self.assertEqual(exp, self.cur.fetchone())
1652+
1653+
def test_fetchmany(self):
1654+
for row in self.data:
1655+
self.cur.execute(
1656+
f"INSERT INTO {self.table_name} VALUES(%s, %s, %s)",
1657+
row,
1658+
)
1659+
1660+
self.cur.execute(f"SELECT * FROM {self.table_name}")
1661+
1662+
exp = [dict(zip(self.column_names, data)) for data in self.data[:2]]
1663+
self.assertEqual(exp, self.cur.fetchmany(size=2))
1664+
1665+
exp = [dict(zip(self.column_names, data)) for data in self.data[2:]]
1666+
self.assertEqual(exp, self.cur.fetchmany(size=2))
1667+
1668+
def test_fetchall(self):
1669+
for row in self.data:
1670+
self.cur.execute(
1671+
f"INSERT INTO {self.table_name} VALUES(%s, %s, %s)",
1672+
row,
1673+
)
1674+
1675+
self.cur.execute(f"SELECT * FROM {self.table_name}")
1676+
exp = [dict(zip(self.column_names, data)) for data in self.data]
1677+
self.assertEqual(exp, self.cur.fetchall())

0 commit comments

Comments
 (0)