Skip to main content
Question

DATE values insertion into Vertica table through python

  • June 9, 2020
  • 1 reply
  • 6 views
tnr

Hi All,
I am new to verticaDB.
I am trying to insert the values into vertica table through python .
INSERT INTO drill VALUES (:node, :as_of_date, :level_1, :level_2, :level_3, :level_4, :level_5, :level_6, :level_7, :level_8, :level_9, :is_leaf_node) {'node': 'Type##!', 'as_of_date': '2020-06-09', 'level_1': 'Type', 'level_2': '', 'level_3': '', 'level_4': '', 'level_5': '', 'level_6': '', 'level_7': '', 'level_8': '', 'level_9': '', 'is_leaf_node': False}

as_of_date column is DATE type in Vertica.

I am able to insert the above row manually into the table.
But the same row trying to insert form python script getting the below error:

raise errors.QueryError.from_error_response(self._message, query)
vertica_python.errors.InvalidDatetimeFormat: Severity: ERROR, Message: Invalid input syntax for date: "", Sqlstate: 22007, Routine: void DateTimeParseError(DTtoken, const char*, const char*), File: /data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_2_1-x_grader/build/vertica/OSS/DateTimeParsing/Datetime.cpp, Line: 438, Error Code: 3679, SQL: "INSERT INTO drill VALUES ('Type##!', '2020-06-09', 'Type', '', '', '', '', '', '', '', '', False)"

Can you please help me to find the root cause or how can insert the values through python.

thanks

    1 reply

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • June 9, 2020

    Hi, you haven't given much to go on. Is your table and Python script similar to below sample, in particular the format of the execute statement with curly braces, quotes, commas in the right places? The following works for me using latest vertica-python (0.10.4) and Vertica server 10.0:
    Create table DRILL as follows:

    CREATE TABLE DRILL (node VARCHAR, as_of_date DATE, level_1 VARCHAR, level_2 VARCHAR, level_3 VARCHAR, level_4 VARCHAR, level_5 VARCHAR, level_6 VARCHAR, level_7 VARCHAR, level_8 VARCHAR, level_9 VARCHAR, is_leaf_node BOOLEAN);

    Run Python script:

    import vertica_python
    connection_str = ('vertica://dbadmin:XXX@192.168.1.206:5433/ddd')
    print(vertica_python.parse_dsn(connection_str))
    conn = vertica_python.connect(dsn=connection_str)
    cur = conn.cursor()
    cur.execute("INSERT INTO drill VALUES (:node, :as_of_date, :level_1, :level_2, :level_3, :level_4, :level_5, :level_6, :level_7, :level_8, :level_9, :is_leaf_node)", {'node': 'Type##!', 'as_of_date': '2020-06-09', 'level_1': 'Type', 'level_2': '', 'level_3': '', 'level_4': '', 'level_5': '', 'level_6': '', 'level_7': '', 'level_8': '', 'level_9': '', 'is_leaf_node': False})

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement