Is asyncpg supported by Django?
AFAIK Django requires some additional features should be implemented by the driver to be able to use it.

Hello!

Are there any other examples or projects other than the tests? I searched for asyncpg on nulledge/SearchEngines and couldn't find anything.

Currently I'm interested in if you can stream results from/to pg?

Many thanks.

Hello! Thank you for your awesome job!

This code creates different values in table

await conn.execute("CREATE table test1 (id serial primary key, ip inet not null)")
ip = '10.78.243.196'
ip32 = '10.78.243.196/32'
await conn.execute("INSERT INTO test1 (ip) VALUES($1)", ip)
await conn.execute("INSERT INTO test1 (ip) VALUES($1)", '10.78.243.196')
await conn.execute("INSERT INTO test1 (ip) VALUES('10.78.243.196')")
await conn.execute("INSERT INTO test1 (ip) VALUES('10.78.243.196/32')")
await conn.execute("INSERT INTO test1 (ip) VALUES($1)", ip32) // Exception ValueError: '10.78.243.196/32' does not appear to be an IPv4 or IPv6 address

 id |       ip
----+-----------------
  1 | 10.78.243.196/0
  2 | 10.78.243.196/0
  3 | 10.78.243.196
  4 | 10.78.243.196
(3 rows)

I have latest version in pip asyncpg==0.6.3

                                         version
------------------------------------------------------------------------------------------
 PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Could you please take a look?

You can pass query parameters (args) with fetch and all its variants. But you can not pass them in to execute. It would be nice if execute also took parameters for insert/update/delete statements and such.

I think it would be convenient to have connection execute and fetch.. methods in pool. Because single queries is not so rare. I mean:

async with pool.acquire() as con:
    await con.fetch('SELECT ...')

Would be just:

await pool.fetch('SELECT ...')

I can make PR if you like it.

In PostgreSQL INSERT support RETURNING values, but asyncpg on con.exec returns only success result of inserting, how to support returning values in asyncpg (except with() select)?

Is there any support to connect to postgres over ssl? I am pretty new to asyncio, would appreciate if there is any other way to do this since heroku postgres db's can only be connected over ssl. Thanks

I am very interested in using this library since type support in psycopg2 is awful to work with. However, some projects still use Python 2.7, and will for another 4 years. Would it be possible to split asyncpg into two parts, one with an asynchronous API, usable with Python 3.5 and above, and one synchronous API that can be compatible across both 2.7 and 3.5 and above?

My proposal is a DB-API 2.0 (PEP 249) compatible API that can be utilized by the asyncio part of the library. The benefits is that applications that do not need asynchronous calls can use the same underlying library as those who do. With this code that doesn't rely on co-routines can use the type support provided by asyncpg.

I haven't used this project yet, or dived into its internals, and don't know whether it is feasible at all to do this. I am just venting my ideas.

How would you recommend converting Records to a pandas dataframe?

Also, what do you think about giving fetch the option of returning a dataframe instead of list? There might be performance concerns.

$ python3.5 ./setup.py build
[...]
x86_64-linux-gnu-gcc: error: asyncpg/protocol/protocol.c: No such file or directory

yep. I know about PYTHON3=python3.5 make compile, but it would be much easier if you could add ext_modules=cythonize(...) to setup.py.

TIA

Would it make sense to use parts of the code to make a django orm adapter that is faster and has zero binary dependencies?

I spent some time investigating an error I got trying out asyncpgsa with a table containing a DATERANGE field (see asyncpgsa issue 12).

I distilled the following script that exhibits the problem with plain asyncpg:

import asyncio
from datetime import date

import asyncpg


