CODEWITHSUNDEEP
mysqlpython-2.7
Jeff Pollet
Jeff Pollet

Reputation: 47

python mysql insert syntax error when only one variable

I'm using Python 2, if that matters for this issue. I'm playing with mysql.connector as a learning exercise, and had built some working inserts, then came across an odd (to me) problem--I'm sure I'm doing something wrong that is simple. I was inserting into two columns for a few tables, something like the first example, below; when I had a case where I was inserting into just one column, I got a syntax error.

Any ideas why syntax like this works just fine:

    query_publisher =   "INSERT INTO publisher (name, name2) "\
                    "VALUES (%s, %s) " \
                    "ON DUPLICATE KEY UPDATE PUBLISHER_ID=LAST_INSERT_ID(PUBLISHER_ID)"
    args_publisher = (publisher_name, publisher_name2)

Yet this syntax, which is what I really want to do, throws a syntax error:

    query_publisher =   "INSERT INTO publisher (name) "\
                    "VALUES (%s) " \
                    "ON DUPLICATE KEY UPDATE PUBLISHER_ID=LAST_INSERT_ID(PUBLISHER_ID)"
    args_publisher = (publisher_name)

[EDIT]: In both cases I'm executing this with:

        cursor.execute(query_publisher, args_publisher)

The error:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s ON DUPLICATE KEY UPDATE PUBLISHER_ID=LAST_INSERT_ID(PUBLISHER_ID)' at line 1

Upvotes: 3

Views: 848

Answers (1)

Haifeng Zhang
Haifeng Zhang

Reputation: 31905

the syntax for Mysql execute is execute(query_statement, tuple_values)

args_publisher = (publisher_name) should be tuple, when it is one-element tuple, it should be (publisher_name,) Don't omit the comma

Upvotes: 4

Related Questions