bibR7kpp

1#!/usr/bin/env python3
2# -*- coding: utf-8 -*-
3
4
5import asyncio
6import warnings
7import aiomysql
8import pprint
9
10
11async def stored_procedure(loop):
12    conn = await aiomysql.connect(host='127.0.0.1', port=3306,
13                                  user='test', password='test',
14                                  db='test', loop=loop)
15    async with conn.cursor() as cur:
16        with warnings.catch_warnings():
17            warnings.simplefilter('ignore')
18            await cur.execute('DROP PROCEDURE IF EXISTS myinc;')
19        await cur.execute("""CREATE PROCEDURE myinc(p1 INT)
20                             BEGIN
21                                SELECT p1 + 1;
22                             END""")
23        await cur.callproc('myinc', [1])
24        (ret, ) = await cur.fetchone()
25        assert 2, ret
26        print(ret)
27
28    conn.close()
29
30
31async def test_example_executemany(loop):
32    conn = await aiomysql.connect(host='127.0.0.1', port=3306,
33                                  user='test', password='test',
34                                  db='test', loop=loop)
35    # cur = await conn.cursor()
36    async with conn.cursor() as cur:
37        await cur.execute("DROP TABLE IF EXISTS music_style;")
38        await cur.execute("""CREATE TABLE music_style(
39                                id INT,
40                                name VARCHAR(255),
41                                PRIMARY KEY(id));""")
42        await conn.commit()
43
44        await cur.execute("INSERT INTO music_style VALUES(1, 'heavy metal')")
45        await cur.execute("INSERT INTO music_style VALUES(2, 'death metal')")
46        await cur.execute("INSERT INTO music_style VALUES(3, 'power metal')")
47        await conn.commit()
48
49        data = [(4, 'gothic metal'), (5, 'doom metal'), (6, 'post metal')]
50        await cur.executemany(
51            "INSERT INTO music_style (id, name)"
52            "VALUES (%s, %s)", data)
53        await conn.commit()
54
55        await cur.execute("SELECT * FROM music_style;")
56        result = await cur.fetchall()
57        print(result)
58
59
60async def pool(loop):
61    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306, user='test',
62                                      password='test', db='test', loop=loop)
63    async with pool.acquire() as conn:
64        async with conn.cursor() as cur:
65            await cur.execute("SELECT 42;")
66            pprint.pprint(cur.description)
67            (r, ) = await cur.fetchone()
68            assert r == 42
69
70    pool.close()
71    await pool.wait_closed()
72
73
74async def test_example_transaction(loop):
75    conn = await aiomysql.connect(host='127.0.0.1', port=3306, user='test',
76                                  password='test', db='test', autocommit=False,
77                                  loop=loop)
78    async with conn.cursor() as cursor:
79        stmt_drop = "DROP TABLE IF EXISTS names;"
80        with warnings.catch_warnings():
81            warnings.simplefilter('ignore')
82            await cursor.execute(stmt_drop)
83        await cursor.execute("""
84            CREATE TABLE names (
85                id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
86                name VARCHAR(30) DEFAULT '' NOT NULL,
87                cnt TINYINT UNSIGNED DEFAULT 0,
88                PRIMARY KEY(id)
89            );""")
90        await conn.commit()
91
92        # Insert 3 records
93        names = (('Geert', ), ('Jan', ), ('Michael', ))
94        stmt_insert = "INSERT INTO names (name) VALUES (%s)"
95        await cursor.executemany(stmt_insert, names)
96
97        # Roll back!!!
98        await conn.rollback()
99
100        # There should be no data!
101        stmt_select = "SELECT id, name FROM names ORDER BY id"
102        await cursor.execute(stmt_select)
103        resp = await cursor.fetchall()
104        assert not resp
105
106        await cursor.executemany(stmt_insert, names)
107
108        await cursor.execute(stmt_select)
109        await conn.commit()
110
111        # Cannot rollback after the commit
112        await conn.rollback()
113        resp = await cursor.fetchall()
114        assert resp
115        print(resp)
116
117        await cursor.execute(stmt_drop)
118        await cursor.close()
119        conn.close()
120
121
122loop = asyncio.get_event_loop()
123loop.run_until_complete(test_example_transaction(loop))