Cursor

class Cursor

A cursor for connection.

Allows Python code to execute MySQL command in a database session. Cursors are created by the Connection.cursor() coroutine: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.

Cursors that are created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors. Cursors created from different connections can or can not be isolated, depending on the connections’ isolation level.

import asyncio
import aiomysql

loop = asyncio.get_event_loop()

async def test_example():
    conn = await aiomysql.connect(host='127.0.0.1', port=3306,
                                  user='root', password='',
                                  db='mysql', loop=loop)

    # create default cursor
    cursor = await conn.cursor()

    # execute sql query
    await cursor.execute("SELECT Host, User FROM user")

    # fetch all results
    r = await cursor.fetchall()

    # detach cursor from connection
    await cursor.close()

    # close connection
    conn.close()

loop.run_until_complete(test_example())

Use Connection.cursor() for getting cursor for connection.

connection

This read-only attribute return a reference to the Connection object on which the cursor was created

echo

Return echo mode status.

description

This read-only attribute is a sequence of 7-item sequences.

Each of these sequences is a collections.namedtuple containing information describing one result column:

  1. name: the name of the column returned.

  2. type_code: the type of the column.

  3. display_size: the actual length of the column in bytes.

  4. internal_size: the size in bytes of the column associated to this column on the server.

  5. precision: total number of significant digits in columns of type NUMERIC. None for other types.

  6. scale: count of decimal digits in the fractional part in columns of type NUMERIC. None for other types.

  7. null_ok: always None.

This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the Cursor.execute() method yet.

rowcount

Returns the number of rows that has been produced of affected.

This read-only attribute specifies the number of rows that the last Cursor.execute() produced (for Data Query Language statements like SELECT) or affected (for Data Manipulation Language statements like UPDATE or INSERT).

The attribute is -1 in case no Cursor.execute() has been performed on the cursor or the row count of the last operation if it can’t be determined by the interface.

rownumber

Row index. This read-only attribute provides the current 0-based index of the cursor in the result set or None if the index cannot be determined.

arraysize

How many rows will be returned by Cursor.fetchmany() call.

This read/write attribute specifies the number of rows to fetch at a time with Cursor.fetchmany(). It defaults to 1 meaning to fetch a single row at a time.

lastrowid

This read-only property returns the 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, Cursor.lastrowid returns the AUTO_INCREMENT value for the new row.

closed

The readonly property that returns True if connections was detached from current cursor

close()

Coroutine to close the cursor now (rather than whenever del is executed). The cursor will be unusable from this point forward; closing a cursor just exhausts all remaining data.

execute(query, args=None)

Coroutine, executes the given operation substituting any markers with the given parameters.

For example, getting all rows where id is 5:

await cursor.execute("SELECT * FROM t1 WHERE id=%s", (5,))
Parameters
  • query (str) – sql statement

  • args (list) – tuple or list of arguments for sql query

Returns int

number of rows that has been produced of affected

executemany(query, args)

The executemany() coroutine will execute the operation iterating over the list of parameters in seq_params.

Example: Inserting 3 new employees and their phone number:

data = [
    ('Jane','555-001'),
    ('Joe', '555-001'),
    ('John', '555-003')
   ]
stmt = "INSERT INTO employees (name, phone)
    VALUES ('%s','%s')"
await cursor.executemany(stmt, data)

INSERT statements are optimized by batching the data, that is using the MySQL multiple rows syntax.

Parameters
  • query (str) – sql statement

  • args (list) – tuple or list of arguments for sql query

callproc(procname, args)

Execute stored procedure procname with args, this method is coroutine.

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 Cursor.execute() to get any OUT or INOUT values. Basic usage example:

conn = await aiomysql.connect(host='127.0.0.1', port=3306,
                              user='root', password='',
                              db='mysql', loop=self.loop)

cur = await conn.cursor()
await cur.execute("""CREATE PROCEDURE myinc(p1 INT)
                  BEGIN
                      SELECT p1 + 1;
                  END
                  """)

await cur.callproc('myinc', [1])
(ret, ) = await cur.fetchone()
assert 2, ret

await cur.close()
conn.close()

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 Cursor.nextset() to advance through all result sets; otherwise you may get disconnected.

