Debian Codegraph @

docs/tutorial.rst

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