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

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

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

Jorge Godoy wrote:
> Gerhard Haering <gh...@gh...> writes:
> 
> 
>>On Fri, Nov 19, 2004 at 06:54:15PM +1000, Timothy Smith wrote:
>>
>>>whats kind of support for ssl connections does pypgsql have?
>>
>>None so far, but it would be easy to add the additional parameters to
>>the connect() function in pyPgSQL/PgSQL.py.
> 
> None?  It seems to work here...  If I connect with pyPgSQL to a remote
> database that only accepts SSL connections it accepts my connection.  If
> I use psql without SSL, it doesn't connect at all, so I assumed it used
> some library from the client side of PostgreSQL.  Am I wrong?

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

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

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

-- Gerhard

[Pypgsql-users] Re: ssl connection support

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

Gerhard Haering <gh...@gh...> writes:

> On Fri, Nov 19, 2004 at 06:54:15PM +1000, Timothy Smith wrote:
>> whats kind of support for ssl connections does pypgsql have?
>
> None so far, but it would be easy to add the additional parameters to
> the connect() function in pyPgSQL/PgSQL.py.

None?  It seems to work here...  If I connect with pyPgSQL to a remote
database that only accepts SSL connections it accepts my connection.  If
I use psql without SSL, it doesn't connect at all, so I assumed it used
some library from the client side of PostgreSQL.  Am I wrong?

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

Re: [Pypgsql-users] ssl connection support

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

On Fri, Nov 19, 2004 at 06:54:15PM +1000, Timothy Smith wrote:
> whats kind of support for ssl connections does pypgsql have?

None so far, but it would be easy to add the additional parameters to
the connect() function in pyPgSQL/PgSQL.py.

-- Gerhard

[Pypgsql-users] ssl connection support

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

whats kind of support for ssl connections does pypgsql have?

Re: [Pypgsql-users] money types, decimal module

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

cheers guys i got it all working. next problem will probably be 
extracting data :\

Re: [Pypgsql-users] money types, decimal module

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

On Thu, Nov 18, 2004 at 02:54:31PM +1000, Timothy Smith wrote:
> Billy G. Allie wrote:
> [...] so i have float types ( becuase for various reasons thats what i'm
> forced to use) how do i pass them in? i understand the first part of
> what you are saying, if i pass string data in it will put it in
> correctly for any database type.

Use %s instead of %.2f as placeholder and you should be fine.

As Billy said, *always* use %s as placeholder when using pyPgSQL, no
matter what type the values are.

-- Gerhard

Re: [Pypgsql-users] money types, decimal module

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

> here is my code i think is the problem
> cur.execute("INSERT INTO DailyTakings VALUES (%s,%s, %s, 
> %s,%s,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f)",
Oh, I think pypgsql wants you to use %s everywhere regardless
of argument type.

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

Re: [Pypgsql-users] money types, decimal module

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

Billy G. Allie wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Timothy Smith wrote:
> | Karsten Hilbert wrote:
> |
> |>> hello i'm stuck with a problem. whats the best way to store money
> |>> types.
> |>
> |> Use numeric.
> |>
> |> Karsten
> |>
> |>
> | Ok i must be doing something wrong here i'm still getting the same 
> error
> | and i have changed my types to numeric.
> |
> | (EnteredBy,DateEntered,ForDate, ForVenue, TillName,Tape,Cash, GM,
> | VenueManager, AsstManager, BarManager, PRCards,   otherPromo, 
> Functions,
> | Accounts, Spill, Orings, Variance))
> |  File "/usr/local/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line
> | 3048, in execute
> |    self.res = self.conn.conn.query(_qstr % parms)
> | TypeError: float argument required
> |
> | here is my code i think is the problem
> | cur.execute("INSERT INTO DailyTakings VALUES (%s,%s, %s,
> | 
> %s,%s,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f)",
> |    (EnteredBy,DateEntered,ForDate, ForVenue, TillName,Tape,Cash, GM,
> | VenueManager, AsstManager, BarManager, PRCards,    otherPromo,
> | Functions, Accounts, Spill, Orings, Variance))
>
> You need to use '%s' for all the parameters.  If your money values are
> defined as money or as numberis(8,2) [actuallay the 9 can be whatever
> precision you desire] it will do what you want.  If the values your are
> passing in are python numeric typs (e.g. float, etc), you will need to
> cast them to the correct (database) type.
>
> For example: Pg_Numeric(cash, 9, 2)
>
> This 'cast' will ensure that the data is sent to the database in the
> correct format.
>
> I hope this makes the water less muddy.
>
> - --
> ____       | 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
>
> iD8DBQFBnAcznmIkMXoVVdURAsaZAKDDmUM43ozJpp5EEJUVTPfxovhATACglsiZ
> KTWlGV73EoIUdHoprEz1HDA=
> =L6PY
> -----END PGP SIGNATURE-----
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: InterSystems CACHE
> FREE OODBMS DOWNLOAD - A multidimensional database that combines
> robust object and relational technologies, making it a perfect match
> for Java, C++,COM, XML, ODBC and JDBC. www.intersystems.com/match8
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users
>
>
so i have float types ( becuase for various reasons thats what i'm
forced to use) how do i pass them in? i understand the first part of
what you are saying, if i pass string data in it will put it in
correctly for any database type.