async def runner():
    conn = await asyncpg.connect(host='localhost', port=5432, database='testdb')

    try:
        async with conn.transaction():
            await conn.execute('''
            CREATE TABLE asyncpgtest (period DATERANGE);
            INSERT INTO asyncpgtest (period) VALUES ('[2016-01-01,2016-12-31]');
            ''')

        async with conn.transaction():
            subperiod = asyncpg.types.Range(date(2016, 2, 1), date(2016, 3, 1))
            r = await conn.fetch('SELECT * FROM asyncpgtest WHERE period @> $1', subperiod)
            print(r)

        async with conn.transaction():
            today = date.today()
            r = await conn.fetch('SELECT * FROM asyncpgtest WHERE period @> $1', today)
            print(r)
    finally:
        await conn.execute('DROP TABLE asyncpgtest')


def main():
    loop = asyncio.get_event_loop()
    loop.run_until_complete(runner())


if __name__ == '__main__':
    main()

It results in the following output:

[<Record period=<Range [datetime.date(2016, 1, 1), datetime.date(2017, 1, 1))>>]
Traceback (most recent call last):
  File "p.py", line 38, in <module>
    main()
  File "p.py", line 34, in main
    loop.run_until_complete(runner())
  File "/usr/local/python3.6/lib/python3.6/asyncio/base_events.py", line 457, in run_until_complete
    return future.result()
  File "/usr/local/python3.6/lib/python3.6/asyncio/futures.py", line 292, in result
    raise self._exception
  File "/usr/local/python3.6/lib/python3.6/asyncio/tasks.py", line 239, in _step
    result = coro.send(None)
  File "p.py", line 26, in runner
    r = await conn.fetch('SELECT * FROM asyncpgtest WHERE period @> $1', today)
  File "/tmp/e/lib/python3.6/site-packages/asyncpg/connection.py", line 224, in fetch
    False, timeout)
  File "asyncpg/protocol/protocol.pyx", line 157, in bind_execute (asyncpg/protocol/protocol.c:45856)
  File "asyncpg/protocol/prepared_stmt.pyx", line 122, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg (asyncpg/protocol/protocol.c:42239)
  File "asyncpg/protocol/codecs/base.pyx", line 123, in asyncpg.protocol.protocol.Codec.encode (asyncpg/protocol/protocol.c:12276)
  File "asyncpg/protocol/codecs/base.pyx", line 90, in asyncpg.protocol.protocol.Codec.encode_range (asyncpg/protocol/protocol.c:11868)
  File "asyncpg/protocol/codecs/range.pyx", line 53, in asyncpg.protocol.protocol.range_encode (asyncpg/protocol/protocol.c:31029)
TypeError: list, tuple or Range object expected (got type <class 'datetime.date'>)

How can I use the range contains a single element variant of the @> operator?

Thank you!

I am connecting to the same pgbouncer with the the same credentials like a hundred times per second and sometimes I have this error:

  File "/usr/local/lib/python3.5/dist-packages/asyncpg/connection.py", line 558, in connect
    await connected
  File "uvloop/future.pyx", line 241, in __await__ (uvloop/loop.c:94875)
  File "uvloop/task.pyx", line 186, in uvloop.loop.BaseTask._fast_wakeup (uvloop/loop.c:100074)
  File "uvloop/future.pyx", line 101, in uvloop.loop.BaseFuture._result_impl (uvloop/loop.c:93110)
asyncpg.exceptions.ProtocolViolationError: Auth failed

Switching to plain auth in pgbouncer fixes the error. I suspect that something in salt is causing the issue, like some cruft in the buffer or leftover message that is not fully read:

        elif status == AUTH_REQUIRED_PASSWORDMD5:
            # AuthenticationMD5Password
            # Note: MD5 salt is passed as a four-byte sequence
            md5_salt = cpython.PyBytes_FromStringAndSize(
                self.buffer.read_bytes(4), 4)
            self.auth_msg = self._auth_password_message_md5(md5_salt)

Simple retry will lower the number of these errors significantly:

            try:
                con = await connect(
                    self.dsn,
                    timeout=self.timeout,
                    command_timeout=self.timeout,
                )
            except ProtocolViolationError:
                con = await connect(
                    self.dsn,
                    timeout=self.timeout,
                    command_timeout=self.timeout,
                )

But maybe there is a cleaner solution? Maybe the connection is not closed properly or some buffer was not flushed here:

            row = await con.fetchrow(fetch_image_query, int(file_id), name)
            await con.close()

