Eric Soroos | 23 Aug 00:51 2011

Updates for PG 9.0


It appears that pyPgSQL has been abandoned and Postgresql is starting to 
pass it by.

I've worked up some changes to the quoting to allow it to use the new to 
9.0 hex encoding
for bytea fields. At the same time, I've changed the string and bytea 
quoting to use
PQescapeStringConn and PQescapeByteaConn so that they properly quote the 
incoming items
based on the connection settings.

Code is at:



uberSVN's rich system and user administration capabilities and model 
configuration take the hassle out of deploying and managing Subversion and 
the tools developers use with it. Learn more about uberSVN and get a free 
download at:
Matheus Fernandes | 5 Apr 22:54 2011

How to revoke/grant to specific database?

My postgres server has at least 10 databases/users. I want to forbid table creation/access from users who aren't the owner of the database.
I actually do know a way to set this by using the psql 'interface' and the phppgadmin, but I can't make it work with pypgsql!!

What I do with psql is this:
I log in with postgres user (the superuser), and use the '\c database-i-want-to-configure' command. When i'm connected to the database, I run the following commands:

revoke create, usage on schema public from public;
grant create, usage on schema public to user-from-connected-database;

By doing this, no one can visualize/create/drop tables from any database, except if the database was set to the user, on the above grant command.. and that's what I want to do on pypgsql.

What I've tried:
On pypgsql I connect with the database I want to set the privileges, run the commands above, and it run well, but makes no change...
And just to make sure i'm connected with the database, I used the select current_database() function and it returns the right db.

Is there any other way to make this happen?


Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
Pypgsql-users mailing list
Kingsley Turner | 12 Jan 23:56 2011

mxDateTime on pyPgSQL/PostgreSQL applies timezone?


I'm confused as to what's going on with a PostgreSQL "timestamp" type converted back to string.  The field in question is a "timestamp without time zone".

Selecting my timestamp from PostgreSQL via psql gives:

2011-01-12 21:54:11

Yet in my code, when I str() or mx.DateTime.ISO.strUTC() the mx.DateTime representation of the field returned by pyPgSQL, I get

2011-01-13 08:01:04.00
2011-01-13 08:54:11+0000

I'm currently in UTC+11 (10+summer)

Any suggestions as to what might be happening here?

When I first connect to PostgreSQL, I always SET TIME ZONE 'UTC'.


PS> Cross-posted to the Egenix mailing list

Protect Your Site and Customers from Malware Attacks
Learn about various malware tactics and how to avoid them. Understand 
malware threats, the impact they can have on your business, and how you 
can protect your company and customers by using code signing.
Pypgsql-users mailing list
QBox Linux Admin | 4 Sep 23:47 2010

Centos 5 support?


I'm trying to install pypgsql into a Centos 5 distribution.
I don't see pypgsql package.

I have downloaded RedHat .rpm package but itd don't install.
Also I tried downloading .tar.gz and build it. But I get some errors:

     in libpqmodule.c:74:20: error: Python.h: File or Directory doesn't 
         (also same error with structmember.h and fileobject.h)

     Other errors are:
         pgboolean.h:44: error: expected specifier-qualifier-list before 
         pgboolean.h:48: error: expected '=' , ',' , ';' ...

Is there an easy way to install an rpm package for Centos 5?
do you have an idea of the necesary paths or mising installation files 
for build the .tar.gz ?

Thank you,


This Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
Ivan De Masi | 18 Jun 09:21 2010

Is pypgsql dead?


is the project dead? It would be a pity!

Still no pypgsql for python 2.6 available...


ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win:
Sidney Cadot | 9 Mar 22:58 2009

Re: Inserting BYTEA values

On Mar 9, 2009, at 18:36, Clarence Gardner (ADECN) wrote:

> I don't know the details off the top of my head (it's been a couple  
> of years), but I recall there's a Binary class somewhere (possibly  
> in pypgsql, possible in a standard library) that you can wrap those  
> things in.

Well, there's PgSQL.PgBytea, but I haven't been able to use to solve  
my particular problem, unfortunately. I have a feeling it /should/  
work, but it doesn't.

Regards Sidney

Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
Sidney Cadot | 9 Mar 18:08 2009

Inserting BYTEA values

