Building a Django driver for Psycopg 3
psycopg.org
psycopg.org
Main takeaways:
- Asyncio from the ground up
- Uses PQexecParams to do database-side escaping and interpolation
- ContextManager and transaction api improvements for
- Python only fallback if the C extension fails to build
> - Python only fallback if the C extension fails to build
These two are really big! The former, for first-party async support, making it much easier to use with async frameworks.
The latter would make it much easier to ship in containers. I assume it's still better use the C version, for performance reasons.
Otherwise it’s going to be a source of mysterious performance issues with a single error message hidden somewhere in your CI or other logs.
I'd agree with something like a warning on first load or maybe something like an optional package so you could pin `psycopg3[cext]` if you want to guarantee the extension installed.
In Perl, all the database specific modules have a DBI backend, and all the higher-level modules (django-like frameworks, for example) rely on DBI.
In Python, SQLAlchemy has its own psycopg and cmysql integrations, and does django, and likely several other frameworks.
(Java has a similar standard, with JDBC, I believe; though I have never used it, so I might be misunderstanding something here).
PEP 249 is great to have, and it's nice to at least have a starting point for learning a new database connector library.
But not all database connector libraries implement the specification. One well-known library [0] explicitly and deliberately violates the spec with no spec-compliant "escape hatch". Also the style of passing parameters as a single sequence (e.g. list or tuple) tends to be a newbie trap, and having 4 different placeholder styles can be annoying.
That said, there are ODBC connector libraries [1] if you really do need a uniform interface. But at that point you might be better off with the SQLAlchemy "Core" query builder [2].
[0]: Asyncpg, https://magicstack.github.io/asyncpg/
is there even support for async jdbc? I tought they dropped the idea ? (https://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/20...). The reasoning is stupid because async drivers is not only about threading and more about i/o, but w/e.
All of that said the reality is that databases are so different in how they define client interactions you're going to have these problems with either approach. The transparency of Python allows it to be ultimately easier to work through these issues, though I've always wished there was a better pep249 story.
This was after a mypy/typing talk, so highly tangentially relevant I suppose.
While it's not perfect, I think JDBC really is the gold standard, and I wish there was a DBAPI spec that was a bit closer to that, especially something with proper prepared statements. I haven't used database/sql in Go but it seems okay too.
Thanks for sqlalchemy - it has become the de facto DBAPI in many projects I've worked on.
I had déjà vu reading the section on bind parameters in aggregate queries. The oracle backend had a similar issue that was fixed/hacked by comparing values and grouping them into named parameters. https://code.djangoproject.com/ticket/27632
with connect(DSN) as conn:
with conn.transaction():
do_something()
with conn.transaction():
do_something_nested()
with conn.transaction() as tx:
do_something_else()
# we were just testing and we don't really want to do this
tx.rollback()
# and here the connection is closed
I ended just giving up and doing a close() after searching for every use. But even then didn't immediately understand what was going on.
With XXX do Y
is totally standard - but here we open but do not close.
Do you want to specify parameters that have a scope larger than a single statement and send the whole parameterized batch in one go?
Thank you!!
(Assuming you are only talking about Postgres libraries.)
Maybe it's somewhat obsolete now that Psycopg itself supports async. I think most people used Asyncpg because it's "easier" and supports useful stuff like connection pools.