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

Re: [Pypgsql-users] Re: ssl connection support

[Gerhard H. <gh...@gh...>]

On Sun, Nov 28, 2004 at 02:57:25PM +0100, Gerhard H=E4ring wrote:
> Timothy Smith wrote:
> >hmm it doesn't work under windows. [...]
>=20
> The pyPgSQL Windows binaries are statically linked against a version of=
=20
> the PostgreSQL client libraries that are *not* SSL-enabled.
>=20
> I'll see if I can produce updated Windows binaries soon.

http://ghaering.de:9080/pypgsql/

pyPgSQL win32 binaries for win32. For Python 2.3 and Python 2.4.

This time, *dynamically* linked against PostgreSQL 8, beta 5.

I used the PostgreSQL libraries from PgAdmin 3.

So, I can guarantee it works with the libpq.dll from PgAdmin 3
somewhere in the search path. Tested it against my SSL-enabled
PostgreSQL 7.3.x and it works fine.

Still won't work against PostgreSQL 8 betas, because I built it
against the latest pyPgSQL release, which (and CVS still too, I
suppose) has this silly version string parsing problem.

Also unfortunately, the Python 2.4 binaries are of no great use,
because there are no readily available mxExtensions for Python 2.4.

HTH,

-- Gerhard

Re: [Pypgsql-users] providing arguements to queries

[Timothy S. <ti...@op...>]

I've already solved this, but.

sql gives you so many abilities, why should you be limited to providing 
aruguement to a single clause? it really is very limiting.
theres no way to provide a variable list of arguements to your query.

Chuck Bearden wrote:

>On Sun, 28 Nov 2004 10:42:12 +0100, Karsten Hilbert
><kar...@gm...> wrote:
>[...]
>  
>
>>The query arguments are intended to be values for the *where*
>>clause, no ?
>>    
>>
>
>I am inclined to agree with Karsten that query arguments are for the
>WHERE clause.  To insert the names of columns to be retrieved into a
>SELECT statement at runtime, I would use Python string formatting and
>a helper function that returns a tuple of days of the week:
>
>def daysOfWeek():
>    <build list 'L' with range of days>
>    return tuple(L)
>[...]
>cur.excute("Select %s from table" % (daysOfWeek()))
>
>Chuck
>
>
>-------------------------------------------------------
>SF email is sponsored by - The IT Product Guide
>Read honest & candid reviews on hundreds of IT Products from real users.
>Discover which products truly live up to the hype. Start reading now. 
>http://productguide.itmanagersjournal.com/
>_______________________________________________
>Pypgsql-users mailing list
>Pyp...@li...
>https://lists.sourceforge.net/lists/listinfo/pypgsql-users
>
>
>  
>

Re: [Pypgsql-users] providing arguements to queries

[Chuck B. <cfb...@gm...>]

On Sun, 28 Nov 2004 10:42:12 +0100, Karsten Hilbert
<kar...@gm...> wrote:
[...]
> The query arguments are intended to be values for the *where*
> clause, no ?

I am inclined to agree with Karsten that query arguments are for the
WHERE clause.  To insert the names of columns to be retrieved into a
SELECT statement at runtime, I would use Python string formatting and
a helper function that returns a tuple of days of the week:

def daysOfWeek():
    <build list 'L' with range of days>
    return tuple(L)
[...]
cur.excute("Select %s from table" % (daysOfWeek()))

Chuck

Re: [Pypgsql-users] providing arguements to queries

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Timothy Smith wrote:
| is there another way to provide arguements to a query besides tuples? eg.
| cur.excute("Select %s from table", (column))
| becuase tuples cannot grow or shrink this is VERY limiting, i am in a
| situation right now where i need to select a range of days of week,
| because the length might change using a tuple is a pain.
|
|
| -------------------------------------------------------
| SF email is sponsored by - The IT Product Guide
| Read honest & candid reviews on hundreds of IT Products from real users.
| Discover which products truly live up to the hype. Start reading now.
| http://productguide.itmanagersjournal.com/
| _______________________________________________
| Pypgsql-users mailing list
| Pyp...@li...
| https://lists.sourceforge.net/lists/listinfo/pypgsql-users
|
You can just list them in the query command:
	cur.execute(<query string>, arg1, arg2, ...)

You can provide a list or a tupple containing the parameters (Note, when
providing the areguments as a list or tupple, it must be the only
areument after the query striing).

