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

RE: [Pypgsql-users] [ANN] Newsgroup gateway of this list

[Mark M. <mar...@mc...>]

[Gerhard Häring]
> Hi folks. As this mailing list is getting excessive traffic <wink>,
> there has been a mail-to-news/news-to-mail gateway installed at
> gmane.org.
>
> News server: news.gmane.org
> Newsgroup:   gmane.comp.python.db.pypgsql.user
>
> news.gmane.org carries many gatewayed Python related mailing lists,
> and tons of other lists, too, like for PostgreSQL, Debian, FreeBSD,
> Apache, ...

Gerhard, as an aside, does this mean the group's postings will be searchable
via http://google.groups.com/?

That, to me, is a big upside to the fact that the Python list is mirrored to
comp.lang.python.  If only Python-Dev were too.

Cheers,

// m

-

[Pypgsql-users] [ANN] Newsgroup gateway of this list

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

Hi folks. As this mailing list is getting excessive traffic <wink>,
there has been a mail-to-news/news-to-mail gateway installed at
gmane.org.

News server: news.gmane.org
Newsgroup:   gmane.comp.python.db.pypgsql.user

news.gmane.org carries many gatewayed Python related mailing lists,
and tons of other lists, too, like for PostgreSQL, Debian, FreeBSD,
Apache, ...

-- Gerhard

[Pypgsql-users] FAQ updated

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

The Frequently Asked Questions have been updated:
http://pypgsql.sourceforge.net/pypgsql-faq.html

And yes, I'll try to customize the XSL stylesheets for HTML output one
day ;-)

-- Gerhard

Re: [Pypgsql-users] Parameters etc.

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

you can

"Insert into "tablenm" (column1, column2) Values (%(value1)s, 
%(value2)s)" % {'value1':'data1','value2':'data2')

%(varname)s looks up varname in a dict that you can pass

Or you can
value1 = 'data1'
value2 = 'data2'

"Insert into "tablenm" (column1, column2) Values (%(value1)s, 
%(value2)s)" % locals()


If you pass the dict in the execute method then the driver will format 
the strings properly

-Aaron

Dick Kniep wrote:
> Hi List,
> 
> Is it possible to write a simple "Insert into "tablenm" (column1,
> column2) Values (value1, value2)" instead of 
> "Insert into "tablenm" (column1, column2) Values (%s, %s)" and give the
> values as a tuple?
> 
> Kind regard
> D Kniep
> 
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users

Re: [Pypgsql-users] Parameters etc.

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

* Dick Kniep <d.j...@ch...> [2002-10-03 15:39 +0200]:
> Hi List,
> 
> Is it possible to write a simple "Insert into "tablenm" (column1,
> column2) Values (value1, value2)" instead of "Insert into "tablenm"
> (column1, column2) Values (%s, %s)" and give the values as a tuple?

Dick,

I don't think I understand what you're trying to accomplish.

The normal way to insert values using pyPgSQL is

    cursor.execute("insert into t(c1, c2) values (%s, %s)", (1, 2))

and I'd really recommend you use this DB-API style.

If I understand correctly, you don't quite like this and want to do it
somehow different?

Of course you can use something like:

    cursor.execute("insert into t(c1, c2) values (1, 2)")

and omit the optional second parameter of the execute (and executemany
and callproc) methods, if the values you're trying to insert _constant_.


But please note that using something like:

    cursor.execute("insert into t(c1, c2) values (%s, %s)" % (1, 2))

does work for ints but you'll certainly get into trouble for other
types. IOW, the DB-API way of quoting for SQL is highly recommended, as
it will take care of proper quoting _for SQL_ (think of single
apostrophs in a Python string, for example):

    cursor.execute("insert into t(s) values (%s)" % "Fred's house")

    ^
    +----- This will _not_ work and yield an error. Using DB-API style,
           this is would have been no problem at all.

    cursor.execute("insert into t(s) values (%s)", ("Fred's house"))

