MySQLdb.cursors

1 """MySQLdb Cursors 2 3 This module implements Cursors of various types for MySQLdb. By 4 default, MySQLdb uses the Cursor class. 5 6 """ 7 8 import re 9 insert_values = re.compile(r"\svalues\s*(\(((?<!\\)'.*?\).*(?<!\\)?'|.)+?\))", re.IGNORECASE) 10 from _mysql_exceptions import Warning, Error, InterfaceError, DataError, \ 11 DatabaseError, OperationalError, IntegrityError, InternalError, \ 12 NotSupportedError, ProgrammingError 13 14

15 -class BaseCursor(object):

16 17 """A base for Cursor classes. Useful attributes: 18 19 description 20 A tuple of DB API 7-tuples describing the columns in 21 the last executed query; see PEP-249 for details. 22 23 description_flags 24 Tuple of column flags for last query, one entry per column 25 in the result set. Values correspond to those in 26 MySQLdb.constants.FLAG. See MySQL documentation (C API) 27 for more information. Non-standard extension. 28 29 arraysize 30 default number of rows fetchmany() will fetch 31 32 """ 33 34 from _mysql_exceptions import MySQLError, Warning, Error, InterfaceError, \ 35 DatabaseError, DataError, OperationalError, IntegrityError, \ 36 InternalError, ProgrammingError, NotSupportedError 37 38 _defer_warnings = False 39

40 - def __init__(self, connection):

41 from weakref import proxy 42 43 self.connection = proxy(connection) 44 self.description = None 45 self.description_flags = None 46 self.rowcount = -1 47 self.arraysize = 1 48 self._executed = None 49 self.lastrowid = None 50 self.messages = [] 51 self.errorhandler = connection.errorhandler 52 self._result = None 53 self._warnings = 0 54 self._info = None 55 self.rownumber = None

56

57 - def __del__(self):

58 self.close() 59 self.errorhandler = None 60 self._result = None

61

62 - def close(self):

63 """Close the cursor. No further queries will be possible.""" 64 if not self.connection: return 65 while self.nextset(): pass 66 self.connection = None

67

68 - def _check_executed(self):

69 if not self._executed: 70 self.errorhandler(self, ProgrammingError, "execute() first")

71

72 - def _warning_check(self):

73 from warnings import warn 74 if self._warnings: 75 warnings = self._get_db().show_warnings() 76 if warnings: 77 # This is done in two loops in case 78 # Warnings are set to raise exceptions. 79 for w in warnings: 80 self.messages.append((self.Warning, w)) 81 for w in warnings: 82 warn(w[-1], self.Warning, 3) 83 elif self._info: 84 self.messages.append((self.Warning, self._info)) 85 warn(self._info, self.Warning, 3)

86

87 - def nextset(self):

88 """Advance to the next result set. 89 90 Returns None if there are no more result sets. 91 """ 92 if self._executed: 93 self.fetchall() 94 del self.messages[:] 95 96 db = self._get_db() 97 nr = db.next_result() 98 if nr == -1: 99 return None 100 self._do_get_result() 101 self._post_get_result() 102 self._warning_check() 103 return 1

104

105 - def _post_get_result(self): pass

106

107 - def _do_get_result(self):

108 db = self._get_db() 109 self._result = self._get_result() 110 self.rowcount = db.affected_rows() 111 self.rownumber = 0 112 self.description = self._result and self._result.describe() or None 113 self.description_flags = self._result and self._result.field_flags() or None 114 self.lastrowid = db.insert_id() 115 self._warnings = db.warning_count() 116 self._info = db.info()

117

118 - def setinputsizes(self, *args):

119 """Does nothing, required by DB API."""

120

121 - def setoutputsizes(self, *args):

122 """Does nothing, required by DB API."""

123

124 - def _get_db(self):

125 if not self.connection: 126 self.errorhandler(self, ProgrammingError, "cursor closed") 127 return self.connection

128

129 - def execute(self, query, args=None):