- --
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBqtOFnmIkMXoVVdURArRgAKDynI56zsqK3kfOtVcB6EELovL6UACdGQP5
54AqNkekj2AUvhocDVA79DY=
=9Syk
-----END PGP SIGNATURE-----

Re: [Pypgsql-users] Re: ssl connection support

[<gh...@gh...>]

Timothy Smith wrote:
> hmm it doesn't work under windows. [...]

The pyPgSQL Windows binaries are statically linked against a version of 
the PostgreSQL client libraries that are *not* SSL-enabled.

I'll see if I can produce updated Windows binaries soon.

-- Gerhard

Re: [Pypgsql-users] Re: ssl connection support

[Jorge G. <go...@ie...>]

Timothy Smith <ti...@op...> writes:

> just an update, my windows python does include support for ssl. i have 
> the _ssl.pyd file in dlls...

There should be support for SSL at the PostgreSQL client library that
comes with pypgsql...  

-- 
Godoy.     <go...@ie...>

Re: [Pypgsql-users] providing arguements to queries

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

> > is there another way to provide arguements to a query besides tuples? eg.
> > cur.excute("Select %s from table", (column))
> > becuase tuples cannot grow or shrink this is VERY limiting, i am in a 
> > situation right now where i need to select a range of days of week, 
> > because the length might change using a tuple is a pain.
> 
> The second argument to execute() must be a sequence. You can use tuples, or
> lists, for example.

Be weary of one-element sequences, though. They tend to be
troublesome. The trick we tend to use is

if len(seq) == 1:
    seq.append(seq[0])


BTW, I am not sure you are intended to do

    "select %s from table", (column)

The query arguments are intended to be values for the *where*
clause, no ?

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

Re: [Pypgsql-users] providing arguements updated

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

