CODEWITHSUNDEEP
pythonmysqlsqlsql-updatesql-insert
Ajility
Ajility

Reputation: 567

Python UpSert - not enough arguments

This has been a bit frustrating today. I'm sure its something simple that I'm overlooking. See code below (simplified for example sake):

def dbUpsert(sqlQuery, sqlValues):
    # Connect to the database
    connection = pymysql.connect(host='localhost',
                                user='db_user',
                                password='###',
                                db='garden',
                                charset='utf8mb4',
                                cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            # Create a new record
            cursor.execute(sqlQuery, sqlValues)
        # Commit the changes
        connection.commit()
    finally:
        connection.close()


sqlValues = real_plants, datetime.datetime.today().strftime('%Y-%m-%d'),  10
print (sqlValues)
##### Output: ('roses', '2020-02-19', 10)

sqlQuery = """
    INSERT INTO summ_plants_day 
        (
            real_plants, d_date, acc_plants
        )
    VALUES (%s, %s, %s)
    ON DUPLICATE KEY UPDATE
        real_plants = %s,
        d_date = %s,
        acc_plants = %s
    """
dbUpsert(sqlQuery, sqlValues)

In my database, I have 4 columns. One is an auto-increment primary key, real_plants and d_date together form an index, and acc_plants is the value I want to update / insert.

If an entry for roses exists in table summ_plants_day for the d_date I want to UPDATE, otherwise I want to INSERT.

Here is the error:

Traceback (most recent call last):
 in dbUpsert
    cursor.execute(sqlQuery, sqlValues)
 in execute
    query = self.mogrify(query, args)
 in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string

Also, I used %s based on W3Schools examples. I saw conflicting information online while Googling my issue, is this proper/modern format to protect against SQL injection?

Upvotes: 3

Views: 579

Answers (1)

GMB
GMB

Reputation: 222482

I would suggest using the VALUES() syntax in the ON DUPLICATE KEY UPDATE clause. This avoids the need to pass the same variable twice, so it should work just fine with your existing code:

INSERT INTO summ_plants_day (real_plants, d_date, acc_plants)
VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE
    real_plants = VALUES(real_plants),
    d_date = VALUES(d_date),
    acc_plants = VALUES(acc_plants)

Upvotes: 6

Related Questions