130 131 """Execute a query. 132 133 query -- string, query to execute on server 134 args -- optional sequence or mapping, parameters to use with query. 135 136 Note: If args is a sequence, then %s must be used as the 137 parameter placeholder in the query. If a mapping is used, 138 %(key)s must be used as the placeholder. 139 140 Returns long integer rows affected, if any 141 142 """ 143 from types import ListType, TupleType 144 from sys import exc_info 145 del self.messages[:] 146 db = self._get_db() 147 charset = db.character_set_name() 148 if isinstance(query, unicode): 149 query = query.encode(charset) 150 if args is not None: 151 query = query % db.literal(args) 152 try: 153 r = self._query(query) 154 except TypeError, m: 155 if m.args[0] in ("not enough arguments for format string", 156 "not all arguments converted"): 157 self.messages.append((ProgrammingError, m.args[0])) 158 self.errorhandler(self, ProgrammingError, m.args[0]) 159 else: 160 self.messages.append((TypeError, m)) 161 self.errorhandler(self, TypeError, m) 162 except: 163 exc, value, tb = exc_info() 164 del tb 165 self.messages.append((exc, value)) 166 self.errorhandler(self, exc, value) 167 self._executed = query 168 if not self._defer_warnings: self._warning_check() 169 return r

170

171 - def executemany(self, query, args):

172 173 """Execute a multi-row query. 174 175 query -- string, query to execute on server 176 177 args 178 179 Sequence of sequences or mappings, parameters to use with 180 query. 181 182 Returns long integer rows affected, if any. 183 184 This method improves performance on multiple-row INSERT and 185 REPLACE. Otherwise it is equivalent to looping over args with 186 execute(). 187 188 """ 189 del self.messages[:] 190 db = self._get_db() 191 if not args: return 192 charset = db.character_set_name() 193 if isinstance(query, unicode): query = query.encode(charset) 194 m = insert_values.search(query) 195 if not m: 196 r = 0 197 for a in args: 198 r = r + self.execute(query, a) 199 return r 200 p = m.start(1) 201 e = m.end(1) 202 qv = m.group(1) 203 try: 204 q = [ qv % db.literal(a) for a in args ] 205 except TypeError, msg: 206 if msg.args[0] in ("not enough arguments for format string", 207 "not all arguments converted"): 208 self.messages.append((ProgrammingError, msg.args[0])) 209 self.errorhandler(self, ProgrammingError, msg.args[0]) 210 else: 211 self.messages.append((TypeError, msg)) 212 self.errorhandler(self, TypeError, msg) 213 except: 214 from sys import exc_info 215 exc, value, tb = exc_info() 216 del tb 217 self.errorhandler(self, exc, value) 218 r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]])) 219 if not self._defer_warnings: self._warning_check() 220 return r

221

222 - def callproc(self, procname, args=()):

223 224 """Execute stored procedure procname with args 225 226 procname -- string, name of procedure to execute on server 227 228 args -- Sequence of parameters to use with procedure 229 230 Returns the original args. 231 232 Compatibility warning: PEP-249 specifies that any modified 233 parameters must be returned. This is currently impossible 234 as they are only available by storing them in a server 235 variable and then retrieved by a query. Since stored 236 procedures return zero or more result sets, there is no 237 reliable way to get at OUT or INOUT parameters via callproc. 238 The server variables are named @_procname_n, where procname 239 is the parameter above and n is the position of the parameter 240 (from zero). Once all result sets generated by the procedure 241 have been fetched, you can issue a SELECT @_procname_0, ... 242 query using .execute() to get any OUT or INOUT values. 243 244 Compatibility warning: The act of calling a stored procedure 245 itself creates an empty result set. This appears after any 246 result sets generated by the procedure. This is non-standard 247 behavior with respect to the DB-API. Be sure to use nextset() 248 to advance through all result sets; otherwise you may get 249 disconnected. 250 """ 251 252 from types import UnicodeType 253 db = self._get_db() 254 charset = db.character_set_name() 255 for index, arg in enumerate(args): 256 q = "SET @_%s_%d=%s" % (procname, index, 257 db.literal(arg)) 258 if isinstance(q, unicode): 259 q = q.encode(charset) 260 self._query(q) 261 self.nextset() 262 263 q = "CALL %s(%s)" % (procname, 264 ','.join(['@_%s_%d' % (procname, i) 265 for i in range(len(args))])) 266 if type(q) is UnicodeType: 267 q = q.encode(charset) 268 self._query(q) 269 self._executed = q 270 if not self._defer_warnings: self._warning_check() 271 return args