One more reason for using DB-API style is security. Consider this:

    val = {something that comes from an untrusted source, for example a
           CGI script}

    # Now let val be something that a user entered in a web form. If the
    # user was me, it will look like for an "age" field:
    # 5) * 1(; DELETE FROM AGE;

    cursor.execute("insert into t(age) values (%s)" % val)

    This will expand to:

    insert into t(age) values (5) * (1); DELETE FROM AGE;

    Again, with DB-API style quoting this could not have happened, as it
    would have been expanded to:

    insert into t(age) values ('5) * 1(; DELETE FROM AGE;')

    and would have yielded an error from the backend, as it tries to
    insert a string into an int field.

-- Gerhard

[Pypgsql-users] Parameters etc.

[Dick K. <d.j...@ch...>]

Hi List,

Is it possible to write a simple "Insert into "tablenm" (column1,
column2) Values (value1, value2)" instead of 
"Insert into "tablenm" (column1, column2) Values (%s, %s)" and give the
values as a tuple?

Kind regard
D Kniep

Re: [Pythonmac-SIG] Re: [Pypgsql-users] Mac os x revisited

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

* Don Robertson <dcr...@pa...> [2002-10-02 11:12 +1200]:
> Quoting Brian Lenihan <br...@ma...>:
> 
> > The autoconf (2.52) which comes with Jaguar is broken on BSDish 
> > systems. Either install the autoconf from Fink, or install the latest 
> > version from GNU (2.54+). An alternative which may work is to edit 
> > line 7294 of your /usr/share/autoconf.autoconf.m4f like so:
> > 
> > exit (setpgrp (1,1) == -1 ? 0 : 1);])]
> > 
> I have installed autoconf 2.52, and have rebuilt and reinstalled the pypgsql module.
> 
> I am still getting :
>  pgversion.c: In function `PgVersion_New':
> pgversion.c:125: warning: `c1' might be used uninitialized in this function
> pgversion.c:125: warning: `c2' might be used uninitialized in this function
> pgversion.c:125: warning: `c1' might be used uninitialized in this function
> pgversion.c:125: warning: `c2' might be used uninitialized in this function
> pgversion.c:125: warning: `c1' might be used uninitialized in this function
> pgversion.c:125: warning: `c2' might be used uninitialized in this function
> 
> and 
>  pglargeobject.c: In function `PgLo_pickle':
> pglargeobject.c:1201: warning: unused variable `cnx'
> pglargeobject.c:1202: warning: unused variable `fd' 
> 
> while building.

These are just gcc warnings. I believe I've eliminated all of them in
the CVS version already.

> I am still getting ImportError: Failure linking new module when I try use the module
> 
> eg:
> 
> /Users/don/Documents/archive/Applications/pypgsql/test# python 
> Python 2.2 (#1, 07/14/02, 23:25:09) 
> [GCC Apple cpp-precomp 6.14] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import sys, os, string
> >>> from pyPgSQL import PgSQL
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 356, in ?
>     from libpq import *
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/libpq/__init__.py", line 23, in ?
>     from libpq import *
> ImportError: Failure linking new module
> >>> from pyPgSQL import *
> >>> from pyPgSQL import PgSQL
> >>> from libpq import *
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> ImportError: No module named libpq
> >>> from pyPgSQL.libpq import *
> >>> from libpq import *
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> ImportError: No module named libpq
> >>> 

Sorry, at this point I'm not able to help, as the error message is one
that is MacOS specific. I'd recommend you resend this to the PythonMac
SIG's mailing list (does your webmail MUA not allow a group reply?).

-- Gerhard

Re: [Pythonmac-SIG] Re: [Pypgsql-users] Mac os x revisited

[Don R. <dcr...@pa...>]

Quoting Brian Lenihan <br...@ma...>:

> The autoconf (2.52) which comes with Jaguar is broken on BSDish 
> systems. Either install the autoconf from Fink, or install the latest 
> version from GNU (2.54+). An alternative which may work is to edit 
> line 7294 of your /usr/share/autoconf.autoconf.m4f like so:
> 
> exit (setpgrp (1,1) == -1 ? 0 : 1);])]
> 
I have installed autoconf 2.52, and have rebuilt and reinstalled the pypgsql module.

I am still getting :
 pgversion.c: In function `PgVersion_New':
pgversion.c:125: warning: `c1' might be used uninitialized in this function
pgversion.c:125: warning: `c2' might be used uninitialized in this function
pgversion.c:125: warning: `c1' might be used uninitialized in this function
pgversion.c:125: warning: `c2' might be used uninitialized in this function
pgversion.c:125: warning: `c1' might be used uninitialized in this function
pgversion.c:125: warning: `c2' might be used uninitialized in this function

and 
 pglargeobject.c: In function `PgLo_pickle':