Parameters
  • procname (str) – name of procedure to execute on server

  • args – sequence of parameters to use with procedure

Returns

the original args.

fetchone()

Fetch the next row coroutine.

fetchmany(size=None)

Coroutine the next set of rows of a query result, returning a list of tuples. When no more rows are available, it returns an empty list.

The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor’s Cursor.arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned

cursor = await connection.cursor()
await cursor.execute("SELECT * FROM test;")
r = cursor.fetchmany(2)
print(r)
# [(1, 100, "abc'def"), (2, None, 'dada')]
r = await cursor.fetchmany(2)
print(r)
# [(3, 42, 'bar')]
r = await cursor.fetchmany(2)
print(r)
# []
Parameters

size (int) – number of rows to return

Returns list

of fetched rows

fetchall()

Coroutine returns all rows of a query result set:

await cursor.execute("SELECT * FROM test;")
r = await cursor.fetchall()
print(r)
# [(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]
Returns list

list of fetched rows

scroll(value, mode='relative')

Scroll the cursor in the result set to a new position according to mode. This method is coroutine.

If mode is relative (default), value is taken as offset to the current position in the result set, if set to absolute, value states an absolute target position. An IndexError should be raised in case a scroll operation would leave the result set. In this case, the cursor position is left undefined (ideal would be to not move the cursor at all).

Note

According to the DBAPI, the exception raised for a cursor out of bound should have been IndexError. The best option is probably to catch both exceptions in your code:

try:
    await cur.scroll(1000 * 1000)
except (ProgrammingError, IndexError), exc:
    deal_with_it(exc)
Parameters
  • value (int) – move cursor to next position according to mode.

  • mode (str) – scroll mode, possible modes: relative and absolute

class DictCursor

A cursor which returns results as a dictionary. All methods and arguments same as Cursor, see example:

import asyncio
import aiomysql

loop = asyncio.get_event_loop()

async def test_example():
    conn = await aiomysql.connect(host='127.0.0.1', port=3306,
                                  user='root', password='',
                                  db='mysql', loop=loop)

    # create dict cursor
    cursor = await conn.cursor(aiomysql.DictCursor)

    # execute sql query
    await cursor.execute(
        "SELECT * from people where name='bob'")

    # fetch all results
    r = await cursor.fetchone()
    print(r)
    # {'age': 20, 'DOB': datetime.datetime(1990, 2, 6, 23, 4, 56),
    # 'name': 'bob'}

loop.run_until_complete(test_example())

You can customize your dictionary, see example:

import asyncio
import aiomysql

class AttrDict(dict):
    """Dict that can get attribute by dot, and doesn't raise KeyError"""

    def __getattr__(self, name):
        try:
            return self[name]
        except KeyError:
            return None

class AttrDictCursor(aiomysql.DictCursor):
    dict_type = AttrDict

loop = asyncio.get_event_loop()

async def test_example():
    conn = await aiomysql.connect(host='127.0.0.1', port=3306,
                                  user='root', password='',
                                  db='mysql', loop=loop)

    # create your dict cursor
    cursor = await conn.cursor(AttrDictCursor)

    # execute sql query
    await cursor.execute(
        "SELECT * from people where name='bob'")

    # fetch all results
    r = await cursor.fetchone()
    print(r)
    # {'age': 20, 'DOB': datetime.datetime(1990, 2, 6, 23, 4, 56),
    # 'name': 'bob'}
    print(r.age)
    # 20
    print(r.foo)
    # None

loop.run_until_complete(test_example())
class SSCursor

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. All methods are the same as in Cursor but with different behaviour.

fetchall()
Same as :meth:`Cursor.fetchall` :ref:`coroutine <coroutine>`,
useless for large queries, as all rows fetched one by one.
fetchmany(size=None, mode='relative')
Same as :meth:`Cursor.fetchall`, but each row fetched one by one.
scroll(size=None)
Same as :meth:`Cursor.scroll`, but move cursor on server side one by
one. If you want to move 20 rows forward scroll will make 20 queries
to move cursor. Currently only forward scrolling is supported.
class SSDictCursor

An unbuffered cursor, which returns results as a dictionary.