> cur.execute("select daily_takings.cash, transactions.for_date from 
> transactions, daily_takings, daily_stats where transactions.id = 
> daily_takings.id and transactions.for_date >= %s and 
> transactions.for_date <= %s and transactions.for_venue = %s and 
> (EXTRACT(DOW FROM transactions.for_date)::integer+6) % 7 BETWEEN %s AND 
                                                       ^
you need to escape that %, eg. write %%

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

Re: [Pypgsql-users] Re: ssl connection support

[Timothy S. <ti...@op...>]

Jorge Godoy wrote:

>Timothy Smith <ti...@op...> writes:
>
>  
>
>>nah's it ok man i got it working, i was just having a blonde day that day.
>>the reason i asked about the ssl was i read on postgres's website that 
>>in order for ssl to work the client need the libaries as well. on your 
>>server you only allow hostssl and it works for you? have you done 
>>tcpdump to see if it is encrypted?
>>    
>>
>
>The server only allows SSL connections.  I tried non-SSL connections and
>they were refused (with pqsl, I can't specify how to connect with
>pypgsql).  I haven't done any check on the Windows machine, but I've
>seen that the traffic was encrypted from a linux box.
>
>  
>
hmm it doesn't work under windows.
any ideas? i have openssl installed. and it still doesn't work.
thats the error i get, so obviously under windows it's not turning it on.
works perfectly under frebsd 4.10, i'm using pypgsql 2.4 on both
no pg_hba.conf entry for host "192.168.1.1", user "timothy", database
"PubWare", SSL off


just an update, my windows python does include support for ssl. i have 
the _ssl.pyd file in dlls...

Re: [Pypgsql-users] Re: ssl connection support

[Timothy S. <ti...@op...>]

Jorge Godoy wrote:

>Timothy Smith <ti...@op...> writes:
>
>  
>
>>nah's it ok man i got it working, i was just having a blonde day that day.
>>the reason i asked about the ssl was i read on postgres's website that 
>>in order for ssl to work the client need the libaries as well. on your 
>>server you only allow hostssl and it works for you? have you done 
>>tcpdump to see if it is encrypted?
>>    
>>
>
>The server only allows SSL connections.  I tried non-SSL connections and
>they were refused (with pqsl, I can't specify how to connect with
>pypgsql).  I haven't done any check on the Windows machine, but I've
>seen that the traffic was encrypted from a linux box.
>
>  
>
hmm it doesn't work under windows.
any ideas? i have openssl installed. and it still doesn't work.
thats the error i get, so obviously under windows it's not turning it on.
works perfectly under frebsd 4.10, i'm using pypgsql 2.4 on both
no pg_hba.conf entry for host "192.168.1.1", user "timothy", database
"PubWare", SSL off

Re: [Pypgsql-users] Re: ssl connection support

[Timothy S. <ti...@op...>]

Jorge Godoy wrote:

>Timothy Smith <ti...@op...> writes:
>
>  
>
>>nah's it ok man i got it working, i was just having a blonde day that day.
>>the reason i asked about the ssl was i read on postgres's website that 
>>in order for ssl to work the client need the libaries as well. on your 
>>server you only allow hostssl and it works for you? have you done 
>>tcpdump to see if it is encrypted?
>>    
>>
>
>The server only allows SSL connections.  I tried non-SSL connections and
>they were refused (with pqsl, I can't specify how to connect with
>pypgsql).  I haven't done any check on the Windows machine, but I've
>seen that the traffic was encrypted from a linux box.
>
>  
>
by golly you are RIGHT

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

when connecting by pgsql. and when doing a tcpdump -w /test i get random 
noise

my only connections allowed is hostssl

Re: [Pypgsql-users] providing arguements to queries

[Gerhard H. <gh...@gh...>]

On Fri, Nov 26, 2004 at 02:36:55PM +1000, Timothy Smith wrote:
> is there another way to provide arguements to a query besides tuples? eg.
> cur.excute("Select %s from table", (column))
> becuase tuples cannot grow or shrink this is VERY limiting, i am in a=20
> situation right now where i need to select a range of days of week,=20
> because the length might change using a tuple is a pain.

The second argument to execute() must be a sequence. You can use tuples, or
lists, for example.

-- Gerhard

[Pypgsql-users] providing arguements updated

[Timothy S. <ti...@op...>]

i have since found way around the % limitation only to be hit with a 
different problem
i am trying to perform a modal operation ( it's in order to change DOW 
from starting on a sunday to starting on a monday ) but i get the 
following error
ValueError: unsupported format character ' ' (0x20) at index 317

this is my code

cur.execute("select daily_takings.cash, transactions.for_date from 
transactions, daily_takings, daily_stats where transactions.id = 
daily_takings.id and transactions.for_date >= %s and 
transactions.for_date <= %s and transactions.for_venue = %s and 
(EXTRACT(DOW FROM transactions.for_date)::integer+6) % 7 BETWEEN %s AND 
%s ORDER BY transactions.for_date",
            (FromDate, ToDate, SelectedVenue, FromDay, ToDay));   

[Pypgsql-users] providing arguements to queries

[Timothy S. <ti...@op...>]

is there another way to provide arguements to a query besides tuples? eg.
cur.excute("Select %s from table", (column))
becuase tuples cannot grow or shrink this is VERY limiting, i am in a 
situation right now where i need to select a range of days of week, 
because the length might change using a tuple is a pain.

[Pypgsql-users] bug?

[Timothy S. <ti...@op...>]

when i query a date column i get this returned <DateTime object for 
'2004-11-28 00:00:00.00' at 8973c60>
now i don't have hours:min:secs in my column.

[Pypgsql-users] Join problem

[Harry T. <hte...@ya...>]

I am having a problem with a query which uses the IN
operator or a join:

cu.execute("SELECT column1 FROM table 1 WHERE
column2.table1 = column.table2")

-OR-

cu.execute("SELECT column1 FROM table 1 WHERE
column2.table1 IN (SELECT column FROM table2)")

When run fron the shell I get correct results; run
from within a module no rows are returned.  (I have
verified the connection and cursor objects are OK by
running the SELECT that's in the IN clause in the
module immediately before the subject query and
getting correct results.

Python and PyPgSQL are both current.  PostgreSQL is v
7.3 on OpenBSD.  Same results running the module fron
a command line and within IDLE and Boa-constructor.

Any suggestions as to where to look greatly
appreciated.

Harry Terkanian


		
__________________________________ 
Do you Yahoo!? 
Meet the all-new My Yahoo! - Try it today! 
http://my.yahoo.com 
 

[Pypgsql-users] error pypgsql

[Hector M. M. G. <mi...@gm...>]

Hello, I have problems with pypgsql, by means of psql I can be
connected to a server with Postgresql=7.3.6, to a data base in which I
have access to everything.
When attempt to connect to me with pypgsql I cannot, with others
librerias for poststoneware if I can, this is the error message:

>>> from pyPgSQL import PgSQL
>>> c1=PgSQL.connect(user="xxxx", password="xxxxxxx",
host="serverxxx.xxx", database="xxx")
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 2211,
in connect
    return Connection(connInfo, client_encoding, unicode_results)
  File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 2331,
in __init__
    raise DatabaseError, m
libpq.DatabaseError: Ivalid format for PgVersion construction.

I have:
python2.3             2.3.4-13 
python2.3-pgsql       2.4.0-5

[Pypgsql-users] data returned from fetching SUM()

[Timothy S. <ti...@op...>]

i have a multi table query which returns a SUM() of my columns and i get 
this as the return value?
[[<PgNumeric instance - precision: 6 scale: 2 value: 4070.00>, 400]]

the values are right.. but why do i get info about the precision of my 
numeric value?

Re: [Pypgsql-users] db.commit()

[Gerhard H. <gh...@gh...>]

On Mon, Nov 22, 2004 at 07:06:38PM +1000, Timothy Smith wrote:
> .rollback(), can i rollback the the last commit for the db object?

An explanation might be this:

changes are accumulated until you COMMIT or ROLLBACK the transaction.
They are accumulated fro mthe point you commited or rolled back the
last time. If you .commit() then the changes are permanent. If you
rollback, then you discard the changes.

You cannot undo changes that you already committed with a single
command.

-- Gerhard
--=20
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

Re: [Pypgsql-users] db.commit()

[Timothy S. <ti...@op...>]

.rollback(), can i rollback the the last commit for the db object?

Gerhard Haering wrote:

>On Mon, Nov 22, 2004 at 01:03:04PM +1000, Timothy Smith wrote:
>  
>
>>can anyone point me to some docs or explainf .commit() for me. what i'm 
>>intrested in is what happens if i perform some actions, but say i find 
>>an error and do not want to commit those. if i do db.close() are thos 
>>updates/inserts cancled?
>>    
>>
>
>To discard the changes, use .rollback() on the connection object.
>
>Btw. a .close() does an implicit .rollback().
>
>-- Gerhard
>  
>

Re: [Pypgsql-users] db.commit()

[Gerhard H. <gh...@gh...>]

On Mon, Nov 22, 2004 at 01:03:04PM +1000, Timothy Smith wrote:
> can anyone point me to some docs or explainf .commit() for me. what i'm=
=20
> intrested in is what happens if i perform some actions, but say i find=20
> an error and do not want to commit those. if i do db.close() are thos=20
> updates/inserts cancled?

To discard the changes, use .rollback() on the connection object.

Btw. a .close() does an implicit .rollback().

-- Gerhard

Re: [Pypgsql-users] return values just commited

[Gerhard H. <gh...@gh...>]

On Mon, Nov 22, 2004 at 10:15:12AM +1000, Timothy Smith wrote:
> after i do a db.commit() is it possible to return the values written
> to the db? i have a transaction id that is an auto incrementing
> number, it would be much quicker if i could know that number without
> having to do a query

No, the only thing that's possible without issuing two queries is
cursor.oidValue. It's the PostgreSQL-internal id of the object (i. e.
row) you created.

PostgreSQL not having a RETURNING clause AFAIK if you insist on only
one statement, then I think you can achieve this with an appropriate
stored procedure that you call via pyPgSQL.

-- Gerhard

[Pypgsql-users] db.commit()

[Timothy S. <ti...@op...>]

can anyone point me to some docs or explainf .commit() for me. what i'm 
intrested in is what happens if i perform some actions, but say i find 
an error and do not want to commit those. if i do db.close() are thos 
updates/inserts cancled?

[Pypgsql-users] return values just commited

[Timothy S. <ti...@op...>]

after i do a db.commit() is it possible to return the values written to 
the db? i have a transaction id that is an auto incrementing number, it 
would be much quicker if i could know that number without having to do a 
query

Re: [Pypgsql-users] Re: ssl connection support

[Jorge G. <go...@ie...>]

Gerhard H=E4ring <gh...@gh...> writes:

> Well, SSL support in pyPgSQL is just as good as the client library it's=20
> linked against.

I see.  It is fine for me :-)

> It's just that pyPgSQL's connect() function doesn't have any way to pass=
=20
> information about SSL to the server. Like, if you require SSL, prefer=20
> SSL etc.

Yep.  I was looking for that but when I didn't find it I tried
connecting and everything just worked.

> That's important if the server supports both SSL and non-SSL and you=20
> want to make sure which method is used.

I wonder if there's too much overhead to justify using a non-SSL
connection when there's SSL available...  Isn't the order configurable
somewhere for the library used?  Here mine tries SSL first and then it
tries connecting without SSL...=20

--=20
Godoy.     <go...@ie...>