0

I created a table in mysql like this :

create table if not exists  data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
execute_time DATETIME NOT NULL default current_timestamp,
db VARCHAR(25) NOT NULL,
book varchar(6),
`open` float(20,3) not null,
`close` float(20,3) not null)

and when I want to insert value into this table, it fails and show the exception as DataError: (1265, "Data truncated for column 'open' at row 1")

and the values I want to insert into open is 3535929.559. I code the insert data in python like this:

insertData = """INSERT INTO {table_name} (execute_time,db,book,`open`,`close`) values 
                        (%(execute_time)s,%(db)s,%(book)s,%(open)s,%(close)s)""" .format(table_name = settings["dumpTable"]["data"])

    conn.cursor().executemany(insertData,data_in_dict)
    conn.commit()

The most strange thing is when I insert the same value in MySQL workbench ,it went smoothly, the query is like this :

insert into data (db,book,`open`,`close`,execute_time) values ('au','book','3535929.559','1079339.851','2016-7-22');  

But the data shown in the table is:

 '5', '2016-07-05 00:00:00', 'au', 'book', '3535929.500', '1079339.875'

I think the problem maybe insertion from python is more restricted? Then how to set it to be less restricted? Any help would be appreciated!

1

Not addressing the question, but addressing 2 bugs in your schema.

  • FLOAT(m,n) takes the decimal input, rounds to n decimal places in decimal, then converts to binary with another rounding error.
  • FLOAT (with or without (m,n)) can hold only about 7 digits of precision. Hence, both numbers in your example lose precision at the low end. As you found out, storing 3535929.559 into a FLOAT(20,3), then retrieving it will yield only 3535929.500. This is because a FLOAT has only 24 "significant bits of precision".

There are two solutions for both bugs:

  • DECIMAL(m,n) -- (20,3) will still round to 3 decimal places, but that might be OK for your application. But it does not have the extra rounding to binary nor the loss of precision.
  • DOUBLE (without (m,n)) -- This has about 16 digits of precision, more than enough for most applications. And, even though the decimal input will be rounded to binary, it will be so far out that it is unlikely to cause errors.

Sizes...

FLOAT (with or without (m,n)) takes 4 bytes.
DOUBLE (with or without (m,n)) takes 8 bytes.
DECIMAL(20,3) takes 10 bytes. Shrinking either number may save space.

  • Thank you for your explanation, but could you please detail this sentence:"FLOAT (with or without (m,n)) can hold only about 7 digits of precision. Hence, both numbers in your example lose precision at the low end (.559 --> .500, etc.)" I cannot understand it, especially the figures. – tina Sep 7 '16 at 2:20
0

I have found the reason, it is because that the data for open in my data_in_dict is not a type of float ,it is a string. From my experience, the two reason that can cause the data truncated exception is :1) the data exceeds the type precision 2) the data type is not consistent with the type in the table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.