For Netezza, how can only use 'TIMESTAMP' data type other than 'TIMESTAMP WITHOUT TIME ZONE' from sqlalchemy default?

Viewed 134

When creating Netezza table via to_sql(..., if exists = 'replace'..) with a DATETIME or TIMESTAMP date type, an error is raised. The error message poins to the field which is assigend by sqlalchemy to 'TIMESTAMP WITHOUT TIME ZONE', and says 'found "WITHOUT" (at char 255) expecting next item or end of list (27) (SQLExecDirectW)').

In fact initially I just declare DATETIME or TIMESTAMP to this field in the date type dict, but then sqlalchemy changes the originally declared data type to TIMESTAMP WITHOUT TIME ZONE, and gets Netezza server rejects it. I have tried 'import sqlalchemy and nzalchemy' (a netezza dialect) already, the issue persist.

import sqlalchemy
import nzalchemy                # for netezza data types
...
targetDATATYPES = {
        'LAB': VARCHAR(100),
        'feed_start_date': TIMESTAMP,      # or DATETIME (the same issue)
        'file_name':    VARCHAR(100),
        'curr_hash_sum': nzalchemy.NUMERIC(15, 2),
        'fact_table_name': NVARCHAR(100)
    }
...
queryResult.to_sql(targetTableName, targetDBConnection, targetSchemaName, if_exists = 'replace', index = False, dtype = targetDataTypes)

When python executes above to_sql(), it says:

(pyodbc.ProgrammingError) ('42000', '[42000] ERROR:  \'CREATE TABLE [targetTableName] ( lab NVARCHAR(100), feed_start_date TIMESTAMP WITHOUT TIME ZONE, file_name NVARCHAR(100), curr_hash_sum NUMERIC(15, 2), fact_table_name NVARCHAR(100) ) DISTRIBUTE ON RANDOM\'\n error
      ^ found "WITHOUT" (at char xxx) expecting next item or end of list (xx) (SQLExecDirectW)')
[SQL:
CREATE TABLE "xxx"."[targetTableName]" (
        lab NVARCHAR(100),
        feed_start_date TIMESTAMP WITHOUT TIME ZONE,
        file_name NVARCHAR(100),
        curr_hash_sum NUMERIC(15, 2),
        fact_table_name NVARCHAR(100)
) DISTRIBUTE ON RANDOM

When I remove this DATETIME or TIMESTAMP field, the code works well, and I can get a table as expected. Also when I just simply use the CREATE statement in Netezza tool, by changing 'TIMESTAMP WITHOUT TIME ZONE' to 'TIMESTAMP', it works, otherwise it will fails as well.

0 Answers
Related