@ChrisRahme , But it doesn’t allows me to save the date in database (datatype: date). How can I save the date then? If I don’t convert the date, then I get this error " can’t compare offset-naive and offset-aware datetimes"
If you’re using MySQL, I suggest using the datetime datatype for that.
Also, try to insert the value as "FROM_UNIXTIME("+str(value)+")"
. For example:
INSERT INTO mytable (time, ...) values (FROM_UNIXTIME(1632034800), ...)
thanks @ChrisRahme , this is working in database.
Could you please suggest the library for FROM_UNIXTIME in Python too?
I am getting name error as below:
NameError: name 'FROM_UNIXTIME' is not defined
FROM_UNIXTIME
is MySQL, not Python. It should be included in your query string.
@ChrisRahme, The input from user will get saved in slots and then inserted using database_connectivity.py file which contains sql query for insertion. I included FROM_UNIXTIME in my query string of .py file and got NameError. database_connectivity.py (757 Bytes)
Can you show me your query please?
By the way, this is not a Rasa problem anymore but MySQL, so try to ask on Stack Overflow, you may find people who can help you better than me
import mysql.connector
def DataInsert(patient_name,phone,email,time_slot,time):
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="deepali162",
database="rasa_db"
)
mycursor = mydb.cursor()
sql = 'INSERT INTO Patients (patientName, patientPhone, patientEmail, timeAppt, dateAppt) VALUES ("{0}","{1}","{2}","{3}","{4}");'.format(patient_name,phone,email,time_slot,time)
mycursor.execute(sql)
mydb.commit()
database_connectivity.py
from database_connectivity import DataInsert
class ActionDataSubmit(Action):
def name(self) -> Text:
return "action_data_submit"
def run(self, dispatcher: CollectingDispatcher,
tracker: Tracker,
domain: Dict[Text, Any]) -> List[Dict[Text, Any]]:
DataInsert(tracker.get_slot("pname"),tracker.get_slot("phone-number"),tracker.get_slot("email"),tracker.get_slot("timeslot"),tracker.get_slot("time"))
message = "Record inserted."
dispatcher.utter_message(message)
return[]
Sure, will ask my question on Stack Overflow. Thank you for your help so far
Where are you using FROM_UNIXTIME
?
You should do the following:
DataInsert(
tracker.get_slot("pname"),
tracker.get_slot("phone-number"),
tracker.get_slot("email"),
tracker.get_slot("timeslot"),
"FROM_UNIXTIME(" + tracker.get_slot("time") + ")"
)
Another problem after doing this could be that you have quotes around {4}
in your function. FROM_UNIXTIME(...)
should not be in quotes since it is a function. So I suggest this if it’s the case: Remove FROM_UNIXTIME
again from the DataInsert
call and do this instead in its definition:
sql = 'INSERT INTO Patients (patientName, patientPhone, patientEmail, timeAppt, dateAppt) VALUES ("{0}", "{1}", "{2}", "{3}", FROM_UNIXTIME({4}))'.format(patient_name,phone,email,time_slot,time)
By the way, something that I like to do when querying MySQL in Rasa is to print the query to make sure it’s all correct. Try it, it really helped me many times There’s always a missing quote or something in those things.
def DataInsert(...):
mydb = mysql.connector.connect(...)
mycursor = mydb.cursor()
sql = ...
print(sql) # Life savior here
mycursor.execute(sql)
mydb.commit()
Hey @ChrisRahme , thank you so much…it worked this time. I didn’t had to mention below, it worked with the sql query.
DataInsert(
tracker.get_slot("pname"),
tracker.get_slot("phone-number"),
tracker.get_slot("email"),
tracker.get_slot("timeslot"),
"FROM_UNIXTIME(" + tracker.get_slot("time") + ")"
)
thanks a ton!!
Awesome