magicstack / asyncpg Goto Github PK
View Code? Open in Web Editor NEWA fast PostgreSQL Database Client Library for Python/asyncio.
License: Apache License 2.0
A fast PostgreSQL Database Client Library for Python/asyncio.
License: Apache License 2.0
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?
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()
Is asyncpg supported by Django?
AFAIK Django requires some additional features should be implemented by the driver to be able to use it.
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.
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.
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
Are there plans to support the TABLESAMPLE clause?
It returns a pseudo-random sample of the query, and was introduced in 9.5.
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.
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!
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"
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 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
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?
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?
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
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?
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.
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())
Is it possible to load a local file into a table on remote server using COPY
statement?
Would it make sense to use parts of the code to make a django orm adapter that is faster and has zero binary dependencies?
Check out #18 for a quick PR
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
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.
Let's say I have two long running queries that I'd like to run concurrently
Here is my questions :
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
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
(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?
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(...)
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?
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 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?
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
prepareThreshold
, but Unsupported.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
Record.__repr__
crashes in presence of duplicates in field name mapping. I added a test for this.
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.
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?
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
The documentation available at https://magicstack.github.io/asyncpg/current/# is currently showing version 0.5.3.
setup
will accept a coroutine, which will initialize the connection before returning it from pool.acquire()
(setup listeners etc).
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?
the docs are ok, but leave one wanting for some code snippet examples. Providing some example files would be nice
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.
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!
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)?
$ 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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.