aiomysql.sa
aiomysql.sa
While core API <aiomysql-core>
provides a core support for access to MySQL
database, manipulations with raw SQL strings too annoying.
Fortunately we can use excellent core_toplevel
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.
sqlalchemy
has rich and very powerful set of SQL construction functions, please read tutorial <core_toplevel>
for full list of available operations.
Also we provide SQL transactions support. Please take a look on SAConnection.begin
method and family.
create_engine(, minsize=1, maxsize=10, loop=None, dialect=dialect,*kwargs)
A coroutine <coroutine>
for Engine
creation.
Returns Engine
instance with embedded connection pool.
The pool has minsize opened connections to MySQL
server.
At kwargs function accepts all parameters that aiomysql.connect
does.
dialect
An instance of SQLAlchemy
dialect set up for pymysql
usage.
An sqlalchemy.engine.interfaces.Dialect
instance.
sqlalchemy.dialects.mysql.pymysql
PyMySQL dialect.
Connects a aiomysql.Pool
and sqlalchemy.engine.interfaces.Dialect
together to provide a source of database connectivity and behavior.
An Engine
object is instantiated publicly using the create_engine
coroutine.
dialect
A sqlalchemy.engine.interfaces.Dialect
for the engine, readonly property.
name
A name of the dialect, readonly property.
driver
A driver of the dialect, readonly property.
minsize
A minimal size of the pool (read-only), 1
by default.
maxsize
A maximal size of the pool (read-only), 10
by default.
size
A current size of the pool (readonly). Includes used and free connections.
freesize
A count of free connections in the pool (readonly).
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 wait_closed
after close
.
Warning
The method is not a
coroutine <coroutine>
.
terminate()
Terminate engine.
Close engine's pool with instantly closing all acquired connections also.
wait_closed
should be called after terminate
for waiting for actual finishing.
Warning
The method is not a
coroutine <coroutine>
.
wait_closed()
A coroutine <coroutine>
that waits for releasing and closing all acquired connections.
Should be called after close
for waiting for actual engine closing.
acquire()
Get a connection from pool.
This method is a coroutine <coroutine>
.
Returns a SAConnection
instance.
release()
Revert back connection conn to pool.
Warning
The method is not a
coroutine <coroutine>
.
A wrapper for aiomysql.Connection
instance.
The class provides methods for executing SQL queries and working with SQL transactions.
execute(query, multiparams,*params)
Executes a SQL query with optional parameters.
This method is a coroutine <coroutine>
.
- param query
a SQL query string or any
sqlalchemy
expression (seecore_toplevel
)- 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
ResultProxy
instance with results of SQL query execution.
scalar(query, multiparams,*params)
Executes a SQL query and returns a scalar value.
This method is a coroutine <coroutine>
.
SAConnection.execute
and ResultProxy.scalar
.
closed
The readonly property that returns True
if connections is closed.
begin()
Begin a transaction and return a transaction handle.
This method is a coroutine <coroutine>
.
The returned object is an instance of .Transaction
. This object represents the "scope" of the transaction, which completes when either the .Transaction.rollback
or .Transaction.commit
method is called.
Nested calls to .begin
on the same .SAConnection
will return new .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 .Transaction.commit
only have an effect when invoked via the outermost .Transaction
object, though the .Transaction.rollback
method of any of the .Transaction
objects will roll back the transaction.
.SAConnection.begin_nested
- use a SAVEPOINT
.SAConnection.begin_twophase
- use a two phase (XA)transaction
begin_nested()
Begin a nested transaction and return a transaction handle.
This method is a coroutine <coroutine>
.
The returned object is an instance of .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 MySQL
server.
.SAConnection.begin
, .SAConnection.begin_twophase
.
begin_twophase(xid=None)
Begin a two-phase or XA transaction and return a transaction handle.
This method is a coroutine <coroutine>
.
The returned object is an instance of .TwoPhaseTransaction
, which in addition to the methods provided by .Transaction
, also provides a ~.TwoPhaseTransaction.prepare
method.
- param xid
the two phase transaction id. If not supplied, a random id will be generated.
.SAConnection.begin
, .SAConnection.begin_twophase
.
recover_twophase()
Return a list of prepared twophase transaction ids.
This method is a coroutine <coroutine>
.
rollback_prepared(xid)
Rollback prepared twophase transaction xid.
This method is a coroutine <coroutine>
.
commit_prepared(xid)
Commit prepared twophase transaction xid.
This method is a coroutine <coroutine>
.
in_transaction
The readonly property that returns True
if a transaction is in progress.
close()
Close this SAConnection
.
This method is a coroutine <coroutine>
.
This results in a release of the underlying database resources, that is, the aiomysql.Connection
referenced internally. The aiomysql.Connection
is typically restored back to the connection-holding aiomysql.Pool
referenced by the .Engine
that produced this SAConnection
. Any transactional state present on the aiomysql.Connection
is also unconditionally released via calling Transaction.rollback
method.
After ~.SAConnection.close
is called, the .SAConnection
is permanently in a closed state, and will allow no further operations.
Wraps a DB-API like Cursor
object to provide easier access to row columns.
Individual columns may be accessed by their integer position, case-sensitive column name, or by 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.
ResultProxy
also handles post-processing of result column data using sqlalchemy.types.TypeEngine
objects, which are referenced from the originating SQL statement that produced this result set.
dialect
The readonly property that returns sqlalchemy.engine.interfaces.Dialect
dialect for the ResultProxy
instance.
dialect
global data.
keys()
Return the current set of string keys for rows.
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 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.
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.
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.
returns_rows
A readonly property that returns True
if this ResultProxy
returns rows.
I.e. if it is legal to call the methods ResultProxy.fetchone
, ResultProxy.fetchmany
, ResultProxy.fetchall
.
closed
Return True
if this ResultProxy
is closed (no pending rows in underlying cursor).
close()
Close this ResultProxy
.
Closes the underlying aiomysql.Cursor
corresponding to the execution.
Note that any data cached within this 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.
fetchall()
Fetch all rows, just like aiomysql.Cursor.fetchall
.
This method is a coroutine <coroutine>
.
The connection is closed after the call.
Returns a list of RowProxy
.
fetchone()
Fetch one row, just like aiomysql.Cursor.fetchone
.
This method is a 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 RowProxy
instance or None
.
fetchmany(size=None)
Fetch many rows, just like aiomysql.Cursor.fetchmany
.
This method is a 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 RowProxy
.
first()
Fetch the first row and then close the result set unconditionally.
This method is a coroutine <coroutine>
.
Returns None
if no row is present or an RowProxy
instance.
scalar()
Fetch the first column of the first row, and close the result set.
Returns None
if no row is present or an RowProxy
instance.
A 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 sqlalchemy.schema.Column
` object.
Has overloaded operators __eq__
and __ne__
for comparing two rows.
The RowProxy
is not hashable.
..method:: as_tuple()
Return a tuple with values from
RowProxy.values
.
Represent a database transaction in progress.
The Transaction
object is procured by calling the SAConnection.begin
method of 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 .rollback
and .commit
methods in order to control transaction boundaries.
SAConnection.begin
, SAConnection.begin_twophase
, SAConnection.begin_nested
.
is_active
A readonly property that returns True
if a transaction is active.
connection
A readonly property that returns SAConnection
for transaction.
close()
Close this Transaction
.
This method is a coroutine <coroutine>
.
If this transaction is the base transaction in a begin/commit nesting, the transaction will Transaction.rollback
. Otherwise, the method returns.
This is used to cancel a Transaction
without affecting the scope of an enclosing transaction.
rollback()
Roll back this Transaction
.
This method is a coroutine <coroutine>
.
commit()
Commit this Transaction
.
This method is a coroutine <coroutine>
.
Represent a 'nested', or SAVEPOINT transaction.
A new NestedTransaction
object may be procured using the SAConnection.begin_nested
method.
The interface is the same as that of Transaction
.
SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT__ on
MySQL
:
Represent a two-phase transaction.
A new TwoPhaseTransaction
object may be procured using the SAConnection.begin_twophase
method.
The interface is the same as that of Transaction
with the addition of the TwoPhaseTransaction.prepare
method.
xid
A readonly property that returns twophase transaction id.
prepare()
Prepare this TwoPhaseTransaction
.
This method is a coroutine <coroutine>
.
After a PREPARE, the transaction can be committed.
MySQL
commands for two phase transactions: