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!