How to create a Date slot such that it should be greater than current date

@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 :slight_smile:

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 :slight_smile:

1 Like

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 :joy: 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()
1 Like

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!!

1 Like

Awesome :slight_smile:

1 Like