i just got an error
RuntimeError: unexpected attribute data type: 23, expected 26
@ File "asyncpg/protocol/codecs/base.pyx", line 155,

which confused me. actually it happens when i try to select whole record.

create table mytab (a int);
insert into mytab values (1);
select mytab from mytab;

it can't decode "record" and fails with "unexpected attribute data type: 23, expected 26"

I have returned to python after some years, and I would like to build a projects using falcon and asyncpg and I can't find any examples of how to build my models using these technologies.

I looked both on github and google for code examples but couldn't found anything, I even looked for asyncpg with other frameworks (except asyncio) and falcon with async db drivers, nothing.

Is there any documentation/code examples that could help me?
Thank you

Check out #18 for a quick PR

(python 3.5.1, asyncpg 0.5.4)

Consider the next code

import asyncpg
import asyncio

async def run():
    con = await asyncpg.connect(user="coldmind", database="test")
    await con.execute('SELECT ($1); SELECT 2;', 1)
asyncio.get_event_loop().run_until_complete(run())

The executing fails with asyncpg.exceptions.PostgresSyntaxError: cannot insert multiple commands into a prepared statement error, while the another example (without passing params to the query) works well with multiple commands:

import asyncpg
import asyncio


async def run():
    con = await asyncpg.connect(user="coldmind", database="test")
    await con.execute('SELECT 1; SELECT 2;')
asyncio.get_event_loop().run_until_complete(run())

Why is the first example using a prepared statement instead of executing it?

The syntax row.col looks a lot cleaner than row['col'].
What are you thoughts on adding a __getattr__ method to a Record so that both are possible?

I dont understand why I can only get a cursor inside a transaction.
What if I am only doing reads (no writes), why would I need a transaction?

connection = await asyncpg.connect(host='host', user='user', password='password', database='database')

works fine

pool = await asyncpg.create_pool(host='host', user='user', password='password', database='database')

Traceback (most recent call last):
File "main.py", line 126, in
app = loop.run_until_complete(get_app())
File "uvloop/loop.pyx", line 1133, in uvloop.loop.Loop.run_until_complete (uvloop/loop.c:19911)
File "uvloop/future.pyx", line 123, in uvloop.loop.BaseFuture.result (uvloop/loop.c:93421)
File "uvloop/future.pyx", line 78, in uvloop.loop.BaseFuture._result_impl (uvloop/loop.c:92960)
File "uvloop/task.pyx", line 128, in uvloop.loop.BaseTask._fast_step (uvloop/loop.c:98739)
File "main.py", line 23, in get_app
database='database')
File "/usr/local/lib/python3.5/site-packages/asyncpg/pool.py", line 103, in _init
con = await self._new_connection()
File "/usr/local/lib/python3.5/site-packages/asyncpg/pool.py", line 89, in _new_connection
**self._working_opts)
File "/usr/local/lib/python3.5/site-packages/asyncpg/connection.py", line 506, in connect
await connected
File "uvloop/future.pyx", line 218, in await (uvloop/loop.c:94725)
File "uvloop/task.pyx", line 186, in uvloop.loop.BaseTask._fast_wakeup (uvloop/loop.c:99922)
File "uvloop/future.pyx", line 78, in uvloop.loop.BaseFuture._result_impl (uvloop/loop.c:92960)
asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "user"

# coding=utf-8
import asyncio

import asyncpg
import uvloop
from aiohttp import web

loop = uvloop.new_event_loop()
asyncio.set_event_loop(loop)


async def get_app():
    app = web.Application()

    app['db1'] = await asyncpg.connect(host='host', user='user', password='password', database='database')

    app['db2'] = await asyncpg.create_pool(host='host', user='user', password='password', database='database')

    return app


app = loop.run_until_complete(get_app())

if __name__ == '__main__':
    web.run_app(app, port=8083)

Am I doing something wrong?