Dear all,

I have problems inserting 'BYTEA'-type values using PgSQL.

The code fragment given below fails to do what I want; as is, it gives  
me a 'libpq.OperationalError: ERROR: invalid byte sequence for  
encoding "UTF8"'.

I've tried many variations: wrapping 'data' as a PgSQL.PgBytea  
instance; using PgSQL.QuotePgBytea(); setting the encoding in the  
PgSQL.connect() call ...

However, I have not succeeded in doing what the code below intends to  
do: inserting a BYTEA field into the database comprising all 256  
possible byte values in sequence.

What am I doing wrong? Should I not use the Python 'str' type for this  
perhaps? I've checked the docs but I have been unable to find a  

Any help would be greatly appreciated.



from pyPgSQL import PgSQL

# open the database; make a cursor

connection = PgSQL.connect(database="testdb")
cursor = connection.cursor()

# Suppose I have a 256-byte string like this,
# containing all 256 possible byte values:

data = str.join("", [chr(i) for i in range(0, 256)])

# And I want to insert this into a 'bintab' table
# that has a 'data' field of PostgreSQL type 'BYTEA':

query = "INSERT INTO bintab(data) VALUES (%s);"
params = (data, )


cursor.execute(query, params)

# Say goodbye



Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
Tänia Jesini | 30 Jan 15:44 2009


This email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
Pypgsql-users mailing list
Ted Pederson | 30 Jan 01:06 2009

installing under windows and python2.6

First, is this possible? I see there are only binary installers up to version 2.4.

I tried to download the platform independent pyPgSQL. I built and installed it. The results of the build were:

running build
running build_py
running build_ext
building 'pyPgSQL.libpq.libpq' extension
error: None

I don't know if Error:None means success, or if it's an error without any other info.

I can import pyPgSQL.

When I try to import libpq from pyPgSQL I get an error in the "from libpq import *" line of the __init__ file of my libpq package.

I have added a path to the lib and bin dirs of my postPres installation, this is where libpq.dll resides.

I am lost, any help greatly appreciated.


This email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
Pypgsql-users mailing list
Hualan Rui | 31 Oct 15:09 2008

[Fwd: Why "to_date" is not working with pypgsql?]

Why the query "select starttime from dataproduct" is working fine with 
pypgsql, but the query "select to_date(starttime, 'DD-Mon-YYYY') from 
dataproduct" is not? What is the problem with the 'to_date"?

Any one can help? Please. Thank you!

This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
Roland Koebler | 1 Jun 16:51 2008

PgArrays and Unicode ?


I tried to insert unicode-values into a PostGreSQL-array, but
unfortunately that did not work but raised an UnicodeEncodeError (see
below). so:
- is is right, that pyPgSQL doesn't support unicode in arrays?
- if yes, how can this be solved?

here's the examle:

assume the following PostGreSQL-table:
    CREATE TABLE test (
	arr varchar[]

now in python:
    >>> from pyPgSQL import PgSQL
    >>> db = PgSQL.connect(database='test', user='test', password='test',
client_encoding=('UTF-8','ignore'), unicode_results=1)
    >>> cu = db.cursor()
    >>> cu.execute(u"""INSERT INTO test (arr) VALUES(%s)""", PgSQL.PgArray([u'ä']))
    Traceback (most recent call last):
      File "<stdin>", line 1, in ?
      File "/usr/lib/python2.4/site-packages/pyPgSQL/", line 3086, in execute
	parms = tuple(map(_quote, self.__unicodeConvert(parms)));
      File "/usr/lib/python2.4/site-packages/pyPgSQL/", line 2257, in _quote
	return value._quote()
      File "/usr/lib/python2.4/site-packages/pyPgSQL/", line 1328, in _quote
	return _handleArray(self.value)
      File "/usr/lib/python2.4/site-packages/pyPgSQL/", line 2239, in _handleArray
	_j = '%s%s,' % (_j, PgQuoteString(str(_i), 1))
    UnicodeEncodeError: 'ascii' codec can't encode character u'\xe4' in position 0: ordinal not in range(128)

inserting non-unicode values works:
    >>> cu.execute(u"""INSERT INTO test (arr) VALUES(%s)""", PgSQL.PgArray(['ä']))


This email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.