Python "INSERT INTO" vs. "INSERT INTO...ON DUPLICATE KEY UPDATE"

Question

I am trying to use python to insert a record into a MySQL database and then update that record. To do this I have created 2 functions:

def insert_into_database():

query = "INSERT INTO pcf_dev_D.users(user_guid,username) VALUES (%s, %s) "
data = [('1234', 'user1234')]
parser = ConfigParser()
parser.read('db/db_config.ini')
db = {}
section = 'mysql'

if parser.has_section(section):
    items = parser.items(section)
    for item in items:
        db[item[0]] = item[1]
else:
    raise Exception('{0} not found in the {1} file'.format(section, filename))

try:
    conn = MySQLConnection(**db)
    cursor = conn.cursor()
    cursor.executemany(query, data)
    conn.commit()
except Error as e:
    print('Error:', e)
finally:
    # print("done...")
    cursor.close()
    conn.close()

This works fine and inserts 1234, user1234 into the db.

Now I want to update this particular user's username to '5678', so I have created another function:

def upsert_into_database():

query = "INSERT INTO pcf_dev_D.users(user_guid,username) " \
        "VALUES (%s, %s) ON DUPLICATE KEY UPDATE username='%s'"
data = [('1234', 'user1234', 'user5678')]
parser = ConfigParser()
parser.read('db/db_config.ini')
db = {}
section = 'mysql'

if parser.has_section(section):
    items = parser.items(section)
    for item in items:
        db[item[0]] = item[1]
else:
    raise Exception('{0} not found in the {1} file'.format(section, 'db/db_config.ini'))

try:
    conn = MySQLConnection(**db)
    cursor = conn.cursor()
    cursor.executemany(query, data)
    conn.commit()
except Error as e:
    print('Error:', e)
finally:
    # print("done...")
    cursor.close()
    conn.close()

Which produces the following error: Error: Not all parameters were used in the SQL statement

What's interesting is if I modify query and data to be:

query = "INSERT INTO pcf_dev_D.users(user_guid,username) " \
        "VALUES (%s, %s) ON DUPLICATE KEY UPDATE username='user5678'"
data = [('1234', 'user1234')]

Then python updates the record just fine...what am I missing?

Shadow · Accepted Answer · 2018-01-20 00:56:55Z

3

You included the 3rd parameter within single quotes in the update clause, therefore it is interpreted as part of a string, not as a placeholder for parameter. You must not enclose a parameter by quotes:

query = "INSERT INTO pcf_dev_D.users(user_guid,username) " \
        "VALUES (%s, %s) ON DUPLICATE KEY UPDATE username=%s"

UPDATE

If you want to use the on duplicate key update clause with a bulk insert (e.g. executemany()), then you should not provide any parameters in the update clause because you can only have one update clause in the bulk insert statement. Use the values() function instead:

query = "INSERT INTO pcf_dev_D.users(user_guid,username) " \
        "VALUES (%s, %s) ON DUPLICATE KEY UPDATE username=VALUES(username)"

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in the ON DUPLICATE KEY UPDATE clause or INSERT statements and returns NULL otherwise.

edited Jan 20, 2018 at 0:56

answered Jan 15, 2018 at 0:13

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

7 Comments

cs95 Over a year ago

This would mean data would also need to be modified, right?

Shadow Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ no because it has 3 parameters already (hence the error message)

Shadow Over a year ago

@nollie11 I have tried it (different table, obviously) and for me it works without the quotes.

nollie11 Over a year ago

Ugh, I believe you @Shadow - can you share the exact lines of query and data?

Shadow Over a year ago

@nollie11 The table name was simply test, otherwise everything else was the same. Also, see the following very similar SO question and its answer: stackoverflow.com/questions/25322880/… The code in the accepted answer also does not include the placeholders within quotes.

|

Collectives™ on Stack Overflow

Python "INSERT INTO" vs. "INSERT INTO...ON DUPLICATE KEY UPDATE"

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related