Ubuntu – What is causing rounding errors when querying data from MySQL in python

I’m querying data from a table(schema and example data below) in mysql(mysql-connector-python). In this table I have two float values "ourCostPerSegment" and "theirCostPerSegment" the values that are stored in mysql are 0.0069 and 0.02 respectively. When I query the data from python I get values like 0.006899999920278788 and 0.019999999552965164. These issues are causing a margin of error in calculation that is to large.

This is the table schema:

DESCRIBE companyProperties;
+---------------------+------------+------+-----+---------+-------+
| Field               | Type       | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| dateEffective       | date       | NO   |     | NULL    |       |
| isAgeGated          | tinyint(1) | NO   |     | 0       |       |
| ourCostPerSegment   | float      | NO   |     | NULL    |       |
| theirCostPerSegment | float      | NO   |     | NULL    |       |
| monthlyBaseFee      | int        | NO   |     | NULL    |       |
| friendlyName        | text       | NO   |     | NULL    |       |
+---------------------+------------+------+-----+---------+-------+

Table values:

SELECT * FROM companyProperties;
+---------------+------------+-------------------+---------------------+----------------+--------------+
| dateEffective | isAgeGated | ourCostPerSegment | theirCostPerSegment | monthlyBaseFee | friendlyName |
+---------------+------------+-------------------+---------------------+----------------+--------------+
| 2023-08-03    |          1 |            0.0069 |                0.02 |            250 | test         |
+---------------+------------+-------------------+---------------------+----------------+--------------+

This is the code that is running the query:
This is part of an API request using the FastAPI framework.

python3 --version
Python 3.10.12

conn = db.cursor(dictionary=True, prepared=True)
getCompanyProperties = "SELECT * FROM `{}`.companyProperties ORDER BY dateEffective DESC LIMIT 1".format(companyKey["companyKey"])
conn.execute(getCompanyProperties, [])
companyPropertiesObject = conn.fetchall()
print(companyPropertiesObject)

This is the response:

[{'dateEffective': datetime.date(2023, 8, 3), 'isAgeGated': 1, 'ourCostPerSegment': 0.006899999920278788, 'theirCostPerSegment': 0.019999999552965164, 'monthlyBaseFee': 250, 'friendlyName': 'test'}]

Server Info

SHOW VARIABLES LIKE "%version%";
+--------------------------+-------------------------+
| Variable_name            | Value                   |
+--------------------------+-------------------------+
| admin_tls_version        | TLSv1.2,TLSv1.3         |
| immediate_server_version | 999999                  |
| innodb_version           | 8.0.33                  |
| original_server_version  | 999999                  |
| protocol_version         | 10                      |
| replica_type_conversions |                         |
| slave_type_conversions   |                         |
| tls_version              | TLSv1.2,TLSv1.3         |
| version                  | 8.0.33-0ubuntu0.22.04.2 |
| version_comment          | (Ubuntu)                |
| version_compile_machine  | x86_64                  |
| version_compile_os       | Linux                   |
| version_compile_zlib     | 1.2.13                  |
+--------------------------+-------------------------+

I tried to strictly specify the format of the float in mySQL like FLOAT(M,D). This didn’t fix the issue. One solution that did work was changing the column type to a TEXT and converting the string that is returned to a float in python but this is not ideal.

I have seen some things about decimal point numbers having issues in binary. How do I get around this issue?