As mentioned in HN, documenting internals (the https://github.com/MagicStack/asyncpg/tree/master/asyncpg/protocol would make it easier to contribute or even use asyncpg as a reference implementation of the protocol

Let's say I have two long running queries that I'd like to run concurrently
Here is my questions :

• Does asyncpg will help in that case ?
• using concurrent.futures will do the same ?

import asyncio
import asyncpg

async def connect_to_db():
    pool = await asyncpg.create_pool("postgres://user:pass@localhost:5555/dev",
                                     command_timeout=60)
    conn1 = await pool.acquire()
    conn2 = await pool.acquire()
    return pool, conn1, conn2

async def create_table_a(conn):
    await conn.execute('CREATE TABLE my_schema.mytab_a (a int)')

async def create_table_b(conn):
    await conn.execute('CREATE TABLE my_schema.mytab_b (b int)')

loop = asyncio.get_event_loop()
pool, conn1, conn2 = loop.run_until_complete(connect_to_db())

tasks = [asyncio.ensure_future(create_table_a(conn1)), asyncio.ensure_future(create_table_b(conn2))]
loop.run_until_complete(asyncio.wait(tasks))
#release the pool and the connections

Thanks for this library

Are there plans to support the TABLESAMPLE clause?

It returns a pseudo-random sample of the query, and was introduced in 9.5.

It looks like for prepared statements, statements have to be in the form
'select $1 from $2 where baz=$3 with the arguments ('foo', 'bar', 'baz')

It is common in python database api's to support the pyformat int PEP 249. http://legacy.python.org/dev/peps/pep-0249/#paramstyle

Can this support at least one of the param styles in the pep? Preferably pyformat.

Ideally, the parameters could then be passed in as a dictionary instead of a ordered list.

conn.fetch(myquery, {bar='bar', foo='foo'})

I'm using cursors but experimenting performance issues with large datasets.

The cursor is incremented by factor of 1K rows, but in most cases it's taking seconds, even more than 60 seconds in order to retrieve the next slot of results. The cursor only takes milliseconds to be created, but for each iteration it looks like it's processing the whole dataset from scratch since looks like it's taking more time as the cursor pointer increases.

Just to provide some context, the table has around ~50M rows, and I'm running queries against with a simple WHERE clausure that filters rows by foreign key (which is indexed).
We're also querying data with a concurrency between 50-200 queries.

I'm wondering if the current cursors implementation in asyncpg is a compliant server-side cursors implementation. Also, we're facing multiple issues on the server side, since PostgreSQL seems to be creating GBs of temporal files, until the disk is fully filled and the server simply stop working.

Any help or advice would be highly appreciated.

Is it possible to load a local file into a table on remote server using COPY statement?

Specifically document that the below pattern will cause a deadlock under load.

async def coro():
    async with pool.acquire() as conn:
        await conn.execute(...)
        async with pool.acquire() as conn:
            await conn.execute(...)

Since records work like dictionaries (record['my_column']), it would be awesome if the in operator worked. Would it be possible to add a __contains__ method to a record?

I was going to submit a PR with this, but for the life of me I cant find the record object, I think it might be implemented in C

Hi, I' use asyncpg to connect the pgbouncer whill occer this error.

Error: prepared statement "stmt_1" already exists
Exception: Traceback (most recent call last):
  File "/home/www/py360dev/lib/python3.6/site-packages/sanic/sanic.py", line 194, in handle_request
    response = await response
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "main.py", line 58, in applogin
    sresult = await conn.fetch('select ipaddr,port from user_manager_srvlist order by concount ASC limit 1')
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 128, in throw
    return self.gen.throw(type, value, traceback)
  File "/home/www/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 268, in fetch
    stmt = await self._get_statement(query, timeout)
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 128, in throw
    return self.gen.throw(type, value, traceback)
  File "/home/www/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 212, in _get_statement
    state = await protocol.prepare(None, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 138, in prepare (asyncpg/protocol/protocol.c:49760)
asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement "stmt_1" already exists

• I want use dsn to disable prepareThreshold, but Unsupported.
  await asyncpg.create_pool(dsn="postgres://mqtt:[email protected]:6432/mqtt?prepareThreshold=0")

Traceback (most recent call last):
  File "main.py", line 109, in <module>
    engine = loop.run_until_complete(initdb_pool())
  File "uvloop/loop.pyx", line 1203, in uvloop.loop.Loop.run_until_complete (uvloop/loop.c:24223)
  File "main.py", line 24, in initdb_pool
    return  await asyncpg.create_pool(dsn="postgres://mqtt:[email protected]:6432/mqtt?prepareThreshold=0")
  File "/home/www/py360dev/lib/python3.6/site-packages/asyncpg/pool.py", line 103, in _init
    con = await self._new_connection()
  File "/home/www/py360dev/lib/python3.6/site-packages/asyncpg/pool.py", line 75, in _new_connection
    **self._connect_kwargs)
  File "/home/www/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 592, in connect
    await connected
asyncpg.exceptions.ProtocolViolationError: Unsupported startup parameter: prepareThreshold

setup will accept a coroutine, which will initialize the connection before returning it from pool.acquire() (setup listeners etc).

Is there any way to pass tuple/list as argument for IN clause? Seems like the only way to do it right now is to manually construct a query string like this WHERE "my_field" IN ($1, $2, $3). Am I missing something?

Running 'python -m pip install asyncpg' causes an error on Windows:
`
running build_ext
building 'asyncpg.protocol.protocol' extension
creating build\temp.win32-3.5
creating build\temp.win32-3.5\Release
creating build\temp.win32-3.5\Release\asyncpg
creating build\temp.win32-3.5\Release\asyncpg\protocol
creating build\temp.win32-3.5\Release\asyncpg\protocol\record
C:\Program Files\Microsoft Visual Studio 14.0\VC\BIN\cl.exe /c /nologo /Ox /
W3 /GL /DNDEBUG /MD -IC:\Python35\include -IC:\Python35\include "-IC:\Program Files\Microsoft Visual Studio 14.0\VC\INCLUDE" "-IC:\Program Files\Windows Kits\10\include\10.0.10240.0\ucrt" "-IC:\Program Files\Windows Kits\8.1\include\shared" "-IC:\Program Files\Windows Kits\8.1\include\um" "-IC:\Program Files\Windows Kits\8.1\include\winrt" /Tcasyncpg/protocol/record/recordobj.c Fobuild\temp.win32-3.5\Release\asyncpg/protocol/record/recordobj.obj -O2
recordobj.c
C:\Program Files\Microsoft Visual Studio 14.0\VC\BIN\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MD -IC:\Python35\include -IC:\Python35\include "-IC:\Program Files\Microsoft Visual Studio 14.0\VC\INCLUDE" "-IC:\Program Files\Windows Kits\10\include\10.0.10240.0\ucrt" "-IC:\Program Files\Windows Kits\8.1\include\shared" "-IC:\Program Files\Windows Kits\8.1\include\um" "-IC:\Program Files\Windows Kits\8.1\include\winrt" /Tcasyncpg/protocol/protocol.c /Fobuild\temp.win32-3.5\Rel
ease\asyncpg/protocol/protocol.obj -O2
protocol.c
asyncpg/protocol/protocol.c(276): fatal error C1083: Cannot open include file: 'arpa/inet.h': No such file or directory
error: command 'C:\Program Files\Microsoft Visual Studio 14.0\VC\BIN\cl.exe' failed with exit status 2

Currently connection reset does the following:
asyncpg v0.7.0
connection.py:370

async def reset(self):
        self._listeners = {}
        await self.execute('''
            SET SESSION AUTHORIZATION DEFAULT;
            RESET ALL;
            CLOSE ALL;
            UNLISTEN *;
            SELECT pg_advisory_unlock_all();
        ''')

Which is totally incomatible with postgresql in hot standby mode:

Transactions started during hot standby will never be assigned a transaction ID and cannot write to the system write-ahead log. Therefore, the following actions will produce error messages:
...
LISTEN, UNLISTEN, NOTIFY

Maybe there is a way to explicitly listen and then unlisten only in case listen command was issued?

The documentation available at https://magicstack.github.io/asyncpg/current/# is currently showing version 0.5.3.

In order to provide for alternative ways of accessing fields, or adding custom functionality, make it possible to specify a Record factory on a connection.

See #38 and #13 for related discussions.

Hi, I have troubles using prepared statements with connection pool. AFAIK you have to setup prepared statement for each connection you are using (https://www.postgresql.org/docs/9.3/static/sql-prepare.html):

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use.

So what is preferred way to work with prepared statements with connection pool? Should I, after connect, prepare statements for each connection of connection pool (how to iterate them?)? Should I use setup keyword of create_pool? But that is called each time acquire and we do not get references to statements easily. How to handle statement references? Dict of connection: statements?
Is there a way to do it with asyncpg? Could you send me an code example how to do it or even better add it to documentation?

Hi, here is my code ,please help resovle the problem.thanks a lot.

from sanic import Sanic
from sanic.response import json
import asyncpg
import asyncio
import uvloop
loop = uvloop.new_event_loop()
app = Sanic()
app.debug = True

async def initdb_pool():
    dbdict = {"database":"mqtt","user":"mqtt","password":"mqtt123",
            "host":"192.168.25.100","port":5433}
    return await asyncpg.create_pool(**dbdict)

@app.route("/iot/v1.0/app/auth/<user:[A-z0-9]\w+>/<pwd:[A-z0-9]\w+>/")
async def applogin(request,user,pwd):
    async with engine.acquire() as connection:
        #async with connection.transaction():
        stmt   = await connection.prepare('select key from user_manager_appuser where uname = $1 or email = $2 or phone = $3')
        result =  await stmt.fetchval(user,user,user)
        #    result = await connection.fetchval('select key from user_manager_appuser where uname = $1 or email = $2 or phone = $3',
        #                                        user,user,user)
        if not result:
            return json({'ok':False,'err':'Pwd error'})
        print("result is ",result)
        return json({'ok':True,'data':str(result)})

if __name__ == "__main__":
    engine = loop.run_until_complete(initdb_pool())
    app.run(host="0.0.0.0",port=8000,debug=True)

 curl "http://127.0.0.1:8000/iot/v1.0/app/auth/abc/123456/"
Error: cannot perform operation: another operation is in progress
Exception: Traceback (most recent call last):
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/sanic/sanic.py", line 194, in handle_request
    response = await response
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 128, in throw
    return self.gen.throw(type, value, traceback)
  File "main.py", line 32, in applogin
    return json({'ok':True,'data':str(result)})
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/pool.py", line 252, in __aexit__
    await self.pool.release(con)
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/pool.py", line 184, in release
    await connection.reset()
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 411, in reset
    ''')
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 170, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 247, in query (asyncpg/protocol/protocol.c:51830)
  File "asyncpg/protocol/protocol.pyx", line 352, in asyncpg.protocol.protocol.BaseProtocol._ensure_clear_state (asyncpg/protocol/protocol.c:54136)
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

the docs are ok, but leave one wanting for some code snippet examples. Providing some example files would be nice

Hi, our db uses ltrees (https://www.postgresql.org/docs/current/static/ltree.html) and asyncpg fails with

asyncpg.exceptions.UndefinedFunctionError: no binary output function available for type ltree

In psycopg2 you could write an adapter to cast the SQL type to a python object (http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects).

Is there a way to do something like that for this driver?

Very interesting project, btw!

Record.__repr__ crashes in presence of duplicates in field name mapping. I added a test for this.

How to insert multiple rows without a traditional for loop?
Something like executemany in psycopg2

I am asking because I failed to find any example or method in the documentation.

Actually what I am doing is:

async def insertdemo(data, dns=DNS):
    async with asyncpg.create_pool(dns) as pool:
        async with pool.acquire() as con:
            async with con.transaction():
                stmt = '''insert into demo (num, name) select * from unnest($1::int[], $2::varchar[]);'''
                await con.execute(stmt, *zip(*data))

I would like to avoid to unzip the data array.

It's unclear at the moment how to pass composite type values (in my case, arrays of them) to asyncpg.

For example, if I want to do something like this:

CREATE TYPE keyvalue AS (key text, value text);
CREATE TABLE test (id int, data keyvalue);
INSERT INTO test (id, data) VALUES (1, ROW('stuff', 'things'));

How to do so with asyncpg isn't clear. I've tried tuples and received errors about integer types (one part of my composite type is an integer). The docs mention something about a Record type but not where to find it.

The code as in Example of https://magicstack.github.io/asyncpg/current/api/index.html?highlight=context#connection fails with
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "stmt_2" does not exist

versions:
python-3.5.2
asyncpg-0.7.0
postgresql-9.3.14

I have this test code:

import asyncpg
import asyncio

import json

async def run():
    con = await asyncpg.connect(**json.load(open('db_credentials.json')))
    types = await con.fetch('SELECT * FROM pg_type')
    print(types)
asyncio.get_event_loop().run_until_complete(run())

Traceback:

Traceback (most recent call last):
  File "broken.py", line 10, in <module>
    asyncio.get_event_loop().run_until_complete(run())
  File "/usr/lib64/python3.5/asyncio/base_events.py", line 387, in run_until_complete
    return future.result()
  File "/usr/lib64/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
  File "/usr/lib64/python3.5/asyncio/tasks.py", line 241, in _step
    result = coro.throw(exc)
  File "broken.py", line 8, in run
    types = await con.fetch('SELECT * FROM pg_type')
  File "/tmp/venv/lib/python3.5/site-packages/asyncpg/connection.py", line 243, in fetch
    stmt = await self._get_statement(query, timeout)
  File "/tmp/venv/lib/python3.5/site-packages/asyncpg/connection.py", line 194, in _get_statement
    types = await self._types_stmt.fetch(list(ready))
  File "/tmp/venv/lib/python3.5/site-packages/asyncpg/prepared_stmt.py", line 160, in fetch
    self._state, args, '', 0, True, timeout)
  File "asyncpg/protocol/protocol.pyx", line 163, in bind_execute (asyncpg/protocol/protocol.c:50184)
  File "/usr/lib64/python3.5/asyncio/futures.py", line 361, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib64/python3.5/asyncio/tasks.py", line 296, in _wakeup
    future.result()
  File "/usr/lib64/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "stmt_2" does not exist

This code using transaction context does work:

import asyncpg
import asyncio

import json

async def run():
    con = await asyncpg.connect(**json.load(open('db_credentials.json')))
    async with con.transaction():
        types = await con.fetch('SELECT * FROM pg_type')
    print(types)
asyncio.get_event_loop().run_until_complete(run())

How asyncpg is 3x times faster than psycopg2 ?

I copy-pasted the example code from Readme and compared with example code from psycopg2.

asyncpg example code:

import asyncio
import asyncpg

async def run():
    conn = await asyncpg.connect(user='sardor', password='',
                                 database='sardor', host='127.0.0.1')
    values = await conn.fetch('''SELECT * FROM person''')
    print(values)
    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())

Running

$ time python asyncpg_app.py
[<Record id=1 name='Steven' data=None>, <Record id=2 name='Steven' data=None>, <Record id=3 name='Steven' data=None>, <Record id=4 name='Steven' data=None>]

real	0m0.259s
user	0m0.215s
sys	0m0.031s

psycopg2 example code

import psycopg2


def main():
    conn = psycopg2.connect("dbname='sardor' user='sardor' host='localhost'")
    cur = conn.cursor()
    cur.execute("""SELECT * FROM person""")
    rows = cur.fetchall()
    conn.close()
    print(rows)


if __name__ == '__main__':
    main()

Running

$ time python psycopg2_app.py
[(1, 'Steven', None), (2, 'Steven', None), (3, 'Steven', None), (4, 'Steven', None)]

real	0m0.131s
user	0m0.098s
sys	0m0.021s

Am I missing something here?