CODEWITHSUNDEEP
sqlmysql-python
MVA_16
MVA_16

Reputation: 1

Error in SQL syntax: How can I update a null record with a datetime value in MySQL Python

I want to set NULL Shipped Date values in a MYSQL table to today's current date and time. So for example, an Order with ID 11077 have a null shipped date in the table. I want to change this specific order's shipped date to today's date. The table name is orders and the columns names are OrderID and ShippedDate. OrderID data type is int and ShippedDate data type is datetime(yyyy-mm-dd tt:tt:tt). I appreciate any help! Thank you.

The error message I am getting is:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '14:38:12.234562 WHERE OrderID LIKE 11077' at line 1

mycursor.execute("SELECT ShippedDate FROM orders WHERE OrderID LIKE {}".format(orderID))
    data = mycursor.fetchone()
    for result in data:
        if result is None:
            mycursor.execute("UPDATE orders SET ShippedDate={} WHERE OrderID LIKE {}".format(datetime.now(), orderID))
            mydb.commit()

Upvotes: 0

Views: 185

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19205

There are many issues with your existing code. I suggest you take this approach instead:

mycursor.execute("UPDATE orders SET ShippedDate=CURDATE() WHERE ShippedDate is NULL")

I don't know what this is written in, but as I understand it the issues with your code are:

mycursor.execute("SELECT ShippedDate FROM orders WHERE OrderID LIKE {}".format(orderID))

  • You aren't searching for NULL dates here
  • What is orderID here? It's never defined
  • You're using LIKE on what is probably a number

if result is None:

  • You're only doing something if the query doesn't return anything

mycursor.execute("UPDATE orders SET ShippedDate={} WHERE OrderID LIKE {}".format(datetime.now(), orderID))

  • It seems like you're setting ShippedDate to a date representation of orderid, this really makes no sense to me.

Note that today's current date and time never make an appearance in your code anywhere

Regardless keep on trying and learning!

Upvotes: 1

Related Questions