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

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

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

[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...>]

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

[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

[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

[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