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

[Pypgsql-users] typprtlen

[Oliver V. <vec...@ao...>]

Hi!

I tried to use PgSQL on Debian/Woody and received following error:

Python 2.1.3 (#1, Sep  7 2002, 15:29:56)
[GCC 2.95.4 20011002 (Debian prerelease)] on linux2
Type "copyright", "credits" or "license" for more information.
 >>> from pyPgSQL import PgSQL
 >>> cx = PgSQL.connect('p200::test')
 >>> cu = cx.cursor()
 >>> cu.execute("select * from t1")
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.1/site-packages/pyPgSQL/PgSQL.py", line 2157, 
in execute
     self.__makedesc__()
   File "/usr/lib/python2.1/site-packages/pyPgSQL/PgSQL.py", line 1892, 
in __makedesc__
     _tn, _pl, _ia, _bt = _cache.getTypeInfo(_typ)
   File "/usr/lib/python2.1/site-packages/pyPgSQL/PgSQL.py", line 576, 
in getTypeInfo
     _res = self.__conn.conn.query("SELECT typname, typprtlen, typelem "
libpq.OperationalError: ERROR:  Attribute "typprtlen" not found

 >>>


ii  python-pgsql   2.0-3.1        A Python DB-API 2.0 interface to 
PostgreSQL

Am I doing something wrong, or is this a broken package in Debian?

Oliver

Re: [Pypgsql-users] Re: pyPgSQL 2.3 Released.

[<Bil...@mu...>]

Oliver Vecernik wrote:
> Billy G. Allie schrieb:
> > [...]
>  >
> > Support for reference cursors was added.  Since PostgreSQL 7.2, you could
> > create cursors in PL/pgSQL and pass a reference to the open cursor back to
> > the client.  PgSQL will now cast those referenced cursors into a Cursor
> > object that can be used to fetch the results in  the cursor.  For example
> > (assuming that mmYearInfo() returns a reference curosr):
> 
> Is it possible to get the source for mmYearInfo() to run this sample?

Here is the source for mmYearInfo().  It can be used as an example for 
creating functions that returns a reference cursor, but it will not work 
without the schema and data.  Unfortunately, the schema and data are not mine 
to give.

CREATE FUNCTION mmyearinfo () RETURNS refcursor
    AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR
        SELECT t1.model_year, t1.mktg_div_name, t1.model_desc,
               t1.book_types, t3.vehicle_syskey
        FROM mmyearen t1, mktgdiv t2, vehicle t3
        WHERE t1.model_year = t3.model_year AND
              t1.mktg_div_name = t2.mktg_div_name AND
              t1.model_desc = t3.model_desc AND
              t2.mktg_div_id = t3.mktg_div_id AND
              t3.lang_code = ''EN''
        ORDER BY model_year DESC, mktg_div_name, model_desc;
    RETURN ref;
END;
'
    LANGUAGE plpgsql;

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

[Pypgsql-users] Re: pyPgSQL 2.3 Released.

[Oliver V. <vec...@ao...>]

Billy G. Allie schrieb:
> [...]
 >
> Support for reference cursors was added.  Since PostgreSQL 7.2, you could
> create cursors in PL/pgSQL and pass a reference to the open cursor back to
> the client.  PgSQL will now cast those referenced cursors into a Cursor
> object that can be used to fetch the results in  the cursor.  For example
> (assuming that mmYearInfo() returns a reference curosr):

Is it possible to get the source for mmYearInfo() to run this sample?

Regards,
Oliver

Re: [Pypgsql-users] v 2.3 warning messages

[Billy G. A. <bg...@mu...>]

Michael Engelhart wrote:
> Hi -
> i'm running pyPgSQL 2.3 on Mac OS X 10.2.2, PostgreSQL 7.3, and  Python 
> 2.2
> 
> When doing any SELECT, UPDATE, INSERT or DELETE operations on the 
> database I get the following database warning errors:
> 
> WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
> WARNING:  Closing pre-existing portal "PgSQL_0061BE10"
> WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
> WARNING:  PerformPortalClose: portal "pgsql_006e0460" not found
> 
> 
> These do not occur when I access the database using the psql CLI 
> application.
> 
> Everything appears to work as it should even with these warnings but 
> they certainly are preventing me from releasing a production system 
> until I can figure out what is causing this.
> 
> I have already posted this to postgresql-general but Bruce Mojiam 
> seemed to think it was related to the Python interface since he was 
> unable to make it occur with any of the other Python interfaces.
> 
> Any ideas?

Here is the patch to correct the problem.  CVS will be updated shortly.

Re: [Pypgsql-users] v 2.3 warning messages

[Billy G. A. <bg...@mu...>]

Gerhard Haering wrote:
> * Michael Engelhart <men...@ma...> [2002-12-22 17:48 -0500]:
> > Hi -
> > i'm running pyPgSQL 2.3 on Mac OS X 10.2.2, PostgreSQL 7.3, and  Python 
> > 2.2
> > 
> > When doing any SELECT, UPDATE, INSERT or DELETE operations on the 
> > database I get the following database warning errors:
> > 
> > WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
> > WARNING:  Closing pre-existing portal "PgSQL_0061BE10"
> > WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
> > WARNING:  PerformPortalClose: portal "pgsql_006e0460" not found
> > 
> > These do not occur when I access the database using the psql CLI 
> > application.
> 
> They do [1] ;-)
> 
> > Everything appears to work as it should even with these warnings but 
> > they certainly are preventing me from releasing a production system 
> > until I can figure out what is causing this.
> > 
> > I have already posted this to postgresql-general but Bruce Mojiam 
> > seemed to think it was related to the Python interface since he was 
> > unable to make it occur with any of the other Python interfaces.
> 
> I already answered there.
> 
> > Any ideas?
> 
> Yes, con.conn.toggleShowQuery does the trick, again:
> 
> #v+
> gerhard@gargamel:~$ python
> Python 2.2.2 (#1, Nov 30 2002, 23:19:58) 
> [GCC 2.95.4 20020320 [FreeBSD]] on freebsd4
> Type "help", "copyright", "credits" or "license" for more information.
> >>> from pyPgSQL import PgSQL
> >>> con = PgSQL.connect()
> >>> con.conn.toggleShowQuery
> 'On'
> >>> cursor = con.cursor()
> QUERY: BEGIN WORK
> >>> cursor.execute("select * from test")
> QUERY: DECLARE "PgSQL_0811F1EC" CURSOR FOR select * from test
> QUERY: FETCH 1 FROM "PgSQL_0811F1EC"
> QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 23
> QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 1043
> >>> result = cursor.fetchmany(5)
> QUERY: FETCH 4 FROM "PgSQL_0811F1EC"
> >>> result
> [[None, 'A'], [None, 'B'], [None, 'C'], [None, 'F'], [None, 'F']]
> >>> con.commit()
> QUERY: CLOSE PgSQL_0811F1EC
> QUERY: COMMIT WORK
> >>> 
> #v-
> 
> This gives me a warning like this:
> 
> #v+
> WARNING:  PerformPortalClose: portal "pgsql_0811f1ec" not found
> #v-
> 
> The SQL pyPgSQL emits looks perfectly valid to me. So I'm inclined to think
> that the PostgreSQL warning is superfluous, at best.
> 
> Gerhard

The close of the cursor isn't quoted, so PostgreSQL converts it to lower case. 
 This causes the problem.  I will generate a patch for this tomorrow.

Later.

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] v 2.3 warning messages

[Gerhard H. <ger...@gm...>]

* Michael Engelhart <men...@ma...> [2002-12-22 17:48 -0500]:
> Hi -
> i'm running pyPgSQL 2.3 on Mac OS X 10.2.2, PostgreSQL 7.3, and  Python 
> 2.2
> 
> When doing any SELECT, UPDATE, INSERT or DELETE operations on the 
> database I get the following database warning errors:
> 
> WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
> WARNING:  Closing pre-existing portal "PgSQL_0061BE10"
> WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
> WARNING:  PerformPortalClose: portal "pgsql_006e0460" not found
> 
> These do not occur when I access the database using the psql CLI 
> application.

They do [1] ;-)

