Pyodbc returning (Decimal('100.00'),)

python decimal
python initialize decimal
pyodbc documentation
pyodbc cursor
pyodbc row
python decimal format
python get number of decimal places
python convert decimal to float

I'm using pyodbc to read a numeric value from a table, but instead of just giving me '100', it gives me "(Decimal('100.00'),).

Is there a way to get just the number?

I'm running:

cursor.execute("SELECT CurrentBalance as Numeric FROM [AccountsQA].[dbo].[AccountBalance] where AccountId = '2000013' and Currency = 'ZAR'")

My apologies if this has already been answered, but I haven't been able to find out how to solve this.


Presumably the table defines that column as DECIMAL type, so pyodbc is returning a Decimal object to faithfully represent that value.

Assuming that the value may have digits after the decimal point, you could "get just the number" by calling float(), but you don't want to do that if the column represents money (as the name "CurrentBalance" suggests). Using float values to represent money is a Bad Idea.

Instead, you should probably just continue to use the value as a Decimal.

MS Access: "SELECT ? AS " returns incorrect value for Decimal , Presumably the table defines that column as DECIMAL type, so pyodbc is returning a Decimal object to faithfully represent that value. Assuming that the value  pyodbc returns decimals rounded to 2 decimal places from SQL Server Showing 1-2 of 2 messages


I managed to get just the number by doing the following:

cursor.execute("SELECT CurrentBalance as Numeric FROM [AccountsQA].[dbo].[AccountBalance] where AccountId = '2000013' and Currency = 'ZAR'")
result = cursor.fetchall()
amount = result[0][0]

"result[0]" was returning "(Decimal('100.00'),)", but when I used "result [0][0] it returns only "100.00"

pyodbc returns decimals rounded to 2 decimal , I'm hoping that someone with a better understanding of pyodbc internals might see something in the ODBC trace to support or refute my suspicion  What row should it be returning? – cmd Mar 5 '13 at 22:58 1 Thanks for your question - it helped me setup the Teradata database connection in Python script that needs to work with result sets :) .


I know that this question is almost three years old, but in case it helps anyone else searching, my suggestion would be to import Pandas and use that to parse through it. This approach has helped me on several occasions.

import pyodbc
import pandas as pd
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=YourServer;'
                      'Database=YourDatabase;') 
##this is for trusted connection.  
###You may also need to add  Username and password for your code

data = pd.read_sql_query(("SELECT CurrentBalance as Numeric FROM [AccountsQA].[dbo].[AccountBalance] where AccountId = '2000013' and Currency = 'ZAR'"), conn)

print(data.CurrentBalance[0])

This will give you the value of the first response in the CurrentBalance column.

decimal.Decimal Python Example, Here's a script to demonstrate usage: import pyodbc import sys from decimal import getcontext print('python version is: {0}'.format(sys.version)) When you are iterating over rows, each row is a Row instance and not a list.You can convert it to a list (which is JSON serializable) as follows: rows = cursor.fetchall() for row in rows: data.append([x for x in row]) # or simply data.append(list(row))


It would be cleaner to take care of it in your query from the start:

cursor.execute("SELECT CAST(CurrentBalance AS INTEGER) AS Numeric FROM [AccountsQA].[dbo].[AccountBalance] WHERE AccountId = '2000013' AND Currency = 'ZAR'")

#!/usr/bin/python # -*- coding: utf-8 -*- x = 1 # Getting an error if , Here's a script to demonstrate usage: import pyodbc import sys from decimal import getcontext print('python version is: {0}'.format(sys.version)) How do I serialize pyodbc cursor output (from .fetchone, .fetchmany or .fetchall) as a Python dictionary? I'm using bottlepy and need to return dict so it can return it as JSON.


pyodbc - Google Code Archive, Decimal): return float(value) else: return value return process. Example else: return value return process # these routines needed for older versions of pyodbc. The return type of GetValue is Object, inspecting it in the debugger show's it's a boxed decimal. If I change the select to SELECT CAST(@@IDENTITY as int), the return of GetValue is a boxed int and the exception isn't thrown. The Id column is definitely of type int; Why would SELECT @@IDENTITY return a decimal? Some additional information:


Microsoft SQL Server, if length <= len(_TESTSTR): return _TESTSTR[:length] c = int((length + we # could maintain a map from collation to encoding?) self.cnxn.setdecoding(pyodbc. + decStr value = Decimal(decStr) self.cursor.execute("insert into t1 values(?)",  If it is a string then you should be able to insert them as such, letting the server do the conversion. In any case I was unable to reproduce your error with latest pyodbc; inserting Decimal ('0.0999999999999996') into a decimal (7,3) column works and becomes a Decimal ('0.100') when retrieved.


sqlobject.mssql.mssqlconnection, 402, New, Crash when Null is cast as decimal Type-Defect Priority-Medium 389, New, Teradata - INT columns being returned as 0 instead of value on  I am using pyodbc to return rows from a SQL database, and I can connect and get all of the rows just fine. But now I am confused how to deal with the data returned. I would like to join all of the values in the returned list, into a string that I can then write to a file. But I am unsure how to deal with multiple data types in the list.