from trollius import From, Return
import trollius as asyncio
from aiopg_trollius.pool import create_pool
from contextlib import closing, contextmanager

dsn = 'dbname=jetty user=nick password=1234 host=localhost port=5432'


@asyncio.coroutine
def yield_conn_context(pool):
    """
    Helper to manage the connection context.
    """
    conn = yield From(pool.acquire())

    @contextmanager
    def context():

        try:
            yield conn
        finally:
            pool.release(conn)
    raise Return( context() )


@asyncio.coroutine
def yield_cur_context(conn):
    """
    Helper to manage the cursor context.
    """
    cur = yield From(conn.cursor())

    raise Return( closing(cur) )



@asyncio.coroutine
def test_select():
    pool = yield From(create_pool(dsn))

    with (yield From(yield_conn_context(pool)) as conn:
        with (yield From(yield_cur_context(conn))) as cur:
            cur = yield From(conn.cursor())
            yield From(cur.execute('SELECT 1'))
            ret = yield From(cur.fetchone())
            assert ret == (1,), ret


asyncio.get_event_loop().run_until_complete(test_select())

import asyncio
from aiopg.sa import create_engine
import sqlalchemy as sa


metadata = sa.MetaData()

tbl = sa.Table('tbl', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('val', sa.String(255)))


@asyncio.coroutine
def go():
    engine = yield from create_engine(user='aiopg',
                                      database='aiopg',
                                      host='127.0.0.1',
                                      password='passwd')

    with (yield from engine) as conn:
        yield from conn.execute(tbl.insert().values(val='abc'))

        res = yield from conn.execute(tbl.select())
        for row in res:
            print(row.id, row.val)


asyncio.get_event_loop().run_until_complete(go())

$ python3 runtests.py