Date Values format for MySql-CodePudding

my plan is to insert the date (day_of_test) into a MySql database, where the dataformat is referenced as a Date.

I have to keep the syntax in the format you see below. But unfortunately my attempts to store specifically the date as a string within the sytax VALUES(%s...) don't seem to work.

Does anyone know the easiest way to adjust my syntax to insert day_of_test successfully?

Thank you all very much

import mysql.connector

mydb = mysql.connector.connect(host="34.xxx.xxx.xxx", user="xxxx", password="xxxxx", database='btc-analysis-db')

c = mydb.cursor()
btc_est = 150.2
sap_close = 100.23
gold_close = 120.2
bonds_close = 210.12
btc_actual = 130.12
day_of_test = "2022-04-20"

c = mydb.cursor()

c.execute(
    "INSERT INTO `ML1`(`day_of_test`, `btc_est`, `sap_close`, `bonds_close`, `gold_close`, `btc_actual`) VALUES (%s, %d, %d, %d, %d, %d);" % (
        day_of_test, btc_est, sap_close, bonds_close, gold_close, btc_actual))

mydb.commit()

c.execute("select * from ML1")

result = c.fetchall()

CodePudding user response：

If you are using python string formating, then you are missing quotation marqs around the value for date, and your sintax should be:

c.execute(
    "INSERT INTO `ML1`(`day_of_test`, `btc_est`, `sap_close`, `bonds_close`,
    `gold_close`, `btc_actual`) VALUES ('%s', %d, %d, %d, %d, %d);" % (
        day_of_test, btc_est, sap_close, bonds_close, gold_close, btc_actual))

But you shouldn´t do it that way. you should use prepared statements (to avoid SQL injection an other problems), and your sintax should be like this:

c.execute(
    "INSERT INTO `ML1`(`day_of_test`, `btc_est`, `sap_close`, `bonds_close`, 
    `gold_close`, `btc_actual`) VALUES (%s, %s, %s, %s, %s, %s);", (
        day_of_test, btc_est, sap_close, bonds_close, gold_close, btc_actual))