pypgsql-users — Discussion list for the pyPgSQL user community.

[Pypgsql-users] Updates for PG 9.0

[Eric S. <eri...@so...>]

Hello,

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: https://github.com/wiredfool/PyPgSQL

thanks

eric

[Pypgsql-users] How to revoke/grant to specific database?

[Matheus F. <mat...@gm...>]

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?

Thanks,

Matheus

Re: [Pypgsql-users] mxDateTime on pyPgSQL/PostgreSQL applies timezone?

[Karsten H. <Kar...@gm...>]

On Thu, Jan 13, 2011 at 09:56:49AM +1100, Kingsley Turner wrote:

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

...

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

UTC *is* +0000 ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

[Pypgsql-users] mxDateTime on pyPgSQL/PostgreSQL applies timezone?

[Kingsley T. <kin...@op...>]

Hi,

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

cheers,
-Kingsley

PS> Cross-posted to the Egenix mailing list

Re: [Pypgsql-users] Centos 5 support?

[QBox L. A. <li...@qb...>]

  El 04/09/10 23:47, QBox Linux Admin escribió:

Ok,

Finally I build pypgsql from tar.

I installed various packages and reboot, I'm not sure what of them are 
really required: python-setuptools, python-tools, python-devel, 
postgresql-devel, postgresql-libs, postgresql-python.

Thank You anyway,



>    Hi,
>
> 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
> exist.
>           (also same error with structmember.h and fileobject.h)
>
>
>       Other errors are:
>           pgboolean.h:44: error: expected specifier-qualifier-list before
> 'PyObject_HEAD'
>           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,
>
>
>           S.R.Parda
>
>
>
> ------------------------------------------------------------------------------
> This SF.net Dev2Dev email is sponsored by:
>
> Show off your parallel programming skills.
> Enter the Intel(R) Threading Challenge 2010.
> http://p.sf.net/sfu/intel-thread-sfd
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users

[Pypgsql-users] Centos 5 support?

[QBox L. A. <li...@qb...>]

  Hi,

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 
exist.
         (also same error with structmember.h and fileobject.h)


     Other errors are:
         pgboolean.h:44: error: expected specifier-qualifier-list before 
'PyObject_HEAD'
         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,


         S.R.Parda

Re: [Pypgsql-users] Is pypgsql dead?

[Ivan De M. <it-...@as...>]

Am 18.06.2010 18:23, schrieb Dietmar Schwertberger:
> Ivan De Masi schrieb:
>> is the project dead? It would be a pity!
> Almost...

Hi Dietmar,

hmm, that's sad!

> 
>> Still no pypgsql for python 2.6 available...
> I'm still using pypgsql. If you want to have a binary for win32, I
> can email it it to you.

Thanks, but I'm working with linux. Tested now pypgsql-2.5 with
python-2.6 on openSUSE 11.2 and it seems to work!

Regards,
Ivan

Re: [Pypgsql-users] Is pypgsql dead?

[Dietmar S. <mai...@sc...>]

Ivan De Masi schrieb:
> is the project dead? It would be a pity!
Almost...

> Still no pypgsql for python 2.6 available...
I'm still using pypgsql. If you want to have a binary for win32, I
can email it it to you.


Regards,

Dietmar

[Pypgsql-users] Is pypgsql dead?

[Ivan De M. <it-...@as...>]

Hi,

is the project dead? It would be a pity!

Still no pypgsql for python 2.6 available...

Regards,
Ivan

Re: [Pypgsql-users] Inserting BYTEA values

[Dietmar S. <mai...@sc...>]

Sidney Cadot wrote:
> 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.

Hello Sidney,

I'm storing compressed strings as binary data in PostgreSQL. For me 
PgBytea(data) is working to convert the data into an argument suitable 
for pyPgSQL.
I can't show the SQL code, as I'm using a wrapper, but I don't
remember doing anything special.

I'm using named parameters and a dictionary for the data.
The revisions are 2.5.1 for Python and pyPgSQL.


