_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax;

Question

        sql = ("INSERT INTO {0} "
               "(id, timestamp, status, priority, client, group, capacity, level, mail_id) "
               "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)".format(TABLE_NAME_MAIL))
        values = ('NULL', report['timestamp'], 'succeeded', report['priority'], c.strip(), report['group'], 'NULL', 'NULL', ref_mailid)
        cursor.execute(sql, values)
        #cursor.execute('INSERT INTO %s VALUES (NULL,"%s","%s","%s","%s","%s",NULL,NULL,"%s") ' % (TABLE_NAME_REPORT, report['timestamp'], 'succeeded', report['priority'], c.strip(), report['group'], ref_mailid))

The commented out cursor.execute works, the uncommented throws an error:

_mysql_exceptions.ProgrammingError: (1064, "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 'group, capacity, level, mail_id) VALUES ('NULL', '2014-12-05 23:46:56', 'succeed' at line 1")

Column 'id' has AUTO_INCREMENT

Why am I getting this error?

juergen d · Accepted Answer · 2018-10-21 18:14:37Z

5

group is a reserved keyword in MySQL. Use backticks to escape the name

INSERT INTO {0} (id, timestamp, status, priority, client, `group`, capacity ...
                      here--------------------------------^-----^

or even better use a different column name.

And don't use 'NULL' as parameter. Either use NULL without the quotes or remove it completely from the query:

INSERT INTO {0} (timestamp, ...
                ^----------no ID

edited Oct 21, 2018 at 18:14

answered Oct 21, 2018 at 16:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

pymat Over a year ago

Vielen Dank Jürgen, es hat geklappt! Actually, to add I use None instead of NULL.

Norbert · Accepted Answer · 2018-10-21 16:18:00Z

0

When using an auto increment column, you have two options:

Insert a value in there (ie: Override the auto increment)
Do not set the column at all

1:

In case of setting a value, and you want to set "NULL" the column has to allow a NULL. This is rarely the case with an auto increment column since it would defeat its purpose.

So this is allowed:

INSERT INTO tab (id, sometext) VALUES (10,"Hi");

2:

Alternative (most used) is not to name the column at all:

INSERT INTO tab (sometext) VALUES ("Hi");

which would lead to mysql assigning a value to id in your table.

answered Oct 21, 2018 at 16:18

Norbert

6,1043 gold badges19 silver badges40 bronze badges

Collectives™ on Stack Overflow

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax;

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related