Python PostgreSQL modules. Which is best? [closed]
I've seen a number of postgresql modules for python like pygresql, pypgsql, psyco. Most of them are Python DB API 2.0 compliant, some are not being actively developed anymore. Which module do you recommend? Why?
Asked by: Lily411 | Posted: 05-10-2021
psycopg2 seems to be the most popular. I've never had any trouble with it. There's actually a pure Python interface for PostgreSQL too, called bpgsql. I wouldn't recommend it over psycopg2, but it's recently become capable enough to support Django and is useful if you can't compile C modules.Answered by: Maddie976 | Posted: 06-11-2021
I suggest Psycopg over Psycopg2 since the first one seems a bit more sable. At least in my experience. I have an application running 24/7 and sometimes I was getting random memory crashes (double free or corruption errors) from Psycopg2. Nothing I could have debug fast or easily since it's not a Python error but a C error. I just switched over to Pyscopg and I did not get any crashes after that.
Also, as said in an other post, bpgsql seems a very good alternative. It's stable and easy to use since you don't need to compile it. The only bad side is that library is not threadsafe.
Pygresql seems nice, there's a more direct way to query the database with this library. But I don't know how stable this one is though.Answered by: Vanessa583 | Posted: 06-11-2021
I've used pg8000 without any problems in the past 3 years. It's uptodate and available on pypi and works on both python2 and python3. You can use "pip install pg8000" to quickly get it (don't forget to use --proxy=yourproxy:yourport if you're behind a firewall).
If you're worried about thread safety, it also provides a score for thread safety (see: http://pybrary.net/pg8000/dbapi.html and https://www.python.org/dev/peps/pep-0249/ for definitions of the different levels of thread safety) (although I have no yet used threads with psql).Answered by: Freddie223 | Posted: 06-11-2021
In my experience, psycopg2 is the most used library for this. Like you said, it's DB API 2.0 compliant, wich gives a solid interface to work with.
For those who find the standard API to be a little too verbose and hard to work with, I made a small library that might help:
Psycopg1 is known for better performance in heavilyy threaded environments (like web applications) than Psycopg2, although not maintained. Both are well written and rock solid, I'd choose one of these two depending on use case.Answered by: Kelvin596 | Posted: 06-11-2021
I uses only psycopg2 and had no problems with that.Answered by: Kimberly889 | Posted: 06-11-2021
python - PostgreSQL procedural languages: to choose?
I have been working with PostgreSQL, playing around with Wikipedia's millions of hyperlinks and such, for 2 years now. I either do my thing directly by sending SQL commands, or I write a client side script in python to manage a million queries when this cannot be done productively (efficiently and effectively) manually. I would run my python script on my 32bit laptop and have it communicate with a $6000 64bit serv...
python - Dynamic data in postgresql
I intend to have a python script do many UPDATEs per second on 2,433,000 rows. I am currently trying to keep the dynamic column in python as a value in a python dict. Yet to keep my python dict synchronized with changes in the other columns is becoming more and more difficult or nonviable. I know I could put the autovacuum on overdrive, but I wonder if this would be enough to catch up with the sheer amount of UPDAT...
postgresql - python postgres cursor timestamp issue
I am somewhat new to transactional databases and have come across an issue I am trying to understand. I have created a simple demonstration where a database connection is stored inside each of the 5 threads created by cherrypy. I have a method that displays a table of timestamps stored in the database and a button to add a new record of time stamps. the table has 2 fields, one for the datetime.datetime.n...
python - psycopg2 on OSX: do I have to install PostgreSQL too?
I want to access a postgreSQL database that's running on a remote machine, from Python in OS/X. Do I have to install postgres on the mac as well? Or will psycopg2 work on its own. Any hints for a good installation guide for psycopg2 for os/x?
Python threads - crashing when they access postgreSQL
here is a simple threading program which works fine: import psycopg2 import threading import time class testit(threading.Thread): def __init__(self, currency): threading.Thread.__init__(self) self.currency = currency def run(self): global SQLConnection global cursor SQLString = "Select dval from ddata where dname ='%s' and ddate = '2009-07-17'" \ ...
postgresql - Python to Postgres interface with real prepared statements?
I've been trying to find a postgres interface for python 2.x that supports real prepared statements, but can't seem to find anything. I don't want one that just escapes quotes in the params you pass in and then interpolates them into the query before executing it. Anyone have any suggestions?
python - Django 1.1.1: How should I store an empty IP address using PostgreSQL?
I am writing a Django application that stores IP addresses with optional routing information. One of the fields for the IP model I have created is nexthop (for next-hop routes), which will usually be empty. Originally we intended to use MySQL, but now project requirements have changed to use PostgreSQL. Here is a stripped down version of my model: class IP(models.Model): address =...
Checking if a postgresql table exists under python (and probably Psycopg2)
How can I determine if a table exists using the Psycopg2 Python library? I want a true or false boolean.
postgresql - To have two Pg queries in one Python method
Thank you for Denis who solves the first bug! How can you have two Postgres queries in one Python method? Example where the 2nd query is not run def comp_func(pgmasi): pgmasi.query("""CREATE TABLE courses ( course_id SERIAL PRIMARY KEY)""") pgmasi.query("""CREATE TABLE files ( # not executed for some unknown reason fi...
postgresql - Recreating Postgres COPY directly in Python?
I have a block of data, currently as a list of n-tuples but the format is pretty flexible, that I'd like to append to a Postgres table - in this case, each n-tuple corresponds to a row in the DB. What I had been doing up to this point is writing these all to a CSV file and then using postgres' COPY to bulk load all of this into the database. This works, but is suboptimal, I'd prefer to be able to do this all direc...