> Everything appears to work as it should even with these warnings but 
> they certainly are preventing me from releasing a production system 
> until I can figure out what is causing this.
> 
> I have already posted this to postgresql-general but Bruce Mojiam 
> seemed to think it was related to the Python interface since he was 
> unable to make it occur with any of the other Python interfaces.

I already answered there.

> Any ideas?

Yes, con.conn.toggleShowQuery does the trick, again:

#v+
gerhard@gargamel:~$ python
Python 2.2.2 (#1, Nov 30 2002, 23:19:58) 
[GCC 2.95.4 20020320 [FreeBSD]] on freebsd4
Type "help", "copyright", "credits" or "license" for more information.
>>> from pyPgSQL import PgSQL
>>> con = PgSQL.connect()
>>> con.conn.toggleShowQuery
'On'
>>> cursor = con.cursor()
QUERY: BEGIN WORK
>>> cursor.execute("select * from test")
QUERY: DECLARE "PgSQL_0811F1EC" CURSOR FOR select * from test
QUERY: FETCH 1 FROM "PgSQL_0811F1EC"
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 23
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 1043
>>> result = cursor.fetchmany(5)
QUERY: FETCH 4 FROM "PgSQL_0811F1EC"
>>> result
[[None, 'A'], [None, 'B'], [None, 'C'], [None, 'F'], [None, 'F']]
>>> con.commit()
QUERY: CLOSE PgSQL_0811F1EC
QUERY: COMMIT WORK
>>> 
#v-

This gives me a warning like this:

#v+
WARNING:  PerformPortalClose: portal "pgsql_0811f1ec" not found
#v-

The SQL pyPgSQL emits looks perfectly valid to me. So I'm inclined to think
that the PostgreSQL warning is superfluous, at best.

Gerhard

[1] ... if under psql you use the same SQL that pyPgSQL sends over the wire.
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/
Embedded database for Python:   http://pysqlite.sf.net/

[Pypgsql-users] v 2.3 warning messages

[Michael E. <men...@ma...>]

Hi -
i'm running pyPgSQL 2.3 on Mac OS X 10.2.2, PostgreSQL 7.3, and  Python 
2.2

When doing any SELECT, UPDATE, INSERT or DELETE operations on the 
database I get the following database warning errors:

WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
WARNING:  Closing pre-existing portal "PgSQL_0061BE10"
WARNING:  PerformPortalClose: portal "pgsql_0061be10" not found
WARNING:  PerformPortalClose: portal "pgsql_006e0460" not found


These do not occur when I access the database using the psql CLI 
application.

Everything appears to work as it should even with these warnings but 
they certainly are preventing me from releasing a production system 
until I can figure out what is causing this.

I have already posted this to postgresql-general but Bruce Mojiam 
seemed to think it was related to the Python interface since he was 
unable to make it occur with any of the other Python interfaces.

Any ideas?

thanks
Mike Engelhart

[Pypgsql-users] pyPgSQL 2.3 Released.

[Billy G. A. <Bil...@mu...>]

[Yes, I know this announcement is going out a week late]

Announce: pyPgSQL - Version 2.3 is released.
===========================================================================

pyPgSQL v2.3 has been released.

It is available at http://pypgsql.sourceforge.net.

pyPgSQL is a package of two (2) modules that provide a Python DB-API 2.0
compliant interface to PostgreSQL databases. The first module, libpq, is
written in C and exports the PostgreSQL C API to Python.  The second module,
PgSQL, provides the DB-API 2.0 compliant interface and support for various
PostgreSQL data types, such as INT8, NUMERIC, MONEY, BOOL, ARRAYS, etc. This
module is written in Python and works with PostgreSQL 7.0 or later and Python
2.0 or later.

It was tested with PostgreSQL 7.0.3, 7.1.3, 7.2.2, 7.3, Python 2.0.1, 2.1.3
and 2.2.2.

Note: It is highly recommended that you use PostgreSQL 7.2 or later and
      Python 2.1 or later.  If you want to use PostgreSQL Large Objects
      under Python 2.2.x, you *must* use Python 2.2.2, because of a bug in
      earlier 2.2 versions.

Project homepages:
pyPgSQL:        http://pypgsql.sourceforge.net/
PostgreSQL:     http://www.postgresql.org/
Python:         http://www.python.org/

While there didn't change much under the hood, the build process was
completely rewritten, so pyPgSQL should now build out of the box on most
popular platforms.  For you, this means that a "python setup.py build" will
now hopefully just work. And for us, this means we'll have to answer less
boring support questions :-)

    These platforms were tested and will build out of the box (of course,
    more testing won't hurt): UnixWare 7/OpenUNIX 8, Windows native, Cgwin,
    FreeBSD 4.7, Redhat Linux 7.3, SuSE Linux 7.3, Debian Linux 3.0.

    The build process is also designed to work with these platforms, but
    they could not be tested in real life (please do so, if you can!):
    Mandrake Linux, NetBSD, OpenBSD, MacOS X.

    If your platform isn't supported out of the box, you can edit a
    setup.py file to configure the build process.  Patches for
    supporting additional platforms are more than welcome. Look into
    setup.py for how to do it.

The other big change is that Gerhard finally gave up on getting more
feedback on his Unicode patch and merged it into the pyPgSQL 2.x line.
Hey, if it did work for Linux 2.4, it can work for us, too <0.7 wink>.

Using Unicode works like this:

    from pyPgSQL import PgSQL
    con = PgSQL.connect(client_encoding=("utf-8", "ignore"), unicode_results=1)

    # where client_encoding are the parameters you normally give to the encode
    # method of a Unicode string. unicode_results=1 means pyPgSQL will return
    # VARCHAR/CHAR/TEXT fields as Unicode strings instead of byte strings.

    # As I refuse to do any magic, you'll also have to set the client encoding
    # for the database manually:
    cursor = con.cursor()
    cursor.execute("SET CLIENT_ENCODING TO UNICODE")

    # There are other ways to set the client encoding, including setting a
    # special environment variable (see PostgreSQL manual), but I recommend
    # doing it in code. 

Support for reference cursors was added.  Since PostgreSQL 7.2, you could
create cursors in PL/pgSQL and pass a reference to the open cursor back to
the client.  PgSQL will now cast those referenced cursors into a Cursor
object that can be used to fetch the results in  the cursor.  For example
(assuming that mmYearInfo() returns a reference curosr):

    $ python
    Python 2.2.2 (#7, Nov 27 2002, 17:10:05) [C] on openunix8
    Type "help", "copyright", "credits" or "license" for more information.
    >>> from pyPgSQL import PgSQL
    >>> cx = PgSQL.connect(database='esi')
    >>> cu = cx.cursor()
    >>> cu.callproc('mmYearInfo')
    >>> rs = cu.fetchone()
    >>> rs
    [<pyPgSQL.PgSQL.Cursor instance at 0x818495c>]
    >>> c = rs[0]
    >>> for i in c.description:
    ...     print i
    ... 
    ['model_year', varchar, 4, 8, None, None, None, 0]
    ['mktg_div_name', varchar, 50, 54, None, None, None, 0]
    ['model_desc', varchar, 50, 54, None, None, None, 0]
    ['book_types', varchar, 50, 54, None, None, None, 0]
    ['vehicle_syskey', integer, 4, 4, None, None, None, 0]
    >>> r = c.fetchone()
    >>> r
    ['2003', 'Buick', 'Century', '1;8;9', 2211]
    >>> 

---------------------------------------------------------------------------
ChangeLog:
===========================================================================

Changes since pyPgSQL Version 2.2
=================================

The following source code files were added to Version 2.3 of pyPgSQL:

    unicode_tests.py	- Test suite for the new Unicode support. Merged from
			  the Unicode patch.

Changes to setup.py
-------------------
* This file was rewritten entirely so that pyPgSQL now builds out of the box
  on Windows, Cygwin, Debian Linux, Redhat Linux, SuSE Linux, FreeBSD,
  UnixWare 7 and OpenUNIX 8. These are the platforms the new build process has
  been tested on. Untested support is available for Mandrake Linux, NetBSD,
  OpenBSD and MacOS X.

Changes to README
-----------------
* Updates for 2.3.
* Converted the whole document into reStructuredText, so we
  can easily built a HTML version using docutils (http://docutils.sf.net/).

Changes to README.win32
-----------------------
* Remove out of date warning about PostgreSQL on win32
* Reflected change from windows/ to ports/

Changes to PgSQL.py
-------------------
* Fixed various problems with the PgNumeric constructor and formatting
  routines.
* Fixed problems with new __setupTransaction function:	|
      1. Made it a method of Connection, not Cursor.
      2. inTransaction was only set if TransactionLevel was set.
* Fixed instances where method name was incorrect:
      Connection__gcCursor    -> _Connection__gcCursor
      Connection__closeCursor -> _Connection__closeCursor
* Cleaned up code where there was unneeded references to conn in Connection
  class.				|
* Handle the new '__quote__' method for arrays, too.
* Still handle '_quote' methods for backwards compatibility.
* Fixed changed PG_TIMESTAMP oid, added PG_TIMESTAMPTZ and PG_REFCURSOR oids.
* Reference cursors are now type-casted into cursor objects.
* Completed the emulation of a String object for the PgBytea and PgOther
  classes.  This corrects several problems with PgBytea concerning compari-
  sons, using PgBytea types as keys in dictionaries, etc.
* Added the __hash__ function to the PgNumeric class.  Cleaned up the code
  in PgNumeric class and made some small improvments to it.
* Added the PgArray class.  This is a wrapper around a Python list and is
  used for all PostgreSQL arrays.  This change was made so that lists and
  tuples no longer have a special meaning in the Cursor.execute() method.
* Changed the quoting methods defined in the various classes defining
  PostgreSQL support types to __quote__.
* Merged the Unicode patch. pyPgSQL now has full Unicode support.
* Added support for the INTERVAL type.
* mxDateTime 1.x is no longer supported. Require mxDateTime 2.x and give
  useful error message if import fails.
* Cosmetic changes: use cmp builtin where appropriate.
* Fixed typo where PgTypes.__str__ was spelled incorrectly; compare to None
  using "is" operator.
* Take into account that libpq.PgInt8Type might not be
  available.
* Convert ROWID to PgInt8 instead of PgInt4 (the original behaviour led to 
  overflow errors.)
* Always set the displaysize field of cursor.description to -1.
  PostgreSQL 7.3 doesn't provide this information any more and it's pretty
  useless nowadays that we've mostly left line printers beyond us.

Changes to pyPgSQL/__init__.py
------------------------------
* Register libpq.PgInt8 with copy_reg only if available.

Changes to pglargeobject.c
--------------------------
* Made the 'closed' attribute of PgLargeObject an int instead of a long.
	
Changes to libpqmodule.c:
-------------------------
* Fixed the changed PG_TIMESTAMP oid, added PG_TIMESTAMPTZ and PG_REFCURSOR
  oids. [Bug #845360]
* Fixed the format string of ParseTuple in libPQbool_FromInt.
  This closes a bug that appeared on Linux/Alpha (#625121).

Changes to pgversion.c:
-----------------------
* Changed pgversion to recognize release canidate versions of PostgreSQL.

Changes to PgSQL test cases:
----------------------------
* Updated test cases to allow for changes in PostgreSQL 7.3.
* Don't check for the obsolete displaysize field in cursor.description.
  Also don't check the backend encoding.

Multiple files
--------------
The windows/ directory has been moved to a port/ directory. Now we *always*
use our own version of various string versions instead of special casing for a
dozen different Windows and Unix platforms.

In order to get the C version of PgInt8Type, it is now required that the
constants LLONG_MAX, LLONG_MIN, ULLONG_MAX and ULLONG_MIN are defined when
including limits.h and that including Python.h defines HAVE_LONG_LONG.
Otherwise, a Python class is used instead.

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] Re: How to stop query execution from Python/pyPgS QL

[Billy G. A. <bg...@mu...>]

Gerhard =?iso-8859-1?Q?H=E4ring?= wrote:
> Billy G. Allie <bga=Ndj...@pu...> wrote:
> > [cancelling a query]
> > Since PgSQL is built upon the libpq module, you can access the
> > PGrequestCancel function as follows (assuming 'cx' is the connection
> > running the request you wish to cancel):
> > 
> > 	cx.conn.requestCancel()
> > 
> > This would have to be issued by a different thread of execution or from a
> > signal handler since the execute method does not return until the query
> > has finished processing at the backend.
> 
> Can this work? I was under the impression that this was one of the functions
> that's supposed to be used under the asynchronous API.
> 
> Incidentally, I've tried to use the async functions in pyPgSQL.libpq a few
> days back (and cancelling a query), but didn't succeed.  Today, I've found
> out why and committed a change to pgconnection.c. I believe you'll have to
> use the CVS version of pyPgSQL to cancel queries for now.
> 
> Here's a toy example with which I tested this functionality:
> [...]
> ciao,
> 
> Gerhard

You should be able to send the cancel request from a signal handler.  I don't 
think (now) that it can be issued in a different thread.  Here is the relevant 
part of the PostgreSQL manual:

"PQrequestCancel can safely be invoked from a signal handler. So, it is also 
possible to use it in conjunction with plain PQexec, if the decision to cancel 
can be made in a signal handler. For example, psql invokes PQrequestCancel 
from a SIGINT signal handler, thus allowing interactive cancellation of 
queries that it issues through PQexec. Note that PQrequestCancel will have no 
effect if the connection is not currently open or the backend is not currently 
processing a command."

Later.


-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

RE: [Pypgsql-users] Re: How to stop query execution from Python/p yPgSQL

[Murthy K. <mur...@go...>]

Gerhard, thanks. I think I can use this for what I need. At a more
fundamental level though, if you submit a synchronous query through =
libpq,
and then kill the process from which the connection was opened/query =
was
submitted, shouldn't libpq assume and implicit requestCancel, and clean =
up?
I'll ask on pgsql-admin admin also; there was a recent thread on =
canceling
queries which I'll pick up.

Thanks again,
	Murthy

-----Original Message-----
From: Gerhard H=E4ring [mailto:ger...@op...]
Sent: Friday, December 13, 2002 06:37
To: pyp...@li...
Subject: [Pypgsql-users] Re: How to stop query execution from
Python/pyPgS QL


Billy G. Allie <bga=3DN...@pu...> wrote:
> [cancelling a query]
> Since PgSQL is built upon the libpq module, you can access the
> PGrequestCancel function as follows (assuming 'cx' is the connection
> running the request you wish to cancel):
>=20
> 	cx.conn.requestCancel()
>=20
> This would have to be issued by a different thread of execution or =
from a
> signal handler since the execute method does not return until the =
query
> has finished processing at the backend.

Can this work? I was under the impression that this was one of the =
functions
that's supposed to be used under the asynchronous API.

Incidentally, I've tried to use the async functions in pyPgSQL.libpq a =
few
days back (and cancelling a query), but didn't succeed.  Today, I've =
found
out why and committed a change to pgconnection.c. I believe you'll have =
to
use the CVS version of pyPgSQL to cancel queries for now.

Here's a toy example with which I tested this functionality:

#v+
# Experiment with asynchronous query processing and cancelling queries.

from pyPgSQL import libpq
import select

conn =3D libpq.PQconnectdb("host=3Dmyhost dbname=3Dmydb")

class BreakOut(Exception): pass

counter =3D 0

for i in range(10):
    try:
        print "-" * 50
        print "Starting query."
        conn.sendQuery("SELECT NAME FROM TEST") # 100000 rows
        while True:
            counter +=3D 1
            if counter =3D=3D 300:
                raise BreakOut

            fds =3D select.select([conn.socket], [], [])
            if len(fds) !=3D 0:
                conn.consumeInput()
                if not conn.isBusy:
                    break

        res =3D conn.getResult()
        # here we have the entire resultset

        res =3D conn.getResult()
        assert res =3D=3D None
        print "Query successfully processed."
    except BreakOut:
        # Cancel currently running query.
        print "Cancelling query ..."
        conn.requestCancel()
        while True:
            fds =3D select.select([conn.socket], [], [])
            if len(fds) !=3D 0:
                conn.consumeInput()
                if not conn.isBusy:
                    break
        try:
            res =3D conn.getResult()
        except libpq.OperationalError, e:
            assert str(e).strip() =3D=3D "ERROR:  Query was cancelled."
            res =3D conn.getResult()
            assert res =3D=3D None
        print "Query sucessfully cancelled."

conn.finish()
#v-

ciao,

Gerhard
--=20
Gerhard H=E4ring
OPUS GmbH M=FCnchen
Tel.: +49 89 - 889 49 7 - 32
http://www.opus-gmbh.net/




-------------------------------------------------------
This sf.net email is sponsored by:
With Great Power, Comes Great Responsibility=20
Learn to use your power at OSDN's High Performance Computing Channel
http://hpc.devchannel.org/
_______________________________________________
Pypgsql-users mailing list
Pyp...@li...
https://lists.sourceforge.net/lists/listinfo/pypgsql-users

[Pypgsql-users] Re: How to stop query execution from Python/pyPgS QL

[Gerhard <ger...@op...>]

Billy G. Allie <bga=Ndj...@pu...> wrote:
> [cancelling a query]
> Since PgSQL is built upon the libpq module, you can access the
> PGrequestCancel function as follows (assuming 'cx' is the connection
> running the request you wish to cancel):
> 
> 	cx.conn.requestCancel()
> 
> This would have to be issued by a different thread of execution or from a
> signal handler since the execute method does not return until the query
> has finished processing at the backend.

Can this work? I was under the impression that this was one of the functions
that's supposed to be used under the asynchronous API.

Incidentally, I've tried to use the async functions in pyPgSQL.libpq a few
days back (and cancelling a query), but didn't succeed.  Today, I've found
out why and committed a change to pgconnection.c. I believe you'll have to
use the CVS version of pyPgSQL to cancel queries for now.

Here's a toy example with which I tested this functionality:

#v+
# Experiment with asynchronous query processing and cancelling queries.

from pyPgSQL import libpq
import select

conn = libpq.PQconnectdb("host=myhost dbname=mydb")

class BreakOut(Exception): pass

counter = 0

for i in range(10):
    try:
        print "-" * 50
        print "Starting query."
        conn.sendQuery("SELECT NAME FROM TEST") # 100000 rows
        while True:
            counter += 1
            if counter == 300:
                raise BreakOut

            fds = select.select([conn.socket], [], [])
            if len(fds) != 0:
                conn.consumeInput()
                if not conn.isBusy:
                    break

        res = conn.getResult()
        # here we have the entire resultset

        res = conn.getResult()
        assert res == None
        print "Query successfully processed."
    except BreakOut:
        # Cancel currently running query.
        print "Cancelling query ..."
        conn.requestCancel()
        while True:
            fds = select.select([conn.socket], [], [])
            if len(fds) != 0:
                conn.consumeInput()
                if not conn.isBusy:
                    break
        try:
            res = conn.getResult()
        except libpq.OperationalError, e:
            assert str(e).strip() == "ERROR:  Query was cancelled."
            res = conn.getResult()
            assert res == None
        print "Query sucessfully cancelled."

conn.finish()
#v-

ciao,

Gerhard
-- 
Gerhard Häring
OPUS GmbH München
Tel.: +49 89 - 889 49 7 - 32
http://www.opus-gmbh.net/

Re: [Pypgsql-users] How to stop query execution from Python/pyPgS QL

[Billy G. A. <bg...@mu...>]

Aaron Held wrote:
> I created a hack in my app that may not be appropriate for you but....
> 
> I made an process monitor that used an activity view (I forgot the 
> details, but its in PG docs somewhere under Monitoring) to deterimine 
> the PID of the backend and then kill it from the python using the 
> command like kill the errant PID .
> 
> I wonder if the MySQL functionality is due to the client or the server?
> I often run queries from various PG tools that run away.
> 
> -Aaron
  [...]
> Murthy Kambhampaty wrote:
> > Aaron, thanks for the reply. I have been doing that, but I have several
> > Windows users and I was hoping to avoid having to (1) show them how and (2)
> > give them privileges. I guess I will, for now. Is there any chance that
> > pyPgSQL will use the libpq function "PGrequestCancel" to support killing
> > queries by closing Python is a manner similar to issuing ^C in psql?
> > 
> > Thanks,
> > 	Murthy
> >  [...]

Since PgSQL is built upon the libpq module, you can access the PGrequestCancel function as follows (assuming 'cx' is the connection running the request you wish to cancel):

	cx.conn.requestCancel()


This would have to be issued by a different thread of execution or from a signal handler since the execute method does not return until the query has finished processing at the backend.

NOTE:  Successfully dispatching a cancel request does not mean the request
       will be cancelled.
-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] How to stop query execution from Python/pyPgS QL

[Aaron H. <aa...@Me...>]

I created a hack in my app that may not be appropriate for you but....

I made an process monitor that used an activity view (I forgot the 
details, but its in PG docs somewhere under Monitoring) to deterimine 
the PID of the backend and then kill it from the python using the 
command like kill the errant PID .

I wonder if the MySQL functionality is due to the client or the server?
I often run queries from various PG tools that run away.

-Aaron




Murthy Kambhampaty wrote:
> Aaron, thanks for the reply. I have been doing that, but I have several
> Windows users and I was hoping to avoid having to (1) show them how and (2)
> give them privileges. I guess I will, for now. Is there any chance that
> pyPgSQL will use the libpq function "PGrequestCancel" to support killing
> queries by closing Python is a manner similar to issuing ^C in psql?
> 
> Thanks,
> 	Murthy
> 
> PS: We actually submit Python code from SciTE which opens a new Python for
> each "Tools->Go" action. The SciTE "Tools->Stop Query Execution" action
> tries to get Python to interrupt execution and close. In MySQL-DB this would
> cancel the query, but it doesn't in with pyPgSQL. The main advantage to
> doing this from SciTE is that the script is executed in a separate
> window/thread and does not lock the editor; slick!
> 
> -----Original Message-----
> From: Aaron Held [mailto:aa...@Me...]
> Sent: Thursday, December 12, 2002 11:53
> To: Murthy Kambhampaty
> Subject: Re: [Pypgsql-users] How to stop query execution from
> Python/pyPgSQL
> 
> 
> You can kill the backend pid
> 
> ps -ax | grep post
> 
> -Aaron
> 
> Murthy Kambhampaty wrote:
> 
>>Is there a way to stop a query submitted with cx.query() or cur.execute()
>>using pyPgSQL on Windows? Killing the python shell does not seem to do it.
>>
>>Thanks,
>>	Murthy
>>
>>
>>-------------------------------------------------------
>>This sf.net email is sponsored by:
>>With Great Power, Comes Great Responsibility 
>>Learn to use your power at OSDN's High Performance Computing Channel
>>http://hpc.devchannel.org/
>>_______________________________________________
>>Pypgsql-users mailing list
>>Pyp...@li...
>>https://lists.sourceforge.net/lists/listinfo/pypgsql-users
> 

[Pypgsql-users] How to stop query execution from Python/pyPgSQL

[Murthy K. <mur...@go...>]

Is there a way to stop a query submitted with cx.query() or cur.execute()
using pyPgSQL on Windows? Killing the python shell does not seem to do it.

Thanks,
	Murthy

Re: [Pypgsql-users] A bug in PgNumeric from current CVS head.

[Billy G. A. <bg...@mu...>]

Adam Buraczewski wrote:
> Hallo everyone,
> 
> I've just seen some new changes in PgNumeric code on CVS and tried to
> play a bit with it :)  Generally it works nice, I hope pyPgSQL 2.3
> would be the point where I start switching my applications to use this
> class more heavily.  However, I think I found some bugs in it.  Look
> at this:

Yes, you definitely found some bugs.  

[...]


> The other, minor problem is with numbers which have a decimal dot but
> don't have any digits after it:

And another bug is found!

[...]

> I think that there is also another problem with the constructor, which
> causes the class to treat minus sign as a digit which counts into
> precision of the number.  I think it shouldn't.  Look at this:

Number three (3) and counting :-)

[...]

> However I am not sure if I correctly understood the meaning of prec
> parameter passed to PgNumeric constructor.  What should happen when
> someone calls it with setting to something else than None?

The prec and scale parameters are there to allow the user to set the precision 
and scale to specific values.  For example, if you wanted a PgNumeric with a 
precision of 10, a scale of 5, and a value of 0.001, you could enter:

    >>> a = PgSQL.PgNumeric('00000.00100')
    >>> a
    <PgNumeric instance - precision: 10 scale: 5 value: 0.00100>
or
    >>> b = PgSQL.PgNumeric('.001', 10, 5)
    >>> b
    <PgNumeric instance - precision: 10 scale: 5 value: 0.00100>


You can also use them to 'cast' an existing PgNumeric to a different precision 
and scale:

    >>> a = PgSQL.PgNumeric(a, 20, 8)
    >>> a
    <PgNumeric instance - precision: 20 scale: 8 value: 0.00100000>

>                                                             Please,
> check my patch before applying.

I did.  I also decided to rework the input routine somewhat.  The routines for 
FloatType and StringType input now share the same code instead of duplicating 
code.  This has the added benefit of of being able to specify an exponent in 
the input string.  For example:

    >>> a = PgSQL.PgNumeric('123.45678e-6')
    >>> a
    <PgNumeric instance - precision: 11 scale: 11 value: 0.00012345678>

> BTW, I wonder why __repr__ operator of all pyPgSQL classes returns
> strings of the form "<something" instead of "<something>" (without
> closing '>' sign)?  Is it made on purpose or is it a mistake? :)

