28962


pythonのpsycopg2ライブラリを使用して、int型とtimestamp型のカラムにnull値を入れようとすると下記エラー  
が発生します。

【エラー内容】  
psycopg2.DataError: invalid input syntax for type timestamp: "None"  
LINE 1: ...'?$filter=id%20eq%20174556','uno','1','V12102629','None','Ou...

エラーが発生している変数には下記のようにNoneを代入しています。  
clearance_time_at = None

エラーが発生しないようにnullを格納する方法をご教示お願いします。

\-----エラーが発生しているコード------------

[code]

             if alarm.get('vpnGroupId'):
                vpn_group_id = alarm['vpnGroupId']
             else:
                alarm['vpnGroupId'] = None
                vpn_group_id = alarm['vpnGroupId']
    
             customer_id = alarm['customerId']
    
             if alarm.get('clearanceTime'):
                clearance_time_at = alarm['clearanceTime']
             else:
                clearance_time_at = None　　　#エラーが発生している変数
    
             state = alarm['state']
             managed_object = alarm['managedObject']
             probable_cause = alarm['probableCause']
             specific_problems = alarm['specificProblems']
             creation_time_at = alarm['creationTime']　　#以下変数省略・・・
    query_alarms = "INSERT INTO alarms(alarm_id,element,customer_system,planned_maintenance,customer_planned_maintenance,first_content,\
                                                latest_content,first_severity,latest_severity,identifier,href,service_name,process_type,\
                                                vpn_group_id,clearance_time_at,state,managed_object,probable_cause,specific_problems,creation_time_at,\
                                                last_modification_time_at,equipment_pattern,root_cause_pattern,user_cause_judge_reason,\
                                                root_cause_description_jp,root_cause_description_en,request_description_jp,request_description_en,\
                                                ticket_id,problem_alarm,sub_alarm,first_occurrence_at,last_occurrence_at,created_at,created_by,\
                                                last_update_at,last_update_by,data_virtualizer,data_virtualizer_pkey,is_active\
                                                ,alarm_description_jp,alarm_description_en,is_root_cause,item_code,object_code)\
                            VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
                                    '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')\
                            ON CONFLICT ON CONSTRAINT alarms_alarm_id_key \
                            DO UPDATE SET element='%s',customer_system='%s',planned_maintenance='%s',customer_planned_maintenance='%s',\
                            first_content='%s',latest_content='%s', first_severity='%s', latest_severity='%s', identifier='%s',\
                            href='%s', service_name='%s',process_type='%s', vpn_group_id='%s', customer_id='%s',\
                            clearance_time_at='%s', state='%s', managed_object='%s', probable_cause='%s',specific_problems='%s',\
                            creation_time_at='%s', last_modification_time_at='%s',equipment_pattern='%s', root_cause_pattern='%s', user_cause_judge_reason='%s',\
                            root_cause_description_jp='%s', root_cause_description_en='%s', request_description_jp='%s', request_description_en='%s',ticket_id='%s',\
                            problem_alarm='%s', sub_alarm='%s',first_occurrence_at='%s', last_occurrence_at='%s', last_update_at='%s',\
                            last_update_by='%s', data_virtualizer='%s',is_active='%s',alarm_description_jp='%s', alarm_description_en='%s',\
                            is_root_cause ='%s',item_code = '%s',object_code='%s'"
    
    
             cursor.execute(query_alarms % (alarm_id, element, customer_system, planned_maintenance, customer_planned_maintenance, first_content,
                                           latest_content, first_severity, latest_severity, identifier, href, service_name, process_type,
                                           vpn_group_id, clearance_time_at, state, managed_object, probable_cause, specific_problems, creation_time_at,
                                           last_modification_time_at, equipment_pattern, root_cause_pattern, user_cause_judge_reason, root_cause_description_jp,
                                           root_cause_description_en, request_description_jp, request_description_en, ticket_id, problem_alarm, sub_alarm,
                                           first_occurrence_at, last_occurrence_at, created_at, created_by, last_update_at, last_update_by, data_virtualizer,
                                           data_virtualizer_pkey, is_active, alarm_description_jp, alarm_description_en, is_root_cause, item_code, object_code,
                                           element, customer_system, planned_maintenance, customer_planned_maintenance,
                                           first_content, latest_content, first_severity, latest_severity, identifier,
                                           href, service_name, process_type, vpn_group_id, customer_id,
                                           clearance_time_at, state, managed_object, probable_cause, specific_problems,
                                           creation_time_at, last_modification_time_at, equipment_pattern, root_cause_pattern, user_cause_judge_reason,
                                           root_cause_description_jp, root_cause_description_en, request_description_jp, request_description_en, ticket_id,
                                           problem_alarm, sub_alarm, first_occurrence_at, last_occurrence_at, last_update_at,
                                           last_update_by, data_virtualizer, is_active, alarm_description_jp, alarm_description_en, is_root_cause, item_code, object_code))
    
[/code]


CC BY-SA 3.0
2016-09-17T04:32:08.407
28946
2016-09-18T05:04:58.273
2016-09-18T03:56:39.900
18820
18820
question
python postgresql
pythonからpostgres int型のカラムにnull値を格納したい


そちらのコードについて、たぶんこのような状況となりました

[code]

    clearance_time_at = None
    query_alarms = "INSERT INTO alarms (clearance_time_at) VALUES ('%s')"
    
    sql_to_execute = query_alarms % (clearance_time_at)
    # sql_to_execute ==> INSERT INTO alarms (clearance_time_at) VALUES ('None')
    # 結局 clearance_time_at の値は、null ではなく、文字列 'None' で指定されてしまいました
    
    cur.execute(sql_to_execute) # エラーは出てきます
    
[/code]

正確の使い方は

[code]

    query_alarms = "INSERT INTO alarms (clearance_time_at) VALUES (%s)"
    #                                                              ^^ '%s' ではなく
    #                            パラメータにクォーテーションマークを付ける必要がありません
    cur.execute(query_alarms, (clearance_time_at,))
    
[/code]

`execute` 呼び出し時に、`%` で SQL 文に各引数を埋め込んだらいけません。代わりに SQL 文と引数分けて `execute`
に渡すべきです。[こちら](http://initd.org/psycopg/docs/usage.html)の例をご覧ください

[code]

    cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abc'def"))
    #                                                         ^ SQL と引数タプルを別々で渡します
    
[/code]

下記は誤用で、SQL インジェクションされるかもしれないから大変危険です。

[code]

    >>> SQL = "INSERT INTO authors (name) VALUES ('%s');" # NEVER DO THIS
    >>> data = ("O'Reilly", )
    >>> cur.execute(SQL % data) # THIS WILL FAIL MISERABLY
    ProgrammingError: syntax error at or near "Reilly"
    LINE 1: INSERT INTO authors (name) VALUES ('O'Reilly')
    
[/code]


CC BY-SA 3.0
2016-09-18T05:04:58.273
28962
2016-09-18T05:04:58.273
18621
28946
answer