272

273 - def _do_query(self, q):

274 db = self._get_db() 275 self._last_executed = q 276 db.query(q) 277 self._do_get_result() 278 return self.rowcount

279

280 - def _query(self, q): return self._do_query(q)

281

282 - def _fetch_row(self, size=1):

283 if not self._result: 284 return () 285 return self._result.fetch_row(size, self._fetch_type)

286

287 - def __iter__(self):

288 return iter(self.fetchone, None)

289 290 Warning = Warning 291 Error = Error 292 InterfaceError = InterfaceError 293 DatabaseError = DatabaseError 294 DataError = DataError 295 OperationalError = OperationalError 296 IntegrityError = IntegrityError 297 InternalError = InternalError 298 ProgrammingError = ProgrammingError 299 NotSupportedError = NotSupportedError

300 301

302 -class CursorStoreResultMixIn(object):

303 304 """This is a MixIn class which causes the entire result set to be 305 stored on the client side, i.e. it uses mysql_store_result(). If the 306 result set can be very large, consider adding a LIMIT clause to your 307 query, or using CursorUseResultMixIn instead.""" 308

309 - def _get_result(self): return self._get_db().store_result()

310

311 - def _query(self, q):

312 rowcount = self._do_query(q) 313 self._post_get_result() 314 return rowcount

315

316 - def _post_get_result(self):

317 self._rows = self._fetch_row(0) 318 self._result = None

319

320 - def fetchone(self):

321 """Fetches a single row from the cursor. None indicates that 322 no more rows are available.""" 323 self._check_executed() 324 if self.rownumber >= len(self._rows): return None 325 result = self._rows[self.rownumber] 326 self.rownumber = self.rownumber+1 327 return result

328

329 - def fetchmany(self, size=None):

330 """Fetch up to size rows from the cursor. Result set may be smaller 331 than size. If size is not defined, cursor.arraysize is used.""" 332 self._check_executed() 333 end = self.rownumber + (size or self.arraysize) 334 result = self._rows[self.rownumber:end] 335 self.rownumber = min(end, len(self._rows)) 336 return result

337

338 - def fetchall(self):

339 """Fetchs all available rows from the cursor.""" 340 self._check_executed() 341 if self.rownumber: 342 result = self._rows[self.rownumber:] 343 else: 344 result = self._rows 345 self.rownumber = len(self._rows) 346 return result

347

348 - def scroll(self, value, mode='relative'):

349 """Scroll the cursor in the result set to a new position according 350 to mode. 351 352 If mode is 'relative' (default), value is taken as offset to 353 the current position in the result set, if set to 'absolute', 354 value states an absolute target position.""" 355 self._check_executed() 356 if mode == 'relative': 357 r = self.rownumber + value 358 elif mode == 'absolute': 359 r = value 360 else: 361 self.errorhandler(self, ProgrammingError, 362 "unknown scroll mode %s" % `mode`) 363 if r < 0 or r >= len(self._rows): 364 self.errorhandler(self, IndexError, "out of range") 365 self.rownumber = r

366

367 - def __iter__(self):

368 self._check_executed() 369 result = self.rownumber and self._rows[self.rownumber:] or self._rows 370 return iter(result)

371 372

373 -class CursorUseResultMixIn(object):

