5 ms·
Psycopg 3.2 released – PostgreSQL driver for Python
- odie5533 2y agoEveryone seems still on psycopg2. Is there a compelling reason to switch? Is Psycopg3 faster?
- __s 2y agoYes, https://www.timescale.com/blog/psycopg2-vs-psycopg3-performance-benchmark https://www.timescale.com/blog/psycopg2-vs-psycopg3-performa...
- odie5533 2y agoThanks! The testing here though shows asyncpg is a lot faster https://github.com/MagicStack/asyncpg https://github.com/MagicStack/asyncpg Not sure there is a compelling reason to stay with psycopg3 then if asyncpg exists.
- frou_dh 2y agoI like that $1 etc can now be written instead of %s for placeholders in queries. Even though the latter was already safe, it simply looked toe-curling (like you were writing naive SQL-injectable queries all the time).
- glzone1 2y agoRight - and there does have to be some kind of non native transform mixed in? At least with shell scripting this kind of thing has bitten me frequently.
- JoelJacobson 2y agoAuthor here of the PostgreSQL raw queries feature mentioned in the release notes, which allows users to use PostgreSQL's native placeholders ($1, $2, etc.) instead of the standard %s placeholder [1]. from psycopg import connect, RawCursor with connect(dsn, cursor_factory=RawCursor) as conn: with conn.cursor() as cur: cur.execute("SELECT $1, $2", [1, "Hello"]) assert cur.fetchone() == (1, "Hello") The release notes highlight several interesting advantages I hadn't considered. However, they don't mention the primary motivation behind my contribution, so I thought I'd share it here: I was concerned that psycopg replaces the %s placeholders using regexes, which might inadvertently alter my SQL queries. My goal was to ensure that my SQL queries were passed directly to PostgreSQL "as is," without any intermediate modifications. Only PostgreSQL's parser can safely and accurately parse all SQL queries. While this concern might seem theoretical if you're certain your queries don't contain any %s, it still felt much more reassuring to eliminate this potential source of bugs entirely. [1] https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#raw-query-cursors https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#...
- whalesalad 2y agoIf you are operating in an environment with asyncio check out asyncpg - https://github.com/MagicStack/asyncpg https://github.com/MagicStack/asyncpg Performance charts speak for themselves. This lib uses the binary protocol while most are using text based comms.
- odie5533 2y agoWhy is asyncpg so much faster?
- whalesalad 2y agohttp://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/ http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...
- odie5533 2y agoFrom reading that, it's still unclear to me why asyncpg is faster than async psycopg3.
- whalesalad 2y ago> Unlike psycopg2, asyncpg implements PostgreSQL binary I/O protocol, which, aside from performance benefits, allows for generic support of container types (arrays, composites and ranges).
- alanwreath 2y agoAny lineage between these two libraries? Any reason to mention it here other than being a different driver?
- whalesalad 2y agoThey are unrelated lineage wise, but accomplish similar things. One just happens to be substantially more performant than the other. I have been a psycopg user for many years but have started to utilize this lib in projects that rely on asyncio.