5 ms·
Pg8000 – Pure-Python PostgreSQL driver
- mdaniel 6y agoYou'll want to be careful in "convert_paramstyle" as it does not appear to take python's dollar literals into account: https://www.postgresql.org/docs/13/sql-syntax-lexical.html#id-1.5.3.5.9.7.2 https://www.postgresql.org/docs/13/sql-syntax-lexical.html#i...
- jquaint 6y agoQuestion, what advantages does a pure python driver give you over the competition? Is it multi threading support? (psycopg2 does not support cursors over threads)
- jka 6y agoPersonal opinion, not really inherited from any authoritative wisdom: pure Python is useful because it: - Simplifies and improves the performance of deployment: it becomes a straightforward case of copying code files - Largely achieves the same stated goal of Java: write once, run anywhere - Allows for easy code inspection and debugging in production (it's debatable whether that's a good thing; I think it is, overall)
- grandinj 5y agoAgreed. It also unlocks another level of optimisation - the human one - I'm much better at reasoning my way through code improvements when I don't have to manage dealing with switching language/runtime models in the middle of the code :-)
- GateCrasher 5y agoIt is also easy to add to an executable with pyinstaller
- lmns 6y agoPackaging is simpler. psycopg2-binary is actually not recommended for production use, so you have to compile it yourself.
- ohlookabird 5y agoThanks for the pointer, I wasn't aware of this! I was wondering what the problem is, because I use psycopg2-binary in production without problems for ages. It seems the danger of psycopg2-binary is that one can end up with segfaults, due to the libpq library (and hence psycopg2) might use a different libssl than the system one (use by Python, with e.g. urllib and requests): https://www.psycopg.org/articles/2018/02/08/psycopg-274-released/ https://www.psycopg.org/articles/2018/02/08/psycopg-274-rele...
- BiteCode_dev 5y agoI've never heard of that, why would it not be?
- lmns 5y agoI don't know, that's what the developers say (last paragraph): https://pypi.org/project/psycopg2/ https://pypi.org/project/psycopg2/
- willvarfar 5y agoI once speeded up a program by changing from the normal native wrap MySQL drivers to a pure python one. The pure python MySQL drivers were marginally slower for cpython. The native drivers worked fine with pypy, but pypy didn’t give any real performance gains as the program spent all its time in the native code Then when testing pure python drivers we found pypy made the whole program massively faster.
- qeternity 5y agoThat’s surprising if your app spent most of the time in database calls, since 1) native code is still faster than pypy and 2) most of the time spent in database libraries is spent waiting for the database to return.
- dragonwriter 5y ago> That’s surprising if your app spent most of the time in database calls, since 1) native code is still faster than pypy and 2) most of the time spent in database libraries is spent waiting for the database to return I would argue a speedup (though maybe not a “massive” one) from pure python over a native extension is not that surprising, specifically because of (2), which makes it possible for the runtime-extension interface overhead to be more significant than (1). Native extensions tend to give a speed boost for computationally intensive tasks, but can’t really wait any faster, and aren’t free of overhead.
- willvarfar 5y agoMore details of my use case: The programs were ETLs that are generating big REPLACE INTO statements. Different databases and drivers do prepared statements in different ways. Postgres, for example, can receive the statement and the parameters and do the interpolation in the server. The params still need to be serial used by the client and the template isn’t reused, so it’s no big perf win. MySQL, on the other hand, needs the driver to do the interpolation. The server only knows how to execute sql strings. It turns out it is far better to prepare the sql in pure python where pypy knows all about types etc than to have the c MySQL driver do it. Exactly the same thing plays out in java. The MySQL java drivers are staggeringly inefficient at large prepared statements, and in my profiling massive amounts of time is spent in java functions in the MySQL driver that are trying to work out where the next question-mark is. Sadly the drivers are the poo they are. Iirc the mariadb java drivers are rather more optimized.
- fulafel 5y agoWider compatibility than just CPython, potentially at least.
- mjbrownie 5y agoI just managed to get it working on an ipads iVim python environment which is pretty crippled (no compiler etc).
- anentropic 5y agoIt is useful if you want a speed boost by running under PyPy Take all benchmarking with a pinch of salt, but see some stats here: https://suade.org/dev/12-requests-per-second-with-python/#the-baseline https://suade.org/dev/12-requests-per-second-with-python/#th...
- bencollier49 5y agoDelighted to see this. Installation of psycopg2 can be a nuisance thanks to the native compilation steps which don't always work as planned and rely on specific versions of libssl.
- deleted 5y ago[deleted]
- gjvc 5y agoexactly my motivation for ferreting it out and it's worked great for the simple scenarios. the only thing i'm missing is a .mogrify() function. this guy has put one together though; might see if we can get it migrated into the original version. https://github.com/realazthat/aiopg8000/blob/master/aiopg8000/mogrify.py https://github.com/realazthat/aiopg8000/blob/master/aiopg800...
- bogcotton 5y agoAfter suffering with this myself, I started using the psycopg2-binary package exclusively: https://pypi.org/project/psycopg2-binary/ https://pypi.org/project/psycopg2-binary/ Haven't had any issues since!
- gjvc 5y agogood to hear, but beware what it says in the last paragraph here https://pypi.org/project/psycopg2/ https://pypi.org/project/psycopg2/ ... "The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources." ...or use one from your operating system distribution.
- SonOfLilit 5y agoTo all those wondering _why_ that is the recommendation, I did some digging and the answer is "the binary package is compiled against one version of libssl, and if your system happens to use a different one, in some weird edge cases it will lead to a segfault"
- quietbritishjim 5y agoBeware that for strict compliance with Python's DB-API 2.0 the database driver needs to obey weird rules about transactions: * All statements start a transaction automatically if one isn't already open. * You need to call .commit() or .rollback() on the connection object to do the commit or rollback. * The driver can optionally support an "autocommit mode" where, instead of the above rules, every individual statement is wrapped in its own transaction and committed immediately. That's the theory. Many database drivers, including the built-in SQLite driver, differ in practice (for example, it doesn't automatically start a transaction for a SELECT statement). The biggest common practical difference is an implementation detail of so-called "autocommit mode". Often that is NOT implemented by actually issuing a "BEGIN TRANSACTION" before and "COMMIT" after every statement. Instead, it often just issues the statement without any automatic transaction statements whatsoever, relying on the fact that the underlying database engine will immediately commit any statements outside an explicit transaction (i.e. has its own autocommit mode). That means if you do want complete manual control of transactions in a Python DB-API driver (including sqlite), then - confusingly - you need to set the autocommit option to true! It's not clear to me whether/how this applies to pg8000 driver in the OP. There is a section on autocommit but it doesn't explicitly say what it does.
- jeltz 5y agoPersonally I think this was q huge mistake since almost all of the rest of the world uses autocommit by default.
- muxator 5y agoThe existence of the autocommit feature in DB drivers is something I never really understood: why an application developer would want to relinquish the direct control of one of the most powerful features of a relational DB? Unless one's interaction with the database are really basic (i.e. a single SELECT statement), one would want to be as explicit as possible regarding where transaction boundaries are. Having a default that hides this aspect from my code always seemed a false convenience to me. edit: this is just a tangential reflection inspired by your comment, not an answer / nor I wanted to detract from your fact statement.
- gjvc 5y agothe title was editorialised from pg8000 to Pg8000 -- the github project clearly shows the former is correct.