Retrieving SQL Server data using pyodbc is returning unexpected data values

Question

I am learning Python and have written some code to retrieve data from a SQL Server table. The code is as follows:

import pyodbc

connection = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=xxx"
"Database=xxx"
"uid=xxx"
"pwd=xxx"
)

cursor = connection.cursor()
cursor.execute('SELECT * FROM Dealers')

for row in cursor:
print(list(row))

This code works fine but the output, for some data types isn't what I expected. For example, I have a line of output that looks like this:

Decimal('0.000'), Decimal('83.360'), True, True, Decimal('0.000'), datetime.date(2017, 5, 31), True, 1, True, True, datetime.datetime(2017, 5, 31, 18, 28, 57, 686666)

Instead of something like Decimal('83.360'), how do I simply get 83.360? Same for the date/time values?

You could simply cast it as a float, but the float representation won't be 1:1 accurate. For datetime objects you can use the datetime isoformat() or strftime() methods. — jordanm
– jordanm, Commented Dec 30, 2020 at 20:31
@jordanm - If you want to write this up as an answer, and show how it would be done, I would vote it up and accept it as an answer. — Randy Minder
– Randy Minder, Commented Dec 30, 2020 at 20:33

Gord Thompson · Accepted Answer · 2020-12-30 20:42:38Z

1

Decimal('83.360') and datetime.date(2017, 5, 31) are the default representations (repr()) of a Decimal object and a datetime.date object. If you want "just 83.360 and 2017-05-31" then you can call str() on them (as opposed to repr()) and you will get a string representation of the value. Note however that

x = str(Decimal('83.360'))

will result in x being a str, i.e., it will have lost its number-ness, so

y = x + 1

results in an error ('TypeError: can only concatenate str (not "int") to str'), not 84.360.

answered Dec 30, 2020 at 20:42

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Randy Minder Over a year ago

Thank you for your response. It seems your solution is to cast the value to a string after it's been generated. Is there a way I can simply prevent the default representation from occurring, or changing the default representation?

Gord Thompson Over a year ago

There may be a way to override the default representation – Python seems to be pretty flexible that way – but I don't know why you would want to. It's really only programmers that ever see the repr() of an object; when we present that value to the user (e.g., on a web page or a report) the object gets converted to some sort of a string representation anyway.

Randy Minder Over a year ago

Yes, you are right. I guess I really don't need to. I just tried exporting the results of my SQL query using Pandas, and the results were as expected.

Collectives™ on Stack Overflow

Retrieving SQL Server data using pyodbc is returning unexpected data values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related