CODEWITHSUNDEEP
pythonmysql
nollie11
nollie11

Reputation: 47

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

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?

Upvotes: 1

Views: 2965

Answers (1)

Shadow
Shadow

Reputation: 34294

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.

Upvotes: 3

Related Questions