374 375 """This is a MixIn class which causes the result set to be stored 376 in the server and sent row-by-row to client side, i.e. it uses 377 mysql_use_result(). You MUST retrieve the entire result set and 378 close() the cursor before additional queries can be peformed on 379 the connection.""" 380 381 _defer_warnings = True 382

383 - def _get_result(self): return self._get_db().use_result()

384

385 - def fetchone(self):

386 """Fetches a single row from the cursor.""" 387 self._check_executed() 388 r = self._fetch_row(1) 389 if not r: 390 self._warning_check() 391 return None 392 self.rownumber = self.rownumber + 1 393 return r[0]

394

395 - def fetchmany(self, size=None):

396 """Fetch up to size rows from the cursor. Result set may be smaller 397 than size. If size is not defined, cursor.arraysize is used.""" 398 self._check_executed() 399 r = self._fetch_row(size or self.arraysize) 400 self.rownumber = self.rownumber + len(r) 401 if not r: 402 self._warning_check() 403 return r

404

405 - def fetchall(self):

406 """Fetchs all available rows from the cursor.""" 407 self._check_executed() 408 r = self._fetch_row(0) 409 self.rownumber = self.rownumber + len(r) 410 self._warning_check() 411 return r

412

413 - def __iter__(self):

414 return self

415

416 - def next(self):

417 row = self.fetchone() 418 if row is None: 419 raise StopIteration 420 return row

421 422

423 -class CursorTupleRowsMixIn(object):

424 425 """This is a MixIn class that causes all rows to be returned as tuples, 426 which is the standard form required by DB API.""" 427 428 _fetch_type = 0

429 430

431 -class CursorDictRowsMixIn(object):

432 433 """This is a MixIn class that causes all rows to be returned as 434 dictionaries. This is a non-standard feature.""" 435 436 _fetch_type = 1 437

438 - def fetchoneDict(self):

439 """Fetch a single row as a dictionary. Deprecated: 440 Use fetchone() instead. Will be removed in 1.3.""" 441 from warnings import warn 442 warn("fetchoneDict() is non-standard and will be removed in 1.3", 443 DeprecationWarning, 2) 444 return self.fetchone()

445

446 - def fetchmanyDict(self, size=None):

447 """Fetch several rows as a list of dictionaries. Deprecated: 448 Use fetchmany() instead. Will be removed in 1.3.""" 449 from warnings import warn 450 warn("fetchmanyDict() is non-standard and will be removed in 1.3", 451 DeprecationWarning, 2) 452 return self.fetchmany(size)

453

454 - def fetchallDict(self):

455 """Fetch all available rows as a list of dictionaries. Deprecated: 456 Use fetchall() instead. Will be removed in 1.3.""" 457 from warnings import warn 458 warn("fetchallDict() is non-standard and will be removed in 1.3", 459 DeprecationWarning, 2) 460 return self.fetchall()

461 462

463 -class CursorOldDictRowsMixIn(CursorDictRowsMixIn):

464 465 """This is a MixIn class that returns rows as dictionaries with 466 the same key convention as the old Mysqldb (MySQLmodule). Don't 467 use this.""" 468 469 _fetch_type = 2

470 471

472 -class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn, 473 BaseCursor):

474 475 """This is the standard Cursor class that returns rows as tuples 476 and stores the result set in the client."""

477 478

479 -class DictCursor(CursorStoreResultMixIn, CursorDictRowsMixIn, 480 BaseCursor):

481 482 """This is a Cursor class that returns rows as dictionaries and 483 stores the result set in the client."""

484 485

486 -class SSCursor(CursorUseResultMixIn, CursorTupleRowsMixIn, 487 BaseCursor):

488 489 """This is a Cursor class that returns rows as tuples and stores 490 the result set in the server."""

491 492

493 -class SSDictCursor(CursorUseResultMixIn, CursorDictRowsMixIn, 494 BaseCursor):

495 496 """This is a Cursor class that returns rows as dictionaries and 497 stores the result set in the server."""

498

Source Code for Module MySQLdb.cursors