Hacker News Reader

Comment by perlgeek | Hacker News Reader

A somebody who first started using DB code from Perl, and later learned Python, I always wondered by Python doesn't have a general database interface like DBI in Perl.

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).

View on HN

Creator of sqlalchemy here, I came from the perl DBI and JDBC worlds prior to starting python. We have pep 249, but it's generally a very loose spec, projects that implement it basically choose how much they want to follow or not follow it, and then with the introduction of asyncio people are walking away from the whole thing as the spec has not evolved pretty much at all for many years. At the top is that there is no actual library in which these drivers all need to bind towards as is the case with DBI and JDBC.

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.

I had the chance to complain to Guido once about wanting a better DBAPI, especially on the subject of result set metadata/typing (looking at you, pysqlite with your worthless cursor.description)

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.

There is https://www.python.org/dev/peps/pep-0249/ . I suppose part of the issue is that there is probably deviation from the standard and lots of extensions. I imagine that the standard means there is a lot of shared code between the different connectors though.

As far as I know, there is zero or close to zero shared code.

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/

[1]: https://wiki.python.org/moin/ODBC

[2]: https://docs.sqlalchemy.org/en/14/core/

JDBC exists but I honestly think it's a mistake. Higher-level frameworks still end up with a bunch of special cases for handling different lower-level implementations, and meanwhile a lot of functionality gets stuck behind the lowest common denominator interface that JDBC is (e.g. it took forever to get any kind of async support even when both the things above and the things below JDBC were doing great at it).

> e.g. it took forever to get any kind of async support even when both the things above and the things below JDBC were doing great at it

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.

Reply on news.ycombinator.com