633 lines
19 KiB
ReStructuredText
633 lines
19 KiB
ReStructuredText
.. _aiomysql-sa:
|
|
|
|
:mod:`aiomysql.sa` --- support for SQLAlchemy functional SQL layer
|
|
==================================================================
|
|
|
|
.. module:: aiomysql.sa
|
|
:synopsis: support for SQLAlchemy functional SQL layer
|
|
.. currentmodule:: aiomysql.sa
|
|
|
|
|
|
Intro
|
|
-----
|
|
.. note:: :term:`sqlalchemy` support ported from aiopg_, so api should be
|
|
very familiar for aiopg_ user.
|
|
|
|
While :ref:`core API <aiomysql-index>` provides a core support for
|
|
access to :term:`MySQL` database, manipulations with raw SQL strings
|
|
too annoying.
|
|
|
|
Fortunately we can use excellent :ref:`aiomysql <aiomysql-index>` as **SQL query builder**.
|
|
|
|
|
|
Example::
|
|
|
|
import asyncio
|
|
import sqlalchemy as sa
|
|
|
|
from aiomysql.sa import create_engine
|
|
|
|
|
|
metadata = sa.MetaData()
|
|
|
|
tbl = sa.Table(
|
|
"tbl",
|
|
metadata,
|
|
sa.Column("id", sa.Integer, primary_key=True),
|
|
sa.Column("val", sa.String(255)),
|
|
)
|
|
|
|
|
|
async def go():
|
|
engine = await create_engine(
|
|
user="root",
|
|
db="test_pymysql",
|
|
host="127.0.0.1",
|
|
password="",
|
|
)
|
|
|
|
async with engine.acquire() as conn:
|
|
async with conn.begin() as transaction:
|
|
await conn.execute(tbl.insert().values(val="abc"))
|
|
await transaction.commit()
|
|
|
|
res = await conn.execute(tbl.select())
|
|
async for row in res:
|
|
print(row.id, row.val)
|
|
|
|
engine.close()
|
|
await engine.wait_closed()
|
|
|
|
|
|
asyncio.run(go())
|
|
|
|
|
|
So you can execute SQL query built by
|
|
``tbl.insert().values(val='abc')`` or ``tbl.select()`` expressions.
|
|
|
|
:term:`sqlalchemy` has rich and very powerful set of SQL construction
|
|
functions, please read :ref:`tutorial <aiomysql-index>` for full list
|
|
of available operations.
|
|
|
|
Also we provide SQL transactions support. Please take a look on
|
|
:meth:`SAConnection.begin` method and family.
|
|
|
|
|
|
.. _aiopg: https://github.com/aio-libs/aiopg
|
|
|
|
Engine
|
|
------
|
|
|
|
.. function:: create_engine(*, minsize=1, maxsize=10, loop=None, \
|
|
dialect=dialect, **kwargs)
|
|
|
|
A :ref:`coroutine <coroutine>` for :class:`Engine` creation.
|
|
|
|
Returns :class:`Engine` instance with embedded connection pool.
|
|
|
|
The pool has *minsize* opened connections to :term:`MySQL` server.
|
|
|
|
At *kwargs* function accepts all parameters that
|
|
:func:`aiomysql.connect` does.
|
|
|
|
|
|
.. data:: dialect
|
|
|
|
An instance of :term:`SQLAlchemy` dialect set up for :term:`pymysql` usage.
|
|
|
|
An :class:`sqlalchemy.engine.interfaces.Dialect` instance.
|
|
|
|
.. seealso:: :mod:`sqlalchemy.dialects.mysql.pymysql`
|
|
PyMySQL dialect.
|
|
|
|
|
|
.. class:: Engine
|
|
|
|
Connects a :class:`aiomysql.Pool` and
|
|
:class:`sqlalchemy.engine.interfaces.Dialect` together to provide a
|
|
source of database connectivity and behavior.
|
|
|
|
An :class:`Engine` object is instantiated publicly using the
|
|
:func:`create_engine` coroutine.
|
|
|
|
|
|
.. attribute:: dialect
|
|
|
|
A :class:`sqlalchemy.engine.interfaces.Dialect` for the engine,
|
|
readonly property.
|
|
|
|
.. attribute:: name
|
|
|
|
A name of the dialect, readonly property.
|
|
|
|
.. attribute:: driver
|
|
|
|
A driver of the dialect, readonly property.
|
|
|
|
.. attribute:: minsize
|
|
|
|
A minimal size of the pool (*read-only*), ``1`` by default.
|
|
|
|
.. attribute:: maxsize
|
|
|
|
A maximal size of the pool (*read-only*), ``10`` by default.
|
|
|
|
.. attribute:: size
|
|
|
|
A current size of the pool (*readonly*). Includes used and free
|
|
connections.
|
|
|
|
.. attribute:: freesize
|
|
|
|
A count of free connections in the pool (*readonly*).
|
|
|
|
.. method:: close()
|
|
|
|
Close engine.
|
|
|
|
Mark all engine connections to be closed on getting back to engine.
|
|
Closed engine doesn't allow to acquire new connections.
|
|
|
|
If you want to wait for actual closing of acquired connection please
|
|
call :meth:`wait_closed` after :meth:`close`.
|
|
|
|
.. warning:: The method is not a :ref:`coroutine <coroutine>`.
|
|
|
|
.. method:: terminate()
|
|
|
|
Terminate engine.
|
|
|
|
Close engine's pool with instantly closing all acquired connections
|
|
also.
|
|
|
|
:meth:`wait_closed` should be called after :meth:`terminate` for
|
|
waiting for actual finishing.
|
|
|
|
.. warning:: The method is not a :ref:`coroutine <coroutine>`.
|
|
|
|
.. method:: wait_closed()
|
|
|
|
A :ref:`coroutine <coroutine>` that waits for releasing and
|
|
closing all acquired connections.
|
|
|
|
Should be called after :meth:`close` for waiting for actual engine
|
|
closing.
|
|
|
|
.. method:: acquire()
|
|
|
|
Get a connection from pool.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
Returns a :class:`SAConnection` instance.
|
|
|
|
.. method:: release()
|
|
|
|
Revert back connection *conn* to pool.
|
|
|
|
.. warning:: The method is not a :ref:`coroutine <coroutine>`.
|
|
|
|
Connection
|
|
----------
|
|
|
|
.. class:: SAConnection
|
|
|
|
A wrapper for :class:`aiomysql.Connection` instance.
|
|
|
|
The class provides methods for executing *SQL queries* and working with
|
|
*SQL transactions*.
|
|
|
|
.. method:: execute(query, *multiparams, **params)
|
|
|
|
Executes a *SQL* *query* with optional parameters.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
:param query: a SQL query string or any :term:`sqlalchemy`
|
|
expression (see :ref:`aiomysql core <aiomysql-index>`)
|
|
|
|
:param \*multiparams/\**params: represent bound parameter values
|
|
to be used in the execution. Typically, the format is either a
|
|
dictionary passed to \*multiparams::
|
|
|
|
await conn.execute(
|
|
table.insert(),
|
|
{"id":1, "value":"v1"}
|
|
)
|
|
|
|
...or individual key/values interpreted by \**params::
|
|
|
|
await conn.execute(
|
|
table.insert(), id=1, value="v1"
|
|
)
|
|
|
|
In the case that a plain SQL string is passed, a tuple or
|
|
individual values in \*multiparams may be passed::
|
|
|
|
await conn.execute(
|
|
"INSERT INTO table (id, value) VALUES (%d, %s)",
|
|
(1, "v1")
|
|
)
|
|
|
|
await conn.execute(
|
|
"INSERT INTO table (id, value) VALUES (%s, %s)",
|
|
1, "v1"
|
|
)
|
|
|
|
:returns: :class:`ResultProxy` instance with results of SQL
|
|
query execution.
|
|
|
|
.. method:: scalar(query, *multiparams, **params)
|
|
|
|
Executes a *SQL* *query* and returns a scalar value.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
.. seealso:: :meth:`SAConnection.execute` and :meth:`ResultProxy.scalar`.
|
|
|
|
.. attribute:: closed
|
|
|
|
The readonly property that returns ``True`` if connections is closed.
|
|
|
|
.. method:: begin()
|
|
|
|
Begin a transaction and return a transaction handle.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
The returned object is an instance of :class:`.Transaction`.
|
|
This object represents the "scope" of the transaction,
|
|
which completes when either the :meth:`.Transaction.rollback`
|
|
or :meth:`.Transaction.commit` method is called.
|
|
|
|
Nested calls to :meth:`.begin` on the same :class:`.SAConnection`
|
|
will return new :class:`.Transaction` objects that represent
|
|
an emulated transaction within the scope of the enclosing
|
|
transaction, that is::
|
|
|
|
trans = await conn.begin() # outermost transaction
|
|
trans2 = await conn.begin() # "inner"
|
|
await trans2.commit() # does nothing
|
|
await trans.commit() # actually commits
|
|
|
|
Calls to :meth:`.Transaction.commit` only have an effect
|
|
when invoked via the outermost :class:`.Transaction` object, though the
|
|
:meth:`.Transaction.rollback` method of any of the
|
|
:class:`.Transaction` objects will roll back the
|
|
transaction.
|
|
|
|
.. seealso::
|
|
|
|
:meth:`.SAConnection.begin_nested` - use a SAVEPOINT
|
|
|
|
:meth:`.SAConnection.begin_twophase` - use a two phase (XA)
|
|
transaction
|
|
|
|
.. method:: begin_nested()
|
|
|
|
Begin a nested transaction and return a transaction handle.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
The returned object is an instance of :class:`.NestedTransaction`.
|
|
|
|
Any transaction in the hierarchy may ``commit`` and
|
|
``rollback``, however the outermost transaction still controls
|
|
the overall ``commit`` or ``rollback`` of the transaction of a
|
|
whole. It utilizes SAVEPOINT facility of :term:`MySQL` server.
|
|
|
|
.. seealso::
|
|
|
|
:meth:`.SAConnection.begin`, :meth:`.SAConnection.begin_twophase`.
|
|
|
|
.. method:: begin_twophase(xid=None)
|
|
|
|
Begin a two-phase or XA transaction and return a transaction
|
|
handle.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
The returned object is an instance of
|
|
:class:`.TwoPhaseTransaction`, which in addition to the methods
|
|
provided by :class:`.Transaction`, also provides a
|
|
:meth:`~.TwoPhaseTransaction.prepare` method.
|
|
|
|
:param xid: the two phase transaction id. If not supplied, a
|
|
random id will be generated.
|
|
|
|
.. seealso::
|
|
:meth:`.SAConnection.begin`, :meth:`.SAConnection.begin_twophase`.
|
|
|
|
.. method:: recover_twophase()
|
|
|
|
Return a list of prepared twophase transaction ids.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
.. method:: rollback_prepared(xid)
|
|
|
|
Rollback prepared twophase transaction *xid*.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
.. method:: commit_prepared(xid)
|
|
|
|
Commit prepared twophase transaction *xid*.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
.. attribute:: in_transaction
|
|
|
|
The readonly property that returns ``True`` if a transaction is
|
|
in progress.
|
|
|
|
.. method:: close()
|
|
|
|
Close this :class:`SAConnection`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
This results in a release of the underlying database
|
|
resources, that is, the :class:`aiomysql.Connection` referenced
|
|
internally. The :class:`aiomysql.Connection` is typically restored
|
|
back to the connection-holding :class:`aiomysql.Pool` referenced
|
|
by the :class:`.Engine` that produced this
|
|
:class:`SAConnection`. Any transactional state present on
|
|
the :class:`aiomysql.Connection` is also unconditionally released via
|
|
calling :meth:`Transaction.rollback` method.
|
|
|
|
After :meth:`~.SAConnection.close` is called, the
|
|
:class:`.SAConnection` is permanently in a closed state,
|
|
and will allow no further operations.
|
|
|
|
ResultProxy
|
|
-----------
|
|
|
|
.. class:: ResultProxy
|
|
|
|
Wraps a *DB-API like* :class:`Cursor` object to provide easier
|
|
access to row columns.
|
|
|
|
Individual columns may be accessed by their integer position,
|
|
case-sensitive column name, or by :class:`sqlalchemy.schema.Column``
|
|
object. e.g.::
|
|
|
|
async for row in conn.execute(...):
|
|
col1 = row[0] # access via integer position
|
|
col2 = row['col2'] # access via name
|
|
col3 = row[mytable.c.mycol] # access via Column object.
|
|
|
|
:class:`ResultProxy` also handles post-processing of result column
|
|
data using :class:`sqlalchemy.types.TypeEngine` objects, which are
|
|
referenced from the originating SQL statement that produced this
|
|
result set.
|
|
|
|
.. attribute:: dialect
|
|
|
|
The readonly property that returns
|
|
:class:`sqlalchemy.engine.interfaces.Dialect` dialect
|
|
for the :class:`ResultProxy` instance.
|
|
|
|
.. seealso:: :data:`dialect` global data.
|
|
|
|
.. method:: keys()
|
|
|
|
Return the current set of string keys for rows.
|
|
|
|
.. attribute:: rowcount
|
|
|
|
The readonly property that returns the 'rowcount' for this result.
|
|
|
|
The 'rowcount' reports the number of rows *matched*
|
|
by the WHERE criterion of an UPDATE or DELETE statement.
|
|
|
|
.. note::
|
|
|
|
Notes regarding :attr:`ResultProxy.rowcount`:
|
|
|
|
* This attribute returns the number of rows *matched*,
|
|
which is not necessarily the same as the number of rows
|
|
that were actually *modified* - an UPDATE statement, for example,
|
|
may have no net change on a given row if the SET values
|
|
given are the same as those present in the row already.
|
|
Such a row would be matched but not modified.
|
|
* :attr:`ResultProxy.rowcount` is *only* useful in conjunction
|
|
with an UPDATE or DELETE statement. Contrary to what the Python
|
|
DBAPI says, it does *not* return the
|
|
number of rows available from the results of a SELECT statement
|
|
as DBAPIs cannot support this functionality when rows are
|
|
unbuffered.
|
|
* Statements that use RETURNING does not return a correct
|
|
rowcount.
|
|
|
|
.. attribute:: lastrowid
|
|
|
|
Returns the 'lastrowid' accessor on the DBAPI cursor.
|
|
|
|
value generated for an *AUTO_INCREMENT* column by the previous INSERT
|
|
or UPDATE statement or None when there is no such value available. For
|
|
example, if you perform an *INSERT* into a table that contains an
|
|
AUTO_INCREMENT column, `lastrowid` returns the *AUTO_INCREMENT* value
|
|
for the new row.
|
|
|
|
.. attribute:: returns_rows
|
|
|
|
A readonly property that returns ``True`` if this
|
|
:class:`ResultProxy` returns rows.
|
|
|
|
I.e. if it is legal to call the methods
|
|
:meth:`ResultProxy.fetchone`,
|
|
:meth:`ResultProxy.fetchmany`,
|
|
:meth:`ResultProxy.fetchall`.
|
|
|
|
.. attribute:: closed
|
|
|
|
Return ``True`` if this :class:`ResultProxy` is closed (no
|
|
pending rows in underlying cursor).
|
|
|
|
.. method:: close()
|
|
|
|
Close this :class:`ResultProxy`.
|
|
|
|
Closes the underlying :class:`aiomysql.Cursor` corresponding to the
|
|
execution.
|
|
|
|
Note that any data cached within this :class:`ResultProxy` is
|
|
still available. For some types of results, this may include
|
|
buffered rows.
|
|
|
|
This method is called automatically when:
|
|
|
|
* all result rows are exhausted using the fetchXXX() methods.
|
|
* cursor.description is None.
|
|
|
|
.. method:: fetchall()
|
|
|
|
Fetch all rows, just like :meth:`aiomysql.Cursor.fetchall`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
The connection is closed after the call.
|
|
|
|
Returns a list of :class:`RowProxy`.
|
|
|
|
.. method:: fetchone()
|
|
|
|
Fetch one row, just like :meth:`aiomysql.Cursor.fetchone`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
If a row is present, the cursor remains open after this is called.
|
|
|
|
Else the cursor is automatically closed and ``None`` is returned.
|
|
|
|
Returns an :class:`RowProxy` instance or ``None``.
|
|
|
|
.. method:: fetchmany(size=None)
|
|
|
|
Fetch many rows, just like :meth:`aiomysql.Cursor.fetchmany`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
If rows are present, the cursor remains open after this is called.
|
|
|
|
Else the cursor is automatically closed and an empty list is returned.
|
|
|
|
Returns a list of :class:`RowProxy`.
|
|
|
|
.. method:: first()
|
|
|
|
Fetch the first row and then close the result set unconditionally.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
Returns ``None`` if no row is present or an :class:`RowProxy` instance.
|
|
|
|
.. method:: scalar()
|
|
|
|
Fetch the first column of the first row, and close the result set.
|
|
|
|
Returns ``None`` if no row is present or an :class:`RowProxy` instance.
|
|
|
|
|
|
.. class:: RowProxy
|
|
|
|
A :class:`collections.abc.Mapping` for representing a row in query result.
|
|
|
|
Keys are column names, values are result values.
|
|
|
|
Individual columns may be accessed by their integer position,
|
|
case-sensitive column name, or by :class:`sqlalchemy.schema.Column``
|
|
object.
|
|
|
|
Has overloaded operators ``__eq__`` and ``__ne__`` for comparing two rows.
|
|
|
|
The :class:`RowProxy` is *not hashable*.
|
|
|
|
..method:: as_tuple()
|
|
|
|
Return a tuple with values from :meth:`RowProxy.values`.
|
|
|
|
Transaction objects
|
|
-------------------
|
|
|
|
.. class:: Transaction
|
|
|
|
Represent a database transaction in progress.
|
|
|
|
The :class:`Transaction` object is procured by
|
|
calling the :meth:`SAConnection.begin` method of
|
|
:class:`SAConnection`::
|
|
|
|
async with engine.acquire() as conn:
|
|
trans = await conn.begin()
|
|
try:
|
|
await conn.execute("insert into x (a, b) values (1, 2)")
|
|
except Exception:
|
|
await trans.rollback()
|
|
else:
|
|
await trans.commit()
|
|
|
|
The object provides :meth:`.rollback` and :meth:`.commit`
|
|
methods in order to control transaction boundaries.
|
|
|
|
.. seealso::
|
|
|
|
:meth:`SAConnection.begin`, :meth:`SAConnection.begin_twophase`,
|
|
:meth:`SAConnection.begin_nested`.
|
|
|
|
.. attribute:: is_active
|
|
|
|
A readonly property that returns ``True`` if a transaction is active.
|
|
|
|
.. attribute:: connection
|
|
|
|
A readonly property that returns :class:`SAConnection` for transaction.
|
|
|
|
.. method:: close()
|
|
|
|
Close this :class:`Transaction`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
If this transaction is the base transaction in a begin/commit
|
|
nesting, the transaction will :meth:`Transaction.rollback`.
|
|
Otherwise, the method returns.
|
|
|
|
This is used to cancel a :class:`Transaction` without affecting
|
|
the scope of an enclosing transaction.
|
|
|
|
.. method:: rollback()
|
|
|
|
Roll back this :class:`Transaction`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
.. method:: commit()
|
|
|
|
Commit this :class:`Transaction`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
|
|
.. class:: NestedTransaction
|
|
|
|
Represent a 'nested', or SAVEPOINT transaction.
|
|
|
|
A new :class:`NestedTransaction` object may be procured
|
|
using the :meth:`SAConnection.begin_nested` method.
|
|
|
|
The interface is the same as that of :class:`Transaction`.
|
|
|
|
.. seealso:: `SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT`__
|
|
on :term:`MySQL`:
|
|
|
|
.. __: https://dev.mysql.com/doc/refman/8.4/en/savepoint.html
|
|
|
|
|
|
.. class:: TwoPhaseTransaction
|
|
|
|
Represent a two-phase transaction.
|
|
|
|
A new :class:`TwoPhaseTransaction` object may be procured
|
|
using the :meth:`SAConnection.begin_twophase` method.
|
|
|
|
The interface is the same as that of :class:`Transaction`
|
|
with the addition of the :meth:`TwoPhaseTransaction.prepare` method.
|
|
|
|
.. attribute:: xid
|
|
|
|
A readonly property that returns twophase transaction id.
|
|
|
|
.. method:: prepare()
|
|
|
|
Prepare this :class:`TwoPhaseTransaction`.
|
|
|
|
This method is a :ref:`coroutine <coroutine>`.
|
|
|
|
After a `PREPARE`, the transaction can be committed.
|
|
|
|
.. seealso:: :term:`MySQL` commands for two phase transactions:
|
|
|
|
https://dev.mysql.com/doc/refman/8.4/en/xa-statements.html
|