pglargeobject.c:1201: warning: unused variable `cnx'
pglargeobject.c:1202: warning: unused variable `fd' 

while building.

I am still getting ImportError: Failure linking new module when I try use the module

eg:

/Users/don/Documents/archive/Applications/pypgsql/test# python 
Python 2.2 (#1, 07/14/02, 23:25:09) 
[GCC Apple cpp-precomp 6.14] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys, os, string
>>> from pyPgSQL import PgSQL
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 356, in ?
    from libpq import *
  File "/usr/lib/python2.2/site-packages/pyPgSQL/libpq/__init__.py", line 23, in ?
    from libpq import *
ImportError: Failure linking new module
>>> from pyPgSQL import *
>>> from pyPgSQL import PgSQL
>>> from libpq import *
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
ImportError: No module named libpq
>>> from pyPgSQL.libpq import *
>>> from libpq import *
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
ImportError: No module named libpq
>>> 

DOn

Re: [Pythonmac-SIG] Re: [Pypgsql-users] Mac os x revisited

[Brian L. <br...@ma...>]

The autoconf (2.52) which comes with Jaguar is broken on BSDish=20
systems.  Either install the autoconf from Fink, or install the latest=20=

version from GNU (2.54+).  An alternative which may work is to edit=20
line 7294 of your /usr/share/autoconf.autoconf.m4f like so:

exit (setpgrp (1,1) =3D=3D -1 ? 0 : 1);])]

On Tuesday, October 1, 2002, at 12:32 AM, Gerhard H=E4ring wrote:

> (Cc-ing the Python-Mac SIG's list, in the hope that this message=20
> reaches
> it and that somebody there can make any sense of the strange linker
> error.) Could you please Cc Don?
>
> * Don Robertson <dcr...@pa...> [2002-10-01 17:45 =
+1200]:
>> Greetings,
>>
>> I have looked through the archive but it has truncated the posts, and=20=