A mistake.  The only case I could find was in PgNumeric.  Did it occur in other place?

Thank you for the bug report and patch.  It is greatly appreciated.


-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

[Pypgsql-users] A bug in PgNumeric from current CVS head.

[Adam B. <ad...@po...>]

Hallo everyone,

I've just seen some new changes in PgNumeric code on CVS and tried to
play a bit with it :)  Generally it works nice, I hope pyPgSQL 2.3
would be the point where I start switching my applications to use this
class more heavily.  However, I think I found some bugs in it.  Look
at this:

	>>> from pyPgSQL import PgSQL
	>>> print PgSQL.PgNumeric('0.0000') - PgSQL.PgNumeric('0.0010')
	0.0-10
	>>> print PgSQL.PgNumeric('0.0000') + PgSQL.PgNumeric('-0.0020')
	0.0-20
	>>> print PgSQL.PgNumeric('-0.0010')
	0.0-10

(the same happens when -= and += operators are used).  It looks as the
PgNumeric class has some troubles with displaying negative results of
fractional values (in the range of -1 to 0).  After digging into the
code I found that __fmtNumeric() function does not care of negative
numbers at all and treats minus sign as a digit.  The solution is to
find if the number is negative, remove the '-' sign from the
beginning, format as usual and add the sign at the beginning of the
resulting string.

