Python MySQL INSERT ONDUPLICATE KEY

Question

I have a Python script that inserts data into a MySQL database, but to preserve the id values I want it so that if it finds the same name then it will just update the vuln_count column for that record.

I have the following MySQL insert code:

cur.execute("INSERT INTO vuln_sets (vulntype, displayname, bulletinfamily, vulncount)"
                                " values (%s,%s,%s,%s,)", [k, vuln_name, vuln_bulletinfamily, vuln_count], "ON DUPLICATE KEY UPDATE vulncount = values(%s)", [vuln_count])

But I have an error of execute() takes from 2 to 3 positional arguments but 5 were given

That's not how this works...

willeM_ Van Onsem
– willeM_ Van Onsem

2017-10-23 17:00:18 +00:00
Commented Oct 23, 2017 at 17:00 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Oct 23, 2017 at 17:00

willeM_ Van Onsem · Accepted Answer · 2017-10-23 17:14:56Z

1

That's not how execute works: it takes two explicit parameters: the query, and the parameters.

You can not write a part of the query followed by parameters followed by another part of the query and so on.

So you simply write it as a single query, and join the two lists of parameters together:

cur.execute(
    "INSERT INTO vuln_sets (vulntype, displayname, bulletinfamily, vulncount)"
    " VALUES (%s,%s,%s,%s) ON DUPLICATE KEY"
    " UPDATE vulncount = %s",
    [k, vuln_name, vuln_bulletinfamily, vuln_count, vuln_count]
)

edited Oct 23, 2017 at 17:14

answered Oct 23, 2017 at 17:01

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

11 Comments

Luke Over a year ago

this just gives an error of:

1064, "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 ') ON DUPLICATE KEY UPDATE vulncount = VALUES (1765)' at line 1")

willeM_ Van Onsem Over a year ago

@Luke: What if you remove the comma (edited answer).

willeM_ Van Onsem Over a year ago

@Luke: did you actually tested your query before you posted. Beause ON DUPLCATE KEY UPDATE vulncount seems illegal syntax. Third attempt.

willeM_ Van Onsem Over a year ago

@Luke: I edited the answer by what I think is valid MySQL.

willeM_ Van Onsem Over a year ago

@Luke: well I think we make progress, since it is a different error. New attempt.

|

Collectives™ on Stack Overflow

Python MySQL INSERT ONDUPLICATE KEY

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related