Regards,

Dietmar

Re: [Pypgsql-users] Inserting BYTEA values

[Sidney C. <sid...@ji...>]

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

[Pypgsql-users] Inserting BYTEA values

[Sidney C. <sid...@ji...>]

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

Any help would be greatly appreciated.

Sidney

##############

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

# Now execute this (THIS WILL THROW AN EXCEPTION)

cursor.execute(query, params)

# Say goodbye

cursor.close()
connection.close()

##############

Re: [Pypgsql-users] installing under windows and python2.6

[Dietmar S. <mai...@sc...>]

Hello Ted,

not too much traffic on this list any more.
I don't think that pyPgSQL is maintained any more or that it makes sense 
to report any bugs.
It's a pity, because I'm also still using pyPgSQL.


I don't use Python 2.6, but 2.5, so I can't provide a binary.


You probably don't have pg_config in the path and also did not edit 
setup.py with your include directory.
You need to tell setup.py where to find the PostgreSQL include files.
For my installation the automatic approach also did not work because the 
bin directory is not in the path and so I had to add the paths manually:

     include_dirs = [os.popen("pg_config --includedir").read().strip()] 
+ [r"C:\Program Files\PostgreSQL\8.3\include"]
     library_dirs = [os.popen("pg_config --libdir").read().strip()] + 
[r"C:\Program Files\PostgreSQL\8.3\lib"]


I've pasted below the resulting output from "python.exe setup.py install".

When you're using pyPgSQL, check your PostgreSQL log file for any 
warnings regarding (un)escaped characters.
I've just modified my PgSQL.py module to add an "E" before any escaped 
string:

class PgBytea:
[...]
     def _quote(self, forArray=0):
	if self.value:
	    return "E"+PgQuoteBytea(self.value, forArray)
	return 'NULL'
[...]
def _quote(value, forProc=False):
[...]
     elif isinstance(value, StringType):
	return "E"+PgQuoteString(value)


Regards,

Dietmar



running install
running build
running build_py
running build_ext
running install_lib
creating J:\Python25\Lib\site-packages\pyPgSQL
copying build\lib.win32-2.5\pyPgSQL\PgSQL.py -> 
J:\Python25\Lib\site-packages\py
PgSQL
copying build\lib.win32-2.5\pyPgSQL\__init__.py -> 
J:\Python25\Lib\site-packages
\pyPgSQL
creating J:\Python25\Lib\site-packages\pyPgSQL\libpq
copying build\lib.win32-2.5\pyPgSQL\libpq\__init__.py -> 
J:\Python25\Lib\site-pa
ckages\pyPgSQL\libpq
copying build\lib.win32-2.5\pyPgSQL\libpq\libpq.pyd -> 
J:\Python25\Lib\site-pack
ages\pyPgSQL\libpq
byte-compiling J:\Python25\Lib\site-packages\pyPgSQL\PgSQL.py to PgSQL.pyc
byte-compiling J:\Python25\Lib\site-packages\pyPgSQL\__init__.py to 
__init__.pyc

byte-compiling J:\Python25\Lib\site-packages\pyPgSQL\libpq\__init__.py 
to __init
__.pyc
running install_egg_info
Removing J:\Python25\Lib\site-packages\pyPgSQL-2.5.1-py2.5.egg-info
Writing J:\Python25\Lib\site-packages\pyPgSQL-2.5.1-py2.5.egg-info





Ted Pederson wrote:
> 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.
> 
> Ted
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> SourcForge Community
> SourceForge wants to tell your story.
> http://p.sf.net/sfu/sf-spreadtheword
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users

[Pypgsql-users] unsubscribe-me

[Tänia J. <tj...@gm...>]

[Pypgsql-users] installing under windows and python2.6

[Ted P. <ted...@gm...>]

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.

Ted

Re: [Pypgsql-users] [Fwd: Why "to_date" is not working with pypgsql?]

[johnf <jfa...@yo...>]

