Python and Decimal integers from DB

[Sampson, David]

Here is the solution, for the record.
 
 
====================
import pyodbc
conn = pyodbc.connect("DSN=NAPL;UID=NAPL_VIEW;PWD=NAPVIEW")
curs = conn.cursor()
 
roll_num = 'A27255'
Cols = 'TO_CHAR(X_NO) as X_NO'
Table = 'photos'
Select = 'Select ' + Cols + ' from ' + Table + ' where roll_number = '
+"'" + roll_num + "'" 
 
curs.execute(Select)
print "select sucess"
rows= curs.fetchall()
print "fetch success"
for row in rows:
    print row.X_NO
 
print "done"

====================
X_NO was entered in the DB as a number using the comma as a non-standard
separator. Python did not like it in the decimal.py module so we brought
it in as a character string using varchar2.
 
Python liked this solution and returned my results. Now I can give the
chars to a var and change it to numbers for some vector math
 
Hope this helps people in the future


________________________________

From: python-list-bounces+dsampson=nrcan.gc.ca at python.org
[mailto:python-list-bounces+dsampson=nrcan.gc.ca at python.org] On Behalf
Of Sampson, David
Sent: May 10, 2007 12:58
To: python-list at python.org
Subject: Python and Decimal integers from DB



Hey folks 

Freshie looking for help.... 

This is what I am trying to do. 

Use PYODBC to hit an oracle DB that has a ODBC connection.\ 
\The code bellow works when I replace '*' with a specific collumn name
that does not have commas in the number. 

It also works for alphanumberic values. 

It fails when I try '*' or any collumn that has a comman in the number.
The numbers are Latitude longitude readings. 

I get back "Select Success" but not "fetch success" so I feel as thopugh
something is missing on the fetchall() function

So how do I get the numbers to be recognized by python? 

I did lots of searches on python, ODBC, PYODBC, Decimal us and commas as
decimals and various combinations. Lots of mailing lists a few tutorials
and lots of misses.

I came across: 

Import decimal 
Decimal.Decimal() 

This works a bit. It took a coma separated number string and kept the
first part. But that works only when I put in a raw value, it doesn't
work on the Fetchall function.

HELP PLEASE... 


Cheers 


===============PYHTON CODE======================== 

import pyodbc 
conn = pyodbc.connect("DSN=NAPL;UID=NAPL_VIEW;PWD=NAPVIEW") 
curs = conn.cursor() 

roll_num = 'A27255' 
Cols = '*' 
Table = 'photos' 
Select = 'Select ' + Cols + ' from ' + Table + ' where roll_number = '
+"'" + roll_num + "'" 

curs.execute(Select) 
print "select sucess" 
rows= curs.fetchall() 
print "fetch success" 
for row in rows: 
    print row.PHOTO_NUMBER 

print "done" 
==================================== 



=================Standard Output ======================== 
Traceback (most recent call last): 
  File
"C:\Python25\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py"
, line 310, in RunScript 
    exec codeObject in __main__.__dict__ 
  File "U:\My Documents\py_projects\georef\NAPL_DB.py", line 12, in
<module> 
    rows= curs.fetchall() 
  File "C:\Python25\lib\decimal.py", line 614, in __new__ 
    self._sign, self._int, self._exp =
context._raise_error(ConversionSyntax) 
  File "C:\Python25\lib\decimal.py", line 2325, in _raise_error 
    raise error, explanation 
InvalidOperation 

==================================== 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20070511/3d2354ac/attachment.html>