insert datetime from csv to postgres error-CodePudding

I have a csv file:

with open(r'apps/patients/management/commands/events.csv') as csvfile:
        spamreader = csv.DictReader(csvfile, delimiter=',' ,quotechar=' ')
        for row in spamreader:
            print(row)

output:

{'PATIENT ID': '2', 'PATIENT NAME': 'John', 'EVENT TYPE': 'HR', 'EVENT VALUE': '118', 'EVENT UNIT': 'beats/minute', 'EVENT TIME': '2021-07-09T00:05:00Z'}
{'PATIENT ID': '2', 'PATIENT NAME': 'John', 'EVENT TYPE': 'RR', 'EVENT VALUE': '9', 'EVENT UNIT': 'breaths/minute', 'EVENT TIME': '2021-07-09T00:05:00Z'}

And then I tried to insert the file data into postgres database:

with open(r'apps/patients/management/commands/events.csv') as csvfile:
        spamreader = csv.DictReader(csvfile, delimiter=',' ,quotechar=' ')
        for row in spamreader:
            cur.execute(f"""INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES
                  ({row['PATIENT ID']},{row['EVENT TYPE']},{row['EVENT VALUE']},
                   {row['EVENT UNIT']},{row['EVENT TIME']})""")
conn.commit()

Error:

psycopg2.errors.SyntaxError: syntax error at or near "T02"
LINE 3:                    beats/minute,2021-07-07T02:27:00Z)

It works in psql client.

INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES ('1','Jane','HR','82','beats/minute','2021-07-07T02:27:00Z')

I thought it is the string character 'T02' issue,so I use:

row['EVENT TIME'].replace('T',' ')

Then the error became:

psycopg2.errors.SyntaxError: syntax error at or near "02"
LINE 3:                    beats/minute,2021-07-07 02:27:00Z)
                                                   ^

Then I thought I need convert it to datetime first:

{datetime.strptime(row['EVENT TIME'], '%Y-%m-%dT%H:%M:%SZ')}

Same error as the last one.

Then I did some research:

{row['EVENT TIME']::timestamp}

Error:

ValueError: Invalid format specifier

I have stuck here for 2 days,any friend can help?

CodePudding user response：

UPDATE: Comparing the working psql client input against the Line 3 Error, you are missing single quotes around the individual values in the cur.execute() statement.

Original Answer: (This focused on the .Replace('T') issue later in the question)

2021-07-07T02:27:00Z is in ISO 8601 UTC format. It includes a date, time, and a timezone offset. Apparently, your table's event_time column is timestamp format with date and time only.

You will need to convert your incoming date with time zone to your local timezone format to omit the timezone.

It looks like you were on the right track casting the data to a ::timestamp, but it won't discard the timezone information, it expects you to do a proper conversion.

I believe this StackOverflow answer explains the conversions well: PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone