How to use aiomysql - 10 common examples

async def connect(self, **kwargs):
        conn = await connect(db=self.db,
                             user=self.user,
                             password=self.password,
                             host=self.host,
                             loop=self.loop,
                             port=self.port,
                             **kwargs)
        await conn.autocommit(True)
        cur = await conn.cursor()
        await cur.execute("DROP TABLE IF EXISTS sa_tbl")
        await cur.execute("CREATE TABLE sa_tbl "
                          "(id serial, name varchar(255))")
        await cur.execute("INSERT INTO sa_tbl (name)"
                          "VALUES ('first')")

        await cur._connection.commit()
        # yield from cur.close()

async def connect(self, **kwargs):
        conn = await connect(db=self.db,
                             user=self.user,
                             password=self.password,
                             host=self.host,
                             port=self.port,
                             loop=self.loop,
                             **kwargs)
        # TODO: fix this, should autocommit be enabled by default?
        await conn.autocommit(True)
        engine = mock.Mock()
        engine.dialect = sa.engine._dialect

        def release(*args):
            return
        engine.release = release

        ret = sa.SAConnection(conn, engine)

async def connect(self, host='localhost', port=3306, user='root',
                      password='', db='faf_test', minsize=1, maxsize=1):
        if self.engine is not None:
            raise ValueError("DB is already connected!")
        self.engine = await create_engine(
            host=host,
            port=port,
            user=user,
            password=password,
            db=db,
            autocommit=False,
            loop=self._loop,
            minsize=minsize,
            maxsize=maxsize,
            echo=True
        )
        self._keep = self._loop.create_task(self._keep_connection())
        await self._conn_present.wait()

user=self.user,
                             password=self.password,
                             host=self.host,
                             port=self.port,
                             loop=self.loop,
                             **kwargs)
        # TODO: fix this, should autocommit be enabled by default?
        await conn.autocommit(True)
        engine = mock.Mock()
        engine.dialect = sa.engine._dialect

        def release(*args):
            return
        engine.release = release

        ret = sa.SAConnection(conn, engine)
        return ret

host=self.host,
                             loop=self.loop,
                             port=self.port,
                             **kwargs)
        await conn.autocommit(True)
        cur = await conn.cursor()
        await cur.execute("DROP TABLE IF EXISTS sa_tbl")
        await cur.execute("CREATE TABLE sa_tbl "
                          "(id serial, name varchar(255))")
        await cur.execute("INSERT INTO sa_tbl (name)"
                          "VALUES ('first')")

        await cur._connection.commit()
        # yield from cur.close()
        engine = mock.Mock()
        engine.dialect = sa.engine._dialect
        return sa.SAConnection(conn, engine)

conn = self.connections[0]
        data = [
            ('America', '', 'America/Jamaica'),
            ('America', '', 'America/Los_Angeles'),
            ('America', '', 'America/Lima'),
            ('America', '', 'America/New_York'),
            ('America', '', 'America/Menominee'),
            ('America', '', 'America/Havana'),
            ('America', '', 'America/El_Salvador'),
            ('America', '', 'America/Costa_Rica'),
            ('America', '', 'America/Denver'),
            ('America', '', 'America/Detroit'),]

        try:
            cursor = conn.cursor(aiomysql.cursors.SSCursor)

            # Create table
            cursor.execute(('CREATE TABLE tz_data ('
                'region VARCHAR(64),'
                'zone VARCHAR(64),'
                'name VARCHAR(64))'))

            # Test INSERT
            for i in data:
                cursor.execute('INSERT INTO tz_data VALUES (%s, %s, %s)', i)
                self.assertEqual(conn.affected_rows(), 1, 'affected_rows does not match')
            conn.commit()

            # Test fetchone()
            iter = 0
            cursor.execute('SELECT * FROM tz_data')

async def go():
            conn = await self.connect()
            res = await conn.execute("SELECT * FROM sa_tbl;")
            self.assertIsInstance(res.cursor, Cursor)
            self.assertEqual(('id', 'name'), res.keys())
            rows = await res.fetchall()
            self.assertTrue(res.closed)
            self.assertIsNone(res.cursor)
            self.assertEqual(1, len(rows))
            row = rows[0]
            self.assertEqual(1, row[0])
            self.assertEqual(1, row['id'])
            self.assertEqual(1, row.id)
            self.assertEqual('first', row[1])
            self.assertEqual('first', row['name'])
            self.assertEqual('first', row.name)
            # TODO: fix this
            await conn._connection.commit()
        self.loop.run_until_complete(go())

async def go():
            conn = await self.connect()
            res = await conn.execute(tbl.select())
            self.assertIsInstance(res.cursor, Cursor)
            self.assertEqual(('id', 'name'), res.keys())
            rows = await res.fetchall()
            self.assertTrue(res.closed)
            self.assertIsNone(res.cursor)
            self.assertTrue(res.returns_rows)

            self.assertEqual(1, len(rows))
            row = rows[0]
            self.assertEqual(1, row[0])
            self.assertEqual(1, row['id'])
            self.assertEqual(1, row.id)
            self.assertEqual('first', row[1])
            self.assertEqual('first', row['name'])
            self.assertEqual('first', row.name)
            # TODO: fix this
            await conn._connection.commit()

def test_ssursor(self):
        # affected_rows = 18446744073709551615
        conn = self.connections[0]
        cursor = yield from conn.cursor(SSCursor)
        # Create table
        yield from cursor.execute('DROP TABLE IF EXISTS tz_data;')
        yield from cursor.execute(('CREATE TABLE tz_data ('
                                   'region VARCHAR(64),'
                                   'zone VARCHAR(64),'
                                   'name VARCHAR(64))'))

        # Test INSERT
        for i in self.data:
            yield from cursor.execute(
                'INSERT INTO tz_data VALUES (%s, %s, %s)', i)
            self.assertEqual(conn.affected_rows(), 1,
                             'affected_rows does not match')
        yield from conn.commit()

        # Test update, affected_rows()

async def test_cached_sha256_nopw(mysql_server, loop):
    connection_data = copy.copy(mysql_server['conn_params'])
    connection_data['user'] = 'nopass_caching_sha2'
    connection_data['password'] = None

    async with create_pool(**connection_data,
                           loop=loop) as pool:
        async with pool.get() as conn:
            # User doesnt have any permissions to look at DBs
            # But as 8.0 will default to caching_sha2_password
            assert conn._auth_plugin_used == 'caching_sha2_password'