1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
.. _aiomysql-tutorial:
Tutorial
========
Python database access modules all have similar interfaces, described by the
:term:`DBAPI`. Most relational databases use the same synchronous interface,
*aiomysql* tries to provide same api you just need
to use ``await conn.f()`` instead of just call ``conn.f()`` for
every method.
Installation
------------
.. code::
pip3 install aiomysql
.. note:: :mod:`aiomysql` requires :term:`PyMySQL` library.
Getting Started
---------------
Lets start from basic 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)
cur = await conn.cursor()
await cur.execute("SELECT Host,User FROM user")
print(cur.description)
r = await cur.fetchall()
print(r)
await cur.close()
conn.close()
loop.run_until_complete(test_example())
Connection is established by invoking the :func:`connect()` coroutine,
arguments list are keyword arguments, almost same as in :term:`PyMySQL`
corresponding method. Example makes connection to :term:`MySQL` server on
local host to access `mysql` database with user name `root`' and empty password.
If :func:`connect()` coroutine succeeds, it returns a :class:`Connection`
instance as the basis for further interaction with :term:`MySQL`.
After the connection object has been obtained, code in example invokes
:meth:`Connection.cursor()` coroutine method to create a cursor object for
processing statements. Example uses cursor to issue a
``SELECT Host,User FROM user;`` statement, which returns a list of `host` and
`user` from :term:`MySQL` system table ``user``::
cur = await conn.cursor()
await cur.execute("SELECT Host,User FROM user")
print(cur.description)
r = await cur.fetchall()
The cursor object's :meth:`Cursor.execute()` method sends the query the server
and :meth:`Cursor.fetchall()` retrieves rows.
Finally, the script invokes :meth:`Cursor.close()` coroutine and
connection object's :meth:`Connection.close()` method to disconnect
from the server::
await cur.close()
conn.close()
After that, ``conn`` becomes invalid and should not be used to access the
server.
Inserting Data
--------------
Let's take basic example of :meth:`Cursor.execute` method::
import asyncio
import aiomysql
async def test_example_execute(loop):
conn = await aiomysql.connect(host='127.0.0.1', port=3306,
user='root', password='',
db='test_pymysql', loop=loop)
cur = await conn.cursor()
async with conn.cursor() as cur:
await cur.execute("DROP TABLE IF EXISTS music_style;")
await cur.execute("""CREATE TABLE music_style
(id INT,
name VARCHAR(255),
PRIMARY KEY (id));""")
await conn.commit()
# insert 3 rows one by one
await cur.execute("INSERT INTO music_style VALUES(1,'heavy metal')")
await cur.execute("INSERT INTO music_style VALUES(2,'death metal');")
await cur.execute("INSERT INTO music_style VALUES(3,'power metal');")
await conn.commit()
conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(test_example_execute(loop))
Please note that you need to manually call :func:`commit()` bound to your :term:`Connection` object, because by default it's set to ``False`` or in :meth:`aiomysql.connect()` you can transfer addition keyword argument ``autocommit=True``.
Example with ``autocommit=True``::
import asyncio
import aiomysql
async def test_example_execute(loop):
conn = await aiomysql.connect(host='127.0.0.1', port=3306,
user='root', password='',
db='test_pymysql', loop=loop,
autocommit=True)
cur = await conn.cursor()
async with conn.cursor() as cur:
await cur.execute("DROP TABLE IF EXISTS music_style;")
await cur.execute("""CREATE TABLE music_style
(id INT,
name VARCHAR(255),
PRIMARY KEY (id));""")
# insert 3 rows one by one
await cur.execute("INSERT INTO music_style VALUES(1,'heavy metal')")
await cur.execute("INSERT INTO music_style VALUES(2,'death metal');")
await cur.execute("INSERT INTO music_style VALUES(3,'power metal');")
conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(test_example_execute(loop))
|
