Reading integers from database

Python Help
1

Hi all,
I’m reading some records from a Oracle table.
For eache record I create a list and then I export the list to a CSV file.

    for rec in ulv_df.itertuples():
        columnList = []
        columnList.append(rec.instructnumber)
        columnList.append(rec.vehiclekey)
        columnList.append(rec.vehicle_id)
        columnList.append(rec.specification1)
        ...
        print(columnList)
        writer.writerow(columnList)

The ‘vehicle_id’ is defined as NUMBER but it is handled in different way in different records:

['i', 'EAP83225', 'MEP0000085', 786.0, '', '', '', '', '', '', '', datetime.date(1995, 1, 1), datetime.date(1995, 12, 31)]
['i', 'EAP83225', 'FOR0001610', 786.0, '', '', '', '', '', '', '', datetime.date(1995, 1, 1), datetime.date(1995, 12, 31)]
...
['i', 'EAP82205', 'MAZ0000447', 760, '', '', '', '', '', '', '', datetime.date(1996, 3, 1), datetime.date(1996, 12, 31)]
['i', 'EAP82205', 'MAZ0000231', 760, '', '', '', '', '', '', '', datetime.date(1996, 1, 1), datetime.date(1996, 12, 31)]

Why the field in the first two rows has turned to float (786.0 - WRONG), and in the last ones as integers (760 - CORRECT)??

Thanks, regards

2

Do you mean a database, like using SQL? Or exactly what?

Well, probably it depends on the data that you actually have in the original source.

If the value should only ever be an integer in the first place, then the database design is wrong, and that isn’t a problem with the Python code.

See for example: Oracle NUMBER Data Type By Practical Examples

There’s no way that the Python code can “know” that the numbers given to it, that aren’t all integers, should be - unless you tell it, by writing code that checks the type and value.

By this code, I guess that you use the Pandas third-party library to read your database into a Pandas DataFrame. In order to get accurate help with programming problems, it’s important to a) understand what you are using and b) explicitly say these things, so that people don’t have to guess (sometimes they will be wrong).

Pandas can already directly output to a CSV file, and it also has more direct tools to change the data type for an entire column, instead of writing your own loop.

2 Likes
3

Hello Karl,
thanks a lot for your reply and sorry for my poor descriptions.
Yes, you are right for each assumption :+1:

I agree that the database has a poor design and I’ll discuss with the designer.
In the meantime I solved casting to integer the field, after the check that it is not a nan or Null.

Thanks a lot for the last hint: I did not know Pandas can export to csv file.

Thanks!!