upsert.py

import mysql.connector
from mysql.connector import pooling

import sys

# Create a connection pool
dbconfig = {
    "database": "test",
    "user": "root",
    "password": "",
    "host": "127.0.0.1",
    "port": "4000"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        pool_name="mypool",
        pool_size=10,
        pool_reset_session=True,
        **dbconfig
    )
    # database init
    connection = pool.get_connection()
    cursor = connection.cursor()
    cursor.execute("drop table if exists tb")
    cursor.execute("create table tb(a int primary key auto_increment, b int)")
    cursor.close()
    connection.close()
except mysql.connector.Error as err:
        sys.exit("Error: {}".format(err))

def insert_row(pool, a, b):
    try:
        connection = pool.get_connection()
        if connection.is_connected():
            cursor = connection.cursor()
            query = """
                INSERT INTO tb (a,b) 
                VALUES (%s, %s)
                ON DUPLICATE KEY UPDATE 
                    b = VALUES(b)
            """
            data = (a,b)

            cursor.execute(query, data)
            last_rowid = cursor.lastrowid
            print(f"Last affected row ID: {last_rowid}")

            cursor.close()
            connection.commit()
            connection.close()

    except mysql.connector.Error as err:
        sys.exit("Error: {}".format(err))

insert_row(pool,1,1)
insert_row(pool,2,2)
insert_row(pool,3,2)
insert_row(pool,2,2)