This repository has been archived by the owner on Oct 7, 2018. It is now read-only.
forked from PyMySQL/PyMySQL
/
cursors.py
415 lines (347 loc) · 13.2 KB
/
cursors.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
# -*- coding: utf-8 -*-
import struct
import re
try:
import cStringIO as StringIO
except ImportError:
import StringIO
from err import Warning, Error, InterfaceError, DataError, \
DatabaseError, OperationalError, IntegrityError, InternalError, \
NotSupportedError, ProgrammingError
insert_values = re.compile(r'\svalues\s*(\(.+\))', re.IGNORECASE)
class Cursor(object):
'''
This is the object you use to interact with the database.
'''
def __init__(self, connection):
'''
Do not create an instance of a Cursor yourself. Call
connections.Connection.cursor().
'''
from weakref import proxy
self.connection = proxy(connection)
self.description = None
self.rownumber = 0
self.rowcount = -1
self.arraysize = 1
self._executed = None
self.messages = []
self.errorhandler = connection.errorhandler
self._has_next = None
self._rows = ()
def __del__(self):
'''
When this gets GC'd close it.
'''
self.close()
def close(self):
'''
Closing a cursor just exhausts all remaining data.
'''
if not self.connection:
return
try:
while self.nextset():
pass
except:
pass
self.connection = None
def _get_db(self):
if not self.connection:
self.errorhandler(self, ProgrammingError, "cursor closed")
return self.connection
def _check_executed(self):
if not self._executed:
self.errorhandler(self, ProgrammingError, "execute() first")
def setinputsizes(self, *args):
"""Does nothing, required by DB API."""
def setoutputsizes(self, *args):
"""Does nothing, required by DB API."""
def nextset(self):
''' Get the next query set '''
if self._executed:
self.fetchall()
del self.messages[:]
if not self._has_next:
return None
connection = self._get_db()
connection.next_result()
self._do_get_result()
return True
def execute(self, query, args=None):
''' Execute a query '''
from sys import exc_info
conn = self._get_db()
charset = conn.charset
del self.messages[:]
# Ideally we would use %-formatting on byte strings here, however py3k
# did away with this functionality. So instead, we represent the query
# and escaped args as strs, which contain not actual characters, but
# instead raw bytes encoded in the connection charset.
if isinstance(query, str) and charset != 'latin-1':
query = query.encode(charset).decode('latin-1')
if args is not None:
if isinstance(args, tuple) or isinstance(args, list):
escaped_args = tuple(conn.escape(arg).decode('latin-1') for arg in args)
elif isinstance(args, dict):
escaped_args = dict((key, conn.escape(val).decode('latin-1'))
for (key, val) in args.items())
else:
#If it's not a dictionary let's try escaping it anyways.
#Worst case it will throw a Value error
escaped_args = conn.escape(args).decode('latin-1')
query = query % escaped_args
query = query.encode('latin-1')
result = 0
try:
result = self._query(query)
except:
exc, value, tb = exc_info()
del tb
self.messages.append((exc,value))
self.errorhandler(self, exc, value)
self._executed = query
return result
def executemany(self, query, args):
''' Run several data against one query '''
del self.messages[:]
#conn = self._get_db()
if not args:
return
#charset = conn.charset
#if isinstance(query, unicode):
# query = query.encode(charset)
self.rowcount = sum([ self.execute(query, arg) for arg in args ])
return self.rowcount
def callproc(self, procname, args=()):
"""Execute stored procedure procname with args
procname -- string, name of procedure to execute on server
args -- Sequence of parameters to use with procedure
Returns the original args.
Compatibility warning: PEP-249 specifies that any modified
parameters must be returned. This is currently impossible
as they are only available by storing them in a server
variable and then retrieved by a query. Since stored
procedures return zero or more result sets, there is no
reliable way to get at OUT or INOUT parameters via callproc.
The server variables are named @_procname_n, where procname
is the parameter above and n is the position of the parameter
(from zero). Once all result sets generated by the procedure
have been fetched, you can issue a SELECT @_procname_0, ...
query using .execute() to get any OUT or INOUT values.
Compatibility warning: The act of calling a stored procedure
itself creates an empty result set. This appears after any
result sets generated by the procedure. This is non-standard
behavior with respect to the DB-API. Be sure to use nextset()
to advance through all result sets; otherwise you may get
disconnected.
"""
conn = self._get_db()
for index, arg in enumerate(args):
q = "SET @_%s_%d=%s" % (procname, index, conn.escape(arg))
if isinstance(q, unicode):
q = q.encode(conn.charset)
self._query(q)
self.nextset()
q = "CALL %s(%s)" % (procname,
','.join(['@_%s_%d' % (procname, i)
for i in range(len(args))]))
if isinstance(q, unicode):
q = q.encode(conn.charset)
self._query(q)
self._executed = q
return args
def fetchone(self):
''' Fetch the next row '''
self._check_executed()
if self._rows is None or self.rownumber >= len(self._rows):
return None
result = self._rows[self.rownumber]
self.rownumber += 1
return result
def fetchmany(self, size=None):
''' Fetch several rows '''
self._check_executed()
end = self.rownumber + (size or self.arraysize)
result = self._rows[self.rownumber:end]
if self._rows is None:
return None
self.rownumber = min(end, len(self._rows))
return result
def fetchall(self):
''' Fetch all the rows '''
self._check_executed()
if self._rows is None:
return None
if self.rownumber:
result = self._rows[self.rownumber:]
else:
result = self._rows
self.rownumber = len(self._rows)
return result
def scroll(self, value, mode='relative'):
self._check_executed()
if mode == 'relative':
r = self.rownumber + value
elif mode == 'absolute':
r = value
else:
self.errorhandler(self, ProgrammingError,
"unknown scroll mode %s" % mode)
if r < 0 or r >= len(self._rows):
self.errorhandler(self, IndexError, "out of range")
self.rownumber = r
def _query(self, q):
conn = self._get_db()
self._last_executed = q
conn.query(q)
self._do_get_result()
return self.rowcount
def _do_get_result(self):
conn = self._get_db()
self.rowcount = conn._result.affected_rows
self.rownumber = 0
self.description = conn._result.description
self.lastrowid = conn._result.insert_id
self._rows = conn._result.rows
self._has_next = conn._result.has_next
def __iter__(self):
return iter(self.fetchone, None)
Warning = Warning
Error = Error
InterfaceError = InterfaceError
DatabaseError = DatabaseError
DataError = DataError
OperationalError = OperationalError
IntegrityError = IntegrityError
InternalError = InternalError
ProgrammingError = ProgrammingError
NotSupportedError = NotSupportedError
class DictCursor(Cursor):
"""A cursor which returns results as a dictionary"""
def execute(self, query, args=None):
result = super(DictCursor, self).execute(query, args)
if self.description:
self._fields = [ field[0] for field in self.description ]
return result
def fetchone(self):
''' Fetch the next row '''
self._check_executed()
if self._rows is None or self.rownumber >= len(self._rows):
return None
result = dict(zip(self._fields, self._rows[self.rownumber]))
self.rownumber += 1
return result
def fetchmany(self, size=None):
''' Fetch several rows '''
self._check_executed()
if self._rows is None:
return None
end = self.rownumber + (size or self.arraysize)
result = [ dict(zip(self._fields, r)) for r in self._rows[self.rownumber:end] ]
self.rownumber = min(end, len(self._rows))
return tuple(result)
def fetchall(self):
''' Fetch all the rows '''
self._check_executed()
if self._rows is None:
return None
if self.rownumber:
result = [ dict(zip(self._fields, r)) for r in self._rows[self.rownumber:] ]
else:
result = [ dict(zip(self._fields, r)) for r in self._rows ]
self.rownumber = len(self._rows)
return tuple(result)
class SSCursor(Cursor):
"""
Unbuffered Cursor, mainly useful for queries that return a lot of data,
or for connections to remote servers over a slow network.
Instead of copying every row of data into a buffer, this will fetch
rows as needed. The upside of this, is the client uses much less memory,
and rows are returned much faster when traveling over a slow network,
or if the result set is very big.
There are limitations, though. The MySQL protocol doesn't support
returning the total number of rows, so the only way to tell how many rows
there are is to iterate over every row returned. Also, it currently isn't
possible to scroll backwards, as only the current row is held in memory.
"""
def close(self):
conn = self._get_db()
conn._result._finish_unbuffered_query()
try:
if self._has_next:
while self.nextset(): pass
except: pass
def _query(self, q):
conn = self._get_db()
self._last_executed = q
conn.query(q, unbuffered=True)
self._do_get_result()
return self.rowcount
def read_next(self):
""" Read next row """
conn = self._get_db()
conn._result._read_rowdata_packet_unbuffered()
return conn._result.rows
def fetchone(self):
""" Fetch next row """
self._check_executed()
row = self.read_next()
if row is None:
return None
self.rownumber += 1
return row
def fetchall(self):
"""
Fetch all, as per MySQLdb. Pretty useless for large queries, as
it is buffered. See fetchall_unbuffered(), if you want an unbuffered
generator version of this method.
"""
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return tuple(rows)
def fetchall_unbuffered(self):
"""
Fetch all, implemented as a generator, which isn't to standard,
however, it doesn't make sense to return everything in a list, as that
would use ridiculous memory for large result sets.
"""
row = self.fetchone()
while row is not None:
yield row
row = self.fetchone()
def fetchmany(self, size=None):
""" Fetch many """
self._check_executed()
if size is None:
size = self.arraysize
rows = []
for i in range(0, size):
row = self.read_next()
if row is None:
break
rows.append(row)
self.rownumber += 1
return tuple(rows)
def scroll(self, value, mode='relative'):
self._check_executed()
if not mode == 'relative' and not mode == 'absolute':
self.errorhandler(self, ProgrammingError,
"unknown scroll mode %s" % mode)
if mode == 'relative':
if value < 0:
self.errorhandler(self, NotSupportedError,
"Backwards scrolling not supported by this cursor")
for i in range(0, value): self.read_next()
self.rownumber += value
else:
if value < self.rownumber:
self.errorhandler(self, NotSupportedError,
"Backwards scrolling not supported by this cursor")
end = value - self.rownumber
for i in range(0, end): self.read_next()
self.rownumber = value