Debian Codegraph @

docs/cursors.rst

raw
1 .. _aiomysql-cursors:
2
3 Cursor
4 ======
5
6 .. class:: Cursor
7
8 A cursor for connection.
9
10 Allows Python code to execute :term:`MySQL` command in a database
11 session. Cursors are created by the :meth:`Connection.cursor`
12 :ref:`coroutine <coroutine>`: they are bound to the connection for
13 the entire lifetime and all the commands are executed in the context
14 of the database session wrapped by the connection.
15
16 Cursors that are created from the same connection are not isolated,
17 i.e., any changes done to the database by a cursor are immediately
18 visible by the other cursors. Cursors created from different
19 connections can or can not be isolated, depending on the
20 connections’ isolation level.
21
22 .. code:: python
23
24 import asyncio
25 import aiomysql
26
27 loop = asyncio.get_event_loop()
28
29 async def test_example():
30 conn = await aiomysql.connect(host='127.0.0.1', port=3306,
31 user='root', password='',
32 db='mysql', loop=loop)
33
34 # create default cursor
35 cursor = await conn.cursor()
36
37 # execute sql query
38 await cursor.execute("SELECT Host, User FROM user")
39
40 # fetch all results
41 r = await cursor.fetchall()
42
43 # detach cursor from connection
44 await cursor.close()
45
46 # close connection
47 conn.close()
48
49 loop.run_until_complete(test_example())
50
51
52 Use :meth:`Connection.cursor()` for getting cursor for connection.
53
54 .. attribute:: connection
55
56 This read-only attribute return a reference to the :class:`Connection`
57 object on which the cursor was created
58
59 .. attribute:: echo
60
61 Return echo mode status.
62
63 .. attribute:: description
64
65 This read-only attribute is a sequence of 7-item sequences.
66
67 Each of these sequences is a collections.namedtuple containing
68 information describing one result column:
69
70 0. name: the name of the column returned.
71 1. type_code: the type of the column.
72 2. display_size: the actual length of the column in bytes.
73 3. internal_size: the size in bytes of the column associated to
74 this column on the server.
75 4. precision: total number of significant digits in columns of
76 type ``NUMERIC``. None for other types.
77 5. scale: count of decimal digits in the fractional part in
78 columns of type ``NUMERIC``. None for other types.
79 6. null_ok: always None.
80
81 This attribute will be None for operations that do not
82 return rows or if the cursor has not had an operation invoked
83 via the :meth:`Cursor.execute()` method yet.
84
85 .. attribute:: rowcount
86
87 Returns the number of rows that has been produced of affected.
88
89 This read-only attribute specifies the number of rows that the
90 last :meth:`Cursor.execute()` produced (for Data Query Language
91 statements like SELECT) or affected (for Data Manipulation
92 Language statements like ``UPDATE`` or ``INSERT``).
93
94 The attribute is -1 in case no :meth:`Cursor.execute()` has been
95 performed on the cursor or the row count of the last operation if it
96 can't be determined by the interface.
97
98 .. attribute:: rownumber
99
100 Row index. This read-only attribute provides the current 0-based index
101 of the cursor in the result set or ``None`` if the index cannot be
102 determined.
103
104 .. attribute:: arraysize
105
106 How many rows will be returned by :meth:`Cursor.fetchmany()` call.
107
108 This read/write attribute specifies the number of rows to
109 fetch at a time with :meth:`Cursor.fetchmany()`. It defaults to
110 1 meaning to fetch a single row at a time.
111
112 .. attribute:: lastrowid
113
114 This read-only property returns the value generated for an
115 `AUTO_INCREMENT` column by the previous `INSERT` or `UPDATE` statement
116 or None when there is no such value available. For example,
117 if you perform an `INSERT` into a table that contains an
118 `AUTO_INCREMENT` column, :attr:`Cursor.lastrowid` returns the
119 `AUTO_INCREMENT` value for the new row.
120
121 .. attribute:: closed
122
123 The readonly property that returns ``True`` if connections was detached
124 from current cursor
125
126 .. method:: close()
127
128 :ref:`Coroutine <coroutine>` to close the cursor now (rather than
129 whenever ``del`` is executed). The cursor will be unusable from this
130 point forward; closing a cursor just exhausts all remaining data.
131
132 .. method:: execute(query, args=None)
133
134 :ref:`Coroutine <coroutine>`, executes the given operation substituting
135 any markers with the given parameters.
136
137 For example, getting all rows where id is 5::
138
139 await cursor.execute("SELECT * FROM t1 WHERE id=%s", (5,))
140
141 :param str query: sql statement
142 :param list args: tuple or list of arguments for sql query
143 :returns int: number of rows that has been produced of affected
144
145 .. method:: executemany(query, args)
146
147 The `executemany()` :ref:`coroutine <coroutine>` will execute the
148 operation iterating over the list of parameters in seq_params.
149
150 Example: Inserting 3 new employees and their phone number::
151
152 data = [
153 ('Jane','555-001'),
154 ('Joe', '555-001'),
155 ('John', '555-003')
156 ]
157 stmt = "INSERT INTO employees (name, phone)
158 VALUES ('%s','%s')"
159 await cursor.executemany(stmt, data)
160
161 `INSERT` statements are optimized by batching the data, that is
162 using the MySQL multiple rows syntax.
163
164 :param str query: sql statement
165 :param list args: tuple or list of arguments for sql query
166
167 .. method:: callproc(procname, args)
168
169 Execute stored procedure procname with args, this method is
170 :ref:`coroutine <coroutine>`.
171
172 Compatibility warning: PEP-249 specifies that any modified
173 parameters must be returned. This is currently impossible
174 as they are only available by storing them in a server
175 variable and then retrieved by a query. Since stored
176 procedures return zero or more result sets, there is no
177 reliable way to get at OUT or INOUT parameters via `callproc`.
178 The server variables are named `@_procname_n`, where `procname`
179 is the parameter above and n is the position of the parameter
180 (from zero). Once all result sets generated by the procedure
181 have been fetched, you can issue a `SELECT @_procname_0`, ...
182 query using :meth:`Cursor.execute()` to get any OUT or INOUT values.
183 Basic usage example::
184
185 conn = await aiomysql.connect(host='127.0.0.1', port=3306,
186 user='root', password='',
187 db='mysql', loop=self.loop)
188
189 cur = await conn.cursor()
190 await cur.execute("""CREATE PROCEDURE myinc(p1 INT)
191 BEGIN
192 SELECT p1 + 1;
193 END
194 """)
195
196 await cur.callproc('myinc', [1])
197 (ret, ) = await cur.fetchone()
198 assert 2, ret
199
200 await cur.close()
201 conn.close()
202
203 Compatibility warning: The act of calling a stored procedure
204 itself creates an empty result set. This appears after any
205 result sets generated by the procedure. This is non-standard
206 behavior with respect to the DB-API. Be sure to use
207 :meth:`Cursor.nextset()` to advance through all result sets; otherwise
208 you may get disconnected.
209
210 :param str procname: name of procedure to execute on server
211 :param args: sequence of parameters to use with procedure
212 :returns: the original args.
213
214 .. method:: fetchone()
215
216 Fetch the next row :ref:`coroutine <coroutine>`.
217
218 .. method:: fetchmany(size=None)
219
220 :ref:`Coroutine <coroutine>` the next set of rows of a query result,
221 returning a list of tuples. When no more rows are available, it
222 returns an empty list.
223
224 The number of rows to fetch per call is specified by the parameter.
225 If it is not given, the cursor's :attr:`Cursor.arraysize` determines
226 the number of rows to be fetched. The method should try to fetch as
227 many rows as indicated by the size parameter. If this is not possible
228 due to the specified number of rows not being available, fewer rows
229 may be returned ::
230
231 cursor = await connection.cursor()
232 await cursor.execute("SELECT * FROM test;")
233 r = cursor.fetchmany(2)
234 print(r)
235 # [(1, 100, "abc'def"), (2, None, 'dada')]
236 r = await cursor.fetchmany(2)
237 print(r)
238 # [(3, 42, 'bar')]
239 r = await cursor.fetchmany(2)
240 print(r)
241 # []
242
243 :param int size: number of rows to return
244 :returns list: of fetched rows
245
246 .. method:: fetchall()
247
248 :ref:`Coroutine <coroutine>` returns all rows of a query result set::
249
250 await cursor.execute("SELECT * FROM test;")
251 r = await cursor.fetchall()
252 print(r)
253 # [(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]
254
255 :returns list: list of fetched rows
256
257 .. method:: scroll(value, mode='relative')
258
259 Scroll the cursor in the result set to a new position according
260 to mode. This method is :ref:`coroutine <coroutine>`.
261
262 If mode is ``relative`` (default), value is taken as offset to the
263 current position in the result set, if set to ``absolute``, value
264 states an absolute target position. An IndexError should be raised in
265 case a scroll operation would leave the result set. In this case,
266 the cursor position is left undefined (ideal would be to
267 not move the cursor at all).
268
269 .. note::
270
271 According to the :term:`DBAPI`, the exception raised for a cursor out
272 of bound should have been :exc:`IndexError`. The best option is
273 probably to catch both exceptions in your code::
274
275 try:
276 await cur.scroll(1000 * 1000)
277 except (ProgrammingError, IndexError), exc:
278 deal_with_it(exc)
279
280 :param int value: move cursor to next position according to mode.
281 :param str mode: scroll mode, possible modes: `relative` and `absolute`
282
283
284 .. class:: DictCursor
285
286 A cursor which returns results as a dictionary. All methods and arguments
287 same as :class:`Cursor`, see example::
288
289 import asyncio
290 import aiomysql
291
292 loop = asyncio.get_event_loop()
293
294 async def test_example():
295 conn = await aiomysql.connect(host='127.0.0.1', port=3306,
296 user='root', password='',
297 db='mysql', loop=loop)
298
299 # create dict cursor
300 cursor = await conn.cursor(aiomysql.DictCursor)
301
302 # execute sql query
303 await cursor.execute(
304 "SELECT * from people where name='bob'")
305
306 # fetch all results
307 r = await cursor.fetchone()
308 print(r)
309 # {'age': 20, 'DOB': datetime.datetime(1990, 2, 6, 23, 4, 56),
310 # 'name': 'bob'}
311
312 loop.run_until_complete(test_example())
313
314 You can customize your dictionary, see example::
315
316 import asyncio
317 import aiomysql
318
319 class AttrDict(dict):
320 """Dict that can get attribute by dot, and doesn't raise KeyError"""
321
322 def __getattr__(self, name):
323 try:
324 return self[name]
325 except KeyError:
326 return None
327
328 class AttrDictCursor(aiomysql.DictCursor):
329 dict_type = AttrDict
330
331 loop = asyncio.get_event_loop()
332
333 async def test_example():
334 conn = await aiomysql.connect(host='127.0.0.1', port=3306,
335 user='root', password='',
336 db='mysql', loop=loop)
337
338 # create your dict cursor
339 cursor = await conn.cursor(AttrDictCursor)
340
341 # execute sql query
342 await cursor.execute(
343 "SELECT * from people where name='bob'")
344
345 # fetch all results
346 r = await cursor.fetchone()
347 print(r)
348 # {'age': 20, 'DOB': datetime.datetime(1990, 2, 6, 23, 4, 56),
349 # 'name': 'bob'}
350 print(r.age)
351 # 20
352 print(r.foo)
353 # None
354
355 loop.run_until_complete(test_example())
356
357
358 .. class:: SSCursor
359
360 Unbuffered Cursor, mainly useful for queries that return a lot of
361 data, or for connections to remote servers over a slow network.
362
363 Instead of copying every row of data into a buffer, this will fetch
364 rows as needed. The upside of this, is the client uses much less memory,
365 and rows are returned much faster when traveling over a slow network,
366 or if the result set is very big.
367
368 There are limitations, though. The MySQL protocol doesn't support
369 returning the total number of rows, so the only way to tell how many rows
370 there are is to iterate over every row returned. Also, it currently isn't
371 possible to scroll backwards, as only the current row is held in memory.
372 All methods are the same as in :class:`Cursor` but with different
373 behaviour.
374
375 .. method:: fetchall()
376 Same as :meth:`Cursor.fetchall` :ref:`coroutine <coroutine>`,
377 useless for large queries, as all rows fetched one by one.
378
379 .. method:: fetchmany(size=None, mode='relative')
380 Same as :meth:`Cursor.fetchall`, but each row fetched one by one.
381
382 .. method:: scroll(size=None)
383 Same as :meth:`Cursor.scroll`, but move cursor on server side one by
384 one. If you want to move 20 rows forward scroll will make 20 queries
385 to move cursor. Currently only forward scrolling is supported.
386
387
388 .. class:: SSDictCursor
389
390 An unbuffered cursor, which returns results as a dictionary.