python-mysqlHow do I use Python to perform an INSERT ON DUPLICATE KEY UPDATE query in MySQL?
The INSERT ON DUPLICATE KEY UPDATE
query in MySQL can be used to update or insert new data into a table. To do this in Python, the MySQL Connector/Python
library can be used.
First, the MySQL Connector/Python
library needs to be imported and a connection to the MySQL database needs to be established.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="user",
passwd="password",
database="mydatabase"
)
Then, a cursor needs to be created to execute the query.
mycursor = mydb.cursor()
Finally, the INSERT ON DUPLICATE KEY UPDATE
query can be written and executed.
sql = "INSERT INTO customers (name, address) VALUES (%s, %s) ON DUPLICATE KEY UPDATE address = %s"
val = ("John", "Highway 21", "Valley 345")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted/updated.")
Output example
1 record inserted/updated.
The code above consists of the following parts:
import mysql.connector
- imports theMySQL Connector/Python
library.mydb = mysql.connector.connect(...)
- establishes a connection to the MySQL database.mycursor = mydb.cursor()
- creates a cursor to execute the query.sql = "INSERT INTO customers (name, address) VALUES (%s, %s) ON DUPLICATE KEY UPDATE address = %s"
- defines theINSERT ON DUPLICATE KEY UPDATE
query.val = ("John", "Highway 21", "Valley 345")
- defines the values to be inserted/updated.mycursor.execute(sql, val)
- executes the query.mydb.commit()
- commits the changes to the database.
Helpful links
More of Python Mysql
- How do I use Python to show the MySQL processlist?
- How can I connect to MySQL using Python?
- ¿Cómo conectar Python a MySQL usando ejemplos?
- How can I convert data from a MySQL database to XML using Python?
- How can I avoid MySQL IntegrityError when using Python?
- How can I connect Python to a MySQL database?
- How do I use Python to query MySQL with multiple conditions?
- How do Python MySQL and SQLite compare in terms of performance and scalability?
- How do I use a Python variable in a MySQL query?
- How do I use Python to update multiple columns in a MySQL database?
See more codes...