The other, minor problem is with numbers which have a decimal dot but
don't have any digits after it:

	>>> print PgSQL.PgNumeric('1.')
	Traceback (most recent call last):
  	File "<stdin>", line 1, in ?
  	File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 1297, in __init__
	    raise ValueError, \
	ValueError: invalid literal for PgNumeric: 1.

I think that there is also another problem with the constructor, which
causes the class to treat minus sign as a digit which counts into
precision of the number.  I think it shouldn't.  Look at this:

	>>> print repr(PgSQL.PgNumeric('0.0010'))
	<PgNumeric instance - precision: 5 scale: 4 value: 0.0010
	>>> print repr(PgSQL.PgNumeric('-0.0010'))
	<PgNumeric instance - precision: 6 scale: 4 value: -0.0010
	>>> print repr(PgSQL.PgNumeric(-0.001))
	<PgNumeric instance - precision: 5 scale: 3 value: -0.001
	>>> print repr(PgSQL.PgNumeric(0.001))
	<PgNumeric instance - precision: 4 scale: 3 value: 0.001

I tried to remove all bugs mentioned above (patch against current CVS
attached), cleaning up the code and adding some checks and comments
during that.  Now it works correctly:

	>>> print repr(PgSQL.PgNumeric('-0.0010'))
	<PgNumeric instance - precision: 5 scale: 4 value: -0.0010
	>>> print PgSQL.PgNumeric('1.')
	1