>> the attached
>> setup.py is not attached.
>>
>>  So apologies if you have seen all this before.
>>
>>  I am trying to install on Mac OS 10.2, and have tried the fix=20
>> suggested:
>>
>> The lib paths were
>>  include_dirs  =3D [ "/usr/local/pgsql/include" ]
>>  library_dirs  =3D [ "/usr/local/pgsql/lib" ]
>>
>>  This line had to be removed from the setup section
>>  runtime_library_dirs =3D pypgsql_rt_dirs,
>>
>>  My system seems pretty much the same as described.
>>
>>  However, I am still unable to get it to work.
>>
>>  When I build, I get  a load of errors, and I get :
>>
>>  Python 2.2 (#1, 07/14/02, 23:25:09)
>> [GCC Apple cpp-precomp 6.14] on darwin
>> Type "help", "copyright", "credits" or "license" for more =
information.
>>>>> from pyPgSQL import PgSQL
>> Traceback (most recent call last):
>>   File "<stdin>", line 1, in ?
>>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 356,=20=

>> in ?
>>     from libpq import *
>>   File "/usr/lib/python2.2/site-packages/pyPgSQL/libpq/__init__.py",=20=

>> line 23, in ?
>>     from libpq import *
>> ImportError: Failure linking new module
>>>>>
>>
>>  My build and install output is at:
>>  http://homepages.paradise.net.nz/dcrober1/4suite/pypgsql_build.html
>
> ... which reads:
>
> [...]
> gcc -arch i386 -arch ppc -bundle -flat_namespace -undefined suppress=20=

> build/temp.darwin-6.1-Power Macintosh-2.2/libpqmodule.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgboolean.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgint2object.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgint8object.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgversion.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pglargeobject.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgnotify.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgconnection.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pgresult.o=20
> build/temp.darwin-6.1-Power Macintosh-2.2/pymemstrdup.o=20
> -L/usr/local/pgsql/lib -lpq -o build/lib.darwin-6.1-Power=20
> Macintosh-2.2/pyPgSQL/libpq/libpqmodule.so
> ld: for architecture i386
> ld: warning /usr/lib/bundle1.o cputype (18, architecture ppc) does not=20=

> match cputype (7) for specified -arch flag: i386 (file not loaded)
> [...]
>
> I have no idea why gcc picks up "-arch i386" _and_ "-arch ppc" options=20=

> when you
> use distutils.  That's certainly the cause of pyPgSQL not working for=20=

> you, but
> I have no idea /why/ distutils behaves like this. Perhaps your Python
> installation on MacOS X is borked?
>
> -- Gerhard
>
> _______________________________________________
> Pythonmac-SIG maillist  -  Pyt...@py...
> http://mail.python.org/mailman/listinfo/pythonmac-sig
>

Re: [Pypgsql-users] Mac os x revisited

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

(Cc-ing the Python-Mac SIG's list, in the hope that this message reaches
it and that somebody there can make any sense of the strange linker
error.) Could you please Cc Don?

* Don Robertson <dcr...@pa...> [2002-10-01 17:45 +1200]:
> Greetings,
> 
> I have looked through the archive but it has truncated the posts, and the attached 
> setup.py is not attached.
>  
>  So apologies if you have seen all this before.
>  
>  I am trying to install on Mac OS 10.2, and have tried the fix suggested:
> 
> The lib paths were
>  include_dirs  = [ "/usr/local/pgsql/include" ]
>  library_dirs  = [ "/usr/local/pgsql/lib" ]
>  
>  This line had to be removed from the setup section
>  runtime_library_dirs = pypgsql_rt_dirs,
>  
>  My system seems pretty much the same as described.
>  
>  However, I am still unable to get it to work.
>   
>  When I build, I get  a load of errors, and I get :
>  
>  Python 2.2 (#1, 07/14/02, 23:25:09) 
> [GCC Apple cpp-precomp 6.14] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> >>> from pyPgSQL import PgSQL
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 356, in ?
>     from libpq import *
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/libpq/__init__.py", line 23, in ?
>     from libpq import *
> ImportError: Failure linking new module
> >>> 
>  
>  My build and install output is at:
>  http://homepages.paradise.net.nz/dcrober1/4suite/pypgsql_build.html

... which reads:

[...]
gcc -arch i386 -arch ppc -bundle -flat_namespace -undefined suppress build/temp.darwin-6.1-Power Macintosh-2.2/libpqmodule.o build/temp.darwin-6.1-Power Macintosh-2.2/pgboolean.o build/temp.darwin-6.1-Power Macintosh-2.2/pgint2object.o build/temp.darwin-6.1-Power Macintosh-2.2/pgint8object.o build/temp.darwin-6.1-Power Macintosh-2.2/pgversion.o build/temp.darwin-6.1-Power Macintosh-2.2/pglargeobject.o build/temp.darwin-6.1-Power Macintosh-2.2/pgnotify.o build/temp.darwin-6.1-Power Macintosh-2.2/pgconnection.o build/temp.darwin-6.1-Power Macintosh-2.2/pgresult.o build/temp.darwin-6.1-Power Macintosh-2.2/pymemstrdup.o -L/usr/local/pgsql/lib -lpq -o build/lib.darwin-6.1-Power Macintosh-2.2/pyPgSQL/libpq/libpqmodule.so
ld: for architecture i386
ld: warning /usr/lib/bundle1.o cputype (18, architecture ppc) does not match cputype (7) for specified -arch flag: i386 (file not loaded)
[...]

I have no idea why gcc picks up "-arch i386" _and_ "-arch ppc" options when you
use distutils.  That's certainly the cause of pyPgSQL not working for you, but
I have no idea /why/ distutils behaves like this. Perhaps your Python
installation on MacOS X is borked?

-- Gerhard

[Pypgsql-users] Mac os x revisited

[Don R. <dcr...@pa...>]

Greetings,


 I have looked through the archive but it has truncated the posts, and the attached 
setup.py is not attached.
 
 So apologies if you have seen all this before.
 
 I am trying to install on Mac OS 10.2, and have tried the fix suggested:

The lib paths were
 include_dirs  = [ "/usr/local/pgsql/include" ]
 library_dirs  = [ "/usr/local/pgsql/lib" ]
 
 This line had to be removed from the setup section
 runtime_library_dirs = pypgsql_rt_dirs,
 
 My system seems pretty much the same as described.
 
 However, I am still unable to get it to work.
  
 When I build, I get  a load of errors, and I get :
 
 Python 2.2 (#1, 07/14/02, 23:25:09) 
[GCC Apple cpp-precomp 6.14] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from pyPgSQL import PgSQL
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 356, in ?
    from libpq import *
  File "/usr/lib/python2.2/site-packages/pyPgSQL/libpq/__init__.py", line 23, in ?
    from libpq import *
ImportError: Failure linking new module
>>> 
 
 My build and install output is at:
 http://homepages.paradise.net.nz/dcrober1/4suite/pypgsql_build.html
 
 Any help would be appreciated.
 
 Don

Re: [Pypgsql-users] Monitoring Query progress

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

I asked on the pg lists and it kicked off a thread that is winding down=20
with a decision about marking the start time of a query in pg_stat_activi=
ty.

There does not seem to be any easy way to monitor the progess.

The best I can do is get the procID from postgres and then use Python to=20
  monitor its stats (cpu, disk , ram use....)

Thanks,
-Aaron


Gerhard H=E4ring wrote:
> * Aaron Held <aa...@Me...> [2002-09-20 16:03 -0400]:
>=20
>>>>Is there a way to monitor the progress of a query?
>>>
>>>"Chapter 10. Monitoring Database Activity"
>>
>>
>>>What else would they do except working?
>>
>>I have optimized the system as much as possible, but during high loads
>>the queries can take a long time.  I would like to give the user
>>some indication of how long the search will take.
>=20
>=20
> I don't think that's possible for a single SQL statement (if splitting
> it up by rewriting it as a stored procedure isn't possible). At least I
> don't know of any way under PostgreSQL. Or Oracle, for that matter.
>=20
> pyPgSQL has some support for asynchronuous query processing in its
> low-level module libpq, but I don't see how that could help in your
> case.
>=20
> I'd try to ask on one of the PostgreSQL lists, perhaps there's still
> some creative possibility to achieve your goal.
>=20
> -- Gerhard
>=20
>=20
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users

Re: [Pypgsql-users] Monitoring Query progress

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

* Aaron Held <aa...@Me...> [2002-09-20 16:03 -0400]:
> >>Is there a way to monitor the progress of a query?
> >"Chapter 10. Monitoring Database Activity"
> 
> 
> >What else would they do except working?
> 
> I have optimized the system as much as possible, but during high loads
> the queries can take a long time.  I would like to give the user
> some indication of how long the search will take.

I don't think that's possible for a single SQL statement (if splitting
it up by rewriting it as a stored procedure isn't possible). At least I
don't know of any way under PostgreSQL. Or Oracle, for that matter.

pyPgSQL has some support for asynchronuous query processing in its
low-level module libpq, but I don't see how that could help in your
case.

I'd try to ask on one of the PostgreSQL lists, perhaps there's still
some creative possibility to achieve your goal.

-- Gerhard

Re: [Pypgsql-users] Querying large sets with PyPgSQL

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

Mike Watkins wrote:
> I realize my question is largely a Postgres domain question, but would 
> like to know if any PyPgSQL users are routinely manipulating data sets 
> containing tens of millions of records.

100 million records on a dual PIII 1200 w/ 2 Gig RAM and 15k SCSI
updating 20 million via pypgSQL takes about 25 minutes.

Seaches take seconds.

-Aaron

Re: [Pypgsql-users] Monitoring Query progress

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

>>Is there a way to monitor the progress of a query?
> "Chapter 10. Monitoring Database Activity"


> What else would they do except working?

I have optimized the system as much as possible, but during high loads
the queries can take a long time.  I would like to give the user
some indication of how long the search will take.

-Aaron

[Pypgsql-users] Querying large sets with PyPgSQL

[Mike W. <mw...@mi...>]

I realize my question is largely a Postgres domain question, but would like 
to know if any PyPgSQL users are routinely manipulating data sets 
containing tens of millions of records.

I currently use Postgres and PyPgSQL in a web content management 
application, and due to the relatively light loads on the database I don't 
have a good feel for how the tools will stack up when doing statistical 
processing. The application is a trend / membership / donation tracking 
application and will contain data for twenty million or so individuals, 
although the specific interactions that wll be reported on (trends and 
events affecting individuals) will probably touch a very small percentage 
of the total data collection.

Just wondering if anyone has high level comments like "don't!" or "works 
great for us". The absence of horror stories will give me reason enough to 
do some performance testing on my own.

Anecdotes and recipes for chocolate cake always welcome.

Mike

Re: [Pypgsql-users] Monitoring Query progress

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

* Aaron Held <Iam...@ne...> [2002-09-20 11:30 -0400]:
> Is there a way to monitor the progress of a query?

AFAIK not really, apart from

"Chapter 10. Monitoring Database Activity"

http://developer.postgresql.org/docs/postgres/monitoring.html

> I have some queries that take about 15 minutes and I want to ensure
> that they are actively working.  

What else would they do except working?

Did you try to optimize your queries?

You can look at the execution plan of the query by firing up psql and
preprending them with an "explain". This will show where problems are
and which parts of the query you need to optimize.

http://developer.postgresql.org/docs/postgres/performance-tips.html

HTH,

-- Gerhard

[Pypgsql-users] Monitoring Query progress

[Aaron H. <Iam...@ne...>]

Is there a way to monitor the progress of a query?

I have some queries that take about 15 minutes and I want to ensure that 
they are actively working.  

Thanks,
-Aaron Held

Re: [Pypgsql-users] How to pass in arguments for 'in' operator

[Edmund L. <el...@in...>]

On 09/17/2002 02:40:37 AM Gerhard wrote:

>There is a solution in current pyPgSQL, but it isn't very nice: You
>can't use DB-API style quoting, but you need to use pyPgSQL's _quote()
>function and construct the query string yourself:

Thank you (and thanks to Mike Watkins too) for your reply. It is as I
suspected, a messy affair to get arguments to the backend for the 'in'
operator.

Hmmm... Looks like all the other DB API modules like psycopg have the same
difficulty with the 'in' operator too!

...Edmund.

Re: [Pypgsql-users] pypgsql & macosx

[Tony L. <tl...@me...>]

On Saturday, September 14, 2002, at 01:46 PM, Gerhard H=E4ring wrote:

> Could you please send your working setup.py to the pypgsql-user list,=20=

> so
> other MacOS X users can profit from it?

Thanks for the support of Gerhard in getting pypsql to run on MacOSX.

I used the postgresql binary from
http://www.entropy.ch/software/MacOSx/postgresql/

The lib paths were
include_dirs  =3D [ "/usr/local/pgsql/include" ]
library_dirs  =3D [ "/usr/local/pgsql/lib" ]

This line had to be removed from the setup section
runtime_library_dirs =3D pypgsql_rt_dirs,

Attached is the final setup.py file

Re: [Pypgsql-users] How to pass in arguments for 'in' operator

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

* Edmund Lian <el...@in...> [2002-09-16 21:52 -0400]:
> I'm stumped... could somebody please help me? I have a query that uses the
> in operator:
> 
> matchDict = {'my_arg': [1, 2, 3]}
> sql = "select * from my_table where col_name in (%(my_arg)s)"
> c = cursor.execute(sql, matchDict)

The problem is that currently, pyPgSQL escapes tuples and lists as
PostgreSQL ARRAYs, which is nice when you need it, but doesn't help very
much for appropriate quoting for the in-Operator. The plan is to make
quoting for the in-operator the default in pyPgSQL 3.0 and have a
PgArray type (likely just a subclass of list) for people needing ARRAYs.

> What should I do to 'my_arg' in matchDict so that it is correctly
> substituted into the SQL statement by pypgsql? I.e., I need the
> substitution to yield:
> 
> select * from my_table where col_name in (1, 2, 3);

There is a solution in current pyPgSQL, but it isn't very nice: You
can't use DB-API style quoting, but you need to use pyPgSQL's _quote()
function and construct the query string yourself:

sql = "select * from my_table where col_name in (%s)" % \
   ",".join(map(PgSQL._quote,  [3,4,5])) 

If there are any additional query parameters, you can then use DB-API
style quoting for these. This would look like:
    
sql = "select * from my_table where id=%%s or col_name in (%s)" + \
   ",".join(map(PgSQL._quote,  [3,4,5])) 

cursor.execute(sql, (25,))

Note the double percent signs.

> How does the answer change if my_arg is a list of strings ['apple',
> 'orange', 'pear'], and the 'in' operator is applied to a varchar column?
> I.e., the substitution should yield:
> 
> select * from my_table where col_name in ('apple', 'orange', 'pear');

See above, PgSQL._quote will do the right thing.

-- Gerhard

Re: [Pypgsql-users] How to pass in arguments for 'in' operator

[Mike W. <mw...@mi...>]

At 09:52 PM 9/16/2002 -0400, Edmund Lian wrote:
>I'm stumped... could somebody please help me? I have a query that uses the
>in operator:
>
>matchDict = {'my_arg': [1, 2, 3]}
>sql = "select * from my_table where col_name in (%(my_arg)s)"
>c = cursor.execute(sql, matchDict)

Maybe I'm missing something... but should be as simple as:

>sql = "select * from my_table where col_name in (%s)"
>c = cursor.execute(sql, matchDict['my_arg'])

[Pypgsql-users] How to pass in arguments for 'in' operator

[Edmund L. <el...@in...>]

I'm stumped... could somebody please help me? I have a query that uses the
in operator:

matchDict = {'my_arg': [1, 2, 3]}
sql = "select * from my_table where col_name in (%(my_arg)s)"
c = cursor.execute(sql, matchDict)

What should I do to 'my_arg' in matchDict so that it is correctly
substituted into the SQL statement by pypgsql? I.e., I need the
substitution to yield:

select * from my_table where col_name in (1, 2, 3);

How does the answer change if my_arg is a list of strings ['apple',
'orange', 'pear'], and the 'in' operator is applied to a varchar column?
I.e., the substitution should yield:

select * from my_table where col_name in ('apple', 'orange', 'pear');

...Edmund.

Re: [Pypgsql-users] pypgsql & macosx

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

* Gerhard Häring <ger...@gm...> [2002-09-14 01:09 +0200]:
> I have access to a MacOS X box at the Sourceforge compile farm, but
> unfortunately, it doesn't have PostgreSQL installed.

I succeded compiling pyPgSQL on the Sourceforge MacOS X box using the
attached setup.py file. I needed to install the files in the windows/
directory to the build process. Does this work for you, too? Changing
the paths to PostgreSQL, of course?

Could you please also try to run the test suite (test/PgSQLTestcases.py)
once you get pyPgSQL built?

-- Gerhard

Re: [Pypgsql-users] pypgsql & macosx

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

* Tony Lembke <tl...@me...> [2002-09-14 08:19 +1000]:
> On Saturday, September 14, 2002, at 05:08 AM, Gerhard Häring wrote:
> >Could you send a build log? I'd really like to make pyPgSQL work on
> >MacOS/X, too.
> 
> The log is below.
> 
> I get the error
>  ld: unknown flag: -R/usr/local/pgsql/lib
> which I am told now by my fellow GnuMED developers that you can ignore 
> - but the ensuing install command gives the same error.
> [...]
> gcc -Wl,-F. -Wl,-flat_namespace,-U,_environ -bundle -framework Python 
> build/temp.darwin-6.0-Power Macintosh-2.2/libpqmodule.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgboolean.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgint2object.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgint8object.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgversion.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pglargeobject.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgnotify.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgconnection.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pgresult.o 
> build/temp.darwin-6.0-Power Macintosh-2.2/pymemstrdup.o 
> -L/usr/local/pgsql/lib -Wl,-R/usr/local/pgsql/lib -lpq -o 
> build/lib.darwin-6.0-Power Macintosh-2.2/pyPgSQL/libpq/libpqmodule.so
> ld: unknown flag: -R/usr/local/pgsql/lib
> error: command 'gcc' failed with exit status 1

Ok, could you try to edit setup.py, scroll to the end and remove the
line:

runtime_library_dirs = pypgsql_rt_dirs

This should hopefully get rid of the compile error. Where did you get
the PostgreSQL for MacOS X for? Or did you compile it yourself?

I have access to a MacOS X box at the Sourceforge compile farm, but
unfortunately, it doesn't have PostgreSQL installed.

If this already works fine for compiling pyPgSQL, could you then send me
your working setup.py? I'll then try to change the FAQ and build process
accordingly, so that building works out of the box in future pyPgSQL
releases.

-- Gerhard