Re: [Pypgsql-users] money types, decimal module

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

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

Timothy Smith wrote:
| Karsten Hilbert wrote:
|
|>> hello i'm stuck with a problem. whats the best way to store money
|>> types.
|>
|> Use numeric.
|>
|> Karsten
|>
|>
| Ok i must be doing something wrong here i'm still getting the same error
| and i have changed my types to numeric.
|
| (EnteredBy,DateEntered,ForDate, ForVenue, TillName,Tape,Cash, GM,
| VenueManager, AsstManager, BarManager, PRCards,   otherPromo, Functions,
| Accounts, Spill, Orings, Variance))
|  File "/usr/local/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line
| 3048, in execute
|    self.res = self.conn.conn.query(_qstr % parms)
| TypeError: float argument required
|
| here is my code i think is the problem
| cur.execute("INSERT INTO DailyTakings VALUES (%s,%s, %s,
| %s,%s,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f)",
|    (EnteredBy,DateEntered,ForDate, ForVenue, TillName,Tape,Cash, GM,
| VenueManager, AsstManager, BarManager, PRCards,    otherPromo,
| Functions, Accounts, Spill, Orings, Variance))

You need to use '%s' for all the parameters.  If your money values are
defined as money or as numberis(8,2) [actuallay the 9 can be whatever
precision you desire] it will do what you want.  If the values your are
passing in are python numeric typs (e.g. float, etc), you will need to
cast them to the correct (database) type.

For example: Pg_Numeric(cash, 9, 2)

This 'cast' will ensure that the data is sent to the database in the
correct format.

I hope this makes the water less muddy.

- --
____       | 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

iD8DBQFBnAcznmIkMXoVVdURAsaZAKDDmUM43ozJpp5EEJUVTPfxovhATACglsiZ
KTWlGV73EoIUdHoprEz1HDA=
=L6PY
-----END PGP SIGNATURE-----

Re: [Pypgsql-users] money types, decimal module

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

Karsten Hilbert wrote:

>>hello i'm stuck with a problem. whats the best way to store money types. 
>>    
>>
>Use numeric.
>
>Karsten
>  
>
Ok i must be doing something wrong here i'm still getting the same error 
and i have changed my types to numeric.

 (EnteredBy,DateEntered,ForDate, ForVenue, TillName,Tape,Cash, GM, 
VenueManager, AsstManager, BarManager, PRCards,   otherPromo, Functions, 
Accounts, Spill, Orings, Variance))
  File "/usr/local/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 
3048, in execute
    self.res = self.conn.conn.query(_qstr % parms)
TypeError: float argument required

here is my code i think is the problem
cur.execute("INSERT INTO DailyTakings VALUES (%s,%s, %s, 
%s,%s,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f)",
    (EnteredBy,DateEntered,ForDate, ForVenue, TillName,Tape,Cash, GM, 
VenueManager, AsstManager, BarManager, PRCards,    otherPromo, 
Functions, Accounts, Spill, Orings, Variance))
       

Re: [Pypgsql-users] money types, decimal module

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

> hello i'm stuck with a problem. whats the best way to store money types. 
Use numeric.

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

Re: [Pypgsql-users] money types, decimal module

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

Update on whats happening.
i did a "print TillName" right before attempting to do my insert into 
and there is no "" appended there.
here is the problem line

cur.execute("INSERT INTO DailyTakings VALUES (%s, %s, %s, %s, %s, %s, 
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )",
    (EnteredBy,    DateEntered,ForDate, ForVenue, TillName,Tape,Cash, 
GM, VenueManager, AsstManager, BarManager, PRCards,    otherPromo, 
Functions, Accounts, Spill, Orings, Variance))

Timothy Smith wrote:

> hello i'm stuck with a problem. whats the best way to store money 
> types. i know postgre has it's own money data type, but the problem is 
> pythons decminal module. floats obviously aren't arrcurate enough, but 
> when i use the decminal modle i get this error.
>
> libpq.OperationalError: ERROR:  syntax error at or near ""11.00"" at 
> character 88
>
> obviously it's double quotes which are the problem. i'm just stumped 
> where to go from here. surely other people have run into the exact 
> same problem?
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: InterSystems CACHE
> FREE OODBMS DOWNLOAD - A multidimensional database that combines
> robust object and relational technologies, making it a perfect match
> for Java, C++,COM, XML, ODBC and JDBC. www.intersystems.com/match8
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users
>
>

[Pypgsql-users] money types, decimal module

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

hello i'm stuck with a problem. whats the best way to store money types. 
i know postgre has it's own money data type, but the problem is pythons 
decminal module. floats obviously aren't arrcurate enough, but when i 
use the decminal modle i get this error.

libpq.OperationalError: ERROR:  syntax error at or near ""11.00"" at 
character 88

obviously it's double quotes which are the problem. i'm just stumped 
where to go from here. surely other people have run into the exact same 
problem?

Re: [Pypgsql-users] paging through results with fetchmany()

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

On Sun, Nov 07, 2004 at 09:41:50AM -0800, Uzo Uzo wrote:
> I return a set of results, which I break into multiple pages.  This
> is how I have implemented, but I feel that it is too expensive in
> terms of execution.  Is there anyway to tell PgSQL to start fetching
> from a particular number?=20
> =20
> c.execute(Query)
> c.fetchmany( (pagenum-1)*NumPerPage) # skip previous results
> c.fetchmany(NumPerPage) # fetch for page

Looks like you want the LIMIT and OFFSET parameters for SELECT.

http://www.postgresql.org/docs/current/interactive/sql-select.html

-- 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?

[Pypgsql-users] paging through results with fetchmany()

[Uzo U. <uz...@ya...>]

I return a set of results, which I break into multiple pages.  This is how I have implemented, but I feel that it is too expensive in terms of execution.  Is there anyway to tell PgSQL to start fetching from a particular number? 
 
c.execute(Query)
c.fetchmany( (pagenum-1)*NumPerPage) # skip previous results
c.fetchmany(NumPerPage) # fetch for page
 
Uzo


The Opportunity Gap
 
Between what is and what could be exists a potential gap.
This gap is an opportunity gap; With every passing of time, it closes.
The gap becomes closed, when what was becomes what is, 
... OR what was can no longer be what it could be.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [Pypgsql-users] tutorial

[John F. <jfa...@yo...>]

On Thursday 04 November 2004 23:23, Gerhard Haering wrote:
> On Thu, Nov 04, 2004 at 10:21:50PM -0800, John Fabiani wrote:
> > On Tuesday 02 November 2004 17:32, Timothy Smith wrote:
> > > can anyone point me to a good tutorial for pypgsql with examples etc?
> >
> > Look in the doc directory for 1.1.x
> > and the examples directory for 1.99.x.
>
> Are you mistaking it for psycopg?
>
> I already suggested PEP 0249 to the OP in PM.
>
> -- Gerhard

Your right I didn't notice that it was Pypgsql - thanks
John

Re: [Pypgsql-users] tutorial

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

On Thu, Nov 04, 2004 at 10:21:50PM -0800, John Fabiani wrote:
> On Tuesday 02 November 2004 17:32, Timothy Smith wrote:
> > can anyone point me to a good tutorial for pypgsql with examples etc?
> >
> Look in the doc directory for 1.1.x
> and the examples directory for 1.99.x.

Are you mistaking it for psycopg?

I already suggested PEP 0249 to the OP in PM.

-- Gerhard

Re: [Pypgsql-users] tutorial

[John F. <jfa...@yo...>]

On Tuesday 02 November 2004 17:32, Timothy Smith wrote:
> can anyone point me to a good tutorial for pypgsql with examples etc?
>
Look in the doc directory for 1.1.x
and the examples directory for 1.99.x.

John

Re: [Pypgsql-users] [' '] junk around return records

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

Timothy Smith wrote:
> what ever pyPgSQL returns it adds this [' data '] junk around my 
> records. it's going to be very tedious if i have to remove this from 
> everything that gets returned.

It looks like you're confused.

Can you please provide a complete example, sql schema, your query, your 
Python code that executes the query, what you get and what you expect to 
get?

