+1 Daumen
810 Aufrufe

Mit diesem Chatbot möchte ich irgendwann in der Konversation eine SQL-Abfrage durchführen, um eine Tabelle in einer MySQL-Datenbank zu popularisieren, aber sie gibt im Terminal zurück, dass ich einen Fehler in meiner SQL-Syntax habe, und zwar bei der Ausführung von cursor.execute bei meiner SQL-Abfrage:

print("before execute")
cursor = cnx.cursor()

add new booking
add_booking = ("INSERT INTO reservations "
              "(name_room, hour_start, hour_end) "
          "VALUES (%s, %s, %s)")
cursor.execute(add_booking)
print("after execute")

Ich verwende die Werte blue 5-1-2019 2pm 30 minutes und verstehe nicht, warum ich einene Namen (blue), ein Eintrittsdatum und ein Beendigungsdatum in eine Datenbank mit der folgenden Struktur einzugeben.

    mysql> DESCRIBE reservations;
    +------------+--------------+------+-----+---------+-------+
    | Field      | Type        | Null | Key | Default | Extra |
    +------------+--------------+------+-----+---------+-------+
    | name_room  | varchar(100) | NO  |    | NULL    |      |
    | hour_start | varchar(100) | NO  |    | NULL    |      |
    | hour_end  | varchar(100) | NO  |    | NULL    |      |
    +------------+--------------+------+-----+---------+-------+
    3 rows in set (0.06 sec)

Es hat immer funktioniert, wie Sie es aus alten Daten hier sehene können:

    mysql> SELECT * FROM reservations;
    +-----------+---------------------------+---------------------------+
    | name_room | hour_start                | hour_end                  |
    +-----------+---------------------------+---------------------------+
    | blue      | 2019-04-18T14:00:00+00:00 | 2019-04-18T14:30:00+00:00 |
    | blue      | 2019-04-20T09:00:00+00:00 | 2019-04-20T09:30:00+00:00 |
    | blue      | 2019-04-30T13:00:00+00:00 | 2019-04-30T13:30:00+00:00 |
    | blue      | 2019-05-01T14:00:00+00:00 | 2019-05-01T14:30:00+00:00 |
    +-----------+---------------------------+---------------------------+
    4 rows in set (0.00 sec)

Hier ist, was das Terminal zurückgibt:

    (staenv) C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack>python -m rasa_core_sdk.endpoint --actions actions
    2019-05-01 17:46:10 INFO    __main__  - Starting action endpoint server...
    2019-05-01 17:46:20 INFO    rasa_core_sdk.executor  - Registered function for 'action_joke'.
    2019-05-01 17:46:20 INFO    rasa_core_sdk.executor  - Registered function for 'action_room'.
    2019-05-01 17:46:20 INFO    __main__  - Action endpoint is up and running. on ('0.0.0.0', 5055)
    inside run
    before booking_answer
    blue 5-1-2019 2pm 30 minutes
    query_select_all:
    SELECT * FROM reservations
    date: 2019-05-01T14:00:00+00:00
    date: 2019-05-01T14:30:00+00:00
    cur_select_all:
    CMySQLCursorBuffered: SELECT * FROM reservations
    start_time:  2019-04-18T14:00:00+00:00
    end_time:  2019-04-18T14:30:00+00:00
    date: 2019-04-18T14:00:00+00:00
    date: 2019-04-18T14:30:00+00:00
    start_time:  2019-04-20T09:00:00+00:00
    end_time:  2019-04-20T09:30:00+00:00
    date: 2019-04-20T09:00:00+00:00
    date: 2019-04-20T09:30:00+00:00
    start_time:  2019-04-30T13:00:00+00:00
    end_time:  2019-04-30T13:30:00+00:00
    date: 2019-04-30T13:00:00+00:00
    date: 2019-04-30T13:30:00+00:00
    Hey, I just checked and the room is available :-)
    booking_answer : True
    before connexion
    before execute
    before execute
    [2019-05-01 18:10:07,717] ERROR in app: Exception on /webhook [POST]
    Traceback (most recent call last):
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\mysql\connector\connection_cext.py", line 395, in cmd_query
        raw_as_string=raw_as_string)
    _mysql_connector.MySQLInterfaceError: 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 '%s, %s, %s)' at line 1
   
    During handling of the above exception, another exception occurred:
   
    Traceback (most recent call last):
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\actions.py", line 72, in run
        cursor.execute(add_booking)
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
        raw_as_string=self._raw_as_string)
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\mysql\connector\connection_cext.py", line 398, in cmd_query
        sqlstate=exc.sqlstate)
    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 '%s, %s, %s)' at line 1
   
    During handling of the above exception, another exception occurred:
   
    Traceback (most recent call last):
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\flask\app.py", line 2292, in wsgi_app
        response = self.full_dispatch_request()
...     
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\actions.py", line 75, in run
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    NameError: name 'errorcode' is not defined
    2019-05-01 18:10:07 ERROR    flask.app  - Exception on /webhook [POST]
    Traceback (most recent call last):
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\mysql\connector\connection_cext.py", line 395, in cmd_query
        raw_as_string=raw_as_string)
    _mysql_connector.MySQLInterfaceError: 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 '%s, %s, %s)' at line 1
   
    During handling of the above exception, another exception occurred:
   
    Traceback (most recent call last):
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\actions.py", line 72, in run
        cursor.execute(add_booking)
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
        raw_as_string=self._raw_as_string)
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\mysql\connector\connection_cext.py", line 398, in cmd_query
        sqlstate=exc.sqlstate)
    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 '%s, %s, %s)' at line 1
   
    During handling of the above exception, another exception occurred:
   
    Traceback (most recent call last):
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\staenv\lib\site-packages\flask\app.py", line 2292, in wsgi_app
        response = self.full_dispatch_request()
...
      File "C:\Users\antoi\Documents\Programming\Nathalie\18_2_2019\starter-pack-rasa-stack\actions.py", line 75, in run
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    NameError: name 'errorcode' is not defined


Avatar von

Könntest du einmal die Query bei:

add_booking = ("INSERT INTO reservations "
                          "(name_room, hour_start, hour_end) "
                          "VALUES (%s, %s, %s)")
            cursor.execute(add_booking)
erzeugen mittels:

print cursor._last_executed

Dann wird die exakte MySQL-Query angezeigt und das Problem sollte erkennbar sein.

Ein anderes Problem?

Stell deine Frage

Ähnliche Fragen

Willkommen bei der Stacklounge! Stell deine Frage einfach und kostenlos

x
Made by a lovely community