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 can I access MySQL using Python?
- ¿Cómo conectar Python a MySQL usando ejemplos?
- How can I connect Python to a MySQL database?
- How do I access MySQL using Python?
- How can I convert data from a MySQL database to XML using Python?
- How do I set up a secure SSL connection between Python and MySQL?
- How do I download MySQL-Python 1.2.5 zip file?
- How can I connect to MySQL using Python?
- How do I use Python to connect to a MySQL database using XAMPP?
- How can I connect Python and MySQL?
See more codes...