However I am not sure if I correctly understood the meaning of prec
parameter passed to PgNumeric constructor.  What should happen when
someone calls it with setting to something else than None?  Please,
check my patch before applying.

BTW, I wonder why __repr__ operator of all pyPgSQL classes returns
strings of the form "<something" instead of "<something>" (without
closing '>' sign)?  Is it made on purpose or is it a mistake? :)

Regards,

-- 
Adam Buraczewski <ad...@po...> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

RE : [Pypgsql-users] Binary data with postgress

[Mignon, L. <Mi...@PC...>]

If I understood all what you say, the best thing to do is waiting the =
next
release of pypgsql to be sure that the related bug is fixed!=20
Before this release, all test that I do on retrieved data must be done =
on
the value member of PgBytea class.

Thanks for your help!

-----Message d'origine-----
De=A0: Billy G. Allie [mailto:Bil...@mu...]=20
Envoy=E9=A0: mardi 26 novembre 2002 7:40
=C0=A0: Mignon, Laurent; 'pyp...@li...';
ger...@gm...
Objet=A0: Re: [Pypgsql-users] Binary data with postgress=20

Gerhard =3D?iso-8859-1?Q?H=E4ring?=3D wrote:
> I was trying to answer this question, too. In the process of which I =
was
> constructing an example using BYTEA. Unfortunately, there seems to be =
a
> bug in pyPgSQL that we should eliminate before (finally, *cough*)
> releasing 2.3:
>=20
> # Database schema used:
> # create table test(ba bytea, lo oid);
>=20
> from pyPgSQL import PgSQL
>=20
> bindata =3D "".join([chr(x) for x in range(256)] * 10)
>=20
> con =3D PgSQL.connect()
>=20
> cursor =3D con.cursor()
>=20
> # I) BYTEA
>=20
> # Storing BYTEA:
> cursor.execute("insert into test(ba) values (%s)",
(PgSQL.PgBytea(bindata),))
> last_oid =3D cursor.oidValue
>=20
> # Retrieving BYTEA
> cursor.execute("select ba from test where oid=3D%s", (last_oid,))
> row =3D cursor.fetchone()
>=20
> # Check if input is the same as output
> assert bindata =3D=3D row.ba
>=20
> con.close()
>=20
>=20
> It looks like the quoting of BYTEAs is wrong, at least when the BYTEA
> starts with a chr(0). But I haven't got a real clue where the problem
> lies. Though I vaguely remember touching that piece of code once when =
I
> was working on the ARRAY stuff. I hope I didn't introduce the bug =
then
> *blush*.