-- Gerhard

[Pypgsql-users] [' '] junk around return records

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

what ever pyPgSQL returns it adds this [' data '] junk around my 
records. it's going to be very tedious if i have to remove this from 
everything that gets returned.

Re: [Pypgsql-users] problems with returned data sets

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

On Thu, Nov 04, 2004 at 05:55:11PM +1000, Timothy Smith wrote:
> ok i get that but it is still giving me problems. the error i get is
> libpq.OperationalError: ERROR:  column "groupmembership" does not exist
>=20
> but it DOES exisit? there must be some syntax error i'm making. pyPgSQL=
=20
> has bearly any real documentation so these errors are easy to make....
>=20
> #Get groups which this user belongs to
>    cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName =3D=20
> UserName")
>    Groups =3D cur.fetchone()
>    GroupMembership =3D Groups[0]
>=20
> *snip* =20
>  =20
>    #Get menu items which this user has access to
>    cur.execute("SELECT MenuName FROM MenuItems WHERE UserGroup =3D=20
> GroupMembership")

"WHERE UserGroup =3D GroupMembership" means you join two columns in SQL. To
compare a column with a string, you use something like "WHERE UserGroup =3D
'Group1'", to compare it with a number "WHERE UserGroup =3D 42'.

Now, it appears you want to parametrize your query. That's what the additio=
nal
parameter of .execute() is for: to give it a sequence of query parameters:

cur.execute("SELECT Menuname FROM MenuItems WHERE UserGroup =3D %s", (Group=
Membership,))

The placeholder in pyPgSQL is always %s.

-- 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] problems with returned data sets

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

ok i get that but it is still giving me problems. the error i get is
libpq.OperationalError: ERROR:  column "groupmembership" does not exist

but it DOES exisit? there must be some syntax error i'm making. pyPgSQL 
has bearly any real documentation so these errors are easy to make....

#Get groups which this user belongs to
    cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName = 
UserName")
    Groups = cur.fetchone()
    GroupMembership = Groups[0]

*snip*  
   
    #Get menu items which this user has access to
    cur.execute("SELECT MenuName FROM MenuItems WHERE UserGroup = 
GroupMembership")


Gerhard Haering wrote:

>On Thu, Nov 04, 2004 at 04:26:26PM +1000, Timothy Smith wrote:
>  
>
>>Hello, when i use fetch all i have a problem with what s returned.
>>everything is returned as this string ['data'] which makes is very
>>inconvienent to work it. is there better way of fetching things then
>>i am using? here is my code
>>
>>cur = db.cursor()
>>cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName = UserName")
>>groups = cur.fetchone()
>>    
>>
>
>The DB-API requires that the return value of the fetchXX methods is a
>sequence. So even if you only select one column, you still get a
>sequence of length 1.
>
>To access the columns in the sequence, you can use index-based access:
>groups[0]. This will work with all database adapters.
>
>Much more convenient, but not portable is pyPgSQL's extension to
>access columns in the result row by name. Two ways to do this are
>supported, dictionary-style and attribute-style:
>
>groups["UserGroup"]
>groups.UserGroup
>
>HTH,
>
>-- Gerhard
>  
>

Re: [Pypgsql-users] problems with returned data sets

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

On Thu, Nov 04, 2004 at 04:26:26PM +1000, Timothy Smith wrote:
> Hello, when i use fetch all i have a problem with what s returned.
> everything is returned as this string ['data'] which makes is very
> inconvienent to work it. is there better way of fetching things then
> i am using? here is my code
>=20
> cur =3D db.cursor()
> cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName =3D UserNa=
me")
> groups =3D cur.fetchone()

The DB-API requires that the return value of the fetchXX methods is a
sequence. So even if you only select one column, you still get a
sequence of length 1.

To access the columns in the sequence, you can use index-based access:
groups[0]. This will work with all database adapters.

Much more convenient, but not portable is pyPgSQL's extension to
access columns in the result row by name. Two ways to do this are
supported, dictionary-style and attribute-style:

groups["UserGroup"]
groups.UserGroup

HTH,

-- 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?

[Pypgsql-users] problems with returned data sets

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

Hello, when i use fetch all i have a problem with what s returned. 
everything is returned as this string ['data'] which makes is very 
inconvienent to work it. is there better way of fetching things then i 
am using? here is my code

cur = db.cursor()
cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName = UserName")
groups = cur.fetchone()

[Pypgsql-users] tutorial

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

can anyone point me to a good tutorial for pypgsql with examples etc?