My plan is to insert a date (day_of_test) into a MySql database where the data format is referenced as date.
I had to keep the syntax in the format you see below. But unfortunately my attempts to store the date specifically as a string in the syntax VALUES(%s...) doesn't seem to work.
Does anyone know the easiest way to adjust the syntax to successfully insert day_of_test?
Thank you all
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()
P粉9589860702024-02-27 11:12:02
If you are using python string format, then you are missing quotes around the date value, your syntax 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 that. You should use prepared statements (to avoid SQL injection and other problems), and your syntax should look 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))