Gerhard,

The problem is with the PgBytea class. It is missing the hidden methods =
for=20
determining length, comparisons, concatenation (+), repetition (*), =
etc.  I=20
will be very happy when version 3.0 gets out the door and we can just=20
sub-class the string object :-)  I will fix the class by this weekend.

>>> from pyPgSQL import PgSQL
>>> a =3D '\000ab\000'  # Create a string with NUL characters in it.
>>> len(a)
4
>>> a
'\x00ab\x00'
>>> PgSQL.PgQuoteBytea(a)  # Check how it's quoted by pyPgSQL
"'\\\\000ab\\\\000'"
>>> cx =3D PgSQL.connect()   # Lets put it into the database
>>> cu =3D cx.cursor()
>>> cu.execute('create table byteatest (a bytea, b oid)')
>>> cx.commit()
>>> cu.execute('insert into byteatest values(%s, %s)', =
PgSQL.PgBytea(a), 0)
>>> cx.commit()
>>> cu.execute('select * from byteatest')
>>> rs =3D cu.fetchone()
>>> rs
['\x00ab\x00', 0]
>>> cu.execute('insert into byteatest(a) values(%s)', PgSQL.PgBytea(a))
>>> cx.commit()
>>> cu.execute('select * from byteatest');
>>> rs =3D cu.fetchall()
>>> for i in rs:
..     print i
..=20
['\x00ab\x00', 0]
['\x00ab\x00', None]
>>> cu.execute('insert into byteatest(a) values(%s)', =
(PgSQL.PgBytea(a),))
>>> cx.commit()
>>> cu.execute('select * from byteatest');
>>> rs =3D cu.fetchall()
>>> for i in rs:
..     print i
..=20
['\x00ab\x00', 0]
['\x00ab\x00', None]
['\x00ab\x00', None]
>>> len(rs[0].a)	# len() doesn't work on PgBytea types
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AttributeError: PgBytea instance has no attribute '__len__'
>>> len(rs[0].a.value)
4
>>> assert a =3D=3D rs[0].a  # Comparison doesn't work either
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AssertionError
>>> assert a =3D=3D rs[0].a.value
>>>

