Debian Codegraph @

docs/sa.rst

raw
1 .. _aiomysql-sa:
2
3 :mod:`aiomysql.sa` --- support for SQLAlchemy functional SQL layer
4 ==================================================================
5
6 .. module:: aiomysql.sa
7 :synopsis: support for SQLAlchemy functional SQL layer
8 .. currentmodule:: aiomysql.sa
9
10
11 Intro
12 -----
13 .. note:: :term:`sqlalchemy` support ported from aiopg_, so api should be
14 very familiar for aiopg_ user.
15
16 While :ref:`core API <aiomysql-index>` provides a core support for
17 access to :term:`MySQL` database, manipulations with raw SQL strings
18 too annoying.
19
20 Fortunately we can use excellent :ref:`aiomysql <aiomysql-index>` as **SQL query builder**.
21
22
23 Example::
24
25 import asyncio
26 import sqlalchemy as sa
27
28 from aiomysql.sa import create_engine
29
30
31 metadata = sa.MetaData()
32
33 tbl = sa.Table(
34 "tbl",
35 metadata,
36 sa.Column("id", sa.Integer, primary_key=True),
37 sa.Column("val", sa.String(255)),
38 )
39
40
41 async def go():
42 engine = await create_engine(
43 user="root",
44 db="test_pymysql",
45 host="127.0.0.1",
46 password="",
47 )
48
49 async with engine.acquire() as conn:
50 async with conn.begin() as transaction:
51 await conn.execute(tbl.insert().values(val="abc"))
52 await transaction.commit()
53
54 res = await conn.execute(tbl.select())
55 async for row in res:
56 print(row.id, row.val)
57
58 engine.close()
59 await engine.wait_closed()
60
61
62 asyncio.run(go())
63
64
65 So you can execute SQL query built by
66 ``tbl.insert().values(val='abc')`` or ``tbl.select()`` expressions.
67
68 :term:`sqlalchemy` has rich and very powerful set of SQL construction
69 functions, please read :ref:`tutorial <aiomysql-index>` for full list
70 of available operations.
71
72 Also we provide SQL transactions support. Please take a look on
73 :meth:`SAConnection.begin` method and family.
74
75
76 .. _aiopg: https://github.com/aio-libs/aiopg
77
78 Engine
79 ------
80
81 .. function:: create_engine(*, minsize=1, maxsize=10, loop=None, \
82 dialect=dialect, **kwargs)
83
84 A :ref:`coroutine <coroutine>` for :class:`Engine` creation.
85
86 Returns :class:`Engine` instance with embedded connection pool.
87
88 The pool has *minsize* opened connections to :term:`MySQL` server.
89
90 At *kwargs* function accepts all parameters that
91 :func:`aiomysql.connect` does.
92
93
94 .. data:: dialect
95
96 An instance of :term:`SQLAlchemy` dialect set up for :term:`pymysql` usage.
97
98 An :class:`sqlalchemy.engine.interfaces.Dialect` instance.
99
100 .. seealso:: :mod:`sqlalchemy.dialects.mysql.pymysql`
101 PyMySQL dialect.
102
103
104 .. class:: Engine
105
106 Connects a :class:`aiomysql.Pool` and
107 :class:`sqlalchemy.engine.interfaces.Dialect` together to provide a
108 source of database connectivity and behavior.
109
110 An :class:`Engine` object is instantiated publicly using the
111 :func:`create_engine` coroutine.
112
113
114 .. attribute:: dialect
115
116 A :class:`sqlalchemy.engine.interfaces.Dialect` for the engine,
117 readonly property.
118
119 .. attribute:: name
120
121 A name of the dialect, readonly property.
122
123 .. attribute:: driver
124
125 A driver of the dialect, readonly property.
126
127 .. attribute:: minsize
128
129 A minimal size of the pool (*read-only*), ``1`` by default.
130
131 .. attribute:: maxsize
132
133 A maximal size of the pool (*read-only*), ``10`` by default.
134
135 .. attribute:: size
136
137 A current size of the pool (*readonly*). Includes used and free
138 connections.
139
140 .. attribute:: freesize
141
142 A count of free connections in the pool (*readonly*).
143
144 .. method:: close()
145
146 Close engine.
147
148 Mark all engine connections to be closed on getting back to engine.
149 Closed engine doesn't allow to acquire new connections.
150
151 If you want to wait for actual closing of acquired connection please
152 call :meth:`wait_closed` after :meth:`close`.
153
154 .. warning:: The method is not a :ref:`coroutine <coroutine>`.
155
156 .. method:: terminate()
157
158 Terminate engine.
159
160 Close engine's pool with instantly closing all acquired connections
161 also.
162
163 :meth:`wait_closed` should be called after :meth:`terminate` for
164 waiting for actual finishing.
165
166 .. warning:: The method is not a :ref:`coroutine <coroutine>`.
167
168 .. method:: wait_closed()
169
170 A :ref:`coroutine <coroutine>` that waits for releasing and
171 closing all acquired connections.
172
173 Should be called after :meth:`close` for waiting for actual engine
174 closing.
175
176 .. method:: acquire()
177
178 Get a connection from pool.
179
180 This method is a :ref:`coroutine <coroutine>`.
181
182 Returns a :class:`SAConnection` instance.
183
184 .. method:: release()
185
186 Revert back connection *conn* to pool.
187
188 .. warning:: The method is not a :ref:`coroutine <coroutine>`.
189
190 Connection
191 ----------
192
193 .. class:: SAConnection
194
195 A wrapper for :class:`aiomysql.Connection` instance.
196
197 The class provides methods for executing *SQL queries* and working with
198 *SQL transactions*.
199
200 .. method:: execute(query, *multiparams, **params)
201
202 Executes a *SQL* *query* with optional parameters.
203
204 This method is a :ref:`coroutine <coroutine>`.
205
206 :param query: a SQL query string or any :term:`sqlalchemy`
207 expression (see :ref:`aiomysql core <aiomysql-index>`)
208
209 :param \*multiparams/\**params: represent bound parameter values
210 to be used in the execution. Typically, the format is either a
211 dictionary passed to \*multiparams::
212
213 await conn.execute(
214 table.insert(),
215 {"id":1, "value":"v1"}
216 )
217
218 ...or individual key/values interpreted by \**params::
219
220 await conn.execute(
221 table.insert(), id=1, value="v1"
222 )
223
224 In the case that a plain SQL string is passed, a tuple or
225 individual values in \*multiparams may be passed::
226
227 await conn.execute(
228 "INSERT INTO table (id, value) VALUES (%d, %s)",
229 (1, "v1")
230 )
231
232 await conn.execute(
233 "INSERT INTO table (id, value) VALUES (%s, %s)",
234 1, "v1"
235 )
236
237 :returns: :class:`ResultProxy` instance with results of SQL
238 query execution.
239
240 .. method:: scalar(query, *multiparams, **params)
241
242 Executes a *SQL* *query* and returns a scalar value.
243
244 This method is a :ref:`coroutine <coroutine>`.
245
246 .. seealso:: :meth:`SAConnection.execute` and :meth:`ResultProxy.scalar`.
247
248 .. attribute:: closed
249
250 The readonly property that returns ``True`` if connections is closed.
251
252 .. method:: begin()
253
254 Begin a transaction and return a transaction handle.
255
256 This method is a :ref:`coroutine <coroutine>`.
257
258 The returned object is an instance of :class:`.Transaction`.
259 This object represents the "scope" of the transaction,
260 which completes when either the :meth:`.Transaction.rollback`
261 or :meth:`.Transaction.commit` method is called.
262
263 Nested calls to :meth:`.begin` on the same :class:`.SAConnection`
264 will return new :class:`.Transaction` objects that represent
265 an emulated transaction within the scope of the enclosing
266 transaction, that is::
267
268 trans = await conn.begin() # outermost transaction
269 trans2 = await conn.begin() # "inner"
270 await trans2.commit() # does nothing
271 await trans.commit() # actually commits
272
273 Calls to :meth:`.Transaction.commit` only have an effect
274 when invoked via the outermost :class:`.Transaction` object, though the
275 :meth:`.Transaction.rollback` method of any of the
276 :class:`.Transaction` objects will roll back the
277 transaction.
278
279 .. seealso::
280
281 :meth:`.SAConnection.begin_nested` - use a SAVEPOINT
282
283 :meth:`.SAConnection.begin_twophase` - use a two phase (XA)
284 transaction
285
286 .. method:: begin_nested()
287
288 Begin a nested transaction and return a transaction handle.
289
290 This method is a :ref:`coroutine <coroutine>`.
291
292 The returned object is an instance of :class:`.NestedTransaction`.
293
294 Any transaction in the hierarchy may ``commit`` and
295 ``rollback``, however the outermost transaction still controls
296 the overall ``commit`` or ``rollback`` of the transaction of a
297 whole. It utilizes SAVEPOINT facility of :term:`MySQL` server.
298
299 .. seealso::
300
301 :meth:`.SAConnection.begin`, :meth:`.SAConnection.begin_twophase`.
302
303 .. method:: begin_twophase(xid=None)
304
305 Begin a two-phase or XA transaction and return a transaction
306 handle.
307
308 This method is a :ref:`coroutine <coroutine>`.
309
310 The returned object is an instance of
311 :class:`.TwoPhaseTransaction`, which in addition to the methods
312 provided by :class:`.Transaction`, also provides a
313 :meth:`~.TwoPhaseTransaction.prepare` method.
314
315 :param xid: the two phase transaction id. If not supplied, a
316 random id will be generated.
317
318 .. seealso::
319 :meth:`.SAConnection.begin`, :meth:`.SAConnection.begin_twophase`.
320
321 .. method:: recover_twophase()
322
323 Return a list of prepared twophase transaction ids.
324
325 This method is a :ref:`coroutine <coroutine>`.
326
327 .. method:: rollback_prepared(xid)
328
329 Rollback prepared twophase transaction *xid*.
330
331 This method is a :ref:`coroutine <coroutine>`.
332
333 .. method:: commit_prepared(xid)
334
335 Commit prepared twophase transaction *xid*.
336
337 This method is a :ref:`coroutine <coroutine>`.
338
339 .. attribute:: in_transaction
340
341 The readonly property that returns ``True`` if a transaction is
342 in progress.
343
344 .. method:: close()
345
346 Close this :class:`SAConnection`.
347
348 This method is a :ref:`coroutine <coroutine>`.
349
350 This results in a release of the underlying database
351 resources, that is, the :class:`aiomysql.Connection` referenced
352 internally. The :class:`aiomysql.Connection` is typically restored
353 back to the connection-holding :class:`aiomysql.Pool` referenced
354 by the :class:`.Engine` that produced this
355 :class:`SAConnection`. Any transactional state present on
356 the :class:`aiomysql.Connection` is also unconditionally released via
357 calling :meth:`Transaction.rollback` method.
358
359 After :meth:`~.SAConnection.close` is called, the
360 :class:`.SAConnection` is permanently in a closed state,
361 and will allow no further operations.
362
363 ResultProxy
364 -----------
365
366 .. class:: ResultProxy
367
368 Wraps a *DB-API like* :class:`Cursor` object to provide easier
369 access to row columns.
370
371 Individual columns may be accessed by their integer position,
372 case-sensitive column name, or by :class:`sqlalchemy.schema.Column``
373 object. e.g.::
374
375 async for row in conn.execute(...):
376 col1 = row[0] # access via integer position
377 col2 = row['col2'] # access via name
378 col3 = row[mytable.c.mycol] # access via Column object.
379
380 :class:`ResultProxy` also handles post-processing of result column
381 data using :class:`sqlalchemy.types.TypeEngine` objects, which are
382 referenced from the originating SQL statement that produced this
383 result set.
384
385 .. attribute:: dialect
386
387 The readonly property that returns
388 :class:`sqlalchemy.engine.interfaces.Dialect` dialect
389 for the :class:`ResultProxy` instance.
390
391 .. seealso:: :data:`dialect` global data.
392
393 .. method:: keys()
394
395 Return the current set of string keys for rows.
396
397 .. attribute:: rowcount
398
399 The readonly property that returns the 'rowcount' for this result.
400
401 The 'rowcount' reports the number of rows *matched*
402 by the WHERE criterion of an UPDATE or DELETE statement.
403
404 .. note::
405
406 Notes regarding :attr:`ResultProxy.rowcount`:
407
408 * This attribute returns the number of rows *matched*,
409 which is not necessarily the same as the number of rows
410 that were actually *modified* - an UPDATE statement, for example,
411 may have no net change on a given row if the SET values
412 given are the same as those present in the row already.
413 Such a row would be matched but not modified.
414 * :attr:`ResultProxy.rowcount` is *only* useful in conjunction
415 with an UPDATE or DELETE statement. Contrary to what the Python
416 DBAPI says, it does *not* return the
417 number of rows available from the results of a SELECT statement
418 as DBAPIs cannot support this functionality when rows are
419 unbuffered.
420 * Statements that use RETURNING does not return a correct
421 rowcount.
422
423 .. attribute:: lastrowid
424
425 Returns the 'lastrowid' accessor on the DBAPI cursor.
426
427 value generated for an *AUTO_INCREMENT* column by the previous INSERT
428 or UPDATE statement or None when there is no such value available. For
429 example, if you perform an *INSERT* into a table that contains an
430 AUTO_INCREMENT column, `lastrowid` returns the *AUTO_INCREMENT* value
431 for the new row.
432
433 .. attribute:: returns_rows
434
435 A readonly property that returns ``True`` if this
436 :class:`ResultProxy` returns rows.
437
438 I.e. if it is legal to call the methods
439 :meth:`ResultProxy.fetchone`,
440 :meth:`ResultProxy.fetchmany`,
441 :meth:`ResultProxy.fetchall`.
442
443 .. attribute:: closed
444
445 Return ``True`` if this :class:`ResultProxy` is closed (no
446 pending rows in underlying cursor).
447
448 .. method:: close()
449
450 Close this :class:`ResultProxy`.
451
452 Closes the underlying :class:`aiomysql.Cursor` corresponding to the
453 execution.
454
455 Note that any data cached within this :class:`ResultProxy` is
456 still available. For some types of results, this may include
457 buffered rows.
458
459 This method is called automatically when:
460
461 * all result rows are exhausted using the fetchXXX() methods.
462 * cursor.description is None.
463
464 .. method:: fetchall()
465
466 Fetch all rows, just like :meth:`aiomysql.Cursor.fetchall`.
467
468 This method is a :ref:`coroutine <coroutine>`.
469
470 The connection is closed after the call.
471
472 Returns a list of :class:`RowProxy`.
473
474 .. method:: fetchone()
475
476 Fetch one row, just like :meth:`aiomysql.Cursor.fetchone`.
477
478 This method is a :ref:`coroutine <coroutine>`.
479
480 If a row is present, the cursor remains open after this is called.
481
482 Else the cursor is automatically closed and ``None`` is returned.
483
484 Returns an :class:`RowProxy` instance or ``None``.
485
486 .. method:: fetchmany(size=None)
487
488 Fetch many rows, just like :meth:`aiomysql.Cursor.fetchmany`.
489
490 This method is a :ref:`coroutine <coroutine>`.
491
492 If rows are present, the cursor remains open after this is called.
493
494 Else the cursor is automatically closed and an empty list is returned.
495
496 Returns a list of :class:`RowProxy`.
497
498 .. method:: first()
499
500 Fetch the first row and then close the result set unconditionally.
501
502 This method is a :ref:`coroutine <coroutine>`.
503
504 Returns ``None`` if no row is present or an :class:`RowProxy` instance.
505
506 .. method:: scalar()
507
508 Fetch the first column of the first row, and close the result set.
509
510 Returns ``None`` if no row is present or an :class:`RowProxy` instance.
511
512
513 .. class:: RowProxy
514
515 A :class:`collections.abc.Mapping` for representing a row in query result.
516
517 Keys are column names, values are result values.
518
519 Individual columns may be accessed by their integer position,
520 case-sensitive column name, or by :class:`sqlalchemy.schema.Column``
521 object.
522
523 Has overloaded operators ``__eq__`` and ``__ne__`` for comparing two rows.
524
525 The :class:`RowProxy` is *not hashable*.
526
527 ..method:: as_tuple()
528
529 Return a tuple with values from :meth:`RowProxy.values`.
530
531 Transaction objects
532 -------------------
533
534 .. class:: Transaction
535
536 Represent a database transaction in progress.
537
538 The :class:`Transaction` object is procured by
539 calling the :meth:`SAConnection.begin` method of
540 :class:`SAConnection`::
541
542 async with engine.acquire() as conn:
543 trans = await conn.begin()
544 try:
545 await conn.execute("insert into x (a, b) values (1, 2)")
546 except Exception:
547 await trans.rollback()
548 else:
549 await trans.commit()
550
551 The object provides :meth:`.rollback` and :meth:`.commit`
552 methods in order to control transaction boundaries.
553
554 .. seealso::
555
556 :meth:`SAConnection.begin`, :meth:`SAConnection.begin_twophase`,
557 :meth:`SAConnection.begin_nested`.
558
559 .. attribute:: is_active
560
561 A readonly property that returns ``True`` if a transaction is active.
562
563 .. attribute:: connection
564
565 A readonly property that returns :class:`SAConnection` for transaction.
566
567 .. method:: close()
568
569 Close this :class:`Transaction`.
570
571 This method is a :ref:`coroutine <coroutine>`.
572
573 If this transaction is the base transaction in a begin/commit
574 nesting, the transaction will :meth:`Transaction.rollback`.
575 Otherwise, the method returns.
576
577 This is used to cancel a :class:`Transaction` without affecting
578 the scope of an enclosing transaction.
579
580 .. method:: rollback()
581
582 Roll back this :class:`Transaction`.
583
584 This method is a :ref:`coroutine <coroutine>`.
585
586 .. method:: commit()
587
588 Commit this :class:`Transaction`.
589
590 This method is a :ref:`coroutine <coroutine>`.
591
592
593 .. class:: NestedTransaction
594
595 Represent a 'nested', or SAVEPOINT transaction.
596
597 A new :class:`NestedTransaction` object may be procured
598 using the :meth:`SAConnection.begin_nested` method.
599
600 The interface is the same as that of :class:`Transaction`.
601
602 .. seealso:: `SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT`__
603 on :term:`MySQL`:
604
605 .. __: https://dev.mysql.com/doc/refman/8.4/en/savepoint.html
606
607
608 .. class:: TwoPhaseTransaction
609
610 Represent a two-phase transaction.
611
612 A new :class:`TwoPhaseTransaction` object may be procured
613 using the :meth:`SAConnection.begin_twophase` method.
614
615 The interface is the same as that of :class:`Transaction`
616 with the addition of the :meth:`TwoPhaseTransaction.prepare` method.
617
618 .. attribute:: xid
619
620 A readonly property that returns twophase transaction id.
621
622 .. method:: prepare()
623
624 Prepare this :class:`TwoPhaseTransaction`.
625
626 This method is a :ref:`coroutine <coroutine>`.
627
628 After a `PREPARE`, the transaction can be committed.
629
630 .. seealso:: :term:`MySQL` commands for two phase transactions:
631
632 https://dev.mysql.com/doc/refman/8.4/en/xa-statements.html