Syntax error with floats when inserting into a table

Question

Im trying to insert data into a table using python but im getting a syntax error. Here is my table format.

mycursor.execute("""CREATE TABLE workers (
                 `ID` int(5) NOT NULL AUTO_INCREMENT,
                 ` x` FLOAT NOT NULL,
                 ` y` FLOAT NOT NULL,
                 `z` FLOAT NOT NULL,
                 `Acc` int NOT NULL,
                 `Datasource` char(3) NOT NULL,
                 `Datatype` int(5) NOT NULL,
         PRIMARY KEY (`ID`))""")

The table code works fine

Here is the data im trying to insert into those tables

mycursor.execute("""INSERT INTO workers VALUES
(1,\ 
1.2720693 ,\
6.583606 ,\
6.8299093 ,\
3 ,\
"Acc" ,\
1)""")

This is the error im getting

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\ 
1.2720693 ,6.583606 ,6.8299093 ,3 ,"Acc" ,1)' at line 2

Also, you should always name your fields during insert/select. All it takes is someone adding a field in the middle of the table and all of a sudden, things start breaking in new and hard to debug ways — Basic
– Basic, Commented Nov 27, 2015 at 23:51

Chris Seymour · Accepted Answer · 2015-11-27 23:39:50Z

1

The error you are getting back from the server is telling you the backslashes are are the problem.

mycursor.execute("""
                 INSERT INTO workers VALUE (1,                    
                 1.2720693,
                 6.583606,
                 6.8299093,
                 3,
                 "Acc",
                 1)
                 """)

Triple strings in python are designed for multiline strings so you don't need to handle line continuations with backslashes. You also have 7 field values in your INSERT statement but you only need 6 as ID column is auto-incrementing.

edited Nov 27, 2015 at 23:39

answered Nov 27, 2015 at 23:34

Chris Seymour

86.4k32 gold badges166 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Spike7 Over a year ago

Thanks, also ill take on board what you said about the field values

Collectives™ on Stack Overflow

Syntax error with floats when inserting into a table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related