--=20
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |



--=20
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] Binary data with postgress

[Billy G. A. <Bil...@mu...>]

Gerhard =?iso-8859-1?Q?H=E4ring?= wrote:
> I was trying to answer this question, too. In the process of which I was
> constructing an example using BYTEA. Unfortunately, there seems to be a
> bug in pyPgSQL that we should eliminate before (finally, *cough*)
> releasing 2.3:
> 
> # Database schema used:
> # create table test(ba bytea, lo oid);
> 
> from pyPgSQL import PgSQL
> 
> bindata = "".join([chr(x) for x in range(256)] * 10)
> 
> con = PgSQL.connect()
> 
> cursor = con.cursor()
> 
> # I) BYTEA
> 
> # Storing BYTEA:
> cursor.execute("insert into test(ba) values (%s)", (PgSQL.PgBytea(bindata),))
> last_oid = cursor.oidValue
> 
> # Retrieving BYTEA
> cursor.execute("select ba from test where oid=%s", (last_oid,))
> row = cursor.fetchone()
> 
> # Check if input is the same as output
> assert bindata == row.ba
> 
> con.close()
> 
> 
> It looks like the quoting of BYTEAs is wrong, at least when the BYTEA
> starts with a chr(0). But I haven't got a real clue where the problem
> lies. Though I vaguely remember touching that piece of code once when I
> was working on the ARRAY stuff. I hope I didn't introduce the bug then
> *blush*.

Gerhard,

The problem is with the PgBytea class. It is missing the hidden methods for 
determining length, comparisons, concatenation (+), repetition (*), etc.  I 
will be very happy when version 3.0 gets out the door and we can just 
sub-class the string object :-)  I will fix the class by this weekend.

>>> from pyPgSQL import PgSQL
>>> a = '\000ab\000'  # Create a string with NUL characters in it.
>>> len(a)
4
>>> a
'\x00ab\x00'
>>> PgSQL.PgQuoteBytea(a)  # Check how it's quoted by pyPgSQL
"'\\\\000ab\\\\000'"
>>> cx = PgSQL.connect()   # Lets put it into the database
>>> cu = cx.cursor()
>>> cu.execute('create table byteatest (a bytea, b oid)')
>>> cx.commit()
>>> cu.execute('insert into byteatest values(%s, %s)', PgSQL.PgBytea(a), 0)
>>> cx.commit()
>>> cu.execute('select * from byteatest')
>>> rs = cu.fetchone()
>>> rs
['\x00ab\x00', 0]
>>> cu.execute('insert into byteatest(a) values(%s)', PgSQL.PgBytea(a))
>>> cx.commit()
>>> cu.execute('select * from byteatest');
>>> rs = cu.fetchall()
>>> for i in rs:
..     print i
.. 
['\x00ab\x00', 0]
['\x00ab\x00', None]
>>> cu.execute('insert into byteatest(a) values(%s)', (PgSQL.PgBytea(a),))
>>> cx.commit()
>>> cu.execute('select * from byteatest');
>>> rs = cu.fetchall()
>>> for i in rs:
..     print i
.. 
['\x00ab\x00', 0]
['\x00ab\x00', None]
['\x00ab\x00', None]
>>> len(rs[0].a)	# len() doesn't work on PgBytea types
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AttributeError: PgBytea instance has no attribute '__len__'
>>> len(rs[0].a.value)
4
>>> assert a == rs[0].a  # Comparison doesn't work either
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AssertionError
>>> assert a == rs[0].a.value
>>>

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |



-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] Binary data with postgress

[Gerhard <ger...@gm...>]

* Billy G. Allie <Bil...@mu...> [2002-11-25 16:19 -0500]:
> "Mignon, Laurent" wrote:
> > 
> > How can I do to store binary data like program file, picture, document, etc
> > in postgress?
> > 
> > Thanks for your help.
> > 
> You have a couple of choices:
> 
> 1.  Use a PostgreSQL large object.

If you use large objects with Python 2.2, be sure to upgrade to the
latest version (2.2.2), as earlier 2.2 versions had a bug that will bite
you otherwise.