On Friday 31 October 2008 07:09:28 am Hualan Rui wrote:
> to_date

Could it be that starttime is not 'text' data type?

-- 
John Fabiani

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

[Hualan R. <Hua...@na...>]

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!

[Pypgsql-users] PgArrays and Unicode ?

[Roland K. <r.k...@ya...>]

hi,

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/PgSQL.py", line 3086, in execute
	parms = tuple(map(_quote, self.__unicodeConvert(parms)));
      File "/usr/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", line 2257, in _quote
	return value._quote()
      File "/usr/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", line 1328, in _quote
	return _handleArray(self.value)
      File "/usr/lib/python2.4/site-packages/pyPgSQL/PgSQL.py", 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(['ä']))


regards,
Roland

Re: [Pypgsql-users] async usage

[David J B. <shh...@gm...>]

I read something about Twisted
(http://twistedmatrix.com/projects/core/documentation/howto/enterprise.html)
some time ago, but I didn't use it finally.

Maybe you can try it and share the results :)
-- 


David J.Brenes

"Imagination is more important than Knowledge"

[Pypgsql-users] async usage

[Steve B. <st...@hu...>]

Is there a good way to use pyPgSQL in a non-blocking way? I saw some 
stuff about this in the documentation but the PQconnectStart function 
doesn't seem to be implemented. All I could find on that were mailing 
list posts from 2002 saying it is kind of broken.

Thanks

[Pypgsql-users] Limit of the number of retrieved rows?

[David J. B. <shh...@gm...>]

Hi.

First, sorry for my english as I'm not english speaker and I have not
practised it for a while.

I have a table with more than 36 millions of rows, and I'm executing a
very simple query (Select * from table) to retrieve all the data and
porcess it in python.

The problem is that when I have the results, the program only processed
35.999.999 rows. I have tried some versions of my script and it always
process 35.999.999 rows.

My script is as simple as

cursor.execute("SELECT * FROM table")

data = cursor.fetchone()

while (data):

    #a lot of nasty instructions
    consulta = cursor.fetchone()

I have been debugging my script for a while, but I can't imagine where
is the problem (it's difficult to debug more than 36 millions of rows)
and I'm wondering if there's some limit for pypgsql (or postgre) because
the number 35.999.999 seems a little bit curious...

Thanks for the help.
---
David J. Brenes

[Pypgsql-users] pypgsql for openSUSE 10.3

[Ivan De M. <it-...@as...>]

Dear list,

I am looking for a pypgsql-rpm file for openSUSE 10.3 - no chance :-/

Does anybody know a repository for openSUSE 10.3?

The latest one i found is for 10.0.

Thanks,
Ivan

[Pypgsql-users] installer package for python 2.5? [virus checked]

[<Kin...@so...>]

Hi,

Is there an pyPgSQL  installer package planned for windows && python 2.5?

If not, then obviously I need a windows compiler to build this myself, 
would something like 
cygwin gcc suffice?

cheers,
-kt

Re: [Pypgsql-users] pyPgSQL for Python 2.5 on Win32

[Markus S. <ma...@bl...>]

Hey Alec,

Alec Hussey wrote:
> How come there isnt a Python2.5/Win32 binary? This is so frustrating and 
> I've searched everywhere on the net, i read mailing list archives and 
> came up nowhere. I really need my application to be able to run in 
> windows and it uses Python 2.5 specific code. Could you please provide a 
> Python 2.5 binary for Win32.

This is open source software. You are free to create one yourself and 
provide that to the community.

Regards

Markus

[Pypgsql-users] pyPgSQL for Python 2.5 on Win32

[Alec H. <ale...@gm...>]

Hello all,

How come there isnt a Python2.5/Win32 binary? This is so frustrating and
I've searched everywhere on the net, i read mailing list archives and came
up nowhere. I really need my application to be able to run in windows and it
uses Python 2.5 specific code. Could you please provide a Python 2.5 binary
for Win32.

Thanks!
--
Alec Hussey