Menu
Well, the question pretty much summarises it. My db activity is very update intensive, and I want to programmatically issue a Vacuum Analyze. However I get an error that says that the query cannot be executed within a transaction. Is there some other way to do it?
353
Install a Postgres server locally and create a database. Use Python with SQLAlchemy to connect to the database and create tables. Use Python with SQLAlchemy to insert data and query the database.
I've compared the speed of reading form postgresql database in python using SQLAlchemy and psycopg2. The psycopg2 is over 2x faster than SQLAlchemy on small table. This behavior is expected as psycopg2 is a database driver for postgresql while SQLAlchemy is general ORM library.
This is a flaw in the Python DB-API: it starts a transaction for you. It shouldn't do that; whether and when to start a transaction should be up to the programmer. Low-level, core APIs like this shouldn't babysit the developer and do things like starting transactions behind our backs. We're big boys--we can start transactions ourself, thanks.
With psycopg2, you can disable this unfortunate behavior with an API extension: run connection.autocommit = True. There's no standard API for this, unfortunately, so you have to depend on nonstandard extensions to issue commands that must be executed outside of a transaction.
No language is without its warts, and this is one of Python's. I've been bitten by this before too.
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