> 2.  Use a bytea field.
> 
> With later versions of PostgreSQL, it a matter of personal preference as to
> which one you use.  Earlier version had an approximately 8K limit to the
> tuple size, so large objects were were needed for data with a size > 8K.

I was trying to answer this question, too. In the process of which I was
constructing an example using BYTEA. Unfortunately, there seems to be a
bug in pyPgSQL that we should eliminate before (finally, *cough*)
releasing 2.3:

# Database schema used:
# create table test(ba bytea, lo oid);

from pyPgSQL import PgSQL

bindata = "".join([chr(x) for x in range(256)] * 10)

con = PgSQL.connect()

cursor = con.cursor()

# I) BYTEA

# Storing BYTEA:
cursor.execute("insert into test(ba) values (%s)", (PgSQL.PgBytea(bindata),))
last_oid = cursor.oidValue

# Retrieving BYTEA
cursor.execute("select ba from test where oid=%s", (last_oid,))
row = cursor.fetchone()

# Check if input is the same as output
assert bindata == row.ba

con.close()


It looks like the quoting of BYTEAs is wrong, at least when the BYTEA
starts with a chr(0). But I haven't got a real clue where the problem
lies. Though I vaguely remember touching that piece of code once when I
was working on the ARRAY stuff. I hope I didn't introduce the bug then
*blush*.

-- Gerhard

Re: [Pypgsql-users] Binary data with postgress

[Billy G. A. <Bil...@mu...>]

"Mignon, Laurent" wrote:
> 
> How can I do to store binary data like program file, picture, document, etc
> in postgress?
> 
> Thanks for your help.
> 
>  
> 
> lmg

You have a couple of choices:

1.  Use a PostgreSQL large object.

2.  Use a bytea field.

With later versions of PostgreSQL, it a matter of personal preference as to
which one you use.  Earlier version had an approximately 8K limit to the
tuple size, so large objects were were needed for data with a size > 8K.
___________________________________________________________________________
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

[Pypgsql-users] Binary data with postgress

[Mignon, L. <Mi...@PC...>]

How can I do to store binary data like program file, picture, document, etc
in postgress?

Thanks for your help.

 

lmg

Re: Re: [Pypgsql-users] Re: PgBoolean

[<vze...@ve...>]

> The problem is that you are testing the PgResultSet object, not the PgBoolean.  The PgResultSet will always test true unless it's empty.
> 
> Try doing the following:
> 
>     if retval[0]:
>         print "Yes"
>     else:
>         print "No"
> 

That was the problem. Thanks for your help guys.

Re: [Pypgsql-users] Re: PgBoolean

[<Bil...@mu...>]

vze...@ve... wrote:
> Thanks for the python tips. I guess it shows that I am new to the language :-
> )
> 
> Unfortunately, I still can't seem to get this right. I tried this:
> 
> if retval:
>     print "YES"
> else:
>     print "NO"
> 
> When executed, this prints out "YES" when the statement "print retval" prints
>  either
> 
> [<PgBoolean instance at 0x401c890c: Value: True>]
> 
> - or -
> 
> [<PgBoolean instance at 0x401c8900: Value: False>]
> 
> What am I doing?

The problem is that you are testing the PgResultSet object, not the PgBoolean.  The PgResultSet will always test true unless it's empty.

Try doing the following:

    if retval[0]:
        print "Yes"
    else:
        print "No"

Here is an example:

    >>> c = PgSQL.connect()
    >>> cu = c.cursor()
    >>> cu.execute("select 'f'::bool as myBool")
    >>> rs = cu.fetchone()
    >>> type(rs)
    <type 'instance'>
    >>> rs.__class__
    <class pyPgSQL.PgSQL.PgResultSetConcreteClass at 805acdc>
    >>> 
    >>> rs
    [<PgBoolean instance at bfd7e110: Value: False>]
    >>> myBool = rs['myBool'] # or rs.myBool or rs[0]
    >>> myBool
    f
    >>> print myBool
    f
    >>> repr(myBool)
    '<PgBoolean instance at bfd7e110: Value: False>'
    >>> if myBool:
    ...     print "Yes"
    ... else:
    ...     print "No"
    ... 
    No
    >>>

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

Re: [Pypgsql-users] Re: PgBoolean

[Gerhard H. <ger...@gm...>]

* vze...@ve... <vze...@ve...> [2002-11-20 20:18 -0600]:
> Thanks for the python tips. I guess it shows that I am new to the language
> :-)
> 
> Unfortunately, I still can't seem to get this right. I tried this:
> 
> if retval: print "YES" else: print "NO"
> 
> When executed, this prints out "YES" when the statement "print retval" prints
> either
> 
> [<PgBoolean instance at 0x401c890c: Value: True>]
> 
> - or -
> 
> [<PgBoolean instance at 0x401c8900: Value: False>]
> 
> What am I doing wrong?

I don't know. It works just fine here:

gerhard@gargamel:/tmp$ python
Python 2.2.2 (#1, Oct 18 2002, 03:24:30) 
[GCC 2.95.4 20020320 [FreeBSD]] on freebsd4
Type "help", "copyright", "credits" or "license" for more information.
>>> from pyPgSQL import PgSQL
>>> db = PgSQL.connect(); cursor = db.cursor()
>>> cursor.execute("select b from test where b is true")
>>> res = cursor.fetchone()
>>> res.b
t
>>> repr(res.b)
'<PgBoolean instance at 0x2826202c: Value: True>'
>>> res.b == 1
1
>>> res.b == 0
0
>>> if res.b:
...   print "true"
... else:
...   print "false"
... 
true
>>> cursor.execute("select b from test where b is false")
>>> res = cursor.fetchone()
>>> res.b
f
>>> repr(res.b)
'<PgBoolean instance at 0x28262020: Value: False>'
>>> if res.b:
...   print "true"
... else:
...   print "false"
... 
false
>>> 

If you still can't get it to work, could you please post a complete code
section that shows the problem?

-- Gerhard

Re: [Pypgsql-users] Re: PgBoolean

[<vze...@ve...>]

Thanks for the python tips. I guess it shows that I am new to the language :-)

Unfortunately, I still can't seem to get this right. I tried this:

if retval:
    print "YES"
else:
    print "NO"

When executed, this prints out "YES" when the statement "print retval" prints either

[<PgBoolean instance at 0x401c890c: Value: True>]

- or -

[<PgBoolean instance at 0x401c8900: Value: False>]

What am I doing wrong?