Using pymysql INSERT a date using STR_TO_DATE()

Question

This INSERT command in phpmyadmin works fine:

INSERT INTO babydb.sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES ('Big John', 'Kill Everything', 'product_class', 112.34, 678, 76166.52, STR_TO_DATE('05/08/2024', '%d/%m/%Y'))

Now I would like to do this in Python using pymysql. This works fine without the date:

import pymysql.cursors

def insert(cname, pname, pclass, priceKg, kilos, totalprice):
    # Connect to the database
    connection = pymysql.connect(host='localhost',
                                 user='pedro',
                                 password='letmein',
                                 database='babydb',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    with connection:
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value) VALUES (%s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, (cname, pname, pclass, priceKg, kilos, totalprice))

        # connection is not autocommit by default. So you must commit to save
        # your changes.
        connection.commit()

        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT * FROM sales_agro_products" 
            cursor.execute(sql)
            result = cursor.fetchall()
            print(result)

But this does not work:

sql = "INSERT INTO sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES (%s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%d/%m/%Y')"
        cursor.execute(sql, (cname, pname, pclass, priceKg, kilos, totalprice, date))

I get:

TypeError: not enough arguments for format string

I think these are causing the trouble: %d/%m/%Y

How should I correctly use STR_TO_DATE(%s, '%d/%m/%Y') within the pymysql INSERT command?

Van Hudson Galvoso · Accepted Answer · 2024-09-25 13:37:28Z

0

You just missed a closing parenthesis on the VALUES clause VALUES (%s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%d/%m/%Y'))"

answered Sep 25, 2024 at 13:37

Van Hudson Galvoso

967 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using pymysql INSERT a date using STR_TO_DATE()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related