1

I'm trying to extract info from an SQL database to Python. Two columns of the database are numbers, primarily in float format. My problem arises with numbers with more than 6 digits, read_sql_query reads them as int so the decimals do not appear in the dataframe. For example, if the database in SQL looks like this:

Index Voucher Payed
1 225.21 0
2 695.3 0.35
3 6987512.12 635.21
4 654887.36 69995.36

After getting the database to Python, the dataframe looks like this:

Index Voucher Payed
1 225.21 0
2 695.3 0.35
3 6987512 635.21
4 654887 69995

Where the numbers with more than 6 or so digits lose their decimals. My code is:

    query = pd.read_sql_query("SELECT * FROM Database1", conn)

I've already tried:

    query = pd.read_sql_query("SELECT Voucher, Payed FROM Database1", conn, 
              dtype={'Voucher': np.float64, 'Payed': np.float64}))

and

    query = pd.read_sql_query("SELECT CAST(Voucher AS FLOAT) CAST(Payed AS FLOAT) FROM Database1", conn)

Could you help me with this problem. Thanks!

pythonrookie
  • 27
  • 6
  • how are you viewing this dataframe, it may be that the IDE you are using is limiting the number of figures in the output rather than the data being changed – Patrick Oct 08 '21 at 14:50

1 Answers1

0

This Seems to just be a case of your IDE limiting the number of significant figures in the display rather than the data type changing, you could check this by printing the column at issue to the console by

print(query.Payed.values)
Patrick
  • 1,189
  • 5
  • 11
  • 19